SQL ยท Lesson 120 of 126
Gym: Four Tables
The big one.
This is the biggest join in the gym. Germany has high returns. What do German customers actually buy? That needs all four tables.

- German customers
- What do they buy?
- All four tables
What Germany reads
Which three genres sell the most copies to customers in Germany? Pause, and take your time. Here's a solution. Customers, orders, order items, books. Where the country is Germany, group by genre, and sum the copies. Mystery is the favourite, with sixty eight copies.
โ๏ธ Exercise: What Germany reads
Top 3 genres by copies sold to customers in Germany
Show hint
customers โ orders โ order_items โ books, WHERE country = 'Germany'
Show solution
solution.sqlsql
SELECT b.genre, SUM(oi.quantity) AS copies
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE c.country = 'Germany'
GROUP BY b.genre
ORDER BY copies DESC
LIMIT 3;Output
โโโโโโโโโโโโโโฌโโโโโโโโโ โ genre โ copies โ โโโโโโโโโโโโโโผโโโโโโโโโค โ Mystery โ 68 โ โ Thriller โ 42 โ โ Technology โ 32 โ โโโโโโโโโโโโโโดโโโโโโโโโ
Summary
Four tables, one clear answer. You're strong at joins now. Last gym, window functions and CTEs.
Key takeaways- Four-table join
- WHERE then GROUP BY
- SUM the copies
Next up: Practice Gym: Windows and CTEs โ the power tools