Skip to the worksheet
Revision Library
Choose a subject

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.

Grades 1-9

This topic is chapter 6 of GCSE Computer Science Topic Workbook 2.

Revision Library
revisionlibrary.co.uk
GCSE · Computer Science

2.8 SQL and Data Handling

OCR J277 · Calculators not allowed · about 65 minutes
Total Marks
Name: _______________________________    Date: ____ / ____ / ______
Answer ALL questions. Show all your working.
1
The librarian's Members table stores data about every person who has joined the library.
MemberIDFirstNameLastNameTownJoinYearBooksOut
1001AishaKhanPreston20232
1002BenOgunleyePreston20220
1003ChloeWrightKendal20245
1004DmitriNovakKendal20231
1005ElinPryceLancaster20213
1006FrankieOseiPreston20240
1007GraceTaylorLancaster20224
1008HarunMalikKendal20232
1009IslaFernandesLancaster20240
1010JamieConnollyPreston20211
(a)State the number of fields in the Members table.(1)
(b)State the number of records in the Members table.(1)
(c)Identify the most suitable field to use as the primary key for this table, and give one reason for your choice.(2)
(Total for Question 1 is 4 marks)
2
The keyword LIKE, combined with the wildcard %, matches any sequence of characters within a text field (for example, 'A%' matches any value starting with A). Using the Members table from question 1, write the following SQL statements.
(a)Write an SQL statement to display all fields for every member whose LastName begins with the letter O.(2)
(b)A librarian wants to find every member whose Town contains the letters "cas" anywhere within it (for example, to find Lancaster). Write an SQL statement to display the FirstName and Town of these members.(2)
(Total for Question 2 is 4 marks)
3
The library also keeps a Loans table, which records each time a book is borrowed:
LoanIDMemberIDBookTitleDueDate
50011001The Hobbit21/07/2026
50021003Wuthering Heights18/07/2026
50031003Great Expectations25/07/2026
50041007198420/07/2026
(a)State the primary key of the Loans table.(1)
(b)The MemberID field in the Loans table is a foreign key. Explain what is meant by a foreign key, referring to the Members and Loans tables in your answer.(2)
(c)State one benefit of storing member details in a separate Members table, rather than repeating FirstName, LastName and Town directly inside the Loans table every time a book is borrowed.(2)
(Total for Question 3 is 5 marks)
4
A small library currently stores all its data in a single flat-file table, with columns MemberID, FirstName, LastName, Town, BookTitle and DueDate, adding a new row every time a member borrows a book (so a member who borrows three books appears in three separate rows, with their FirstName, LastName and Town repeated each time).

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.
(Total for Question 4 is 6 marks)
5
Write a single SQL statement, using the Members table from question 1, that would display the FirstName, LastName and JoinYear fields for every member whose LastName contains the letter sequence "an" and who joined in 2023 or later (JoinYear ≥ 2023), sorted with the earliest JoinYear first.
(Total for Question 5 is 5 marks)
6
Using the Members table from question 1, answer the following by searching the data manually (without writing any SQL).
(a)State the FirstName of the member whose MemberID is 1005.(1)
(b)State how many members live in the town of Preston.(1)
(c)State how many members currently have 0 books out (BooksOut = 0).(1)
(d)List the MemberIDs of all members who joined in 2023 (JoinYear = 2023).(1)
(Total for Question 6 is 4 marks)
7
SQL (Structured Query Language) can be used to search a database table. The statement SELECT * FROM tablename; displays every field of every record; SELECT field1, field2 FROM tablename; displays only the named fields. Using the Members table from question 1, write the following SQL statements.
(a)Write an SQL statement to display every field, for every record, in the Members table.(2)
(b)Write an SQL statement to display only the FirstName and LastName fields, for every record, in the Members table.(2)
(Total for Question 7 is 4 marks)
8
The keyword WHERE lets an SQL statement filter which records are returned, based on a condition. Using the Members table from question 1, write the following SQL statements.
(a)Write an SQL statement to display the FirstName, LastName and Town fields for every member who lives in Lancaster or Kendal.(2)
(b)Write an SQL statement to display all fields for every member who has more than 2 books currently out (BooksOut > 2).(2)
(Total for Question 8 is 4 marks)
9
State the FirstName and Town that would be output when the following SQL statement is run on the Members table from question 1:
SELECT FirstName, Town FROM Members WHERE JoinYear = 2024;
(Total for Question 9 is 3 marks)
10
Using the Members table from question 1, write the following SQL statements, each combining a WHERE condition with the AND or OR keyword.
(a)Write an SQL statement to display the FirstName and LastName of every member who lives in Preston AND currently has 0 books out.(2)
(b)Write an SQL statement to display all fields for every member who lives in Lancaster OR has more than 3 books currently out (BooksOut > 3).(3)
(Total for Question 10 is 5 marks)
11
The keyword ORDER BY sorts the output of an SQL statement by a chosen field; ASC sorts ascending (smallest/earliest first, and is the default if omitted) and DESC sorts descending (largest first). Using the Members table from question 1, write the following SQL statements.
(a)Write an SQL statement to display the FirstName, LastName and BooksOut fields, for every member, sorted so that the member with the most books out appears first.(3)
(b)Write an SQL statement to display the LastName field, for every member, sorted alphabetically from A to Z.(2)
(Total for Question 11 is 5 marks)
12
Write a single SQL statement, using the Members table from question 1, to display the FirstName, LastName and JoinYear fields for every member who joined before 2023 (JoinYear < 2023), sorted so that the earliest JoinYear appears first.
(Total for Question 12 is 4 marks)
13
A librarian writes the following SQL statement to search the Members table from question 1:
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.
(Total for Question 13 is 3 marks)
Mark scheme · 2.8 SQL and Data Handling

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.

Question 1

4 marks
Did your answer earn the marks?

Question 2

4 marks
Did your answer earn the marks?

Question 3

5 marks
Did your answer earn the marks?

Question 4

6 marks
Did your answer earn the marks?

Question 5

5 marks
Did your answer earn the marks?

Question 6

4 marks
Did your answer earn the marks?

Question 7

4 marks
Did your answer earn the marks?

Question 8

4 marks
Did your answer earn the marks?

Question 9

3 marks
Did your answer earn the marks?

Question 10

5 marks
Did your answer earn the marks?

Question 11

5 marks
Did your answer earn the marks?

Question 12

4 marks
Did your answer earn the marks?

Question 13

3 marks
Did your answer earn the marks?
Mark my answers

Worksheets are written for Revision Library and come with a full mark scheme; a few English packs quote an out-of-copyright text, and name it on the page. Exam board names and specification codes say which qualification a pack suits: we are not affiliated with, endorsed by or approved by any exam board, and no past-paper question is reproduced here. Rights queries: hello@revisionlibrary.co.uk