CAIE 9618 · Paper 4 · Topic 4.5.1

SQL & Relational
Databases

SELECT · FROM · WHERE · JOIN · GROUP BY · INSERT · UPDATE · DELETE · PRIMARY & FOREIGN KEYS

CSZone Cambridge International AS & A Level Computer Science 9618

Relational Database Concepts

Tables, Keys & Relationships

KEY TERMS

Table (relation) — grid of data; rows = records/tuples; columns = attributes/fields

Primary key (PK) — unique identifier for each row; cannot be NULL

Foreign key (FK) — a field in one table that references the PK of another table; creates a link between tables

Referential integrity — FK values must match existing PK values (or be NULL)

Reduces redundancy — data stored once, referenced many times

EXAMPLE SCHEMA

Student (StudentID PK, Name, TutorID FK)

StudentID	Name	TutorID
101	Alice	T01
102	Bob	T02
103	Carol	T01

Tutor (TutorID PK, TutorName)

TutorID	TutorName
T01	Dr Smith
T02	Ms Jones

SQL — SELECT Queries

Retrieving Data from Tables

-- Basic SELECT
SELECT Name, TutorID
FROM Student
WHERE TutorID = 'T01';

-- SELECT ALL columns
SELECT * FROM Student;

-- ORDER BY
SELECT Name FROM Student
ORDER BY Name ASC;

-- JOIN two tables
SELECT Student.Name, Tutor.TutorName
FROM Student
INNER JOIN Tutor
ON Student.TutorID = Tutor.TutorID;

-- GROUP BY + COUNT
SELECT TutorID, COUNT(*) AS NumStudents
FROM Student
GROUP BY TutorID;

SQL CLAUSE ORDER

SELECT columns
FROM table
INNER JOIN table2 ON key
WHERE condition
GROUP BY column
HAVING condition
ORDER BY column ASC/DESC;

DATA MANIPULATION (DML)

-- INSERT
INSERT INTO Student VALUES
(104, 'Dave', 'T02');

-- UPDATE
UPDATE Student
SET TutorID = 'T01'
WHERE StudentID = 104;

-- DELETE
DELETE FROM Student
WHERE StudentID = 104;

Exam Practice

Cambridge-style questions

Question 1

A database has tables: Product(ProductID, ProductName, Price, SupplierID) and Supplier(SupplierID, SupplierName). Write SQL to retrieve the ProductName and SupplierName of all products with a Price greater than 50.00, ordered by Price descending. [4]

1

SELECT Product.ProductName, Supplier.SupplierName — correct columns selected from correct tables

1

FROM Product INNER JOIN Supplier ON Product.SupplierID = Supplier.SupplierID — correct JOIN condition linking the tables on the foreign/primary key

1

WHERE Price > 50.00 — correct filter condition

1

ORDER BY Price DESC — correct ordering with DESC keyword

Common Mistakes

Don't lose easy marks

1

Forgetting the ON clause in a JOIN — INNER JOIN without ON gives a Cartesian product (every combination of all rows from both tables). Always specify which columns link the tables: ON Table1.FK = Table2.PK.

2

Using WHERE instead of HAVING for aggregate conditions — WHERE filters rows BEFORE grouping. HAVING filters groups AFTER GROUP BY. To filter by a COUNT or SUM, you MUST use HAVING, not WHERE.

3

Ambiguous column names without table prefix — when two tables share a column name (e.g. both have 'Name'), you MUST qualify with the table name: Student.Name, NOT just Name. Omitting this causes an ambiguity error.

Topic Summary — 4.5.1

What You Need to Know

RELATIONAL CONCEPTS

Table = relation. PK = unique row identifier. FK = reference to another table's PK. Referential integrity: FK must match existing PK. Reduces redundancy.

SQL CLAUSE ORDER

SELECT → FROM → INNER JOIN ON → WHERE → GROUP BY → HAVING → ORDER BY ASC/DESC. Know INSERT INTO, UPDATE SET WHERE, DELETE FROM WHERE.

KEY DISTINCTIONS

WHERE filters rows before grouping. HAVING filters after GROUP BY. INNER JOIN needs ON clause. Qualify ambiguous column names (Table.Column). SELECT * gets all columns.

CSZone

Next Video

4.5.2

Normalisation

1NF · 2NF · 3NF · Anomalies · Dependencies

Head to CSZone.co.uk for the complete worksheet, quiz, and interactive tools