A-Level Computer Science 9618 / Topic 8 / AS

Query and maintain.

With the tables built, SQL's data manipulation language does the daily work: totals and averages per group, questions that need two linked tables at once, and the inserts, updates and deletes that keep the data current. Every output on this page was checked by running the query.

Full-screen labDatabases and SQL lab → Topic 8All databases lessons →

The key idea

A query uses SELECT fields FROM table, an optional WHERE condition, GROUP BY to make one output row per group, and ORDER BY to sort, in that order. The aggregate functions SUM, COUNT and AVG work on a whole column, or on each group when GROUP BY is used. INNER JOIN ... ON combines two tables, outputting a row for each pair of records whose linking fields match. Data is maintained with INSERT INTO, UPDATE ... SET ... WHERE and DELETE FROM ... WHERE.

Fig. 1: INNER JOIN pairs each loan with the member whose MemberID matches

Section 01

Totals per group.

Every example in this lesson uses the library tables below. Start with one table: SUM, COUNT and AVG reduce a column to a single value, and GROUP BY repeats that for each group of records.

MEMBER
MemberID	FirstName	Surname	FormGroup
M01	Aisha	Khan	12A
M02	Ben	Okafor	12B
M03	Chloe	Wang	12A
M04	Dev	Patel	13C

BOOK
BookID	Title	Author	Genre	Price
B101	Dune	Herbert	SciFi	8.50
B102	Emma	Austen	Classic	6.00
B103	Neuromancer	Gibson	SciFi	10.50
B104	Rebecca	du Maurier	Classic	7.00
B105	Holes	Sachar	Adventure	12.00

LOAN
LoanID	BookID	MemberID	LoanDate	Returned
L01	B101	M01	2026-09-01	TRUE
L02	B103	M02	2026-09-02	FALSE
L03	B102	M01	2026-09-03	FALSE
L04	B105	M03	2026-09-03	TRUE
L05	B101	M04	2026-09-05	FALSE
L06	B104	M01	2026-09-06	FALSE

SELECT Genre, COUNT(BookID)
FROM Book
GROUP BY Genre
ORDER BY Genre;

Output: Adventure 1, Classic 2, SciFi 2. GROUP BY puts the five books into three groups and COUNT counts each group.
Order of clauses: SELECT, FROM, WHERE, GROUP BY, ORDER BY. WHERE removes records before they are grouped.

Worked example AVG per group

Give the output of:

SELECT Genre, AVG(Price)
FROM Book
GROUP BY Genre
ORDER BY Genre;

Adventure: Holes 12.00, so the average is 12.00.
Classic: Emma 6.00 and Rebecca 7.00, average (6.00 + 7.00) / 2 = 6.50.
SciFi: Dune 8.50 and Neuromancer 10.50, average 19.00 / 2 = 9.50.
Sorted alphabetically by Genre.

Answer: Adventure 12.00; Classic 6.50; SciFi 9.50

Worked example WHERE before GROUP BY

Give the output of:

SELECT Genre, COUNT(BookID)
FROM Book
WHERE Price > 7
GROUP BY Genre
ORDER BY Genre;

Price > 7 keeps Dune 8.50, Neuromancer 10.50 and Holes 12.00. Emma 6.00 and Rebecca 7.00 are removed (7.00 is not greater than 7).
Group what is left: Adventure has 1 book, SciFi has 2.
Classic has no records left, so it does not appear at all.

Answer: Adventure 1; SciFi 2

Your turn

Write a query to output the total price of all the SciFi books, and give its output.

SELECT SUM(Price)
FROM Book
WHERE Genre = 'SciFi';

Output: 19.00 (8.50 + 10.50)

Examiner trap

Every field in the SELECT list that is not inside SUM, COUNT or AVG must appear in the GROUP BY. SELECT Title, COUNT(BookID) ... GROUP BY Genre is wrong.

Section 02

Joining two tables.

In the lab's relational database tab, run the first example: an INNER JOIN of MEMBERSHIP and STUDENT. Each output row pairs a membership with the student whose StudentID matches.

SELECT Loan.LoanID, Member.FirstName
FROM Loan
INNER JOIN Member ON Loan.MemberID = Member.MemberID
WHERE Loan.Returned = FALSE
ORDER BY Loan.LoanID;

ON gives the join condition: the foreign key in one table equals the primary key in the other.
Table.Field notation names which table a field comes from. It is essential for a field such as MemberID that is in both tables, and good practice everywhere.
Output (Fig. 1): L02 Ben, L03 Aisha, L05 Dev, L06 Aisha. Aisha appears twice because she has two loans not yet returned.

Worked example Join and group

Give the output of:

SELECT Book.Title, COUNT(Loan.LoanID)
FROM Book
INNER JOIN Loan ON Book.BookID = Loan.BookID
GROUP BY Book.Title
ORDER BY Book.Title;

Match each loan to its book: L01 and L05 are Dune (B101), L02 Neuromancer, L03 Emma, L04 Holes, L06 Rebecca.
Group by title and count the loans in each group: Dune 2, every other title 1.
Sort by title, A to Z.

Answer: Dune 2; Emma 1; Holes 1; Neuromancer 1; Rebecca 1

Your turn

Write a query to output the Title and LoanDate of every book borrowed by member M01, earliest first.

SELECT Book.Title, Loan.LoanDate
FROM Book
INNER JOIN Loan ON Book.BookID = Loan.BookID
WHERE Loan.MemberID = 'M01'
ORDER BY Loan.LoanDate;

Output: Dune 2026-09-01; Emma 2026-09-03; Rebecca 2026-09-06

Examiner trap

An INNER JOIN only outputs records that have a match. A book that has never been lent does not appear in a join of BOOK and LOAN at all.

Stage 1 · Learn

Check what the sim just showed you

Four quick checks. Each correct answer earns XP and records this skill on your profile.

Quick check+10 XP

What does GROUP BY Genre do in a query on BOOK?

Quick check+10 XP

What is the output of SELECT COUNT(LoanID) FROM Loan WHERE MemberID = 'M01'; on the LOAN table?

Quick check+10 XP

In the lab's INNER JOIN example, why can a student's name appear more than once in the output?

Quick check+10 XP

Which statement changes the value stored in a field of existing records?

Section 03

INSERT, UPDATE and DELETE.

In the lab's DDL and DML tab, run "Insert records", "Update records" and "Delete records" and watch the changed rows highlight. Then try the "Duplicate key error" example.

INSERT INTO Book
VALUES ('B106', 'Wonder', 'Palacio', 'Adventure', 9.00);

INSERT INTO Member (MemberID, FirstName, Surname)
VALUES ('M05', 'Eli', 'Brown');

UPDATE Loan
SET Returned = TRUE
WHERE LoanID = 'L02';

DELETE FROM Loan
WHERE Returned = TRUE;

INSERT INTO ... VALUES: one value per field, in the table's field order. If you list the fields after the table name, only those are filled (M05's FormGroup is left empty).
UPDATE ... SET ... WHERE: changes the named field in every record that meets the condition.
DELETE FROM ... WHERE: removes every record that meets the condition. Run in order, the UPDATE marks L02 as returned, so the DELETE removes L01, L02 and L04, leaving L03, L05 and L06.
Values: text and dates go in quotes ('B106', '2026-09-07'); numbers and TRUE/FALSE do not. Follow the date format used in the question.

Worked example Write the maintenance script

Write SQL to (a) record a new loan L07 of book B105 to member M02 on 7 September 2026, not yet returned, and (b) change the price of Dune to 9.00.

(a) A new record, so INSERT INTO Loan VALUES with five values in the order LoanID, BookID, MemberID, LoanDate, Returned.
(b) A change to an existing record, so UPDATE Book SET Price = 9.00 with a WHERE on the primary key, so only Dune changes.

Answer:

INSERT INTO Loan
VALUES ('L07', 'B105', 'M02', '2026-09-07', FALSE);

UPDATE Book
SET Price = 9.00
WHERE BookID = 'B101';

Examiner trap

UPDATE or DELETE without a WHERE clause changes or deletes every record in the table. Always check the condition, and use the primary key when only one record should change.

Stage 2 · Exam

Exam-style questions

Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.

Finish the four checks above to unlock the exam questions

Exam style+20 XP

What is the output of SELECT AVG(Price) FROM Book WHERE Price >= 7; on the BOOK table?

Exam style+20 XP

Which statement removes every loan made by member M04?

Exam style+20 XP

SELECT Member.Surname, COUNT(Loan.LoanID) FROM Member INNER JOIN Loan ON Member.MemberID = Loan.MemberID GROUP BY Member.Surname; How many rows are output?

Exam style+20 XP

Which clause order is correct?

Skill unlocked

SQL data manipulation language, mastered.

This skill is now recorded on your profile. Keep the chain going.

-Rank -Level -Score -Topics

Go deeper · practice

Six original Cambridge-style questions

SQL queries on one or two tables with WHERE, ORDER BY, GROUP BY, INNER JOIN, SUM, COUNT and AVG, and maintaining data with INSERT INTO, UPDATE and DELETE FROM.