1. Money spent per customer with sub-queries
In this lesson, you will learn how to calculate how much money each customer spent on MovieNow rentals by using sub-queries.
2. Subsequent SELECT statements - actresses
Before we calculate how much money each customer spent, we will take a moment to introduce the concept of subsequent select statements with a simpler example.
We have this simple query that selects all actresses. We will call it query one.
Here we have the first two rows of the results table. Now we would like to analyze this table further.
3. Subsequent SELECT statements - actresses
For example, we could be interested in grouping this table by nationality and reporting the year of birth of the oldest and youngest actress in each country.
To do so, we put query one into the FROM clause from another query as you can see here. Note that we have to use parentheses around query one and give it a name. Here we use 'AS af'. Then we build another SQL query around this table 'af'.
Query one in the FROM clause is executed first then the rest of the query is executed for its result table af.
When we select the column nationality we use 'af-dot-nationality' to show from which table this column comes. This is not mandatory, but it helps us to remember where the columns come from in more complex queries.
We select the minimum year of birth and the maximum year of birth and group by 'af-dot-nationality'.
4. Result subsequent SELECT statement - actresses
Here are the first few rows of the resulting table from the query with subsequent select statement.
5. How much money did each customer spend?
When we want to find out how much money each customer spent on movie rentals,
the first step is to join the table renting and the table movies ON movie_id, because we need to add the rental fee from the table movies to the table renting.
Here we have only selected the columns customer_id and renting_price from the joined table. This is all we need for the next step.
6. How much money did each customer spend?
As in the previous example, we put the first query in parentheses into the FROM clause and give it the name rm. We group table rm by customer_id and select customer_id and the sum of renting_price.
7. How much money did each customer spend?
This gives us a table with the customer_id and the sum of all income from renting movies, so we know how much each customer spent on renting movies at MovieNow.
8. Let's practice!
This was the introduction to subsequent SELECT statements. Now it's time to practice.