S2 ICT database tables and queries practice 3: relationships, indexes and condition logic
- 2026-11-20
S2 ICT database tables and queries practice 3: relationships, indexes and condition logic
Ten items starting from a two-table join, covering duplicate rows when one book has many loans, the meaning of a foreign key and the available share, then indexes and removing repeated storage, closing on the difference between OR and AND.
S2 ICT database practice 3: ten challenge items Four loan rows against five books joined on BookID multiply the rows; of fifteen copies four are out, leaving eleven or about seventy-three per cent. A blank year drops the count from five to four, and five Chan books out of eight reach sixty-two point five per cent. Each item carries a note.
01 Paper details (check answers afterwards)
| Item | Detail |
|---|---|
| Questions | 10 |
| Suggested time | 25 |
| Difficulty mix | Foundation 3 · Intermediate 3 · Challenge 3 · Error redo 1 |
| Chapter | Chapter: S2 ICT: Database tables and queries |
| Level | Challenge |
02 Questions (10 items)
- FoundationThe Loans table holds four loan records and the Books table five books, linked on BookID. What must a query avoid?
- FoundationWhat kind of key is BookID inside the Loans table?
- FoundationOf fifteen copies in stock, four are on loan. What share is available?
- IntermediateThe condition is Author = Chan AND Year >= 2019, OR Copies = 1. How many records match?
- IntermediateDoes filtering then sorting give the same rows as sorting then filtering?
- IntermediateAll five Year values are filled, so COUNT and COUNT star both give five. If one Year is left blank, what does each return?
- ChallengeWhy does an index on BookID speed up lookups?
- ChallengeAuthor names are currently stored on every book row. If authors move to their own table, how many times is Chan stored?
- ChallengeChan holds two of five records, forty per cent. Three more Chan books are added for a total of eight. What is the new share?
- Error redoA query should return books by Chan published from 2019 on, but a student writes Author = Chan OR Year >= 2019. How many extra rows appear?
03 Answers and one-line notes
| # | Answer | One-line note |
|---|---|---|
| 1 | Duplicate rows caused by one book having many loans, so group by the key or use a subquery | Joining multiplies one book row by its loan rows. |
| 2 | A foreign key that points to the Books primary key and may repeat | A foreign key is not unique in its own table; it only requires a matching value in the other table. |
| 3 | Eleven copies, about seventy-three per cent | Find the available count first, then divide by the total stock. |
| 4 | Two records: B003 and B005 | OR unions two separate conditions, so both sides contribute. |
| 5 | The same rows, since the two operations act on different aspects; only execution cost differs | Filtering decides which rows survive while sorting only fixes display order. |
| 6 | COUNT then gives four while COUNT star still gives five | COUNT ignores empty values whereas the star counts whole rows. |
| 7 | It lets the system locate rows directly instead of scanning every record, and a primary key is indexed automatically | An index works like a table of contents, allowing jumps rather than a full scan. |
| 8 | Once, in the author table only, with the book table holding the author number | That is the basic way to remove repeated storage. |
| 9 | Five divided by eight, that is sixty-two point five per cent | Both numerator and denominator change, so the share is recomputed. |
| 10 | One extra row such as B005, where the correct answer is one row | OR widens the condition and admits rows that fail the year test. |
04 What to do after marking
- After marking, redo only the wrong items and write down which step went wrong (formula, unit, or misread number).
- Repeat the same item two days later; move to the next paper only when both attempts are correct.
- Parents only need the answer and note columns; there is no need to explain every item.
To keep practising queries and filters for this chapter, work through the other three papers, moving from foundation up to challenge.Tutor Registration →
Questions and answers are written originally by the TutorZone team and are not taken from any school, publisher or examination authority paper. Every value is checked by computer.



