SQL and client–server databases – A-level Computer Science exam questions with mark scheme

1Standard4 marks

A table Book(BookID, Title, Genre, Price, Stock) stores books in a shop.

Write an SQL query to display the Title and Price of all Fantasy books costing less than 10, in descending order of price. [4]

SELECT Title, Price FROM Book(1)
WHERE Genre = 'Fantasy' (allow double quotes; allow the two conditions in either order)(1)
AND Price < 10 (reject <= 10; reject OR)(1)
ORDER BY Price DESC (reject ORDER BY without DESC)(1)
Answer: SELECT Title, Price FROM Book WHERE Genre = 'Fantasy' AND Price < 10 ORDER BY Price DESC;
Examiner tip: Put ORDER BY last, and DESC is needed since the default is ascending.

2Stretch6 marks

Tables: Student(StudentID, Name, FormID) and Result(ResultID, StudentID, Subject, Mark).

Write an SQL query to display each Subject and the average Mark of students in form '12B' only, showing only subjects whose average is greater than 60. [6]

SELECT Subject, AVG(Mark) (allow AVG(Result.Mark); allow an alias, e.g. AS AverageMark)(1)
FROM Student INNER JOIN Result (allow JOIN; allow the two tables in either order; allow FROM Student, Result with the join condition in the WHERE clause; allow FROM Result with a subquery instead of a join, e.g. WHERE StudentID IN (SELECT StudentID FROM Student WHERE FormID = '12B'), for this mark and the next)(1)
ON Student.StudentID = Result.StudentID (allow in WHERE … AND …; StudentID must be qualified by table name or a table alias in a join, e.g. S.StudentID = R.StudentID; allow StudentID IN (SELECT StudentID FROM Student …) as the linking condition in a subquery answer)(1)
WHERE FormID = '12B' (allow Student.FormID; allow double quotes; allow inside a subquery; allow as an extra join condition, e.g. ON … AND FormID = '12B')(1)
GROUP BY Subject(1)
HAVING AVG(Mark) > 60 (reject AVG(Mark) > 60 in the WHERE clause)(1)
Answer: SELECT Subject, AVG(Mark) FROM Student INNER JOIN Result ON Student.StudentID = Result.StudentID WHERE FormID = '12B' GROUP BY Subject HAVING AVG(Mark) > 60;
Examiner tip: Use WHERE to filter rows before grouping and HAVING to filter the groups; AVG cannot go in WHERE.

3Standard4 marks

Using the table Book(BookID, Title, Genre, Price, Stock):

(a) Write an SQL statement to add the book with BookID 6, title 'Sky Fort', genre 'Fantasy', price 7.50 and stock 12. [2]

(b) Write an SQL statement to increase the price of every Fantasy book by 10%. [2]

(a) INSERT INTO Book VALUES … (allow a column list (BookID, Title, Genre, Price, Stock) before VALUES)(1)
(a) (6, 'Sky Fort', 'Fantasy', 7.50, 12) in the column order, with the text values in quotes (allow 7.5)(1)
(b) UPDATE Book SET Price = Price * 1.1 (allow Price + Price * 0.1, Price * 110 / 100)(1)
(b) WHERE Genre = 'Fantasy'(1)
Answer: (a) INSERT INTO Book VALUES (6,'Sky Fort','Fantasy',7.50,12); (b) UPDATE Book SET Price = Price * 1.1 WHERE Genre = 'Fantasy';
Examiner tip: Forgetting WHERE in an UPDATE changes every row in the table.

Stuck on SQL and client–server databases?

Get it sorted in a free 30-minute session

We work through your weakest questions on this topic on Zoom. I reply the same day.

Free 30-min session on this topic Send me 5 questions a day

4Hard5 marks

(a) In a school database, Student.FormID is a foreign key that refers to Form.FormID. Explain what is meant by referential integrity and give an example of an action that would break it in this database. [2]

(b) A bank uses a client–server database. Two clerks open the same account record at the same time and each makes a change to the balance. Explain how record locking prevents one of the updates from being lost. [2]

(c) A transaction that transfers money between two accounts must lock both account records. Explain how this use of record locks could lead to deadlock. [1]

(a) Every foreign key value must match an existing primary key value in the referenced table / a foreign key cannot refer to a record that does not exist(1)
(a) Example: adding a student with a FormID (e.g. '12Z') that is not in the Form table, or deleting/changing a Form record while students still refer to it(1)
(b) The first clerk's transaction locks the record, so the second clerk cannot access/update it until the lock is released(1)
(b) The second change is then made to the already-updated balance, so the first update is not overwritten (lost update problem avoided)(1)
(c) Transaction 1 locks account X and waits for account Y, while transaction 2 has locked Y and waits for X, so neither can continue (each holds a lock the other needs)(1)
Answer: (a) Every foreign key value must match an existing primary key value; e.g. adding a student whose FormID is not in Form. (b) The first transaction locks the record so the second must wait, then works on the updated value, so no update is lost. (c) Each transaction holds one lock and waits for the other's lock, so neither can proceed.
Examiner tip: Deadlock needs two records: each transaction holds one lock and waits for the other – one user waiting for one lock is not deadlock.

Stuck on this topic?

Book a free 30-minute session on WhatsApp and we'll find exactly which step is costing you the marks. Or build a timed paper on it.

Free 30-minute session