AQA 7517 · A-Level Computer Science · ~16 min read
What is SQL?
SQL (Structured Query Language) is the standard language for querying and manipulating relational databases. SQL has two main categories:
DDL (Data Definition Language): defines database structure — CREATE TABLE, ALTER TABLE, DROP TABLE
DML (Data Manipulation Language): works with data — SELECT, INSERT, UPDATE, DELETE
SELECT — Querying Data
Basic syntax:
SELECT column1, column2 FROM TableName WHERE condition ORDER BY column1 ASC/DESC;
Examples:
-- Select all columns from Student table SELECT * FROM Student;
-- Students in Year 12, alphabetically by last name SELECT FirstName, LastName FROM Student WHERE YearGroup = 12 ORDER BY LastName ASC;
WHERE Clause — Conditions
Operator
Meaning
Example
=
Equal to
WHERE Grade = 'A'
<> or !=
Not equal to
WHERE Status <> 'Active'
<, >, <=, >=
Comparison
WHERE Age >= 16
LIKE
Pattern match
WHERE Name LIKE 'Sm%'
BETWEEN
Range
WHERE Salary BETWEEN 20000 AND 50000
AND / OR / NOT
Logical operators
WHERE Age > 16 AND Gender = 'F'
IN
Value in a list
WHERE Grade IN ('A','B')
The % wildcard matches any sequence of characters in LIKE. _ matches exactly one character.
JOIN — Combining Tables
A JOIN links two tables using a matching key:
SELECT Student.FirstName, Course.CourseName FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID INNER JOIN Course ON Enrolment.CourseID = Course.CourseID WHERE Course.CourseName = 'Computer Science';
INNER JOIN: only rows where both tables have a matching key. LEFT JOIN: all rows from the left table, NULLs where no match in the right.
INSERT, UPDATE, DELETE
-- Insert a new student INSERT INTO Student (StudentID, FirstName, LastName, YearGroup) VALUES (1042, 'Amira', 'Patel', 12);
-- Update a student's year group UPDATE Student SET YearGroup = 13 WHERE StudentID = 1042;
-- Delete a student record DELETE FROM Student WHERE StudentID = 1042;
CREATE TABLE
CREATE TABLE Student ( StudentID INTEGER PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DOB DATE, TutorGroupID INTEGER REFERENCES TutorGroup(TutorGroupID) );
Aggregate Functions
Function
Purpose
Example
COUNT(*)
Count rows
SELECT COUNT(*) FROM Student;
SUM(col)
Sum of values
SELECT SUM(Salary) FROM Employee;
AVG(col)
Average
SELECT AVG(Grade) FROM Result;
MAX(col)
Maximum value
SELECT MAX(Age) FROM Student;
MIN(col)
Minimum value
SELECT MIN(Price) FROM Product;
-- Group by and having SELECT CourseID, COUNT(*) AS NumStudents FROM Enrolment GROUP BY CourseID HAVING COUNT(*) > 10;
Exam tip: AQA 7517 SQL questions are common and highly specific. You MUST be able to write SELECT with WHERE, ORDER BY, LIKE, AND/OR, JOIN (INNER), and aggregate functions. Know INSERT, UPDATE, DELETE syntax. For CREATE TABLE, include data types, PRIMARY KEY, NOT NULL, FOREIGN KEY. GROUP BY groups rows; HAVING filters groups (like WHERE but for aggregates). Use % for wildcard in LIKE.
▶
Click through the slides at your own pace. Use arrow keys or click to advance.
Q1Write an SQL query to select the FirstName and LastName of all students in Year 13, ordered alphabetically by LastName.[3]
✅ Mark scheme
Mark scheme
SELECT FirstName, LastName [1] FROM Student [1] WHERE YearGroup = 13 [1] ORDER BY LastName ASC; [1] (ASC optional — default is ascending). Award 1 mark per correct clause.
Q2Write an SQL query to find all students whose last name starts with 'Sm'. Use a wildcard.[2]
✅ Mark scheme
Mark scheme
SELECT * (or specific columns) FROM Student [1] WHERE LastName LIKE 'Sm%' [1]; correct use of LIKE and % wildcard [1].
Q3Write an SQL query to retrieve the name of every student enrolled on the course 'Computer Science' using an INNER JOIN.[4]
✅ Mark scheme
Mark scheme
SELECT Student.FirstName, Student.LastName [1] FROM Student [1] INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID [1] INNER JOIN Course ON Enrolment.CourseID = Course.CourseID [1] WHERE Course.CourseName = 'Computer Science'; [1]
Q4Write an SQL INSERT statement to add a new student: StudentID 1099, FirstName 'Leila', LastName 'Hassan', YearGroup 12.[2]
✅ Mark scheme
Mark scheme
INSERT INTO Student (StudentID, FirstName, LastName, YearGroup) [1] VALUES (1099, 'Leila', 'Hassan', 12); [1] Correct column list and matching values in same order [1].
Q5Write an SQL UPDATE statement to change student 1042's YearGroup to 13.[2]
✅ Mark scheme
Mark scheme
UPDATE Student [1] SET YearGroup = 13 [1] WHERE StudentID = 1042; [1]
Q6Write an SQL query to count the number of students enrolled on each course. Show CourseID and the count, only for courses with more than 15 students.[3]
✅ Mark scheme
Mark scheme
SELECT CourseID, COUNT(*) AS NumStudents [1] FROM Enrolment [1] GROUP BY CourseID [1] HAVING COUNT(*) > 15; [1]
Q7Explain the difference between WHERE and HAVING in SQL.[2]
✅ Mark scheme
Mark scheme
WHERE filters individual rows before grouping [1]; HAVING filters groups after GROUP BY has been applied [1]; HAVING is used with aggregate functions (COUNT, SUM, AVG) — WHERE cannot be used with aggregate functions directly [1].
Q8Write a CREATE TABLE statement for a Course table with: CourseID (integer, primary key), CourseName (varchar 100, not null), TeacherID (integer, foreign key referencing Teacher).[2]
Q1Which SQL clause is used to filter rows before grouping?
Q2Which SQL statement correctly uses a wildcard to find all names starting with 'Jo'?
Q3An INNER JOIN returns:
Q4What does COUNT(*) return?
Q5CREATE TABLE is part of which SQL category?
Section B — SQL Writing [5 marks]
Q6Write SQL to delete the student with StudentID = 500.
Mark schemeDELETE FROM Student WHERE StudentID = 500; [1 mark]
Q7Write SQL to find the average GradeAverage of all students.
Mark schemeSELECT AVG(GradeAverage) FROM Student; [1 mark]
Q8Write SQL to select all students in Year 12 OR Year 13.
Mark schemeSELECT * FROM Student WHERE YearGroup = 12 OR YearGroup = 13; [1] OR: SELECT * FROM Student WHERE YearGroup IN (12, 13); [1]
Q9What SQL keyword is used to sort results in descending order?
Mark schemeDESC (used with ORDER BY: ORDER BY LastName DESC) [1]
Q10Explain why GROUP BY is needed when using COUNT(*) on a per-course basis.
Mark schemeGROUP BY groups rows with the same value in the specified column [1]; COUNT(*) then counts within each group separately — giving a count per course rather than a total across all courses [1].