/en-tutor-prep-s3-ict-databases-sql-queries/
/en-tutor-prep-s3-ict-databases-sql-queries/
General Inquiry: info@TutorZone.com.hk
Office Hours: Monday to Saturday - 9:00 AM to 6:00 PM
WhatsApp Monday to Sunday - 24 hours
Hotline: +852 6828 1809
Instant Support: +852 9061 3106

Junior ICT | Databases and SQL Queries Prep Guide (for tutors) | Keys, WHERE Conditions and Aggregates

Junior ICT | Databases and SQL Queries Prep Guide (for tutors) | Keys, WHERE Conditions and Aggregates

  • 2026-11-18

Students write SELECT star and call it finished, swap WHERE and ORDER BY, and take the wrong denominator for an average. This lesson fixes a query into four slots: which columns, which rows, which order, whether to aggregate. Each slot has one accepted form, and every result is checked against a row count or a total.

Objectives — Students can: (1) distinguish table, record, field, primary key and foreign key, and state that a primary key is unique, never empty and never changes; (2) write SELECT columns FROM table WHERE condition ORDER BY column, and say why the clause order cannot be swapped; (3) use COUNT, AVG and SUM, explaining that an aggregate returns a single value; (4) explain how an index cuts a million-row lookup from 500 milliseconds to 0.02 milliseconds, with comparisons falling from a million to about twenty.

01 Prerequisites (3-minute check)

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

Skill Quick check Pass mark
Tables, rows and columns What does one row of a table represent One record; a column is one field
Comparison operators How are greater-or-equal and not-equal written Use >= and <> or !=; text needs quotes
Meaning of an average Thirty-two scores total 2,192, what is the mean Total divided by count, 68.5

02 Lesson flow (60 minutes)

Time Stage Tutor does Student does
0-6 min Opening probe Show a 32-row score table and ask how to find the top three List the steps aloud at the board
6-18 min Tables and keys Records, fields, primary key, foreign key; a duplicate key is rejected Pick which field can be the key
18-34 min SELECT and WHERE Fill the four slots, model the condition and string quotes Write the query for class 3A
34-46 min Sorting and aggregates ORDER BY both directions with LIMIT, then COUNT and AVG Count passes and take the mean
46-54 min Indexes Contrast a million-row scan with a lookup of twenty comparisons Say when an index is a bad idea
54-60 min Exit check Collect two items, mark clause order and quotes Work independently

03 Teaching script (read it out)

Scene 1: A primary key is an identity card

Act one starts with why a name cannot identify a student: two students can share one, but a student number cannot repeat. That gives the three conditions of a primary key: unique, never empty, never changes. A foreign key follows: the student number in the results table must exist in the student table, which is what stops results being logged for a student who does not exist. The usual confusion is treating the first row as the key, so I ask who the key is once that row is deleted. Students then circle the key in a given table and justify it; if they pick the name, two namesakes settle the argument faster than ten definitions.

Students should state the three conditions and circle the student number field with a reason.

Scene 2: A query has only four slots

Act two fixes SQL into four slots: SELECT for columns, FROM for the table, WHERE for rows, ORDER BY for order. I leave four blanks on the board and fill them one at a time, stressing that the order is fixed and WHERE never precedes FROM. Model class 3A: WHERE class = 3A, and text always takes single quotes, otherwise the system reads it as a field name, which is the commonest error of all. Then ORDER BY score DESC with LIMIT 3 for the top three, plus >= and <> forms. Twenty-four of thirty-two students pass, which is 75 percent, and students must verify a query by its row count, so they learn that a result is checkable rather than a matter of feeling.

Students should reproduce the four slots and explain what a missing quote produces.

Scene 3: Aggregates return one value

Act three covers aggregates: COUNT counts rows, AVG averages, SUM totals, and all three return a single value rather than a table. Model SELECT COUNT(*) as 32 and SELECT AVG(score) as 68.5, then note that the mean is not the pass line, since a few high scores pull it up. Then indexes: a million rows without one means a million comparisons, while an index works like a phone book sorted by surname, needing about twenty comparisons because two to the twentieth power is roughly a million. A full scan measures 500 milliseconds against 0.02 milliseconds for the indexed lookup, which is 25,000 times faster. Close on the price: an index costs storage and must be updated on every write, so not every column gets one.

Students should separate COUNT from AVG and name the two costs of an index.

04 Question types and worked steps

Type Example Steps Answer
Basic filter Names and scores of everyone in class 3A SELECT name, score FROM students WHERE class = 3A, quoted text Returns twelve rows
Sorted top few The three highest scores in the form ORDER BY score DESC with LIMIT 3 Top 96, lowest 41, range 55
Aggregates Roll size, mean score and pass rate COUNT(*), AVG(score), passing rows over all rows 32 students, mean 68.5, pass rate 75 percent
Performance Look up one student number in a million rows Compare scan count with index comparisons A million against twenty, 25,000 times faster
Sanity check (teach this): Reasonable-check list: every column in SELECT must exist in the FROM table, or the table is wrong; text in WHERE needs quotes while numbers do not; a COUNT answer is always a whole number, so a decimal means AVG was used; a mean must fall between the lowest and highest score, otherwise the denominator is wrong; a pass rate lies between zero and one hundred, and a value above one hundred means the numerator is wrong; ORDER BY needs ASC or DESC stated, because the default ascending order turns the top three into the bottom three; an index speeds reads only, since writes still pay the maintenance cost.

05 Independent practice (3 questions)

Question Answer What to watch
Write a filter Students scoring below 50 Check WHERE score < 50 with no quotes on the number
Write a sort Class ascending then score descending Check both fields appear, in that order
Explain an index Why not index every column Check that storage and write cost are both named

06 Common mistakes and what to say

Mistake Student writes Say this
SELECT star as the answer Only SELECT * FROM students That returns every column and answers nothing; add WHERE and ORDER BY.
Wrong clause order Putting WHERE before FROM The order is fixed: SELECT, FROM, WHERE, ORDER BY.
Missing quotes Writing WHERE class = 3A The system reads 3A as a field; text takes single quotes, numbers do not.
COUNT as an average Using COUNT(score) for the mean COUNT counts rows with a score; AVG computes the mean.

07 Homework (paper, section and question numbers)

08 Exit check (last 5 minutes)

Question Answer Decision
Four slots SELECT, FROM, WHERE, ORDER BY A wrong order means rewriting two queries
Primary key Unique, never empty, never changes Answering the first row means act one again
Aggregates COUNT counts rows, AVG averages Confusing them means one more sum

09 Tutor FAQ

What if students have never used a real database

A spreadsheet works as an analogy: one sheet is a table, filtering is WHERE, sorting is ORDER BY, and nothing needs installing.

Must SQL keywords be upper case

Case does not affect execution, but upper case reads better; field and table names must match exactly or the query fails.

Are more indexes always better

No. An index costs storage and is updated on every insert or update, so heavily written columns get slower.

Find a tutor — freeTutor 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.

Join as a tutor or coach — academic, music, sports, arts, STEM. Free to register. See how it works →