/en-tutor-prep-s2-ict-databases-tables-queries-practice-3/
/en-tutor-prep-s2-ict-databases-tables-queries-practice-3/
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

S2 ICT database tables and queries practice 3: relationships, indexes and condition logic

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)

  1. FoundationThe Loans table holds four loan records and the Books table five books, linked on BookID. What must a query avoid?
  2. FoundationWhat kind of key is BookID inside the Loans table?
  3. FoundationOf fifteen copies in stock, four are on loan. What share is available?
  4. IntermediateThe condition is Author = Chan AND Year >= 2019, OR Copies = 1. How many records match?
  5. IntermediateDoes filtering then sorting give the same rows as sorting then filtering?
  6. IntermediateAll five Year values are filled, so COUNT and COUNT star both give five. If one Year is left blank, what does each return?
  7. ChallengeWhy does an index on BookID speed up lookups?
  8. ChallengeAuthor names are currently stored on every book row. If authors move to their own table, how many times is Chan stored?
  9. 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?
  10. 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.

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