James Gillespie's High School · Higher Computing Science 2026–27
Completed:
📁 File in OneNote: Higher Computing Science → Database Design & Development → DDD8
Answer before the lesson begins — it's fine if you're unsure.
An aggregate function calculates a single summary value from a whole set of records: MIN (smallest), MAX (largest), AVG (average), SUM (total), and COUNT (number of records). These five are the only aggregate functions in the Higher specification. ROUND, covered later in this lesson, is a genuinely useful related function — but it isn't itself one of the five aggregate functions, and shouldn't be listed as a sixth if you're ever asked to name them.
A query cannot freely mix an aggregate field (like COUNT(classCode)) with a non-aggregate field (like firstName) in the same SELECT unless GROUP BY is used. Without grouping, SQL has no way to decide which single firstName value should sit alongside a count calculated across many rows — GROUP BY resolves this by collecting rows into one group per distinct value of the grouped field, so the aggregate calculates once per group instead of once overall.
A computed value is any result produced by processing existing data, rather than a value stored directly — this includes aggregate functions, but also simple arithmetic on ordinary fields, like calculating a total cost from feePerSession * numberOfSessions. A computed value becomes its own column in the results table, exactly as if it had been stored, even though it wasn't.
Without an alias, a computed value's column heading is the expression itself (e.g. AVG(feePerSession)), which is technically correct but not reader-friendly. AS [Readable Name] gives it a proper label — this project's house style always uses square brackets, e.g. AS [Average Fee], kept consistent across every query from here on.
SQL's clause order is fixed: SELECT, FROM, WHERE, GROUP BY, then ORDER BY last. Reversing GROUP BY and ORDER BY is a genuine syntax error, not just a style issue — a useful memory aid is GO: Group by, then Order by, always in that order.
A single SQL statement cannot nest one query's aggregate result directly inside another query's WHERE clause at Higher level — subqueries are explicitly outside the specification. Instead, "compare against an aggregate" problems (like "which class has the highest fee?") are solved as two separate, named queries: Query 1 calculates the single aggregate value; Query 2's WHERE clause then uses that value directly, once you know what it is.
AVG(feePerSession), wrapped in ROUND(..., 2).SELECT ROUND(AVG(feePerSession), 2) AS [Average Fee]
FROM Class;firstName, surname) mixed with an aggregate (COUNT(classCode)) — GROUP BY is required.instructorRef — every instructor's classes are counted separately.SELECT Instructor.firstName, Instructor.surname, COUNT(Class.classCode) AS [Number Of Classes]
FROM Instructor, Class
WHERE Instructor.instructorRef = Class.instructorRef
GROUP BY Instructor.instructorRef
ORDER BY [Number Of Classes] DESC;WHERE clause to find the matching class(es).-- Query 1
SELECT MAX(feePerSession) AS [Highest Fee]
FROM Class;
-- Query 2 (using Query 1's result, e.g. 12.50)
SELECT className, feePerSession
FROM Class
WHERE feePerSession = 12.50;Write a SQL statement to find the total number of bookings ever made, with the alias [Total Bookings].
SELECT COUNT(bookingRef) AS [Total Bookings]
FROM Booking;When a question describes a "compare against an aggregate" problem, write it explicitly as two labelled queries (Query 1, Query 2) rather than attempting a single nested statement — this is SQA's own examinable method, and a single-statement subquery answer may not gain full marks even if it technically works in some SQL products.
[Total Fees].SELECT SUM(feePerSession) AS [Total Fees]
FROM Class;[Earliest Join Date].SELECT MIN(dateJoined) AS [Earliest Join Date]
FROM Member;SELECT Class.className, COUNT(Booking.bookingRef) AS [Number Of Bookings] FROM Class, Booking WHERE Class.classCode = Booking.classCode GROUP BY Class.classCode;-- Query 1: find the highest per-member booking count
SELECT MAX(bookingCount) AS [Most Bookings]
FROM (
SELECT COUNT(bookingRef) AS bookingCount
FROM Booking
GROUP BY memberID
);
-- (or, per the two-query pattern taught this lesson, run the
-- GROUP BY query first, then read off the highest count manually: 5)
-- Query 2: find which member(s) made exactly that many bookings
SELECT Member.firstName, Member.surname, COUNT(Booking.bookingRef) AS [Number Of Bookings]
FROM Member, Booking
WHERE Member.memberID = Booking.memberID
GROUP BY Member.memberID
HAVING COUNT(Booking.bookingRef) = 5;📁 File this in OneNote under:
Higher Computing Science → Database Design & Development → DDD8
Suggested timing: 8 min warm up · 15 min notes (aggregates, GROUP BY, aliases, GO mnemonic) · 15 min worked examples, ideally live in DataGrip · 5 min now you try · 20 min Task Set B · 5–10 min Task Set C / review, if time allows.
Key misconception: pupils very commonly try to write ORDER BY before GROUP BY, or forget GROUP BY entirely when mixing aggregate and non-aggregate fields. The GO mnemonic (from Chris Meechan's materials) is worth repeating verbally every time a GROUP BY query is demonstrated.
Spec-accuracy note: do not let ROUND slip into an auto-checked "name the aggregate functions" answer — B1's expected answer is exactly MIN, MAX, AVG, SUM, COUNT, per the confirmed spec reading in ddd/DDD.md §1 and §9 item 3.
B6's model answer note: the "Query 1" shown for B6 demonstrates what a single nested subquery would look like, purely so pupils can see why it's excluded — the taught, examinable method is to run the inner GROUP BY query first, read off the highest count by eye, then hand-type that value into Query 2's HAVING clause, exactly as modelled in Example 3.
SQA command words covered: "describe", "exemplify", "use" (write working aggregate SQL), and "read and explain code" (B5).