Skip to main content
EnglandComputer ScienceSyllabus dot point

How do we create, query and modify the data in a relational database using SQL?

Structured Query Language (SQL): defining tables, and using SELECT, FROM, WHERE, ORDER BY, INSERT INTO, UPDATE, DELETE and joining tables to query and maintain a relational database, with referential integrity.

An OCR H446 answer on Structured Query Language: defining tables and using SELECT, FROM, WHERE, ORDER BY, INSERT INTO, UPDATE and DELETE, joining tables across a relationship, and the role of referential integrity in a relational database.

Generated by Claude Opus 4.813 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Examples in context
  4. Try this

What this dot point is asking

OCR wants you to write SQL that defines tables and queries and maintains data: SELECT with FROM, WHERE and ORDER BY, INSERT INTO, UPDATE, DELETE, and a join across two tables, and to explain referential integrity. Expect to write short SQL statements from a given table structure.

The answer

Retrieving data with SELECT

SELECT Surname, Town
FROM Member
WHERE JoinYear > 2020
ORDER BY Surname ASC;

Modifying data

INSERT INTO Member (MemberID, Surname, Town, JoinYear)
VALUES (43, 'Khan', 'Leeds', 2024);

DELETE FROM Member
WHERE JoinYear < 2000;

Joining tables and referential integrity

SELECT Order.OrderID, Order.Total, Customer.Name
FROM Order
INNER JOIN Customer ON Order.CustomerID = Customer.CustomerID
WHERE Order.Total > 100
ORDER BY Order.Total DESC;

Examples in context

A booking system runs SELECT queries with joins to show each booking with its customer and room, and UPDATE statements to change a booking. An admin script uses DELETE...WHERE to purge old records. Referential integrity is what stops the system deleting a customer who still has open orders. OCR ties SQL to the normalised tables from the database concepts dot point and to transaction processing, which keeps these statements safe under concurrent users.

Try this

Q1. Write SQL to list all fields of every record in a table called Stock where the Quantity is below 10. [2 marks]

  • Cue. SELECT * FROM Stock WHERE Quantity < 10;

Q2. State why a WHERE clause is essential in a DELETE statement. [1 mark]

  • Cue. Without it, every record in the table is deleted; WHERE restricts deletion to the intended rows.

Q3. Explain what referential integrity prevents. [2 marks]

  • Cue. It prevents a foreign key referencing a non-existent record (an orphan) and stops a referenced record being deleted while links to it still exist.

Exam-style practice questions

Practice questions written in the style of OCR exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

OCR 20196 marksA database has a table Member(MemberID, Surname, Town, JoinYear). Write SQL to (a) list the surname and town of all members in 'Oxford', sorted by surname, and (b) change the town of the member with MemberID 42 to 'Reading'.
Show worked answer →

Part (a) SELECT (up to 4):

SELECT Surname, Town
FROM Member
WHERE Town = 'Oxford'
ORDER BY Surname;

Award marks for the correct fields after SELECT, the FROM table, the WHERE condition with the string quoted, and ORDER BY Surname.

Part (b) UPDATE (up to 2):

UPDATE Member
SET Town = 'Reading'
WHERE MemberID = 42;

Award marks for UPDATE...SET with the correct value and, crucially, the WHERE clause restricting it to MemberID 42. A common error is omitting WHERE, which would change every row.

OCR 20215 marksTwo tables exist: Customer(CustomerID, Name) and Order(OrderID, CustomerID, Total). Write SQL to list each order's OrderID and Total alongside the customer's Name, and explain what referential integrity ensures here.
Show worked answer →

SQL join (up to 3):

SELECT Order.OrderID, Order.Total, Customer.Name
FROM Order
INNER JOIN Customer ON Order.CustomerID = Customer.CustomerID;

Award marks for selecting the right fields, the join of the two tables, and the join condition matching the foreign key to the primary key.

Referential integrity (up to 2): it ensures every CustomerID in Order corresponds to an existing CustomerID in Customer, so no order can reference a customer who does not exist, and a customer cannot be deleted while orders still reference them. Markers reward the matching join condition and a correct statement of what referential integrity prevents.

Related dot points

Sources & how we know this