NewThe ManyPI MCP Server is now live
ManyPI
ManyPI
LoginSign up

SQL joins lesson with the shop database

A 90-minute lesson that moves from one table to four, with every query run in the SQL sandbox and its tasks checked automatically.

Lesson plan1 h 30 minBachelor +2CC BY 4.0
Open the tool

What you will learn

  • Explain primary and foreign keys
  • Write INNER and LEFT joins
  • Combine joins with GROUP BY to answer business questions

Before you start: SELECT, WHERE and ORDER BY on a single table.

Everyone opens the SQL sandbox with the office supplies shop dataset.

Running the lesson

90 min
  1. 10 min

    Open the Tables panel. Which column of orders points at customers? Introduce primary and foreign keys.

  2. 15 min

    Shop task 3, "Orders with names": the first join, written together.

  3. 15 min

    LEFT JOIN: who has never ordered? Shop task 5 in pairs.

  4. 20 min

    Shop tasks 4 and 6: joins with GROUP BY. Learners check their own answers.

  5. 20 min

    The "Joining tables in SQL" exercise, with a class link if you want the results.

  6. 10 min

    Wrap up: what happens without ON? Run it and count the rows.

A query to show at the end

Every customer with their number of orders
SELECT customers.name, COUNT(orders.id) AS orders
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.name
ORDER BY orders DESC;

Hannah Wolf shows 0: COUNT(orders.id) counts only rows where an order exists, while COUNT(*) would count her joined row as 1.

Reuse this material

CC BY 4.0

You may copy, adapt and share it, also commercially, as long as you credit ManyPI and link back.

"SQL joins lesson with the shop database" by ManyPI (https://manypi.com/edu/sql-joins-lesson), licensed under CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/)

Related