This module covers how to get the right records out of a database table and present them in a report: searching with several criteria, sorting, adding a calculated field, grouping and checking. It follows database structure and validation, where the table and its fields are set up.
The skills are practical. Check the current Cambridge ICT 0417 syllabus for the exact wording, and use the workflow of the software your centre teaches. The ideas stay the same across spreadsheet and database tools.
What do you need before starting?
You should know what a record, a field and a data type are, and how to open a table. Comparison symbols such as >, >=, < and <> appear throughout, so make sure you can read them aloud.
The data used throughout
Every lesson uses this invented table for a fictional school club (Harbour Hill Clubs). The people and figures are made up.
| ID | Name | Year | Club | Fee (RM) | Sessions (of 12) |
|---|---|---|---|---|---|
| 1 | Aiman | 10 | Chess | 30 | 8 |
| 2 | Bella | 11 | Drama | 45 | 12 |
| 3 | Chen | 10 | Drama | 45 | 9 |
| 4 | Dewi | 9 | Chess | 30 | 11 |
| 5 | Evan | 11 | Robotics | 60 | 7 |
| 6 | Farah | 10 | Robotics | 60 | 12 |
| 7 | Gopal | 9 | Drama | 45 | 6 |
| 8 | Hui | 11 | Chess | 30 | 10 |
| 9 | Imran | 10 | Chess | 30 | 12 |
| 10 | Jia | 9 | Robotics | 60 | 5 |
An orienting example
The task: list the Chess members in Year 10 or above, highest attendance first.
The criteria are Club = “Chess” AND Year >= 10, then sort Sessions from high to low. Reading the table by hand, the Chess members are Aiman, Dewi, Hui and Imran.
Dewi is in Year 9, so she drops out. The report should show Imran (12), Hui (10), Aiman (8).
If your report shows four rows, or shows Aiman first, something in the criteria or the sort is off. That is the habit this module builds: work out the expected answer by hand, then compare.
What order should you study the lessons in?
- Apply multiple query criteria correctly: AND, OR and brackets decide which records appear, so start here.
- Sort records with a secondary key: once the right records appear, put them in the order the task asks for.
- Create a calculated field where supported: add a value worked out from other fields, such as attendance as a percentage.
- Group a report without hiding required values: subtotals and counts that keep every required detail on view.
- Compare displayed results with the source dataset: a method for proving a result is right before you submit it.
Which traps catch most students?
- Mixing AND with OR without brackets, so the query answers a different question.
- Using
>when the task means “at least”. - Sorting by the second key only.
- Showing a summary and losing the detail rows the task asked for.
- Never checking the record count.
How should you use the practice set?
Try the database queries and reports practice set after the lessons. Work out each answer from the table above before you open it. The read-only SQL practice lab and the ICT practical task and evidence checker help you test your reasoning and your evidence.
A teacher can also help you build a personal checking routine. See our online one-to-one ICT tuition for how that works.