Joining, filtering, and summarizing

SQL · Unit 7: Joining tables

Full plan
SQL
10 exercises

Since unit 5 there's been a question you couldn't ask. How much money has come in from each order? orders knows how many units there were, and the price of each one lives in products.

With the two tables joined, price and quantity end up in the same row, and a calculation can use both. These are the store's three biggest sales. The order for 10 Cables, the biggest in units, isn't among them. It's worth 52.5.

SELECT o.id, p.name,
  p.price * o.quantity AS total
FROM orders o
JOIN products p
  ON o.product_id = p.id
ORDER BY total DESC
LIMIT 3;

Returns

id	name	total
9	Laptop	899
6	Monitor	378
12	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. Complete the code
How much was each order from Valerie, customer 5? Fill in the calculation with the column that says how many units she ordered.
2. Predict the output
There are three orders of 2 units. One is for Keyboards, which cost 45.5, another for Monitors, at 189, and another for Headphones, at 59.9. What does this query return?
3. Complete the code
Which orders are going to Denver? Fill in the filter.
4. Put the lines in order
Which orders have there been for Accessories? Put in order the query that shows the product and the quantity of each one, from the biggest order to the smallest. Ties go by order number. One line doesn't belong.
5. Predict the output
That 2289.85 counts all twelve orders. Order 12 is dated January 20, and today is the 15th. It's a Tablet, at 320. What does this query return?
6. Find the bug
How much has the Monitor, which is product 3, sold? 3 units have been ordered, at 189 each, so the answer should be 567. The query runs without an error and says 99.5. Which line has the problem?
7. Put the lines in order
Last unit, whoever had ordered the most units was "customer 4." Put in order the query that gives one row for each customer who has ordered something, with their name and the units their orders add up to, from most to fewest. Ties go by name. It groups by the customer's id, which is what identifies them, and the name goes alongside so it can be shown. One line doesn't belong.
8. Predict the output
Audio has two products. There are 15 Headphones left, and they've been ordered twice, once for 2 units and once for 1. There are no Speakers left, and nobody has ordered one. What does this query return?
9. Find the case that fails
Someone had the sales query for the Laptop, which is product 5, and changed one column to see how many are left in the warehouse. Today there are 3, and a single order has been placed for it. The query should always give the Laptop's stock. Which of these changes makes it give a number that isn't the warehouse's?
10. Write the code
The ranking of products by money. Write a query with three columns, in this order: the product's name, the units of it that have been ordered, and the money they add up to, which is price times quantity. One row for each product someone has ordered, with the one that has brought in the most money 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 →