SQL and Data Handling - Worksheets, Questions and Revision
13 original exam-style questions - 7 pages of questions with a full mark scheme - free printable PDF.
SQL and Data Handling revision guideMore GCSE Computer Science practiceMock exams
This topic is chapter 6 of GCSE Computer Science Topic Workbook 2.
2.8 SQL and Data Handling
| MemberID | FirstName | LastName | Town | JoinYear | BooksOut |
|---|---|---|---|---|---|
| 1001 | Aisha | Khan | Preston | 2023 | 2 |
| 1002 | Ben | Ogunleye | Preston | 2022 | 0 |
| 1003 | Chloe | Wright | Kendal | 2024 | 5 |
| 1004 | Dmitri | Novak | Kendal | 2023 | 1 |
| 1005 | Elin | Pryce | Lancaster | 2021 | 3 |
| 1006 | Frankie | Osei | Preston | 2024 | 0 |
| 1007 | Grace | Taylor | Lancaster | 2022 | 4 |
| 1008 | Harun | Malik | Kendal | 2023 | 2 |
| 1009 | Isla | Fernandes | Lancaster | 2024 | 0 |
| 1010 | Jamie | Connolly | Preston | 2021 | 1 |
| LoanID | MemberID | BookTitle | DueDate |
|---|---|---|---|
| 5001 | 1001 | The Hobbit | 21/07/2026 |
| 5002 | 1003 | Wuthering Heights | 18/07/2026 |
| 5003 | 1003 | Great Expectations | 25/07/2026 |
| 5004 | 1007 | 1984 | 20/07/2026 |
Discuss the problems this flat-file approach could cause, and explain how splitting the data into two related tables, as in the Members and Loans tables in question 3, solves these problems.
SELECT FirstName, Town FROM Members WHERE JoinYear = 2024;
SELECT FirstName FROM Members WHERE Town = 'Preston' AND BooksOut = 0 OR Town = 'Kendal';
In SQL, AND is evaluated before OR when both appear in the same WHERE clause (unless brackets are used to change the order). State the FirstNames that this statement would output, listing every member that meets the condition.
Question 1
- (a) 6 caoB1
- (a) Answer: 6
- (b) 10 caoB1
- (b) Answer: 10
- (c) MemberIDB1
- (c) Reason, e.g. every value in this field is unique/different for each record, so it can identify one specific member with no duplicates (unlike FirstName, LastName or Town, which could repeat)B1
- (c) Answer: MemberID; because each MemberID value is unique to one record, so it can identify a single member without ambiguity.
Question 2
- (a) SELECT * FROM Members WHERE LastName LIKE structure attemptedM1
- (a) SELECT * FROM Members WHERE LastName LIKE 'O%'; oeA1
- (a) Answer: SELECT * FROM Members WHERE LastName LIKE 'O%';
- (b) SELECT FirstName, Town FROM Members WHERE Town LIKE structure attemptedM1
- (b) SELECT FirstName, Town FROM Members WHERE Town LIKE '%cas%'; oeA1
- (b) Answer: SELECT FirstName, Town FROM Members WHERE Town LIKE '%cas%';
Question 3
- (a) LoanID caoB1
- (a) Answer: LoanID
- (b) A foreign key is a field in one table that is the primary key of another (related) table (1)B1
- (b) The MemberID in the Loans table links each loan record to the matching member record (with that same MemberID) in the Members table (1)B1
- (b) Answer: A foreign key is a field that is the primary key in a different table; MemberID in Loans is a foreign key because it links each loan to the matching member's record in the Members table.
- (c) Avoids data redundancy/duplication, since a member's details are not repeated in every loan record (1)B1
- (c) Reduces the risk of inconsistent data, since a member's details only need to be updated in one place (the Members table) rather than in every loan they have (1)B1
- (c) Answer: It avoids repeating each member's details in every loan record, saving storage and making sure a member's details can be updated in one place without risking inconsistent copies.
Question 4
- Level 1 Basic, undeveloped comments about the flat-file table are given, with little or no reference to how a relational structure would help. Little relevant technical terminology is used.1-2
- Level 2 At least one problem with the flat-file approach is described with some understanding, and there is some explanation of how splitting the data into two tables helps, but the discussion may be one-sided or lack detail in places. Relevant technical terminology is generally used correctly.3-4
- Level 3 A well-balanced discussion clearly describes multiple problems with the flat-file approach (e.g. redundancy and update anomalies) and clearly explains how a relational structure with a foreign key solves each problem. Relevant technical terminology is used correctly throughout, and the explanation is well supported and coherent.5-6
- Indicative content:
- In the flat-file table, a member's FirstName, LastName and Town are repeated in every row where they borrow a book, wasting storage space (data redundancy)
- If a member moves to a new Town, every row containing that member's loans would need to be updated; if even one row is missed, the table would hold inconsistent/contradictory data for the same member (an update anomaly)
- If a member's only loan record were deleted (e.g. because the book was returned and the row removed), all record of that member's details could be lost from the table (a deletion anomaly)
- Splitting the data into a Members table (with MemberID as primary key) and a Loans table (with LoanID as primary key and MemberID as a foreign key) means each member's FirstName, LastName and Town are stored only once, in a single row of the Members table
- Loans are linked to members via the MemberID foreign key rather than repeating the member's details, so redundancy is removed and storage is saved
- Updating a member's Town now only requires changing one row in the Members table, and every loan automatically remains linked to the correct, up-to-date member record via MemberID, avoiding inconsistency
- A justified conclusion should recognise that the flat-file approach becomes increasingly wasteful and error-prone as the number of loans grows, whereas the relational structure keeps each fact stored exactly once and connects tables using keys
Question 5
- SELECT FirstName, LastName, JoinYear FROM Members correctly namedM1
- WHERE LastName LIKE '%an%' correctly includedM1
- AND JoinYear ≥ 2023 correctly appended as a second conditionM1
- ORDER BY JoinYear ASC included, placed after the WHERE clauseM1
- SELECT FirstName, LastName, JoinYear FROM Members WHERE LastName LIKE '%an%' AND JoinYear ≥ 2023 ORDER BY JoinYear ASC; oe, fully correct with clauses in the correct orderA1
- Answer: SELECT FirstName, LastName, JoinYear FROM Members WHERE LastName LIKE '%an%' AND JoinYear ≥ 2023 ORDER BY JoinYear ASC;
Question 6
- (a) Elin caoB1
- (a) Answer: Elin
- (b) 4 caoB1
- (b) Answer: 4
- (c) 3 caoB1
- (c) Answer: 3
- (d) 1001, 1004, 1008 (all three, ft consistent with table)B1
- (d) Answer: 1001, 1004, 1008
Question 7
- (a) SELECT ... FROM Members structure attempted, with keywords in the correct orderM1
- (a) SELECT * FROM Members; oe (trailing semicolon not required)A1
- (a) Answer: SELECT * FROM Members;
- (b) SELECT FirstName, LastName ... structure attemptedM1
- (b) SELECT FirstName, LastName FROM Members; oeA1
- (b) Answer: SELECT FirstName, LastName FROM Members;
Question 8
- (a) SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' structure attemptedM1
- (a) SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' OR Town = 'Kendal'; oeA1
- (a) Answer: SELECT FirstName, LastName, Town FROM Members WHERE Town = 'Lancaster' OR Town = 'Kendal';
- (b) SELECT * FROM Members WHERE structure attemptedM1
- (b) SELECT * FROM Members WHERE BooksOut > 2; oeA1
- (b) Answer: SELECT * FROM Members WHERE BooksOut > 2;
Question 9
- Chloe, KendalB1
- Frankie, PrestonB1
- Isla, LancasterB1
- Answer: Chloe, Kendal / Frankie, Preston / Isla, Lancaster (any order)
Question 10
- (a) SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' structure attemptedM1
- (a) SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' AND BooksOut = 0; oeA1
- (a) Answer: SELECT FirstName, LastName FROM Members WHERE Town = 'Preston' AND BooksOut = 0;
- (b) SELECT * FROM Members WHERE Town = 'Lancaster' structure attemptedM1
- (b) OR BooksOut > 3 correctly appended as a second conditionM1
- (b) SELECT * FROM Members WHERE Town = 'Lancaster' OR BooksOut > 3; oe, fully correctA1
- (b) Answer: SELECT * FROM Members WHERE Town = 'Lancaster' OR BooksOut > 3;
Question 11
- (a) SELECT FirstName, LastName, BooksOut FROM Members structure attemptedM1
- (a) ORDER BY BooksOut includedM1
- (a) SELECT FirstName, LastName, BooksOut FROM Members ORDER BY BooksOut DESC; oe (DESC correctly chosen so the highest value is first)A1
- (a) Answer: SELECT FirstName, LastName, BooksOut FROM Members ORDER BY BooksOut DESC;
- (b) SELECT LastName FROM Members ORDER BY LastName structure attemptedM1
- (b) SELECT LastName FROM Members ORDER BY LastName ASC; oe (ASC may be omitted, since it is the default)A1
- (b) Answer: SELECT LastName FROM Members ORDER BY LastName ASC;
Question 12
- SELECT FirstName, LastName, JoinYear FROM Members correctly namedM1
- WHERE JoinYear < 2023 correctly includedM1
- ORDER BY JoinYear included, placed after the WHERE clauseM1
- SELECT FirstName, LastName, JoinYear FROM Members WHERE JoinYear < 2023 ORDER BY JoinYear ASC; oe, fully correctA1
- Answer: SELECT FirstName, LastName, JoinYear FROM Members WHERE JoinYear < 2023 ORDER BY JoinYear ASC;
Question 13
- Recognises the condition is evaluated as (Town = 'Preston' AND BooksOut = 0) OR Town = 'Kendal', i.e. AND is grouped before ORM1
- Ben and Frankie identified (the Preston members with 0 books out)A1
- Chloe, Dmitri and Harun identified (all three Kendal members, regardless of BooksOut)A1
- Answer: Ben, Frankie, Chloe, Dmitri, Harun (any order)
Mark your answers
Check answers without an account. Some answers can be checked automatically; use the mark scheme to mark the rest.