OCR GCSE Computer Science June 2023 Paper 2, Question 6(d): the SQL query

OCR J277/02 June 2023, Question 6(d): write an SQL statement that shows the IDs of door sensors triggered for more than 20 seconds. SELECT, FROM and WHERE with two conditions, the three marks explained, and the query to run on the table.

Past paper questionOCR J277/02June 2023 Paper 23 marksSQL

Question 6(d) of the OCR GCSE Computer Science Paper 2 sat on 25 May 2023 (J277/02) asks for one SQL statement, worth 3 marks. The J277 specification asks for three SQL keywords, SELECT, FROM and WHERE, and on this question each one is a mark. Learn the shape and an SQL question is three marks you can count on.

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

The question in short

An alarm system logs every time a sensor is triggered. The log is a database table called events with four fields: Date, SensorID, SensorType (Door, Motion or Window) and Length (seconds).

Write an SQL statement to display the sensor IDs of the door sensors that have been triggered for more than 20 seconds.

Build it from the sentence

Take the sentence apart. Every SQL question at GCSE can be read this way.

• "display the sensor IDs" is what to show: SELECT SensorID
• the table is named above the question: FROM events
• "of the door sensors ... for more than 20 seconds" is which rows: WHERE SensorType = "Door" AND Length > 20

The answer

SELECT SensorID
FROM events
WHERE SensorType = "Door" AND Length > 20

One mark for each line. The two conditions can go either way round. SELECT * is accepted for the first mark, because it includes SensorID, but name the field when the question names it.

Where the marks are lost

• No quotation marks round Door. It is text, so it must be in quotes. The mark scheme insists on them. 20 is a number, so it has none.
• OR for AND. "Door sensors that have been triggered for more than 20 seconds" is one group of rows meeting both conditions. OR would list every door sensor and every long event.
• >= 20. "More than 20" does not include 20. (>= 21 is accepted, because Length is a whole number.)
• Wrong field names. Sensor ID, Type and Seconds are not in the table. Copy the names exactly. Capital letters are ignored.
• The wrong order. SELECT, then FROM, then WHERE. Out of order caps you at 2.

Run it

Python can run real SQL. This program builds the events table from the paper and runs your query on it.

Look at what comes back: nothing. No door sensor in the table has been triggered for more than 20 seconds. That is not a mistake. The question asks for the statement, not its result, and a correct query on this data gives an empty answer. Change the query and see what else you can ask.

from bugbot import *
import sqlite3
connect()

QUERY = 'SELECT SensorID FROM events WHERE SensorType = "Door" AND Length > 20'

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events (Date TEXT, SensorID TEXT, SensorType TEXT, Length INTEGER)")
rows = [("05/02/2023", "WS2", "Window", 38), ("05/02/2023", "MS1", "Motion", 2),
        ("06/02/2023", "DS3", "Door", 1), ("06/02/2023", "MS2", "Motion", 3),
        ("06/02/2023", "MS1", "Motion", 2), ("07/02/2023", "WS1", "Window", 24),
        ("07/02/2023", "DS1", "Door", 1)]
db.executemany("INSERT INTO events VALUES (?, ?, ?, ?)", rows)

print(QUERY)
results = db.execute(QUERY).fetchall()
print(len(results), "rows")
for row in results:
    print(row)

Now change it

Write a query that shows the date and the length of every motion sensor event on 06/02/2023. Run it. You should get two rows.

Questions

What is the answer to OCR J277 June 2023 Paper 2 Question 6(d)?

SELECT SensorID FROM events WHERE SensorType = "Door" AND Length > 20. One mark for each of the three lines.

What SQL do I need for OCR GCSE Computer Science?

SELECT to choose the fields, FROM to name the table and WHERE to choose the rows. WHERE conditions can be joined with AND and OR, and * selects every field.

When do I need quotation marks in SQL?

Put text values in quotation marks, such as "Door". Do not put numbers in quotation marks. Field names and table names do not need them.

More from this paper

• Question 1(b) to (d): An assignment, the arithmetic operators, and a do until trace table 7 marks
• Question 2: Syntax and logic errors: correct a loop that should total an array 6 marks
• Question 3: Insertion sort: temp, the inner loop, and bubble sort compared 8 marks
• Question 5(c): An adding game: three random questions and a score 6 marks
• Question 6(b): Sound an alarm when armed AND (door OR window) 4 marks

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

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.