Teaching SQL joins to Class 12 with the attendance register and the fee register

Every year my Class 12 children memorise "INNER JOIN returns only matching rows" for the exam, write it perfectly, and understand nothing. This year I tried something different and it worked better than I expected, so I am sharing.

I borrowed two real registers from the office: the attendance register (roll number, name) and the fee register (roll number, term, amount). I asked the children: "Who has paid fees, with their names?" They had to look in one book, find the roll number, then look in the other book. That is a join. They did it with their fingers before they ever typed it.

Then we made the same thing in SQL (names changed, the children chose them):

CREATE TABLE students (roll_no INT PRIMARY KEY, name VARCHAR(40));
CREATE TABLE fees (roll_no INT, term INT, amount INT);

INSERT INTO students VALUES (1, 'Aleena'), (2, 'Farhan'), (3, 'Gokul'), (4, 'Diya');
INSERT INTO fees VALUES (1, 1, 2500), (2, 1, 2500), (2, 2, 2500), (5, 1, 2500);

-- who has paid, with names
SELECT s.name, f.term, f.amount
FROM students s
INNER JOIN fees f ON f.roll_no = s.roll_no;

-- every student, paid or not
SELECT s.name, f.term
FROM students s
LEFT JOIN fees f ON f.roll_no = s.roll_no;

-- who has not paid anything (the office's favourite query)
SELECT s.name
FROM students s
LEFT JOIN fees f ON f.roll_no = s.roll_no
WHERE f.roll_no IS NULL;

Three moments that taught them more than my notes ever did:

• "Miss, why is Farhan coming two times?" Because Farhan paid two terms. One student, many fee rows. One-to-many, explained by Farhan, who was very proud.
• Roll number 5 appears nowhere. There is a fee row for roll 5 but no student 5. A student who transferred out last year. The inner join hides him, the left join from students also hides him. So we talked about RIGHT JOIN, and about what happens when data in two registers does not agree. The office clerk was very interested in this part.
• Gokul and Diya with NULL. In the LEFT JOIN they come with an empty term. "NULL means the book had nothing, not zero." This one finally made sense to them.

And of course one boy asked if he can delete his row from the fees table. I told him only if he deletes himself from the students table also. Referential integrity, explained. Ayyo, the laughter.

Next week, GROUP BY with the same registers: total collected per term. If anyone has a good real-world example for HAVING that sixteen year olds will care about, kindly share.