AS · Practice questions · SQL data manipulation language

Ask, join, change.

Six original Cambridge-style questions on SQL DML using the library tables, every output checked by running the query.

Original questions All questions on this page are original work, written in the Cambridge AS & A Level style. They are not from past papers. They test the same concepts and skills the syllabus rewards.

Keep these straight

Filter, group, sort.

Order: SELECT, FROM, (INNER JOIN ... ON), WHERE, GROUP BY, ORDER BY.
Aggregates: SUM, COUNT, AVG; every other SELECTed field must be in GROUP BY.
Joins: ON foreign key = primary key; use Table.Field names.
Maintenance: INSERT INTO ... VALUES; UPDATE ... SET ... WHERE; DELETE FROM ... WHERE.

Trace

[3 marks]

Using the MEMBER table in this lesson, give the output of:

SELECT FormGroup, COUNT(MemberID)
FROM Member
GROUP BY FormGroup
ORDER BY FormGroup;

12A 2 ✓
12B 1 ✓
13C 1 ✓

Write

[4 marks]

Write an SQL query to output the Title and Author of every SciFi book in the BOOK table, most expensive first. Give the output.

SELECT Title, Author FROM Book ✓
WHERE Genre = 'SciFi' ✓
ORDER BY Price DESC; ✓
Output: Neuromancer Gibson; Dune Herbert. ✓

Write

[4 marks]

Write an SQL query to output the total price of all the books that are currently out on loan (not returned). Give the output.

SELECT SUM(Book.Price) FROM Book ✓
INNER JOIN Loan ON Book.BookID = Loan.BookID ✓
WHERE Loan.Returned = FALSE; ✓
Output 32.00 (Neuromancer 10.50 + Emma 6.00 + Dune 8.50 + Rebecca 7.00). ✓

Write

[4 marks]

Write SQL statements to: (a) add member M05, Eli Brown, in form group 12B; (b) record that loan L05 has been returned; (c) delete every loan that has been returned.

(a) INSERT INTO Member VALUES ('M05', 'Eli', 'Brown', '12B'); ✓
(b) UPDATE Loan SET Returned = TRUE ✓
WHERE LoanID = 'L05'; ✓
(c) DELETE FROM Loan WHERE Returned = TRUE; ✓

Analysis

[3 marks]

A student wants the average price of each genre and writes:

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

Identify the error and give the output of the corrected query.

GROUP BY must come before ORDER BY. ✓
Corrected: ... FROM Book GROUP BY Genre ORDER BY Genre; ✓
Output: Adventure 12.00; Classic 6.50; SciFi 9.50. ✓

Trace

[2 marks]

The statement INSERT INTO Book VALUES ('B106', 'Wonder', 'Palacio', 'Adventure', 9.00); is run. Then the query SELECT AVG(Price) FROM Book WHERE Genre = 'Adventure'; is run. Give the output and show your working.

The Adventure books are now Holes 12.00 and Wonder 9.00. ✓
(12.00 + 9.00) / 2 = 10.50. ✓

Mark this once you have attempted all six and checked your working. It records a Practiced badge on the topic and adds a one-time bonus. Revealing the solutions alone does not count.