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.
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
Students should state the three conditions and circle the student number field with a reason.
Scene 2: A query has only four slots
Students should reproduce the four slots and explain what a missing quote produces.
Scene 3: Aggregates return one value
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 |
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)
- Secondary ICT chapter index — All chapters for this subject
- Prep guide master index — Matching chapters in other subjects
- Redo this chapter — Practice items 1, 2 and 3: filter, sort and index
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.



