DSE ICT | Databases and SQL Tutor Prep Guide | From a 300-row join to a 12,000-row cross product

2026-11-03

Tutor lesson-prep guides · Secondary ICT

Students can recite one SELECT statement, but ask how many rows a join returns and they start guessing. This lesson anchors every idea to row counts: 300 rows against 40 rows gives 300 when matched and 12,000 when nothing is matched.

Objectives — Students can: (1) explain the division of labour between a primary key and a foreign key, and why a foreign key may repeat; (2) write four basic queries with SELECT, WHERE and ORDER BY; (3) use row counts to tell a join from a cross product (300 rows against 12,000); (4) explain why an index replaces a scan of 1,000,000 rows with about 20 comparisons.

01 Prerequisites (3-minute check)

If any of these fails, fix it before the lesson starts.

Skill	Quick check	Pass mark
Sets	Two tables of 3 rows each, how many pairings?	9 rows
Averages	What is 300 divided by 40?	7.5
Units	How many megabytes is 120,000,000 bytes?	120 MB in decimal units
Keywords	What follows SELECT?	The columns to return, or an asterisk

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0-10 min	Tables and keys	Student table, enrolment table, keys	Say which column may repeat
10-22 min	SELECT and WHERE	Unpack four queries	Write the conditions yourself
22-34 min	Join row counts	300 rows against 12,000	Compute both answers
34-44 min	Indexes	1,000,000 row scan against 20 comparisons	State what an index costs
44-52 min	Normalisation	Split one wide table into two	Find the repeated column
52-60 min	Exit check	Three written questions marked live	Work independently

03 Teaching script (read it out)

Scene 1: Keys identify people, foreign keys identify relationships

The student table has 40 rows and the student number is the primary key, so it never repeats. The enrolment table has 300 rows and the student number there is a foreign key, so the same number appears many times because one student takes many subjects. That is 7.5 enrolment records per student on average. Make students say it: the primary key identifies a person, the foreign key identifies a relationship, so repetition is the point.

Ask why a foreign key must be allowed to repeat.

Scene 2: A join matches, it does not multiply

Writing FROM students, enrolment with no condition is a cross product: 40 times 300 gives 12,000 rows, most of them meaningless combinations. Writing a join with a matching condition keeps only the pairs and returns 300 rows. The test is simple: if the answer has more rows than the larger table, the join condition is missing.

Ask which version returns 12,000 rows and why.

Scene 3: An index trades space for comparisons

A table of 1,000,000 rows without an index is scanned row by row, up to 1,000,000 comparisons; with an index it behaves like a dictionary and needs about 20 comparisons, roughly 50,000 times fewer. The costs are extra disk space and slower writes, because every insert updates the index. A full answer names speed, space and write cost; naming only speed reads as memorised.

Ask for the benefits and costs of an index.

04 Question types and worked steps

Type	Example	Steps	Answer
Write a query	List every name in class 4A	Choose columns, choose the table, add the condition, sort	SELECT name FROM students WHERE class=’4A’ ORDER BY name
Predict row counts	A join versus an unconditional FROM	Check the join condition, compare with the larger table, decide cross product	300 rows matched, 12,000 unconditional
Spot the error	A query missing GROUP BY	Check for aggregate functions, check the plain columns, add GROUP BY	COUNT with class needs GROUP BY class
Normalise	One table holding students and subjects	Find the repetition, split the table, link back with a key	A student table plus an enrolment table, the subject stored once

Sanity check (teach this): A check on direction: a join can never return more rows than the two table sizes multiplied, and when every row matches it usually equals the larger side, which is 300 here; an answer of 12,000 means a cross product. The 50,000-fold index gain is idealised, so answers should say comparisons fall sharply rather than quoting the exact figure. 1,000,000 rows at 120 bytes gives 120 MB, a realistic size for a school exercise.

05 Independent practice (3 questions)

Question	Answer	What to watch
Two students take 3 subjects each, how many enrolment rows?	6 rows	Did you confuse students with enrolments
40 students, 300 enrolments, average per student?	7.5	Did you divide 300 by 40
Unconditional FROM on both tables, how many rows?	12,000 rows	Did you assume 300
Comparisons for an index over a million rows?	About 20	Did you assume 100

06 Common mistakes and what to say

Mistake	Student writes	Say this
Join without a condition	SELECT * FROM students, enrolment;	Those tables are unrelated, so the database pairs everything: you have asked for 12,000 rows of rubbish.
Thinking foreign keys cannot repeat	A student number appears once in the enrolment table	Repetition is what shows a student took several subjects; only the primary key must be unique.
COUNT without GROUP BY	SELECT class, COUNT(*) FROM students;	You want one number per class, so group by class, otherwise you get a single total.
Believing every index helps	Put an index on every column	Indexes take space and slow writes, so index the columns you search on.

07 Homework (paper, section and question numbers)

ICT chapter index — Revise programming with arrays and functions, then do the database questions
Programming: arrays and functions — Keep an array index separate from a database index
Data representation and binary encoding — Revise byte and megabyte conversion for the storage questions

08 Exit check (last 5 minutes)

Question	Answer	Decision
Biggest difference between primary and foreign keys	Primary must be unique, foreign may repeat	Error: said both are unique
Rows returned by a join of 40 students and 300 enrolments	300 rows	Error: answered 12,000
Comparisons for an index over a million rows	About 20	Error: answered 100 or 1,000
Cost of an index	Disk space and slower writes	Error: listed benefits only

09 Tutor FAQ

Must every SQL statement be memorised?

School and DSE papers need SELECT, WHERE, ORDER BY, GROUP BY and JOIN; the rest must only be readable.

Is higher normal form always better?

It depends on the queries, because over-splitting forces joins and slows reads, so answers must name the trade-off.

Is Access syntax the same as SQL?

The concepts match but the interface differs; write standard SQL in exams and use query design in Access.

Find a tutor — free Tutor Registration →

A TutorZone lesson-prep guide. Written by the TutorZone team; questions and lesson flow are original and are not taken from any school or publisher material.