A query inside another

SQL · Unit 9: Subqueries

Full plan
SQL
10 exercises

In unit 5 you wanted to know which products cost more than the average. WHERE price > AVG(price) didn't run, and the fix was typing in 201.57 by hand. That number is a snapshot. As soon as one price changes, the average will be a different number and the query will keep comparing against the old one.

You already know how to write the query that calculates the average. It goes in parentheses, in the spot where the number was. That's a subquery, a query inside another.

SELECT name, price
FROM products
WHERE price > (
  SELECT AVG(price)
  FROM products
);

Returns

name	price
Laptop	899
Tablet	320

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. Multiple choice
The Laptop drops from 899 to 499, and the store's average goes with it, from 201.57 to 151.57. The Monitor costs 189. From that day on, what does each version return, the one with 201.57 typed in by hand and the one with the subquery?
2. Put the lines in order
Which orders are bigger than an average order? Put in order the query that gives their number and their quantity, from biggest to smallest. Two lines don't belong.
3. Predict the output
What costs more than an average Audio product? Audio has two products, the Headphones at 59.9 and the Speaker at 74, which average 66.95. The Monitor costs 189, the Tablet 320, and the Laptop 899. Nothing else reaches 50. What does this query return?
4. Put the lines in order
Which Denver customers are older than the average customer in the whole store? Put in order the query that gives their name and their age, by name. Two lines don't belong.
5. Predict the output
What is the warehouse lowest on? The Cable and the Speaker have a stock of 0. Next comes the Laptop, with 3. What does this query return?
6. Find the bug
Someone is looking for something that costs less than any device. The devices are the Laptop, at 899, and the Tablet, at 320. The query runs, and the first thing it offers is the Tablet. Which line has the problem?
7. Multiple choice
Which of these subqueries can never return more than one row, no matter what products go into the table?
8. Find the case that fails
The sign on the display case announces the most expensive thing in the store, which is the Laptop, at 899. If several products share the highest price, all of them have to be on it. Someone wrote it with ORDER BY and LIMIT 1. Which of these changes makes it leave a product off the sign?
9. Complete the code
A customer is looking at the Monitor, which is product 3, and asks what's priced above it. Fill in the blanks so that the subquery returns a single value, the Monitor's price.
10. Write the code
The two products the lesson's first query returned, the Laptop and the Tablet, are the two devices. They cost so much that they pull up the whole store's average, and nothing else reaches it. The owner wants the same question without them. Write a query with two columns, in this order: the name and the price of the products that aren't in the Devices category and cost more than the average of the ones that aren't in Devices. Most expensive first.

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 →