AQA 7517 · Paper 2 · 4.10.3

SQL — Structured
Query Language

SELECT · WHERE · JOIN · INSERT · UPDATE · DELETE · ORDER BY · GROUP BY

WHAT YOU'LL LEARN

Full AQA SQL syntax: SELECT, WHERE, JOIN, INSERT, UPDATE, DELETE, ORDER BY, COUNT, SUM

AQA SPEC LINK

4.10.3 — SQL: SELECT, FROM, WHERE, ORDER BY, GROUP BY, JOIN, INSERT, UPDATE, DELETE

SELECT

SELECT … FROM … WHERE

SELECT FirstName, LastName, DOB
FROM Student
WHERE DOB > '2006-01-01'
ORDER BY LastName ASC;

SELECT — choose which columns to return (* = all columns)

FROM — specify the table

WHERE — filter rows using conditions (=, <, >, <>, LIKE, AND, OR, NOT)

ORDER BY — sort results; ASC = ascending (default), DESC = descending

WHERE Conditions

WHERE Clause Operators

-- Multiple conditions
SELECT * FROM Student
WHERE LastName = 'Smith' AND Age > 16;

-- Pattern matching
SELECT * FROM Student
WHERE Email LIKE '%@school.ac.uk';

LIKE with wildcards: % = any sequence of characters; _ = exactly one character

Operators: = < > <> (not equal) <= >= AND OR NOT BETWEEN IN

JOIN

JOIN — Combining Tables

SELECT Student.FirstName, Course.Title
FROM Student
INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID
INNER JOIN Course ON Enrolment.CourseID = Course.CourseID
WHERE Course.Title = 'Computer Science';

INNER JOIN — returns only records that have matching values in both tables

ON specifies the matching condition (usually FK = PK)

Can chain multiple JOINs to link several tables together

Aggregate Functions

COUNT, SUM, AVG, MAX, MIN

-- Count students per course
SELECT CourseID, COUNT(StudentID) AS NumStudents
FROM Enrolment
GROUP BY CourseID
HAVING COUNT(StudentID) > 5;

GROUP BY — groups rows with the same value in a column; used with aggregate functions

HAVING — filters groups (like WHERE but for grouped results)

Functions: COUNT() · SUM() · AVG() · MAX() · MIN()

INSERT

INSERT INTO

-- Insert a new student
INSERT INTO Student (StudentID, FirstName, LastName, DOB)
VALUES ('S010', 'Layla', 'Ahmed', '2007-06-15');

Specify column names in parentheses after the table name

VALUES must match the order and data types of the columns listed

String values in single quotes; numbers without quotes

UPDATE & DELETE

UPDATE and DELETE

-- Update a student's email
UPDATE Student
SET Email = 'new@email.com'
WHERE StudentID = 'S010';

-- Delete a student record
DELETE FROM Student
WHERE StudentID = 'S010';

⚠️ Always include WHERE — without it, UPDATE or DELETE affects every row in the table!

CREATE TABLE

CREATE TABLE (DDL)

CREATE TABLE Student (
  StudentID VARCHAR(10) PRIMARY KEY,
  FirstName VARCHAR(50) NOT NULL,
  LastName VARCHAR(50) NOT NULL,
  DOB DATE,
  TeacherID VARCHAR(10) REFERENCES Teacher(TeacherID)
);

DDL (Data Definition Language) — used to create/modify/delete table structures (CREATE, ALTER, DROP)

REFERENCES enforces referential integrity (foreign key constraint)

AQA Exam Style

Practice Question

AQA 7517 — Paper 2 Style

A database has tables: Student(StudentID, Name, Age) and Course(CourseID, Title, TeacherID) and Enrolment(StudentID, CourseID).

(a) Write SQL to find the names of all students aged over 17, sorted by name. [3]
(b) Write SQL to find the course titles that student 'S005' is enrolled on. [4]
(c) Write SQL to update the Title of CourseID 'C03' to 'Advanced Computing'. [2]

[9 marks]

3 marks

SELECT Name FROM Student [1] WHERE Age > 17 [1] ORDER BY Name ASC; [1]

4 marks

SELECT Course.Title FROM Course [1] INNER JOIN Enrolment ON Course.CourseID = Enrolment.CourseID [1] INNER JOIN Student ON Enrolment.StudentID = Student.StudentID [1] WHERE Student.StudentID = 'S005'; [1]

2 marks

UPDATE Course SET Title = 'Advanced Computing' [1] WHERE CourseID = 'C03'; [1]

Summary

Key SQL to Know for AQA

SELECT … FROM … WHERE … ORDER BY — retrieve filtered, sorted data

INNER JOIN … ON — combine tables via matching FK/PK

GROUP BY … HAVING — aggregate results; COUNT, SUM, AVG, MAX, MIN

INSERT INTO … VALUES — add new records

UPDATE … SET … WHERE — modify existing records (always use WHERE!)

DELETE FROM … WHERE — remove records (always use WHERE!)

🎉 Lesson complete — move to the quiz!