AQA A-Level Computer Science · 4.10.4

Structured
Query Language

A step-by-step guide with real exam questions from AQA past papers

DDL DML SELECT · INSERT · UPDATE · DELETE CREATE TABLE

Overview

DDL vs DML

The Big Picture

SQL has two categories. Before writing any query, it helps to know which one you're in.

DDL — Data Definition Language

Commands that define or create the structure of the database itself.

CREATE TABLE Define a new table with fields and data types

DML — Data Manipulation Language

Commands that work with the data inside the tables.

SELECT Retrieve data from one or more tables
INSERT Add a new record to a table
UPDATE Modify existing records in a table
DELETE Remove records from a table

💡 Exam note SELECT questions appear most often. INSERT and UPDATE appear less frequently — which is exactly why students perform worse on them. The 2024 examiner report specifically called this out. Make sure you're equally confident with all four DML commands.

Reference

SQL Data Types

You need to know these

Every field in a CREATE TABLE statement needs a data type. AQA accepts a range of sensible types — these are the most useful ones to know.

Data Type	Used For	Example Value	Notes
INT	Whole numbers	42	Also accepted: SMALLINT, TINYINT, INTEGER
VARCHAR(n)	Text up to n characters	'Smith'	n is optional but good practice. Also: CHAR(n)
DATE	Date values	'29/09/2024'	Always use quote marks around dates in queries
DECIMAL / FLOAT	Numbers with decimal points	19.99	Also accepted: REAL, NUMERIC, MONEY
BOOLEAN	True or False	TRUE	Rarely tested directly in SQL questions

⚠️ Watch out AQA accepts a wide range of data type names — writing INTEGER instead of INT is fine. But writing a non-SQL type like string or number risks losing marks. Stick to recognised SQL types.

DDL

CREATE TABLE — Syntax

Step 1 of 5

Template

CREATE TABLE TableName (
    FieldName1  DataType  PRIMARY KEY,
    FieldName2  DataType,
    FieldName3  DataType
)

Example

CREATE TABLE Student (
    StudentID    INT          PRIMARY KEY,
    Surname      VARCHAR(50),
    DateOfBirth  DATE,
    YearGroup    INT
)

1

Field name comes first, then the data type — never the other way round. Writing INT FieldName instead of FieldName INT will lose marks (specifically penalised in the mark scheme).

2

Each field definition is separated by a comma. Missing commas are one of the two most common errors on CREATE TABLE questions.

3

The primary key is declared with PRIMARY KEY after the data type on the key field. Alternatively, you can write PRIMARY KEY(FieldName) on a separate line — both are accepted.

4

In the exam, the shell is usually provided to you: CREATE TABLE X ( and the closing ). You fill in the inside — but the commas and data types still need to be correct.

DDL · Past Paper Question

CREATE TABLE

November 2021 · Q5.3

Database Schema

Facility(FacilityID, Description, MaxPeople, PricePerHour)

Question

AQA A-Level Computer Science · November 2021 · Paper 2 · Question 5.3

Complete the following SQL statement to create the Facility relation specified in the schema above, including the primary key.

CREATE TABLE Facility (

)

[3 marks] · All AO3 (programming) — syntax must be correct to score

DDL · Answer

CREATE TABLE — Answer

November 2021 · Q5.3

Model Answer

CREATE TABLE Facility (
    FacilityID    INT           PRIMARY KEY,
    Description   VARCHAR(100),
    MaxPeople     INT,
    PricePerHour  DECIMAL
)

How marks are awarded

1

Mark 1: FacilityID with a sensible data type, identified as PRIMARY KEY

2

Mark 2: Two other fields with sensible data types and lengths

3

Mark 3: Fully correct SQL — all four fields present, commas separating each line, syntactically correct throughout

⚠️ Watch out — examiner report 2022 The two most common errors across multiple years:

❌ INT FacilityID — data type placed before field name

❌ Missing commas between field definitions

Fewer than a quarter of students get full marks on CREATE TABLE questions.

DML · SELECT

SELECT — Syntax

Step 2 of 5

SELECT retrieves data from a table. There are four clauses — always written in this order, one per line:

Template

SELECT Field1, Field2, Field3
FROM TableName
WHERE FieldName = 'value'
ORDER BY FieldName ASC  -- or DESC

Example

SELECT StudentID, Surname, YearGroup
FROM Student
WHERE YearGroup = 12
ORDER BY Surname ASC

1

SELECT — list only the fields you want. Always list specific fields — avoid SELECT * in exam answers as the question will always tell you exactly which fields are needed.

2

FROM — the table(s) the data comes from.

3

WHERE — filter conditions. Use AND to chain multiple conditions. Text and date values need quote marks: 'David Ferguson'

4

ORDER BY — ASC = smallest/earliest first (the default). DESC = largest/latest first. Very commonly required in the question — and very commonly forgotten in the answer.

DML · SELECT

SELECT — Multiple Tables

The Linking Condition

When data comes from two tables, list both in FROM — and you must include a linking condition in WHERE to join them via their shared key.

Template

SELECT Field1, Field2
FROM Table1, Table2
WHERE Table1.SharedKey = Table2.SharedKey
AND OtherField = 'value'
ORDER BY FieldName DESC

Example

SELECT OrderID, ProductName, Quantity
FROM Order, Product
WHERE Order.ProductID = Product.ProductID
AND CustomerID = 5
ORDER BY OrderID ASC

⚠️ The single most common mark-losing error in the whole spec Missing the linking condition. If you have two tables in FROM, you always need a line in WHERE that joins them: Table1.KeyField = Table2.KeyField

Without it, SQL produces a cartesian product — every row of Table1 combined with every row of Table2. It's wrong, and it loses the AO2 mark every time.

💡 When to use Table.Field notation If the same field name appears in both tables (e.g. both have CustomerID), write Table1.CustomerID to be unambiguous. If field names are unique across both tables, you can write just the field name on its own.

DML · SELECT · Past Paper Question 1 of 2

SELECT — Single Table

Sample Set 1 Additional · Q6.2

Database Schema

Book (BookID, Title, Author, Price, Category, Publisher)

Question

AQA A-Level Computer Science · Sample Set 1 Additional · Paper 2 · Question 6.2

Write an SQL query to retrieve the BookID, Title, Author, Price and Category of all books written by "David Ferguson" that cost less than £25.00. The books should be listed in order, with the most expensive book at the top and the cheapest at the bottom.

[4 marks]

2 marks AO2 — right tables & conditions 2 marks AO3 — correct SQL syntax

DML · SELECT · Answer 1 of 2

SELECT — Answer

Sample Set 1 Additional · Q6.2

Model Answer

SELECT BookID, Title, Author, Price, Category
FROM Book
WHERE Author = 'David Ferguson'
AND Price < 25
ORDER BY Price DESC

Mark breakdown

AO2

Correct table (Book), correct five fields, no extras included

AO2

Both conditions with AND: Author = 'David Ferguson' AND Price < 25

AO3

Correct syntax in at least 2 of the 4 clauses

AO3

Fully correct SQL in all 4 clauses including ORDER BY Price DESC

⚠️ Watch out — three easy marks to drop ❌ Including Publisher in SELECT — the question only asks for five specific fields

❌ Forgetting ORDER BY entirely

❌ Writing ORDER BY Price ASC — the question says most expensive first, so it must be DESC

DML · SELECT · Past Paper Question 2 of 2

SELECT — Two Tables

November 2020 · Q4.5

Database Schema (relevant tables)

Property(PropertyID, HouseNum, Street, Area, Postcode, Bedrooms, Bathrooms, AskingPrice, SellerID)
Buyer(BuyerID, Title, Forename, Surname, Telephone, DesiredArea, MinBedrooms, MaxPrice)

Question

AQA A-Level Computer Science · November 2020 · Paper 2 · Question 4.5

Write an SQL query to retrieve the list of all properties that the buyer with BuyerID 23 might be interested in buying. The properties should: be in the buyer's desired area · have at least the minimum number of bedrooms required · cost no more than the buyer's maximum price.

Return only: PropertyID, Street, Bedrooms, AskingPrice. Order with the most expensive property first.

[5 marks] · 3 marks AO2 + 2 marks AO3

DML · SELECT · Answer 2 of 2

SELECT — Answer

November 2020 · Q4.5

Model Answer

SELECT PropertyID, Street, Bedrooms, AskingPrice
FROM Property, Buyer
WHERE BuyerID = 23
AND Buyer.DesiredArea = Property.Area     -- linking condition
AND Buyer.MinBedrooms <= Property.Bedrooms
AND Buyer.MaxPrice >= Property.AskingPrice
ORDER BY AskingPrice DESC

⚠️ Watch out — four conditions, four places to drop marks Students typically get BuyerID = 23 but miss the inequality conditions. Note the direction carefully:

MinBedrooms <= Bedrooms — the property must have AT LEAST the minimum (so Bedrooms must be >= MinBedrooms)
MaxPrice >= AskingPrice — the asking price must not exceed what the buyer will pay

Swapping <= and >= loses the AO2 mark for conditions, even if the logic feels the same to you.

DML · INSERT

INSERT INTO — Syntax

Step 3 of 5

INSERT adds a new record to a table. You specify the table, the fields, and the values to insert.

Template

INSERT INTO TableName (Field1, Field2, Field3)
VALUES (numericValue, 'text value', 'date value')

Example

INSERT INTO Student (StudentID, Surname, DateOfBirth)
VALUES (101, 'Ahmed', '15/09/2007')

1

Text and date values must be wrapped in quote marks. Numbers should not be. This is the single most commonly dropped mark on INSERT questions.

2

The values in VALUES must match the order of fields listed in the brackets. If you list fields as (SaleID, CustomerID, SaleDate), your VALUES must be in that same order.

3

You can omit the field list and write INSERT INTO TableName VALUES (...) but then values must match the table's own field order exactly. Safer to always list the fields explicitly.

⚠️ Watch out — date delimiters Dates must have quote marks: '29/09/2024' ✓ Writing 29/09/2024 without quotes is wrong and will always lose the mark. AQA also accepts hash delimiters: #29/09/2024#

DML · INSERT · Past Paper Question

INSERT INTO

June 2024 · Q8.2

Database Schema

Product(ProductID, Description, QuantityInStock, SupplierID)
Sale(SaleID, CustomerID, SaleDate)
SaleLine(SaleID, ProductID, QuantitySold)

Question

AQA A-Level Computer Science · June 2024 · Paper 2 · Question 8.2

A sale is made on 29/09/2024 to the customer with CustomerID 48. The sale is to be given the SaleID 4072.

Write an SQL query that will create the new record for sale 4072 in the Sale table.

[2 marks] · All AO3 (programming)

DML · INSERT · Answer

INSERT INTO — Answer

June 2024 · Q8.2

Model Answer

INSERT INTO Sale (SaleID, CustomerID, SaleDate)
VALUES (4072, 48, '29/09/2024')

Mark breakdown

1

Mark 1: INSERT INTO Sale with all three fields listed (if field list is given, must include all three)

2

Mark 2: VALUES (4072, 48, '29/09/2024') — correct values, correct order, quote marks around the date only

⚠️ Flagged in the 2024 examiner report Two specific errors examiners saw repeatedly on this question:

❌ No delimiters around the date: 29/09/2024

❌ Delimiters around numeric values: '4072' or '48' — numbers do not get quotes

DML · UPDATE

UPDATE — Syntax

Step 4 of 5

UPDATE modifies the value of a field in existing records. It has three clauses — always in this order:

Template

UPDATE TableName
SET FieldName = NewValue
WHERE ConditionField = value

Example

UPDATE Student
SET YearGroup = 13
WHERE StudentID = 101

1

UPDATE is followed by the table name — not a field name. Writing the field name here is a specific error called out in the 2024 examiner report. UPDATE Product ✓ not UPDATE QuantityInStock ✗

2

SET — assigns the new value. You can use an expression: SET Qty = Qty - 3 subtracts 3 from the current value. This is different to SET Qty = 3 which replaces the value entirely.

3

WHERE — always include this. Without it, every record in the table gets updated — a potentially catastrophic mistake.

DML · UPDATE · Past Paper Question

UPDATE

June 2024 · Q8.3

Database Schema

Product(ProductID, Description, QuantityInStock, SupplierID)

Question

AQA A-Level Computer Science · June 2024 · Paper 2 · Question 8.3

A sale is made: 3 of the products with ProductID 1 are sold.

Write an SQL query that will update the QuantityInStock of the product with ProductID 1 when this sale is made. The value 3 should be subtracted from the current quantity in stock.

[3 marks] · All AO3 (programming)

DML · UPDATE · Answer

UPDATE — Answer

June 2024 · Q8.3

Model Answer

UPDATE Product
SET QuantityInStock = QuantityInStock - 3
WHERE ProductID = 1

Mark breakdown

1

Mark 1: UPDATE Product — the table name, not the field name

2

Mark 2: SET QuantityInStock = QuantityInStock - 3 — subtracts from the current value

3

Mark 3: WHERE ProductID = 1

⚠️ Key distinction on SET The question says "subtract 3 from the current quantity" — so the expression must reference the field:

✅ SET QuantityInStock = QuantityInStock - 3

❌ SET QuantityInStock = 3 — this replaces the value with 3, not reduces it by 3. That's a fundamentally different operation.

DML · DELETE

DELETE — Syntax

Step 5 of 5

DELETE removes records from a table. It's the simplest DML command — just two clauses.

Template

DELETE FROM TableName
WHERE FieldName = 'value'

Example

DELETE FROM Student
WHERE YearGroup = 11

1

Only name the table you're deleting records from. Do not include other tables — even if your filter condition references data that exists elsewhere. The DELETE query needs only the one table it's deleting from.

2

Text and date values still need quote marks in WHERE: WHERE ShowDate = '29/03/2023'

3

Always include WHERE. Without it, every record in the table is deleted permanently.

⚠️ Watch out — don't add extra tables Students sometimes add a second table in DELETE out of habit from writing SELECT queries. DELETE FROM TableA, TableB is wrong — DELETE FROM takes one table only. This was the exact error tested in the June 2023 exam.

DML · DELETE · Past Paper Question — Error Spotting

DELETE — Spot the Errors

June 2023 · Q5.2

Database Schema (relevant tables)

Film(FilmID, FilmName, Duration, Certificate)
Showing(ShowingID, ScreenNumber, FilmID, ShowTime, ShowDate)

Question

AQA A-Level Computer Science · June 2023 · Paper 2 · Question 5.2

The cinema was closed on 29th March 2023 for maintenance. The SQL query below was written to delete all showings on this date. Some errors were made.

Describe two errors in the query. Do not refer to semi-colons in your answer.

DELETE
FROM Showing, Film
WHERE ShowDate = 29/03/2023

[2 marks] · 1 mark per error · All AO2 (analysis)

DML · DELETE · Answer

DELETE — Answer

June 2023 · Q5.2

Corrected Query

DELETE FROM Showing
WHERE ShowDate = '29/03/2023'

The two errors

1

The Film table should not be included in FROM. Only Showing needs to be there — the ShowDate field is already in the Showing table so Film is entirely unnecessary.

2

The date is missing quote marks. 29/03/2023 should be '29/03/2023' — date values always need delimiters.

⚠️ Common wrong answer — examiners flagged this Many students wrote: "the two tables need to be linked with a joining condition."

This was not accepted. The Film table shouldn't be there at all — it's not just incorrectly linked, it's entirely redundant. The Showing table already has ShowDate. No join is needed.

Reference

SQL — Quick Reference Card

All 5 Commands

CREATE TABLE

CREATE TABLE Name (
    Field1  Type  PRIMARY KEY,
    Field2  Type,
    Field3  Type
)

SELECT (multi-table)

SELECT Field1, Field2
FROM Table1, Table2
WHERE Table1.Key = Table2.Key
AND Field = 'value'
ORDER BY Field DESC

INSERT INTO

INSERT INTO Table (F1, F2, F3)
VALUES (123, 'text', 'date')

UPDATE

UPDATE TableName
SET Field = Field - 3
WHERE IDField = value

DELETE

DELETE FROM TableName
WHERE Field = 'value'

🔑 Three rules that cover most exam errors (1) Field name BEFORE data type in CREATE TABLE · (2) Always include the table-linking condition in multi-table SELECT · (3) Quote marks around ALL text and date values in WHERE and VALUES

StructuredQuery Language

DDL vs DML

DDL — Data Definition Language

DML — Data Manipulation Language

SQL Data Types

CREATE TABLE — Syntax

CREATE TABLE

CREATE TABLE — Answer

How marks are awarded

SELECT — Syntax

SELECT — Multiple Tables

SELECT — Single Table

SELECT — Answer

Mark breakdown

SELECT — Two Tables

SELECT — Answer

INSERT INTO — Syntax

INSERT INTO

INSERT INTO — Answer

Mark breakdown

UPDATE — Syntax

UPDATE

UPDATE — Answer

Mark breakdown

DELETE — Syntax

DELETE — Spot the Errors

DELETE — Answer

The two errors

SQL — Quick Reference Card

CREATE TABLE

SELECT (multi-table)

INSERT INTO

UPDATE

DELETE

Structured
Query Language