4.5.1 SQL & Relational Databases — Cambridge 9618

Relational Database Fundamentals

A relational database stores data in tables (also called relations). Each table represents one entity type. Data in different tables is linked using keys.

Table / Relation

A grid of rows and columns representing one entity type. E.g. the Student table stores all student data.

Row / Tuple / Record

One instance of an entity. One student = one row. All attributes of that student are in that row.

Column / Attribute / Field

One property of the entity. E.g. StudentID, Name, DateOfBirth. Each column has a data type.

Primary Key (PK)

A column (or combination) that UNIQUELY identifies each row. No two rows can have the same PK value. Cannot be NULL. E.g. StudentID.

Foreign Key (FK)

A column in one table that references the Primary Key of another table. Creates a link between tables. Must match an existing PK value (referential integrity).

Example: Student and Enrolment tables

📋 Student

StudentID	Name	Age	CourseID
S001	Alice	17	CS
S002	Bob	18	MATH
S003	Carol	17	CS

StudentID = Primary Key

CourseID = Foreign Key → Course table

SQL — Structured Query Language

SQL (Structured Query Language) is the standard language for interacting with relational databases. Cambridge 9618 requires writing SQL queries for SELECT, INSERT, UPDATE, DELETE, and CREATE TABLE.

SELECT — retrieving data

-- Basic SELECT: all columns
SELECT * FROM Student;

-- SELECT specific columns with WHERE filter
SELECT Name, Age
FROM Student
WHERE Age >= 18;

-- ORDER BY: sort results
SELECT Name, Age
FROM Student
ORDER BY Age DESC;

-- WHERE with multiple conditions
SELECT * FROM Student
WHERE Age = 17 AND CourseID = 'CS';

-- LIKE: pattern matching % = any characters
SELECT * FROM Student
WHERE Name LIKE 'A%'; -- Names starting with A

Aggregate Functions and GROUP BY

-- COUNT: count rows
SELECT COUNT(*) AS TotalStudents FROM Student;

-- AVG, SUM, MIN, MAX
SELECT AVG(Age), MIN(Age), MAX(Age) FROM Student;

-- GROUP BY: aggregate per group
SELECT CourseID, COUNT(*) AS NumStudents
FROM Student
GROUP BY CourseID;

-- HAVING: filter groups (like WHERE but for groups)
SELECT CourseID, COUNT(*) AS NumStudents
FROM Student
GROUP BY CourseID
HAVING COUNT(*) > 1;

JOIN — combining tables

JOIN links data from multiple tables using the relationship between primary and foreign keys.

-- Tables: Student(StudentID, Name, CourseID)
-- Course(CourseID, CourseName, Lecturer)

-- INNER JOIN: only rows with matches in BOTH tables
SELECT Student.Name, Course.CourseName
FROM Student
INNER JOIN Course ON Student.CourseID = Course.CourseID;

-- LEFT JOIN: all rows from left table + matching from right
SELECT Student.Name, Course.CourseName
FROM Student
LEFT JOIN Course ON Student.CourseID = Course.CourseID;

Name	CourseName
Alice	Computer Science
Bob	Mathematics
Carol	Computer Science

INSERT, UPDATE, DELETE

-- INSERT: add a new row
INSERT INTO Student (StudentID, Name, Age, CourseID)
VALUES ('S004', 'Dave', 17, 'MATH');

-- UPDATE: modify existing rows
UPDATE Student
SET Age = 19
WHERE StudentID = 'S002';

-- DELETE: remove rows
DELETE FROM Student
WHERE StudentID = 'S001';

CREATE TABLE — defining structure

CREATE TABLE Student (
  StudentID  VARCHAR(10)  PRIMARY KEY,
  Name  VARCHAR(50)  NOT NULL,
  Age  INTEGER,
  CourseID  VARCHAR(10),
  FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

-- Common data types:
-- INTEGER / INT : whole numbers
-- VARCHAR(n) : variable-length text, max n chars
-- CHAR(n) : fixed-length text, exactly n chars
-- DATE : date value (YYYY-MM-DD)
-- BOOLEAN : TRUE or FALSE
-- REAL / FLOAT : decimal numbers

SQL Clauses Summary

Clause	Purpose	Example
SELECT	Choose which columns to return	SELECT Name, Age
FROM	Which table(s) to query	FROM Student
WHERE	Filter rows (conditions on data)	WHERE Age > 17
ORDER BY	Sort results (ASC or DESC)	ORDER BY Name ASC
GROUP BY	Group rows for aggregation	GROUP BY CourseID
HAVING	Filter groups (after GROUP BY)	HAVING COUNT(*) > 2
INNER JOIN	Combine tables — rows with matches in both	...JOIN Course ON ...
LEFT JOIN	All rows from left + matching from right (NULLs for no match)	LEFT JOIN Course ON ...
INSERT INTO	Add new row(s)	INSERT INTO Student VALUES ...
UPDATE ... SET	Modify existing row(s)	UPDATE Student SET Age=19
DELETE FROM	Remove row(s)	DELETE FROM Student WHERE ...

Cambridge 9618 exam tip: SQL queries on the exam almost always use SELECT with WHERE, often with ORDER BY or a JOIN. Always use a WHERE clause with UPDATE and DELETE — without it, ALL rows are affected! For JOINs, always specify table.column when column names are ambiguous (appear in multiple tables). HAVING filters groups (after GROUP BY) whereas WHERE filters individual rows (before grouping). String values are enclosed in single quotes 'like this'. Know all five aggregate functions: COUNT(*), SUM(col), AVG(col), MIN(col), MAX(col).

⚠️ Common Mistakes

UPDATE without WHERE — "UPDATE Student SET Age = 20;" updates EVERY row in the table. Always add WHERE to target specific rows: "UPDATE Student SET Age = 20 WHERE StudentID = 'S001';"
HAVING vs WHERE — WHERE filters ROWS before grouping; HAVING filters GROUPS after grouping. You cannot use aggregate functions (COUNT, SUM) in a WHERE clause — use HAVING instead.
INNER JOIN vs LEFT JOIN — INNER JOIN only returns rows with matches in BOTH tables. LEFT JOIN returns ALL rows from the left table, even if no match exists in the right table (the right-table columns are NULL for unmatched rows). Choose based on whether you want to exclude unmatched rows.
Forgetting quotes — string/text values need single quotes: WHERE Name = 'Alice'. Numeric values do NOT: WHERE Age = 17. Forgetting quotes is a very common syntax error.
Primary key vs Unique — PRIMARY KEY implies both NOT NULL and UNIQUE. A table can have only one PRIMARY KEY but multiple UNIQUE constraints. Foreign keys must reference the primary key (or unique key) of another table.

✅ Notes completed!

▶

Video coming soon

Click slide or press arrow keys to navigate

Worksheet — 4.5.1 SQL & Relational Databases

6 questions — write SQL queries · Cambridge 9618 standard

Tables: Product(ProductID, Name, Price, CategoryID) | Category(CategoryID, CategoryName)

Q1Write SQL to select the Name and Price of all products with a Price greater than 50, ordered by Price from highest to lowest.[4]

✅ Mark scheme

SELECT Name, Price [1]
FROM Product [1]
WHERE Price > 50 [1]
ORDER BY Price DESC; [1]

Note: > 50 not >= 50 (strictly greater than). DESC means descending (high to low). ASC is ascending (default). All 4 clauses required for full marks.

Q2Write SQL to count how many products are in each category. Show the CategoryID and the count. Only show categories with more than 3 products.[4]

✅ Mark scheme

SELECT CategoryID, COUNT(*) AS NumProducts [1]
FROM Product [1]
GROUP BY CategoryID [1]
HAVING COUNT(*) > 3; [1]

Note: HAVING (not WHERE) is required because we are filtering on an aggregate function COUNT(*). WHERE filters individual rows before grouping; HAVING filters groups after grouping. AS NumProducts is an alias — optional but good practice.

Q3Write SQL to retrieve the Name of each product along with its CategoryName, using a JOIN. Include all products even if they have no matching category.[4]

✅ Mark scheme

SELECT Product.Name, Category.CategoryName [1]
FROM Product [1]
LEFT JOIN Category ON Product.CategoryID = Category.CategoryID; [2]

Note: LEFT JOIN (not INNER JOIN) is required because we want ALL products including those with no matching category. INNER JOIN would exclude products where CategoryID doesn't exist in Category. The ON clause specifies the join condition — matching the foreign key to the primary key. Award 1 for LEFT JOIN keyword, 1 for correct ON condition.

Q4Write SQL to insert a new product: ProductID='P099', Name='Laptop', Price=899.99, CategoryID='ELEC'.[3]

✅ Mark scheme

INSERT INTO Product (ProductID, Name, Price, CategoryID) [1]
VALUES ('P099', 'Laptop', 899.99, 'ELEC'); [2]

Note: String values must be in single quotes ('P099', 'Laptop', 'ELEC'). Numeric value 899.99 does NOT get quotes. The column list and VALUES list must be in the same order. Award 1 for INSERT INTO ... column list, 1 for VALUES with correct string quotes, 1 for correct numeric value without quotes.

Q5Write SQL to update the price of all products in category 'ELEC' to reduce their price by 10% (multiply by 0.9).[3]

✅ Mark scheme

UPDATE Product [1]
SET Price = Price * 0.9 [1]
WHERE CategoryID = 'ELEC'; [1]

Note: Price = Price * 0.9 uses the current value to calculate the new value. WHERE is essential — without it ALL products would get reduced. String value 'ELEC' needs single quotes. All 3 lines required for full marks.

Q6Write the CREATE TABLE SQL statement for the Product table with appropriate data types and constraints: ProductID (primary key, max 10 chars), Name (text max 100 chars, cannot be null), Price (decimal), CategoryID (max 10 chars, references Category).[5]

✅ Mark scheme

CREATE TABLE Product ( [1]
  ProductID VARCHAR(10) PRIMARY KEY, [1]
  Name VARCHAR(100) NOT NULL, [1]
  Price REAL, [1]
  CategoryID VARCHAR(10),
  FOREIGN KEY (CategoryID) REFERENCES Category(CategoryID) [1]
);

Note: PRIMARY KEY implies NOT NULL + UNIQUE. NOT NULL on Name prevents empty entries. Price can be REAL, FLOAT, or DECIMAL — any numeric type with decimals. FOREIGN KEY must reference Category(CategoryID) — the primary key of the Category table. Award 1 per correct attribute definition up to 5 marks.

Q7A database has two tables: Orders(OrderID, CustomerID, TotalAmount) and Customers(CustomerID, Name, City). Write a SQL query to return the Name and TotalAmount for all orders where TotalAmount > 500, ordered by TotalAmount descending.[5]

✅ Mark scheme

SELECT Customers.Name, Orders.TotalAmount [1]; FROM Orders [1]; INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID [1]; WHERE Orders.TotalAmount > 500 [1]; ORDER BY Orders.TotalAmount DESC [1]. Accept equivalent correct SQL with table aliases.

Q8Explain the difference between DELETE FROM and DROP TABLE in SQL. Give a situation where each would be the appropriate command, and state what a transaction and ROLLBACK are used for in the context of SQL data manipulation.[5]

✅ Mark scheme

DELETE FROM removes rows from a table but the table structure remains; DROP TABLE removes the entire table including its structure [1]; DELETE situation: removing a specific customer's records while keeping the Customers table [1]; DROP TABLE situation: removing a temporary staging table that is no longer needed [1]; A transaction groups one or more SQL statements into an atomic unit — either all succeed or none take effect [1]; ROLLBACK undoes all changes made since the start of the transaction if an error occurs, restoring the database to its previous state [1].

Topic Quiz

Question 1 of 10

You scored

out of 10

Card 1 of 8

Click to reveal definition

🎉

All cards reviewed!

Term	Definition

Relational Database Fundamentals

Example: Student and Enrolment tables

SQL — Structured Query Language

SELECT — retrieving data

Aggregate Functions and GROUP BY

JOIN — combining tables

INSERT, UPDATE, DELETE

CREATE TABLE — defining structure

SQL Clauses Summary

Worksheet — 4.5.1 SQL & Relational Databases

Mini Test — 4.5.1 SQL