Lesson 4 of 7
Lesson 4 — Joins, GROUP BY and Aggregates
Combine tables and turn thousands of rows into the one number a manager actually wants.
Learn it
A JOIN stitches rows from two tables together using a matching key.
INNER JOIN keeps only matches. LEFT JOIN keeps every row from the left table, filling missing right-hand values with NULL.
Aggregate functions summarise: COUNT, SUM, AVG, MIN, MAX.
GROUP BY makes one summary row per group; HAVING filters those summary rows.
Key terms
- INNER JOIN
- Returns only rows with a match in both tables.
- LEFT JOIN
- Keeps all left rows; unmatched right columns are NULL.
- Aggregate
- A function that reduces many rows to one value (COUNT, AVG…).
- GROUP BY
- Splits rows into groups, one output row per group.
- CTE
- A named temporary result set defined with WITH, for readable queries.
From rows to insight
- 1Join: student JOIN enrolment ON student.id = enrolment.student_id.
- 2Add the course: JOIN course ON course.id = enrolment.course_id.
- 3Group: GROUP BY course.title to get one row per course.
- 4Aggregate: COUNT(*) AS learners, AVG(grade) AS mean_grade.
- 5Filter groups: HAVING COUNT(*) >= 5 hides tiny classes.
Course popularity report
sqlSELECT c.title,
COUNT(*) AS learners,
ROUND(AVG(e.grade),1) AS mean_grade
FROM course c
JOIN enrolment e ON e.course_id = c.id
WHERE e.enrolled_on >= '2026-01-01'
GROUP BY c.title
HAVING COUNT(*) >= 5
ORDER BY learners DESC;
-- Students who have enrolled in nothing:
SELECT s.name
FROM student s
LEFT JOIN enrolment e ON e.student_id = s.id
WHERE e.student_id IS NULL;The first query summarises; the second uses a LEFT JOIN with IS NULL to find absences of data.
Try it
student has 4 rows. Only 3 of them appear in enrolment (one student appears twice). What does this return?
sqlSELECT COUNT(*)
FROM student s
LEFT JOIN enrolment e ON e.student_id = s.id;Challenge
Using `customer(id, name, country)` and `orders(id, customer_id, total, placed_at)`, write a query listing each country with its number of customers, total revenue and average order value — only for countries with more than 10 orders, richest first.
Pick whichever way suits you — every mode earns the same bonus XP.
Write at least 40 more characters to submit.
Mark your own work
Guided walkthrough — 0/3 clues revealed
- Clue 1 locked — reveal it only if you get stuck.
- Clue 2 locked — reveal it only if you get stuck.
- Clue 3 locked — reveal it only if you get stuck.
Each clue costs 5 XP (never below 23 XP). You'd earn 45 XP right now.
Extension: Rewrite it with a window function so each country also shows its rank by revenue.