Finally! We’ve covered most of the basics!
This tutorial will use everything we learned so far to write some subqueries.

#Table of Contents

Query 1. SUBQUERY with where
SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99) AND active=1;

Query 2. SUBQUERY with from
SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment a WHERE a.staff_id=1) sub WHERE sub.amount<2.99 GROUP BY sub.customer_id HAVING SUM(sub.amount)>=5;

#Subquery using WHERE

Q1. Find customers that have rented movies priced $9.99 (using a subquery).

Subquery using WHERE clause: SELECT [column1] FROM [table 1] WHERE [name of column you want to join] IN (SELECT [column name2] FROM [table2] WHERE condition2) (AND condition 3~ can be omitted )

We're going to use the customer and payment tables for this example. Suppose you want to make a list of customers that rent movies that are $9.99. How can we make it?

Check out the customer and payment tables' data to get more familiar with the information first.

select * from customer;
select * from payment;

For this exercise, we need the column's customer ID, first name, last name, email, and activity status from the customer table. From the payment table, we only need customer ID, rental ID, and amount.

When writing a subquery, we will have an inside query and an outside query. For a subquery to work correctly, we need to make sure the inside query works independently. So let's write and test that first.

We want to check the customers with rentals in the higher price bracket, so let's go into the payment table. Show the customer ID column and filter the amount.

SELECT customer_id FROM payment WHERE amount=9.99;

This will give you the IDs of all customers that have rented $9.99 movies. We need to combine this new table with the customer table so that we can see the name of all the customers instead of just ID numbers. It’s time to write the outside query. Let’s start by stating what columns we want to see in the customer table:

SELECT customer_id, first_name, last_name, active, email FROM customer

Next, we need to plug in the inside query. We’re going to do this using  WHERE and IN . After  WHERE , we will specify which columns to combine. And using  IN, we will designate our inside query:

SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99);

🔑 Be careful not to write a semicolon (;) for the inside query. You only need 1 at the very end, OUTSIDE the parentheses.

⭐️ Notice how in this table, some customers are not active (the number in the active column is 0). To get more accurate information, you can plug inactive = 1 at the end. Make sure to use AND to connect them, like this:

SELECT customer_id, first_name, last_name, active, email FROM customer WHERE customer_id IN (SELECT customer_id FROM payment WHERE amount=9.99) AND active=1;

#Subquery using FROM

Q2. Find customers in store #1 that spent less than $2.99 on individual rentals, but have spent a total higher than $5.

This one might look complicated, but we're going to break it down for you. For this example, we're going to use the payment and store tables.

Before we start, let’s take a look at the payment table.

SELECT * FROM payment;

The payment table doesn’t give any details on the customers besides their ID numbers. And as you can see, it doesn’t say anything about the store either. But we need that information because we have two locations where we rent out our imaginary movies; store ID 1 and store ID 2.

So let’s check out the store table:

SELECT * FROM store;

Wow, look at that. The staff ID corresponds to the store ID! So the staff with the ID "1" are working in the first store. That's good to know!

Now since we want to get the SUM of the amount spent, we need to work in the payment table first. Let's write our inside query like this:

SELECT payment_id, customer_id, amount FROM payment;

This will filter which payment was made by which customer. Now using this information, add up the total amount spent by each customer by using a subquery. There are a few steps we need to follow here. First, we need to name the new subquery. We will use the common title sub and write it at the end of the inside query. Next, we need to use a GROUP BY query to organize the table or get an error. Your query will look like this:

SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment) sub GROUP BY sub.customer_id;

Great! This gives us the total spent by each customer. We’re going to expand on this query to filter the information further by two important factors: which customer spent less than $2.99 (WHERE amount<2.99) on individual rentals, but has spent a total greater than $5.00 (HAVING SUM(amount)>=5))!

Because we only want to look at customers from store1, we need to use store ID 1. That information is in the customer table… but we’re using the payment table to find the total (SUM). This causes a slight dilemma. Slight because.. we already found a solution! Earlier, when we checked the store table, we found that staff ID is relative to the store ID.. and sure enough, we have the staff ID column in the payment table! So we’re going to use that to filter our information (staff_id=1)

🔑 Since we’re using multiple tables, we need to use this format: table.column. We named our new subquery table sub when we reference a column from that new table, we would write it as  sub.column!

⭐️ Don’t forget; we have to use  GROUP BY to organize the new table!

Your query should look like this:

SELECT customer_id, SUM(amount) FROM (SELECT payment_id, customer_id, amount FROM payment a WHERE a.staff_id=1) sub WHERE sub.amount<2.99 GROUP BY sub.customer_id HAVING SUM(sub.amount)>=5;

PRACTICE TIME

📰 Use JOIN as well as a subquery to check the highest rental amount of the customer list (limit 10) with the customer and payment table.

Start by writing your inside query, where you define the highest rental amount:

SELECT customer_id, SUM(amount) AS total FROM payment GROUP BY customer_id ORDER BY total DESC LIMIT 10

And start your outside query by setting the columns you want to see: customer_id, first_name, last_name from a (customer table) and total from b (payment table subquery):

SELECT a.customer_id, a.first_name, a.last_name, b.total

Put them together with INNER JOIN and name the new subquery ‘b’:

SELECT a.customer_id, a.first_name, a.last_name, b.total FROM customer a INNER JOIN (SELECT customer_id, SUM(amount) as total FROM payment GROUP BY customer_id ORDER BY total desc LIMIT 10)b

Clarify which columns will be joined:

ON a.customer_id=b.customer_id

Put it all together:

SELECT a. customer_id, a.first_name, a.last_name, b.total FROM customer a INNER JOIN (SELECT customer_id, SUM(amount) as total FROM payment GROUP BY customer_id ORDER BY total desc LIMIT 10) b ON a.customer_id=b.customer_id;