OCR GCSE Computer Science June 2022 Paper 2, Question 5(a)(iii): correcting an SQL statement

OCR J277/02 June 2022, Question 5(a)(iii): an SQL statement written as SELECT ALL, FROM, IF Nights less than 1 is wrong in three places. Each error explained, the corrected statement, the four marks, and the query to run on a bookings table.

Past paper questionOCR J277/02June 2022 Paper 24 marksSQL

Question 5(a)(iii) of the OCR GCSE Computer Science Paper 2 sat on 27 May 2022 (J277/02) shows an SQL statement that is wrong in three places and asks you to rewrite it. It is worth 4 marks.

We do not copy the exam paper here. Open it beside this page: OCR June 2022 J277/02 question paper (PDF). When you have finished, check the mark scheme too.

The question in short

Hotel bookings are stored in a table called TblBookings, with the fields firstName, surname, nights, room and stayComplete. A statement should display all customer bookings that stay more than one night. As written it says SELECT ALL, then FROM TblBookings, then IF Nights < 1.

Find the errors

SELECT ALL should be SELECT *. The star means every field. ALL is not how SQL says it. Listing all five field names, separated by commas, is accepted as well.

IF should be WHERE. SQL chooses rows with WHERE. IF belongs to programming languages.

< 1 should be > 1. "More than one night" is greater than 1. The sign on the paper is the wrong way round. >= 2 is accepted too.

The FROM TblBookings line is already right, and keeping it is one of the four marks.

The answer

SELECT *
FROM TblBookings
WHERE Nights > 1

One mark each for the SELECT line, the FROM line, the word WHERE, and the condition. The wrong order, or extra invalid code, caps you at 3.

Where the marks are lost

  • Fixing only the keyword. WHERE Nights < 1 still finds bookings of less than one night, which is nobody.
  • >= 1. That includes one night stays. The question says more than one.
  • Changing the table name. TblBookings is correct as given. Spelling must match.
  • Leaving out the line that was right. "Rewrite the statement" means all of it.

Run it

The corrected statement, run on a small bookings table. Change QUERY back to the paper's version and see what SQL makes of it.

It returns three bookings: the ones for 3, 5 and 2 nights. The one night booking is left out.
The program
from bugbot import *
import sqlite3
connect()

QUERY = "SELECT * FROM TblBookings WHERE Nights > 1"

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE TblBookings (firstName TEXT, surname TEXT, nights INTEGER, room TEXT, stayComplete TEXT)")
rows = [("Amaya", "Taylor-Ling", 3, "Premium", "False"), ("Sam", "Okoro", 1, "Basic", "True"),
        ("Priya", "Shah", 5, "Basic", "False"), ("Leo", "Marsh", 2, "Premium", "True")]
db.executemany("INSERT INTO TblBookings VALUES (?, ?, ?, ?, ?)", rows)

print(QUERY)
try:
    for row in db.execute(QUERY).fetchall():
        print(row)
except sqlite3.Error as problem:
    led("red")
    print("SQL error:", problem)
Put this demo on your own site

Paste it into a school website, Moodle, Google Sites or a blog. More options on the embed page.

Questions

What is the answer to OCR J277 June 2022 Paper 2 Question 5(a)(iii)?

SELECT * FROM TblBookings WHERE Nights > 1.

What does SELECT * mean in SQL?

The star is a wildcard meaning every field in the table. SELECT * FROM TblBookings shows all the fields of every record that matches.

What is the difference between WHERE and IF?

WHERE is the SQL keyword that filters the rows of a table by a condition. IF is a selection statement in a programming language. SQL at GCSE never uses IF.

More from this paper

Every OCR J277 question we have worked

Learn it step by step

  1. F7.3 SQL: SELECT Files and databases
  2. F7.2 Relational databases Files and databases
Open the lessons

This is our own explanation of a published exam question. It is not written or endorsed by OCR, and the question paper and mark scheme remain OCR's copyright. Read them on OCR's site with the links on this page.