Subqueries per row

SQL · Unit 9: Subqueries

Full plan
SQL
9 exercises

Is a Keyboard at 45.5 expensive? In the store as a whole it's on the cheap side, because the average is over 200. Among the accessories it's the most expensive. To say whether something is expensive, you have to compare it with its own kind.

A subquery in the SELECT puts each product's section average next to it. It names p.category, which belongs to the outer row, so it's worked out once for each row, like the ones in EXISTS. The same table is inside and outside, and x is the inner copy. This subquery fills one cell, and that's why it has to give a single value.

SELECT p.name, p.price,
  (
    SELECT AVG(x.price)
    FROM products x
    WHERE x.category = p.category
  ) AS section_avg
FROM products p
WHERE p.price < 100;

Returns

name	price	section_avg
Keyboard	45.5	23.55
Mouse	19.9	23.55
Cable	5.25	23.55
Headphones	59.9	66.95
Speaker	74	66.95

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Displays only has the Monitor, at 189. Devices has the Laptop, at 899, and the Tablet, at 320. They're the three products over 100. What does this query return?
2. Complete the code
A per-row subquery can go get a value from another table. Each of Luke's orders is missing the product's name next to it. Luke is customer 2, his orders are 3, 6, and 12, and they're for products 2, 3, and 6. Fill in the blank with the column from the outer row that says which product to look for.
3. Predict the output
How many times has each Audio product been ordered? There are two orders for the Headphones. For the Speaker, none. What does this query return?
4. Find the bug
This query should list the eight customers with how many orders each has placed. Ann and Luke have 3, and Hannah and Brian have none. It runs and gives everyone a 3. Which line has the problem?
5. Put the lines in order
The Denver shop wants to know when each of its three customers last bought something. Put in order the query that gives the name and that date, which stays empty for anyone who hasn't bought. Two lines don't belong.
6. Put the lines in order
Which orders were big for the person who placed them? An order of 2 units is small for Luke and big for Ann. Put in order the query that gives the orders with more units than their own customer's average, by order number, highest first. Two lines don't belong.
7. Multiple choice
products has 8 rows. In the query that compares each product with the maximum of its section, how many times does the subquery have to be worked out, as written?
8. Complete the code
Each customer's first order comes with a welcome card. Which orders were those? Fill in the blanks so that each order is compared with the earliest date of its own customer.
9. Write the code
The question the lesson opened with, for the whole store. Write a query with three columns, in this order: the name, the category, and the price of the products that cost more than the average of their own category. In alphabetical order by name. Outside, products has the alias p, and inside, x.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →