OCR GCSE Computer Science June 2024 Paper 2, Question 9(d): completing an SQL statement

OCR J277/02 June 2024, Question 9(d): complete an SQL statement that shows the student ID and team name of every student in year group 11. SELECT, FROM and WHERE, the four marks explained, and the query to run on the table.

Past paper questionOCR J277/02June 2024 Paper 24 marksSQL

Question 9(d) of the OCR GCSE Computer Science Paper 2 sat on 21 May 2024 (J277/02) gives you the start of an SQL statement and asks you to finish it. It is worth 4 marks.

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

The question in short

A database table called TblResult stores 100 m race times. Its fields are StudentID, YearGroup, TeamName and Time.

Complete the SQL statement to show the student ID and team name of all students who are in year group 11. The statement starts SELECT StudentID, and the word FROM is printed further along.

The answer

SELECT StudentID, TeamName
FROM TblResult
WHERE YearGroup = 11

One mark each for: TeamName (and nothing else) in the first space, TblResult after FROM, the keyword WHERE, and the condition YearGroup = 11.

Build it from the sentence

  • "show the student ID and team name": the fields after SELECT.
  • The table is named in the question: FROM TblResult.
  • "students who are in year group 11": the rows to keep, WHERE YearGroup = 11.

Where the marks are lost

  • SELECT *. The star shows every field. The question asks for two, and the mark scheme refuses * and any extra field here.
  • TblResults. The table has no s on the end. Spelling must be exact. Capitals do not matter.
  • Year Group. Field names have no spaces.
  • Team or Name. Copy the field name from the table heading: TeamName.
  • IF for WHERE. SQL chooses rows with WHERE.

11 is a number, so it needs no quotation marks, though the mark scheme allows them.

Run it

Python can run real SQL. This program builds the table from the paper and runs your statement.

It returns two rows: 11GC1 in Valiants and 11JP2 in Champions. Change 11 to 10 for the two Super-Team students.
The program
from bugbot import *
import sqlite3
connect()

QUERY = "SELECT StudentID, TeamName FROM TblResult WHERE YearGroup = 11"

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE TblResult (StudentID TEXT, YearGroup INTEGER, TeamName TEXT, Time REAL)")
rows = [("11GC1", 11, "Valiants", 20.3), ("10VE1", 10, "Super-Team", 19.7),
        ("10SM1", 10, "Super-Team", 19.2), ("11JP2", 11, "Champions", 19.65)]
db.executemany("INSERT INTO TblResult VALUES (?, ?, ?, ?)", rows)

print(QUERY)
for row in db.execute(QUERY).fetchall():
    print(row)
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.

Now change it

Write a statement that shows the student ID and time of everyone who ran faster than 19.7 seconds. Faster means a smaller time.

Answer SELECT StudentID, Time FROM TblResult WHERE Time < 19.7. It returns 10SM1 (19.2) and 11JP2 (19.65).

Questions

What is the answer to OCR J277 June 2024 Paper 2 Question 9(d)?

SELECT StudentID, TeamName FROM TblResult WHERE YearGroup = 11.

When should I use SELECT * in SQL?

Only when the question asks for all fields, or every detail, of the matching records. If it names the fields to show, list exactly those fields.

Does SQL care about capital letters?

Keywords such as SELECT can be in any case, and OCR mark schemes ignore capitalisation. Field and table names must be spelt exactly as they are in the question.

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.