DSE ICT: Databases and SQL Queries, a Tutor Prep Guide

2026-11-04

Tutor lesson-prep guides · Secondary ICT

Students read a database diagram fine, then swap SELECT and WHERE when writing SQL, and forget GROUP BY in aggregate questions. This lesson fixes four positions: fields, rows, groups and order.

Objectives — Students can: (1) distinguish table, record, field and primary key, and name the joining field of two tables; (2) write SELECT with WHERE and separate which columns are shown from which rows are kept; (3) use COUNT, AVG and MAX with GROUP BY, and HAVING to filter groups; (4) write a class-average query on the 6-row sample in five minutes (answer 77.0).

01 Prerequisites (3-minute check)

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

Skill	Quick check	Pass mark
Records	Does one row describe an entity or an attribute?	An entity
Primary key	Can a student number be a primary key?	Yes, unique and not null
Comparison	Does WHERE mark >= 60 filter columns or rows?	Rows

02 Lesson flow (60 minutes)

Time	Stage	Tutor does	Student does
0-8 min	Three-question check	Records, keys, WHERE	Answer orally
8-20 min	Table structure	Draw two tables and mark the join field	Mark the primary key
20-34 min	SELECT and WHERE	Columns and rows taught separately	Rewrite three queries
34-46 min	Aggregates	COUNT, AVG, then GROUP BY and HAVING	Write one grouped query
46-56 min	Timed practice	Three queries from easy to hard	Work unaided
56-60 min	Exit check	Four positions, one question each	Answer orally

03 Teaching script (read it out)

Scene 1: A query has only four positions

Every school-level query has four slots: SELECT lists the columns to show, FROM names the table, WHERE keeps only matching rows, and ORDER BY sets the sequence. The classic error is putting a column name in WHERE and a condition in SELECT. The routine is to read the question twice: first for what to show, circling the column names, then for the condition, underlining it. With the two marks apart, the SQL almost writes itself.

Before writing, make students say what to show and what the condition is, in two separate sentences.

Scene 2: WHERE filters rows, and strings need quotes

WHERE compares values only: numbers directly, as in mark >= 60; text in single quotes, as in class = ‘4A’; dates as YYYY-MM-DD. Combine conditions with AND or OR, remembering that AND binds tighter than OR, so mixed conditions need brackets. Without them, 4A or 4B and passing can mean something entirely different from what the question asks. BETWEEN reads better than two comparisons for a range. Write one unbracketed OR query on the board and count the extra rows with the class.

After writing WHERE, have students read it back as only rows where … and … to confirm the meaning.

Scene 3: Aggregates: group first, compute, then filter

An aggregate works on one group at a time, so GROUP BY drives the query. On this sample table there are 6 rows, 4 of them pass, class 4A averages 77.0 and the top student is S03 with 91. For an average per class the query is SELECT class, AVG(mark) FROM … GROUP BY class. To keep only classes above 70, use HAVING rather than WHERE, because WHERE runs before grouping. That single sentence is usually the mark that separates the two grades.

Ask which runs first, WHERE or HAVING; a wrong answer means reteach the execution order.

04 Question types and worked steps

Type	Example	Steps	Answer
Single-table filter	List matching records	Circle columns, write WHERE, check quotes	Row count matches
Sort and limit	Top three marks	ORDER BY mark DESC with LIMIT 3	Direction correct
Aggregate	Averages and counts	Pick the function, add GROUP BY	One row per group
Join	Data from two tables	Find the shared field and use ON	Join fields match

Sanity check (teach this): Verify with the sample: 6 rows, 60 marks 60 and above give 4, so COUNT must return 4; class 4A with 78, 65 and 88 averages 77.0; the overall maximum is S03 with 91. The fastest check is to compare the returned row count with the count from the question: six rows when four are asked for means a missing WHERE, and one row when one per class is asked for means a missing GROUP BY.

05 Independent practice (3 questions)

Question	Answer	What to watch
List the ID and name of every passing student (at least 60)	4 rows	WHERE mark >= 60 written
Find the average mark of class 4A	77.0	AVG with WHERE class
Find the top mark in each class	4A: 88, 4B: 91	GROUP BY with MAX

06 Common mistakes and what to say

Mistake	Student writes	Say this
SELECT and WHERE swapped	SELECT class WHERE mark	SELECT picks columns, WHERE filters rows, and you reversed them.
Quotes missing	WHERE class = 4A	4A is text, so it needs single quotes or the database reads it as a field.
GROUP BY missing	SELECT class, AVG(mark) alone	One row per class needs grouping, otherwise you get the overall average.
HAVING written as WHERE	WHERE AVG(mark) > 70	WHERE runs before grouping, so aggregate filters need HAVING.

07 Homework (paper, section and question numbers)

ICT subject index — All chapters
Practice paper — SQL: questions 2 and 5

08 Exit check (last 5 minutes)

Question	Answer	Decision
Four positions	SELECT, FROM, WHERE, ORDER BY	Cannot name them: reteach act one
Row filter	Numbers bare, text quoted	Missing quotes: rewrite
Aggregate order	WHERE, then GROUP BY, then HAVING	Reversed: return to act three

09 Tutor FAQ

Must keywords be capitals?

SQL is case-insensitive, but capitals for keywords and lower case for fields is the convention that reads best.

Is ON required for a join?

Yes, ON states which fields are equal, and without it the result becomes a Cartesian product.

Does COUNT(*) differ from COUNT(field)?

Yes, COUNT(field) skips nulls while COUNT(*) counts every row.

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.