SQL: DDL/DML, queries across two tables and updating data – IB Computer Science exam questions with mark scheme

1Core3 marks

Use the tables below.

Tutor
TutorID	TutorName
1	Mr Khan
2	Ms Reid
3	Mr Cole

Student
StudentID	Name	Year	TutorID
1	Aisha	12	1
2	Ben	13	2
3	Chloe	12	1
4	Dev	13	3
5	Elin	12	2
6	Femi	12	1

Result
StudentID	Subject	Score
1	CS	78
1	Maths	65
2	CS	55
2	Maths	70
3	CS	90
3	Maths	82
4	CS	48
5	CS	66
5	Maths	71
6	CS	72

Write an SQL query to list the names of all Year 12 students in reverse alphabetical order. [3]

SELECT Name FROM Student (allow Student.Name; do not accept SELECT * as the question asks for names only)(1)
WHERE Year = 12 (allow Year = '12')(1)
ORDER BY Name DESC (reject ORDER BY Name or ORDER BY Name ASC, which sort A to Z)(1)
Answer: SELECT Name FROM Student WHERE Year = 12 ORDER BY Name DESC; output Femi, Elin, Chloe, Aisha
Examiner tip: DESC is needed for reverse order; ORDER BY on its own sorts ascending (A to Z).

2Hard5 marks

Use the tables below.

Student
StudentID	Name	Year	TutorID
1	Aisha	12	1
2	Ben	13	2
3	Chloe	12	1
4	Dev	13	3
5	Elin	12	2
6	Femi	12	1

Result
StudentID	Subject	Score
1	CS	78
1	Maths	65
2	CS	55
2	Maths	70
3	CS	90
3	Maths	82
4	CS	48
5	CS	66
5	Maths	71
6	CS	72

Construct an SQL query to output the Name and CS Score of every student in tutor group 1 (TutorID = 1) who scored at least 75 in CS. [5]

SELECT Name, Score (allow Student.Name, Result.Score)(1)
FROM Student JOIN Result (accept INNER JOIN, or FROM Student, Result)(1)
ON Student.StudentID = Result.StudentID (accept the same condition in WHERE; accept USING (StudentID) or NATURAL JOIN; StudentID must be qualified with the table names or with table aliases, e.g. S.StudentID = R.StudentID)(1)
WHERE TutorID = 1(1)
AND Subject = 'CS' AND Score >= 75 (reject Score > 75)(1)
Answer: SELECT Name, Score FROM Student JOIN Result ON Student.StudentID = Result.StudentID WHERE TutorID = 1 AND Subject = 'CS' AND Score >= 75; output Aisha 78, Chloe 90
Examiner tip: Give the join condition; a join with no ON (or WHERE) condition pairs every student with every result.

3Standard5 marks

A hotel stores its bookings in the table below. BookingDate is stored as text in the form YYYY-MM-DD.

Booking
BookingID	RoomID	BookingDate	Guests
1001	4	2027-03-02	2
1002	7	2027-03-05	1
1003	4	2027-03-09	2
1004	12	2027-03-11	4
1005	7	2027-03-12	2

(a) Construct an SQL statement to add booking 1006 for room 12 on 2027-03-14 for 2 guests. [2]

(b) Booking 1003 now has 3 guests. Construct an SQL statement to make this change. [2]

(a) INSERT INTO Booking (allow with or without the column list (BookingID, RoomID, BookingDate, Guests))(1)
VALUES (1006, 12, '2027-03-14', 2) with values in the correct column order (matching the column list if one is given) and the date in quotes(1)
(b) UPDATE Booking SET Guests = 3 (allow SET Guests = Guests + 1)(1)
WHERE BookingID = 1003 (allow any condition that selects only booking 1003, e.g. RoomID = 4 AND BookingDate = '2027-03-09'; reject if WHERE is missing, as every row would change)(1)
(c) The index must also be updated / re-sorted whenever the column value changes, so each write takes extra time (accept: extra processing on every INSERT/UPDATE/DELETE)(1)
Answer: (a) INSERT INTO Booking (BookingID, RoomID, BookingDate, Guests) VALUES (1006, 12, '2027-03-14', 2); (b) UPDATE Booking SET Guests = 3 WHERE BookingID = 1003; (c) the index has to be updated as well as the table, slowing writes.
Examiner tip: An UPDATE without a WHERE clause changes every row in the table.

Stuck on SQL: DDL/DML, queries across two tables and updating data?

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

4Core6 marks

A school library is setting up a database. The following SQL statements are run in order.

S1  CREATE TABLE Book (ISBN CHAR(13) PRIMARY KEY, Title VARCHAR(80), Copies INTEGER);
S2  INSERT INTO Book VALUES ('9780000000001', 'Dune', 3);
S3  ALTER TABLE Book ADD Shelf VARCHAR(5);
S4  UPDATE Book SET Copies = 4 WHERE ISBN = '9780000000001';

(a) For each of the statements S1 to S4, state whether it is an example of a data definition language (DDL) or a data manipulation language (DML) statement. [4]

(b) A technician needs to clear out the Book table. Outline the difference between running DELETE FROM Book; and running DROP TABLE Book; [2]

(a) S1 (CREATE TABLE): DDL(1)
S2 (INSERT INTO): DML(1)
S3 (ALTER TABLE): DDL(1)
S4 (UPDATE): DML(1)
(b) DELETE FROM Book removes all the rows / records but the (empty) table and its structure remain (accept: it is DML, it changes the data only)(1)
DROP TABLE Book removes the table itself / its structure / definition (and all its data), so it no longer exists in the database (accept: it is DDL, it changes the schema). Award only if both statements are addressed(1)
Answer: (a) S1 DDL, S2 DML, S3 DDL, S4 DML. (b) DELETE empties the table of rows but keeps its structure; DROP TABLE removes the table and its definition completely.
Examiner tip: DDL changes the structure (CREATE, ALTER, DROP); DML changes or reads the data inside it (SELECT, INSERT, UPDATE, DELETE). DELETE is DML even though it sounds destructive.

5Core4 marks

A streaming service stores details of films in the table below.

Film
FilmID	Title	Genre	Year	Rating
1	The Long Road	Drama	2014	7.2
2	Road to Mars	Sci-Fi	2019	6.4
3	Silent River	Drama	2021	8.1
4	Mars Rising	Sci-Fi	2016	7.8
5	Crossing Paths	Comedy	2018	5.9
6	River Road	Drama	2015	6.8

(a) State the output of the following query. [2]

SELECT Title
FROM Film
WHERE Title LIKE '%Road%' AND Year BETWEEN 2015 AND 2020
ORDER BY Year ASC;

(b) State the output of the following query. [2]

SELECT Title, Rating
FROM Film
WHERE NOT Genre = 'Drama' OR Rating > 8
ORDER BY Rating DESC;

(a) River Road and Road to Mars only (no other titles; The Long Road excluded as 2014 is outside the range)(1)
in the order River Road then Road to Mars (ignore column heading; allow the year shown alongside)(1)
(b) Exactly four rows: Silent River 8.1, Mars Rising 7.8, Road to Mars 6.4, Crossing Paths 5.9 (titles with their ratings; allow ratings omitted for this mark only)(1)
in the order Silent River, Mars Rising, Road to Mars, Crossing Paths (highest rating first). Allow follow-through on order from an incorrect set of rows if sorted by rating descending(1)
Answer: (a) River Road, Road to Mars. (b) Silent River 8.1; Mars Rising 7.8; Road to Mars 6.4; Crossing Paths 5.9.
Examiner tip: BETWEEN includes both end values, so River Road (2015) is included; and % matches zero or more characters, so 'Road' at the start or end of a title still matches.

6Standard5 marks

An online plant shop uses two tables.

Customer
CustID	Name	Town
1	Priya	Leeds
2	Tom	York
3	Grace	Leeds
4	Omar	Hull
5	Lena	York

Orders
OrderID	CustID	Product	Qty
501	1	Fern	2
502	2	Cactus	1
503	3	Fern	1
504	1	Orchid	1
505	5	Fern	3
506	4	Bonsai	1
507	3	Fern	2

CustID in Orders is a foreign key referencing Customer.

(a) Construct an SQL query that lists each town that has had at least one order for a Fern. Each town must appear only once, in alphabetical order. [4]

(b) Explain how the output would change if the query did not remove duplicate towns (for example, if the keyword DISTINCT were left out). [1]

(a) SELECT DISTINCT Town (allow Customer.Town; DISTINCT must be applied to Town; accept SELECT Town with GROUP BY Town placed before ORDER BY as an alternative way of removing duplicates)(1)
FROM Customer JOIN Orders (accept INNER JOIN, or FROM Customer, Orders)(1)
ON Customer.CustID = Orders.CustID (accept the same join condition in a WHERE clause)(1)
WHERE Product = 'Fern' ORDER BY Town (allow ORDER BY Town ASC; reject DESC; Fern must be in quotes)(1)
(b) Leeds would appear three times (and York once / Leeds repeated), because there is one row for every matching order, not one per town (accept: towns would be duplicated, one per Fern order)(1)
Answer: (a) SELECT DISTINCT Customer.Town FROM Customer JOIN Orders ON Customer.CustID = Orders.CustID WHERE Orders.Product = 'Fern' ORDER BY Customer.Town ASC; gives Leeds, York. (b) Leeds would be listed three times (one row per Fern order).
Examiner tip: Without a join condition every customer is paired with every order, so the output is wrong even though the query runs.

7Standard7 marks

A city bike-hire scheme stores its bikes in the table below. HourlyRate is in pounds.

Bike
BikeID	Type	HourlyRate	Status
B01	Road	4.00	Available
B02	E-bike	8.00	Repair
B03	E-bike	8.00	Available
B04	Mountain	5.50	Retired
B05	Road	4.00	Retired
B06	E-bike	9.00	Available

(a) The hourly rate of every E-bike that is not under repair is to increase by £1.50. Construct an SQL statement to make this change. [3]

(b) Construct an SQL statement to remove all retired bikes from the table. [2]

(c) The manager suggests adding an index on HourlyRate because customers often search by price. Explain one disadvantage of indexing this column, with reference to part (a). [2]

(a) UPDATE Bike SET HourlyRate = HourlyRate + 1.50 (reject SET HourlyRate = 9.50 or similar fixed values, since B06 starts at 9.00)(1)
WHERE Type = 'E-bike'(1)
AND Status <> 'Repair' (accept != 'Repair', AND NOT Status = 'Repair', or Status = 'Available'; reject OR, which would also change B02)(1)
(b) DELETE FROM Bike (reject DROP TABLE; reject DELETE * FROM Bike)(1)
WHERE Status = 'Retired' (reject if WHERE is missing, as every bike would be deleted)(1)
(c) Every time HourlyRate changes (as in the UPDATE in (a)), the index must also be updated / re-ordered as well as the table(1)
so updates take longer / use extra processing (and the index uses extra storage); the cost grows the more often prices change or the more rows are updated(1)
Answer: (a) UPDATE Bike SET HourlyRate = HourlyRate + 1.50 WHERE Type = 'E-bike' AND Status <> 'Repair'; (B03 becomes 9.50, B06 becomes 10.50, B02 stays 8.00). (b) DELETE FROM Bike WHERE Status = 'Retired'; (c) the index has to be maintained on every price update, slowing writes.
Examiner tip: Use SET column = column + value when each row starts at a different value; and check whether the conditions need AND or OR.

8Hard6 marks

A music festival stores its line-up in two tables.

Band
BandID	BandName	Genre
1	Iron Kites	Rock
2	Velvet Tide	Pop
3	Static Bloom	Rock
4	Northern Owls	Folk

Slot
SlotID	BandID	Stage	StartTime
11	1	Main	18:30
12	2	Main	16:00
13	3	Main	14:15
14	4	Field	15:00
15	3	Field	20:00

A programmer wants the name and start time of every Rock band playing on the Main stage, earliest start time first. They write:

SELECT BandName, StartTime
FROM Band, Slot
WHERE Genre = Rock
AND Stage = 'Main'
ORDER BY StartTime DESC;

The query contains three errors. Identify each error and state the correction needed. [6]

Error 1: Rock is not in quotes, so it is treated as a column name (the query fails / is invalid)(1)
Correction: WHERE Genre = 'Rock'(1)
Error 2: the two tables are not linked / there is no join condition, so every band is paired with every slot (allow: wrong bands matched to wrong times)(1)
Correction: add Band.BandID = Slot.BandID (in the WHERE clause with AND, or FROM Band JOIN Slot ON Band.BandID = Slot.BandID)(1)
Error 3: DESC sorts from latest to earliest start time(1)
Correction: ORDER BY StartTime ASC (accept ORDER BY StartTime, since ascending is the default)(1)
Answer: Rock needs quotes ('Rock'); a join condition Band.BandID = Slot.BandID is missing; DESC should be ASC. Corrected output: Static Bloom 14:15, Iron Kites 18:30.
Examiner tip: A query can run without errors and still be wrong: always check that two tables are joined on the matching key.

9Hard6 marks

A veterinary clinic's database has the structure shown in the diagram. Each owner can have many pets; each pet has exactly one owner. The Owner table already exists.

(a) Construct the SQL statement that creates the Pet table. PetID is a whole number, PetName (which must always be entered) is text of up to 20 characters, Species is text of up to 15 characters, and OwnerID links each pet to its owner. [4]

The Owner table currently holds owners with OwnerID 201 and 202 only. A receptionist runs:

INSERT INTO Pet VALUES (2, 'Pepper', 'Cat', 305);

(b) Explain why the database management system rejects this statement. [2]

(a) CREATE TABLE Pet ( … ) with the four named columns (PetID, PetName, Species, OwnerID)(1)
Suitable data types: INTEGER/INT for PetID and OwnerID, VARCHAR(20) for PetName and VARCHAR(15) for Species (accept CHAR(n) or TEXT for the text fields)(1)
PetID declared as PRIMARY KEY, and NOT NULL on PetName(1)
FOREIGN KEY (OwnerID) REFERENCES Owner(OwnerID) (accept OwnerID INTEGER REFERENCES Owner(OwnerID))(1)
(b) OwnerID 305 does not exist in / does not match any primary key value in the Owner table(1)
so the insert would break referential integrity / the foreign key constraint (a pet would point to a non-existent owner)(1)
Answer: (a) CREATE TABLE Pet (PetID INTEGER PRIMARY KEY, PetName VARCHAR(20) NOT NULL, Species VARCHAR(15), OwnerID INTEGER, FOREIGN KEY (OwnerID) REFERENCES Owner(OwnerID)); (b) owner 305 does not exist, so the foreign key constraint (referential integrity) would be violated.
Examiner tip: The foreign key must reference the primary key of the other table: name both the table and the column after REFERENCES.

10StretchHL only8 marks

A tennis club stores members and court bookings. BookDate is stored as text in the form YYYY-MM-DD.

Member
MemberID	Name	Level
1	Hana	Junior
2	Kofi	Senior
3	Maya	Senior
4	Luis	Junior

Booking
BookingID	MemberID	Court	BookDate
1	2	1	2027-02-27
2	1	2	2027-03-01
3	2	1	2027-03-03
4	3	3	2027-03-04
5	2	2	2027-03-10
6	1	1	2027-03-12
7	3	1	2027-03-15
8	2	3	2027-03-18
9	1	2	2027-03-31
10	4	1	2027-03-20
11	3	2	2027-04-01
12	2	1	2027-03-28

(a) Construct an SQL query that shows the name of each member and the number of bookings they made in March 2027, for members with at least three bookings in that month only. The member with the most bookings must appear first. [6]

(b) State the output of your query for the data shown. [2]

(a) SELECT Name, COUNT(*) (allow COUNT(BookingID); allow an alias, e.g. AS NumBookings)(1)
FROM Member JOIN Booking ON Member.MemberID = Booking.MemberID (accept FROM Member, Booking with the condition in WHERE)(1)
WHERE BookDate BETWEEN '2027-03-01' AND '2027-03-31' (accept >= '2027-03-01' AND <= '2027-03-31', or LIKE '2027-03%')(1)
GROUP BY Name (accept GROUP BY Member.MemberID or MemberID, Name)(1)
HAVING COUNT(*) >= 3 (reject > 3; reject the count condition placed in WHERE)(1)
ORDER BY COUNT(*) DESC (accept ordering by the alias, e.g. ORDER BY NumBookings DESC)(1)
(b) Kofi 4 and Hana 3 only (Maya and Luis not shown)(1)
in the order Kofi then Hana. Allow follow-through from the candidate's own query if it is valid SQL(1)
Answer: (a) SELECT Member.Name, COUNT(*) FROM Member JOIN Booking ON Member.MemberID = Booking.MemberID WHERE Booking.BookDate BETWEEN '2027-03-01' AND '2027-03-31' GROUP BY Member.Name HAVING COUNT(*) >= 3 ORDER BY COUNT(*) DESC; (b) Kofi 4, Hana 3.
Examiner tip: WHERE filters individual rows before grouping; HAVING filters the groups after COUNT has been calculated, so the 'at least three' test must go in HAVING.

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