Example (the practice database for the whole course)
This small shop uses four tables. Customer 4 was referred by customer 2 (used in lesson 2). Eva has no city and no orders.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT,
referred_by INTEGER REFERENCES customers(id)
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
category TEXT,
price REAL
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
order_date TEXT,
status TEXT
);
CREATE TABLE order_items (
order_id INTEGER REFERENCES orders(id),
product_id INTEGER REFERENCES products(id),
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES
(1,'Ana','Toronto',NULL), (2,'Ben','Hamilton',1), (3,'Chloe','Ottawa',1),
(4,'Dev','Toronto',2), (5,'Eva',NULL,NULL);
INSERT INTO products VALUES
(1,'Notebook','Paper',12.5), (2,'Pen set','Writing',8.0), (3,'Backpack','Bags',45.0),
(4,'Marker pack','Writing',15.0), (5,'Desk lamp','Home',30.0);
INSERT INTO orders VALUES
(1,1,'2026-09-12','paid'), (2,1,'2026-10-03','paid'), (3,2,'2026-10-05','paid'),
(4,3,'2026-10-07','refunded'), (5,1,'2026-10-09','paid'), (6,2,'2026-11-02','paid'),
(7,4,'2026-11-04','paid');
INSERT INTO order_items VALUES
(1,1,2),(1,2,1),(2,2,3),(3,3,1),(3,1,1),(4,1,2),(5,3,1),(5,4,2),(6,4,1),(7,3,2),(7,2,1);
Example (four tables, one question: what did each customer buy, and what was each line worth?)
SELECT o.id AS order_id, c.name AS customer, p.name AS product,
oi.quantity, p.price * oi.quantity AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
ORDER BY o.id, p.name;
This returns 11 rows. The first three are: (1, Ana, Notebook, 2, 25.0), (1, Ana, Pen set, 1, 8.0), (2, Ana, Pen set, 3, 24.0).
Revenue per customer from the same four tables:
SELECT c.name, SUM(p.price * oi.quantity) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.id, c.name
ORDER BY revenue DESC;
Result: Ana 132.0, Dev 98.0, Ben 72.5, Chloe 25.0. Eva is missing because a plain JOIN drops customers with no orders.
ClaudeStuck? Copy a ready-made prompt for Claude
Copy this, open Claude, paste it, and Claude will walk you through the lesson step by step.
For a grown-up: Claude is for adults (18+). Copy this, paste it into Claude, and use the answer to explain the lesson to your child.
I'm learning "SQL Joins & Analysis: joins, grouping and subqueries" on The Future Market. Lesson 1: "Before you start, and joining three or four tables".
What the lesson teaches:
- Before this course: SQL Basics (course 7.4). You should already know SELECT, WHERE, GROUP BY, JOIN and LEFT JOIN. This course builds on them.
- Real data is split into several tables so nothing is typed twice. To answer one question you often need three or four tables.
- Join one table at a time. Each JOIN ... ON links the new table to a table that is already in the query.
- A table alias is a short nickname: FROM orders o. Aliases keep long queries readable and are required when you use the same table twice (lesson 2).
- When two tables have a column with the same name (like id or name), write alias.column so SQL knows which one you mean.
- An order can hold many products and a product can be in many orders. This many-to-many link needs a small table in the middle, here called order_items. Its primary key is the pair (order_id, product_id).
The exercise I'm working on:
Write a query that shows each product name with the total number of units sold (SUM of quantity), from products and order_items. Check that the units add up to 17 (the sum of all quantities).
I'm stuck. Please:
1. Explain the main idea in plain, simple words with one everyday example.
2. Walk me through the exercise one small step at a time. Ask me what I've done so far before giving the next step.
3. Don't just give me the final answer. Help me do it myself, and check my work when I share it.
I'm a parent helping my child (about 8 years old) with a lesson called "Before you start, and joining three or four tables" from the course "SQL Joins & Analysis: joins, grouping and subqueries".
Here's what the lesson says:
Think of a school. One list says who the students are. Another list says which classes they took. A third list says what each class is about. To answer "which student took which subject", you hold the lists side by side and match the numbers. A join does that matching for you.
The activity:
Write a query that shows each product name with the total number of units sold (SUM of quantity), from products and order_items. Check that the units add up to 17 (the sum of all quantities).
Please explain this so an 8-year-old understands: short sentences, a fun everyday example, and no hard words.
Then give me 3 simple questions I can ask my child to check they understood, and a way to do the activity together, one tiny step at a time.