3 · Summarize and connect · 25 MIN

Project · Join learners to enrollment counts

A join matches rows; one-to-many matches can multiply them.

The second table, enrollments, has one row per learner-course pairing. Ada appears twice because she studies two courses, Grace once, and Lin and Sam not at all. LEFT JOIN keeps every learner and fills the enrollment columns with NULL when no match exists. COUNT(e.learner_id) counts matching enrollments while COUNT(*) would count the placeholder row too. Grouping by the learner id and name returns the desired grain of one result row per learner. These are classroom tables; real systems should also enforce foreign keys.

Keep the source rows visible. Predict which rows and columns will remain after each operation.

Before you start

No installation. The tables below are loaded automatically for every run; edits do not touch the CodingNeed account database.

New words, explained

LEFTJOIN: Preserve rows from the left table even without a match.
INNERJOIN: Return only matching row pairs.
oneToMany: One learner can match multiple enrollment rows.

Follow the example step by step

Inspect the three enrollment source rows.
Match id 1 to two enrollments and predict two joined rows.
Find the NULL placeholders for learners without courses.
Group to one learner and count the nullable matching id.

Practice table: learners
id	name	city	points
1	Ada	Delhi	20
2	Lin	NULL (unknown)	0
3	Grace	Delhi	35
4	Sam	Pune	20

Practice table: enrollments
learner_id	course
1	React
1	SQL
3	SQL

You are ready to move on when: Return each learner name and number of enrolled courses, including zero. Group by l.id, l.name and order by l.id.

Read the example

SELECT l.name, e.course
FROM learners AS l
LEFT JOIN enrollments AS e ON e.learner_id = l.id
ORDER BY l.id, e.course;

Check the expected output

[["Ada","React"],["Ada","SQL"],["Lin",null],["Grace","SQL"],["Sam",null]]

Your challenge

Return each learner name and number of enrolled courses, including zero. Group by l.id, l.name and order by l.id.

Common trap

COUNT(*) after a LEFT JOIN incorrectly reports one course for a learner with no enrollment.

Open interactive exercise →See the syllabus

Further reading: SQLite SELECT reference