DASKEREL published · Year 11 · academic review pending

Computer Science GCSE

Original DASKEREL teaching informed by an attributed Oak unit sequence. This resource remains subject to qualified academic review. Check your examination board and tier before using it for exam preparation.

Year 11 · Original DASKEREL study pack · Academic review pending

Relational data and precise SQL retrieval

Allow up to 60 minutes for reading, practice and review · Source route: Oak AQA Computer Science · DASKEREL teaching requires board review

DASKEREL wrote this resource using the Oak National Academy unit sequence as a curriculum reference. It is a study pack, not a complete unit or an exam-board-approved course. Academic review remains pending.

You will learn to

  • Explain primary and foreign keys using a concrete schema.
  • Write SELECT queries with WHERE and ORDER BY.
  • Predict the result of a query and check whether an unintended row is included.

Key terms: table · record · field · primary key · foreign key · query

Learn the idea

A relational database stores related entities in separate tables. A primary key uniquely identifies each record; a foreign key stores a reference to a record in another table. Separating repeated data reduces inconsistent copies, though relationships still need careful design and validation.

Imagine Students(student_id, name) and Enrolments(student_id, course). Students.student_id is the Students primary key; Enrolments.student_id refers back to it. A student can have several enrolment rows without repeating their name in every row.

SELECT names the fields to return, FROM names the table, WHERE filters rows and ORDER BY sorts the result. In SQL syntax, `SELECT name FROM Students WHERE student_id = 7;` retrieves one matching name if the primary key is unique. Text values need quoted literals; exact syntax may vary by database system.

Before running a query, predict which rows meet the condition. `WHERE score >= 60` includes 60; `WHERE score > 60` does not. A selection query does not update or delete data. For operations that change data, control access and test against a safe copy rather than a live learner database.

Worked example

Given Results(id, pupil, score) rows (1, Ana, 72), (2, Ben, 60), (3, Cia, 59), write a query listing pupils with scores at least 60, highest score first.

  1. Select the requested pupil and score fields.
  2. Filter with score >= 60 so the boundary 60 is included.
  3. Sort by score DESC for highest first.
  4. Query: SELECT pupil, score FROM Results WHERE score >= 60 ORDER BY score DESC;
  5. Predict rows: Ana 72, then Ben 60; Cia 59 is excluded.

The query returns exactly two rows in descending score order.

Try together

Using the same Results table, list pupils scoring below 60 in ascending score order.

  • Use score < 60 in WHERE.
  • Use ORDER BY score ASC.
  • Check the 60 boundary.
Show guided answer

SELECT pupil, score FROM Results WHERE score < 60 ORDER BY score ASC; returns Cia 59 only.

Practise independently

  1. Students has (student_id, name); Enrolments has (student_id, course). Which field can act as the foreign key and why?
    Show answer and reasoning for question 1

    Enrolments.student_id can be a foreign key referring to Students.student_id. It links each enrolment record to one student without repeating the student's name.

  2. Write a query for course names beginning with ‘Cloud’ from Courses(title, duration_hours), ordered shortest first.
    Show answer and reasoning for question 2

    SELECT title, duration_hours FROM Courses WHERE title LIKE 'Cloud%' ORDER BY duration_hours ASC; The wildcard matches titles starting with Cloud; ascending duration puts the shortest first. SQL dialect may vary.

  3. Write a query listing titles of books published in 2022 from Books(title, year_published), sorted alphabetically.
    Show answer and reasoning for question 3

    SELECT title FROM Books WHERE year_published = 2022 ORDER BY title ASC; WHERE keeps only 2022 records; ORDER BY sorts those results by title without changing which rows qualify.

DASKEREL exam-style practice · 6 draft marks

This original practice task and guidance are not an official examination-board paper or mark scheme.

A library database contains Books(book_id, title, year_published) and Loans(loan_id, book_id, borrower_id). Identify a primary key and a foreign key. Write SQL to list titles of Books published from 2020 onwards, newest first, and explain how the boundary works.

Show draft mark guidance
  • Identify Books.book_id as primary key (1).
  • Identify Loans.book_id as foreign key referencing Books.book_id (1).
  • SELECT title FROM Books or SELECT title, year_published FROM Books (1).
  • WHERE year_published >= 2020 (1).
  • ORDER BY year_published DESC (1).
  • Explain that 2020 is included and earlier years are excluded (1).

Check your understanding

  • A foreign key need not be unique; many loan rows can reference one book.
  • WHERE filters records; ORDER BY changes presentation order, not membership.
  • A query that returns the expected row for one example may still mishandle a boundary value.

Retrieve later: Draw two related tables and explain the purpose of each key without using real personal data.

Next step: Study joins, safe updates and data protection using an invented dataset.

Library links: Oak National Academy · Databases and SQL (checked 2026-11-06) · DASKEREL subject library. Oak's applicable post-2022 content licence is OGL v3.0; some source assets have separate third-party rights. No Oak media is embedded here. DASKEREL is not affiliated with Oak.