Community resourceWorksheet

OCR H446 1.3.2 SQL joins and nested SELECT

Part 9 of 13 · H446 1.3.2 · Databases

Once data is split across tables, H446 1.3.2 expects queries that put it back together. This worksheet derives a join condition from the declared keys and evaluates a nested SELECT from the inner query outwards, which is the reading order students most often get wrong.

Students will:

  • write a join condition matching a foreign key to the primary key it references
  • join three tables by starting from the one that holds the foreign keys
  • evaluate a nested SELECT inner query first and state the outer result
  • explain the evaluation order that produced a given set of rows
  • write both a join and a nested SELECT for an unfamiliar membership schema

Inside: 8 explanation cells, 2 multiple-choice questions, 2 fill-in-the-blanks cells, 3 written answers and 1 Python task. 32 marks, about 30 to 45 minutes.

Series: H446 1.3.2 · Databases, part 9 of 13.

Shared by Coding PathwayVerified teacher

  • 16 cells
  • About 30 minutes
  • CC BY-SA 4.0
  • Shared 31 Aug 2026
  • Updated 3 Sept 2026

Preview

The whole resource, exactly as a class sees it. Answers and marking are held back.

SQL joins and nested SELECT

Relational data is split across tables. A JOIN follows matching keys to combine related rows. A nested SELECT uses the result of one query inside another.

By the end, you will be able to

  • connect join conditions to primary and foreign keys;
  • interpret and write JOIN or INNER JOIN;
  • evaluate an inner SELECT before the outer query;
  • choose a join or nested query for a supplied requirement.

Reactivate: Booking.customer_id refers to Customer.customer_id; SELECT chooses result fields.

A join follows the relationship path

From relationship keys to a joined resultCustomercustomer_id PKnameBookingbooking_id PKcustomer_id FKevent_id FKquantityEventevent_id PKtitleResult: Customer.name | Event.title | Booking.quantity

The join condition matches a foreign key to its referenced primary key. If the wrong fields are matched, unrelated records may be combined or no rows may be returned.

OCR treats JOIN as equivalent to INNER JOIN for assumed knowledge. Outer, left and right joins are not required unless a question introduces them.

Worked JOIN

SELECT Customer.name, Event.title, Booking.quantity
FROM Booking
JOIN Customer ON Booking.customer_id = Customer.customer_id
JOIN Event ON Booking.event_id = Event.event_id

Start from Booking because it contains the two foreign keys. Match each foreign key to the corresponding primary key, then select fields from the joined tables.

Worked example
import sqlite3
join_db = sqlite3.connect(':memory:')
j = join_db.cursor()
j.executescript('''
CREATE TABLE Customer(customer_id TEXT, name TEXT);
CREATE TABLE Event(event_id TEXT, title TEXT, price REAL);
CREATE TABLE Booking(booking_id TEXT, customer_id TEXT, event_id TEXT, quantity INTEGER);
''')
j.executemany('INSERT INTO Customer VALUES (?, ?)', [('C1','Amira'),('C2','Ben'),('C3','Chen')])
j.executemany('INSERT INTO Event VALUES (?, ?, ?)', [('E1','Code Lab',12),('E2','Jazz Night',18),('E3','Robotics Studio',22)])
j.executemany('INSERT INTO Booking VALUES (?, ?, ?, ?)', [('B1','C1','E2',2),('B2','C2','E1',1),('B3','C1','E3',3)])
query = '''SELECT Customer.name, Event.title
FROM Booking
JOIN Customer ON Booking.customer_id = Customer.customer_id
JOIN Event ON Booking.event_id = Event.event_id'''
for row in j.execute(query):
    print(row)

Worked nested SELECT

Requirement: return titles costing more than the Code Lab event.

SELECT title
FROM Event
WHERE price > (
    SELECT price
    FROM Event
    WHERE title = 'Code Lab'
)

Read inside out. The inner query returns Code Lab's price, 12. The outer query then returns events whose price is greater than 12.

Multiple choice1 mark

Which condition correctly joins Booking to Customer?

  • ABooking.customer_id = Customer.customer_id
  • BBooking.booking_id = Customer.name
  • CBooking.event_id = Customer.customer_id
  • DCustomer.name = Booking.quantity
Multiple choice1 mark

How should you reason about WHERE price > (SELECT price FROM Event WHERE event_id='E4')?

  • AJoin every table before reading either SELECT
  • BRun the outer query completely and ignore the inner result
  • CFind the inner SELECT's price first, then compare each outer row with that value
  • DTreat the nested SELECT as a DELETE condition
Fill in the blanks2 marks
SELECT Customer.name, Event.title FROM Booking JOIN Customer gap 1 Booking.customer_id = Customer.customer_id JOIN Event gap 2 Booking.event_id = Event.event_id
  • ON
  • WHERE
  • FROM
  • AND

Guided checkpoint

Requirement: return customer names and quantities for bookings for 'Jazz Night'.

Plan: begin with Booking → join Customer using customer_id → join Event using event_id → filter Event.title.

Written answer8 marks

Write the JOIN query for the Jazz Night requirement.

Return Customer.name and Booking.quantity.

Students type their answer here.

Written answer4 marks

Using the supplied Event rows, state the result of the worked nested SELECT and explain the inner-then-outer evaluation.

Begin with the inner result: Code Lab costs 12.

Students type their answer here.

Independent transfer

MEMBER(member_id, name), CLUB(club_id, club_name, fee), MEMBERSHIP(member_id, club_id, role).

Written answer12 marks

Write (1) a JOIN returning member name, club name and role; and (2) a nested SELECT returning club names whose fee is greater than the fee for 'Chess'.

Use the exact field names supplied.

Students type their answer here.

Closed-book checkpoint

Complete each sentence from memory. There is no answer bank and correctness is held for teacher review.

Fill in the blanks4 marks
A completion 1 combines rows from related tables. Its condition normally matches a child foreign key to a parent completion 2 key. A query inside another query is a completion 3 SELECT. Evaluate the inner query first to understand the outer completion 4.

Review your understanding

Before submitting, check that you can explain the main distinction in your own words, apply it in an unfamiliar context and justify each consequence rather than only naming a feature.