AI Hints
Homeโ€บSQL Tutorialโ€บPractice Gym: Groups and Joins
SQL ยท Lesson 120 of 126

Gym: Four Tables

The big one.

โฑ 2 min readโ–ถ Video at 2:51:52๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

This is the biggest join in the gym. Germany has high returns. What do German customers actually buy? That needs all four tables.

Gym: Four Tables
From the video: Gym: 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