SQL: SELECT, INSERT, UPDATE, DELETE – GCSE Computer Science exam questions with mark scheme

1Grade 4–53 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

Write an SQL query to display the first name and surname of every member who lives in Leeds, in alphabetical order of surname. [3]

SELECT FirstName, Surname (either order; allow Members.FirstName etc.; reject SELECT *)(1)
FROM Members WHERE Town = 'Leeds' (allow double quotes; ignore case of keywords)(1)
ORDER BY Surname (allow ASC included or omitted; reject DESC)(1)
Answer: SELECT FirstName, Surname FROM Members WHERE Town = 'Leeds' ORDER BY Surname ASC – returns Femi Adeyemi, Chloe Evans, Amira Khan
Examiner tip: Text values in a WHERE clause need quotation marks: Town = 'Leeds'.

2Grade 6–73 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

Give the results of the following SQL query. [3]

SELECT MemberID, Surname
FROM Members
WHERE JoinYear >= 2022 AND Town <> 'York'
ORDER BY Surname DESC

Correct three records only: 103 Evans, 105 Morgan, 106 Adeyemi (no 102 Clarke)(1)
Only the MemberID and Surname fields shown(1)
In the order 105 Morgan, 103 Evans, 106 Adeyemi(1)
Answer: 105 Morgan; 103 Evans; 106 Adeyemi
Examiner tip: <> means 'not equal to' – Ben Clarke joined in 2023 but lives in York, so he is excluded.

3Grade 6–74 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

(a) Write an SQL statement to add a new booking: booking B07 for member 104 to attend Pilates on Sat. [2]

(b) Member 104 has moved to Wakefield. Write an SQL statement to update their record. [2]

(a) INSERT INTO Bookings (allow with field list (BookingID, MemberID, ClassName, Day))(1)
(a) VALUES ('B07', 104, 'Pilates', 'Sat') with values in the same order as the fields; allow '104' in quotes; allow double quotes(1)
(b) UPDATE Members SET Town = 'Wakefield'(1)
(b) WHERE MemberID = 104 (allow WHERE Surname = 'Okafor' / FirstName = 'Daniel' AND Surname = 'Okafor', as these identify only this record here)(1)
Answer: (a) INSERT INTO Bookings VALUES ('B07', 104, 'Pilates', 'Sat') (b) UPDATE Members SET Town = 'Wakefield' WHERE MemberID = 104
Examiner tip: Without the WHERE clause, UPDATE changes every member's town – the WHERE mark is the one most often lost.

4Grade 8–94 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

Write an SQL query to display the first name and surname of every member who has booked a Yoga class. [4]

SELECT FirstName, Surname; allow Members.FirstName, Members.Surname(1)
FROM Members, Bookings; allow FROM Members JOIN Bookings ON …(1)
WHERE Members.MemberID = Bookings.MemberID (tables linked on the key fields)(1)
AND ClassName = 'Yoga'; allow Bookings.ClassName(1)
Answer: SELECT Members.FirstName, Members.Surname FROM Members, Bookings WHERE Members.MemberID = Bookings.MemberID AND Bookings.ClassName = 'Yoga' – gives Amira Khan, Ben Clarke, Chloe Evans (in any order)
Examiner tip: When using two tables, the condition linking the primary key to the foreign key is a separate mark – don't forget it.

5Grade 6–74 marks

A gym stores data in a relational database with two tables, Members and Bookings.

Members

MemberID	FirstName	Surname	Town	JoinYear
101	Amira	Khan	Leeds	2021
102	Ben	Clarke	York	2023
103	Chloe	Evans	Leeds	2022
104	Daniel	Okafor	Hull	2020
105	Ella	Morgan	Bradford	2024
106	Femi	Adeyemi	Leeds	2023

Bookings

BookingID	MemberID	ClassName	Day
B01	101	Yoga	Mon
B02	103	Spin	Tue
B03	102	Yoga	Wed
B04	106	Pilates	Mon
B05	103	Yoga	Thu
B06	105	Spin	Fri

(a) Write an SQL statement to delete member 102 from the Members table. [2]

(b) Explain the problem this would cause in the Bookings table. [2]

(a) DELETE FROM Members (ignore case of keywords; ignore a * after DELETE)(1)
(a) WHERE MemberID = 102, as part of a DELETE statement (ignore quotes around 102 and a missing/extra semicolon; allow any condition that selects only this record in the data shown, e.g. WHERE FirstName = 'Ben' or WHERE Surname = 'Clarke')(1)
(b) Booking B03 still contains / refers to MemberID 102 (allow 'the booking made by member 102 / Ben Clarke is left in the Bookings table' without naming B03)(1)
(b) But member 102 no longer exists in Members, so the booking cannot be linked to a member / the data is inconsistent / it is not known who made the booking (allow: orphan record). Allow: the DBMS may refuse the deletion because a booking still refers to member 102(1)
Answer: (a) DELETE FROM Members WHERE MemberID = 102 (b) B03 is left pointing to a member that no longer exists
Examiner tip: Use the data: name the actual booking (B03) affected to make the explanation specific.

Book a free introductory session and we'll find exactly which step is costing you.