Practice: Order Details
Time to put the whole lesson together. The shop wants to see the details of its orders: what was in them, who placed them and how much they came to. Each query needs several tables, and when you write one, it helps to go step by step:
- Which tables have the data? List the columns you want to show, to filter by and to sort by, and find the tables they're in.
- How are the tables connected? Follow the foreign keys: each one is a JOIN with its ON. Run the query after each JOIN and look at the rows: does their number make sense?
- JOIN or LEFT JOIN? Do you need the rows that have no match, such as customers without an order? Then LEFT JOIN, and conditions on its right table go in ON.
- WHERE, GROUP BY, HAVING, ORDER BY work just as on one table, only with qualified names. Sort with a tie-breaker.
- Check the numbers. Are they too big? Then some rows may have multiplied: count with COUNT(DISTINCT …).
Order totals work like revenue: each item brought in i.quantity * i.price, and the total of an order is the sum of its items, so you group by the order.
Instructions
The receipts of Sophie Adams: show each item of each of her orders, with the order_id, the ordered_at of the order, the title of the game, the quantity, the price she paid for one copy, and the price of the whole item (quantity times price) as subtotal. Find her orders by her name, and sort the rows by order_id, then by title:
| order_id | ordered_at | title | quantity | price | subtotal |
|---|---|---|---|---|---|
| 23 | 2025-08-30 | Word Quest | 1 | 10 | 10 |
| 28 | 2025-11-28 | Shadow Ninja | 1 | 15 | 15 |
| 28 | 2025-11-28 | Zombie Town | 1 | 20 | 20 |
Below it, show the total of each paid order with the name of its customer: the order_id, the ordered_at, the customer's name, the number of copies in the order as copies, and the total as total. Sort the orders by total from the highest, then by order_id:
| order_id | ordered_at | name | copies | total |
|---|---|---|---|---|
| 11 | 2024-09-30 | Liam Turner | 2 | 85 |
| 14 | 2024-12-20 | Emma Clark | 2 | 75 |
| 5 | 2024-04-02 | Noah Wilson | 2 | 65 |
| 10 | 2024-08-23 | Leo Walker | 2 | 65 |
| 25 | 2025-10-10 | Jack Harris | 2 | 65 |
| … | … | … | … | … |
Finally, the shop wants to know where its money comes from. For each city, show the number of paid orders from its customers as order_count and their revenue as revenue. The customers without a city go under 'unknown': show the city with COALESCE as city. Careful: an order can have several items, so count the orders in a way that counts each of them once. Sort the cities by revenue from the highest, then by city:
| city | order_count | revenue |
|---|---|---|
| London | 11 | 500 |
| Manchester | 7 | 290 |
| Leeds | 4 | 210 |
| Bristol | 4 | 130 |
| unknown | 2 | 70 |
Start programming for free
12/12