Databases and SQL: Question 5

Syllabus 8.3

Structured AS 12 marks

The community sports centre stores its data in two tables. COACH already exists in the database, populated as shown below.

COACH table structure: CoachID (INTEGER, primary key), CoachName (VARCHAR(30)), Specialism (VARCHAR(20)).

COACH data

CoachID	CoachName	Specialism
1	Priya Nair	Swimming
2	Tom Blake	Athletics
3	Sana Malik	Swimming

SESSIONS table structure (does not yet exist): SessionID (INTEGER, primary key), SessionDate (DATE), SportName (VARCHAR(20)), CoachID (INTEGER, foreign key referencing COACH), Fee (REAL).

SESSIONS data, once created and populated

SessionID	SessionDate	SportName	CoachID	Fee
101	2026-09-01	Swimming	1	12.50
102	2026-09-01	Athletics	2	8.00
103	2026-09-02	Swimming	3	11.00
104	2026-09-03	Swimming	1	15.00
105	2026-09-03	Athletics	2	8.00
108	2026-09-04	Athletics	2	14.00
109	2026-09-05	Swimming	3	9.50

Each part below is independent and refers back to this original table data, regardless of any earlier part.

(a) Write an SQL statement to create the SESSIONS table, including appropriate data types, its primary key, and a foreign key referencing COACH. [2]

(b) Write an SQL statement to add a new field, IsCancelled, of type BOOLEAN, to the SESSIONS table. [1]

(c) Write an SQL statement to display the SportName, SessionDate and Fee of every session with a Fee greater than 10.00, ordered by Fee in descending order. [2]

(d) Write an SQL statement, joining SESSIONS to COACH, to display each coach's CoachName together with the number of sessions they run, the total Fee income from those sessions, and the average Fee per session, grouped by coach. [3]

(e) Write an SQL statement to insert a new session: SessionID 106, SessionDate '2026-09-04', SportName 'Athletics', CoachID 2, Fee 8.00. [1]

(f) Write an SQL statement to increase the Fee of every Swimming session by 1.50. [2]

(g) Write an SQL statement to delete any session run by CoachID 2 with a Fee of less than 10.00. [1]

Show worked solution Hide worked solution

Part (a): CREATE TABLE with a primary key and a foreign key

CREATE TABLE SESSIONS (
    SessionID INTEGER,
    SessionDate DATE,
    SportName VARCHAR(20),
    CoachID INTEGER,
    Fee REAL,
    PRIMARY KEY (SessionID),
    FOREIGN KEY (CoachID) REFERENCES COACH (CoachID)
);

[2 marks]: [1] for correct field names and data types with SessionID declared as the primary key, [1] for the correct FOREIGN KEY (CoachID) REFERENCES COACH (CoachID) clause.

Part (b): ALTER TABLE to add a field

ALTER TABLE SESSIONS ADD IsCancelled BOOLEAN;

[1 mark] for the correct ALTER TABLE ... ADD ... BOOLEAN statement.

Part (c): SELECT with WHERE and ORDER BY

SELECT SportName, SessionDate, Fee
FROM SESSIONS
WHERE Fee > 10.00
ORDER BY Fee DESC;

Checking each session against Fee > 10.00: sessions 101 (12.50), 103 (11.00), 104 (15.00) and 108 (14.00) qualify; sessions 102 (8.00), 105 (8.00) and 109 (9.50) do not. Sorting the four qualifying rows by Fee in descending order gives:

SportName	SessionDate	Fee
Swimming	2026-09-03	15.00
Athletics	2026-09-04	14.00
Swimming	2026-09-01	12.50
Swimming	2026-09-02	11.00

[2 marks]: [1] for the correct SELECT ... FROM ... WHERE filter, [1] for ORDER BY Fee DESC producing the rows in the correct (highest-fee-first) order.

Part (d): INNER JOIN with GROUP BY and aggregate functions

SELECT COACH.CoachName,
       COUNT(SESSIONS.SessionID) AS NumSessions,
       SUM(SESSIONS.Fee) AS TotalFees,
       AVG(SESSIONS.Fee) AS AverageFee
FROM COACH INNER JOIN SESSIONS ON COACH.CoachID = SESSIONS.CoachID
GROUP BY COACH.CoachName;

Grouping the joined rows by coach: Priya Nair’s sessions are 101 (12.50) and 104 (15.00), so COUNT = 2, SUM = 27.50, AVG = 13.75. Tom Blake’s sessions are 102 (8.00), 105 (8.00) and 108 (14.00), so COUNT = 3, SUM = 30.00, AVG = 10.00. Sana Malik’s sessions are 103 (11.00) and 109 (9.50), so COUNT = 2, SUM = 20.50, AVG = 10.25.

CoachName	NumSessions	TotalFees	AverageFee
Priya Nair	2	27.50	13.75
Tom Blake	3	30.00	10.00
Sana Malik	2	20.50	10.25

[3 marks]: [1] for the correct INNER JOIN ... ON COACH.CoachID = SESSIONS.CoachID, [1] for GROUP BY COACH.CoachName, [1] for correct use of the COUNT, SUM and AVG aggregate functions on Fee/SessionID.

Part (e): INSERT INTO

INSERT INTO SESSIONS (SessionID, SessionDate, SportName, CoachID, Fee)
VALUES (106, '2026-09-04', 'Athletics', 2, 8.00);

[1 mark] for the correct INSERT INTO ... VALUES statement, matching the field list to the values given in the question.

Part (f): UPDATE… SET

UPDATE SESSIONS
SET Fee = Fee + 1.50
WHERE SportName = 'Swimming';

This matches the four Swimming sessions (101, 103, 104 and 109) leaving the three Athletics sessions (102, 105, 108) unchanged. After the update: session 101’s Fee becomes 12.50 + 1.50 = 14.00, session 103’s becomes 11.00 + 1.50 = 12.50, session 104’s becomes 15.00 + 1.50 = 16.50, and session 109’s becomes 9.50 + 1.50 = 11.00.

[2 marks]: [1] for the correct UPDATE ... SET Fee = Fee + 1.50 syntax, [1] for the correct WHERE SportName = 'Swimming' condition restricting the update to the right rows.

Part (g): DELETE FROM with a compound condition

DELETE FROM SESSIONS
WHERE CoachID = 2 AND Fee < 10.00;

Three sessions have CoachID = 2 (102 (Fee 8.00), 105 (Fee 8.00) and 108 (Fee 14.00). Of these, only 102 and 105 also satisfy Fee < 10.00, so only those two are deleted; session 108 is correctly left in the table because its Fee of 14.00 is not less than 10.00, even though it belongs to the same coach. (Session 109 has Fee = 9.50, which is below 10.00, but it is not deleted, because it belongs to CoachID 3, not CoachID 2) this is exactly why both conditions in the WHERE clause are needed.)

[1 mark] for the correct DELETE FROM ... WHERE statement using both conditions, joined with AND.

Final answers

(a) CREATE TABLE SESSIONS (...) with PRIMARY KEY (SessionID) and FOREIGN KEY (CoachID) REFERENCES COACH (CoachID).
(b) ALTER TABLE SESSIONS ADD IsCancelled BOOLEAN;
(c) Returns sessions 104 (15.00), 108 (14.00), 101 (12.50), 103 (11.00) in that order.
(d) Priya Nair: 2/27.50/13.75; Tom Blake: 3/30.00/10.00; Sana Malik: 2/20.50/10.25.
(e) INSERT INTO SESSIONS (...) VALUES (106, '2026-09-04', 'Athletics', 2, 8.00);
(f) Sessions 101, 103, 104, 109 become 14.00, 12.50, 16.50, 11.00 respectively.
(g) Sessions 102 and 105 are deleted; session 108 (same coach, higher fee) is correctly retained.

Common mistakes

In (a), omitting the FOREIGN KEY (CoachID) REFERENCES COACH (CoachID) clause entirely, creating CoachID as an ordinary field with no link back to COACH.
In (b), writing ALTER TABLE with a CREATE-style full column list, rather than the simpler ALTER TABLE SESSIONS ADD IsCancelled BOOLEAN needed to add a single new field to an existing table.
In (c), using WHERE Fee > 10.00 but forgetting ORDER BY Fee DESC (or writing ORDER BY Fee, which sorts ascending, giving the rows in the wrong order).
In (d), grouping by SESSIONS.CoachID rather than COACH.CoachName, or omitting the INNER JOIN... ON clause so the query cannot combine data from both tables at all.
In (g), writing only WHERE Fee < 10.00 and forgetting the AND CoachID = 2 condition, which would incorrectly also delete session 109 (Sana Malik's low-fee session) - or, conversely, writing only WHERE CoachID = 2 and forgetting the Fee condition, which would incorrectly also delete session 108 (Tom Blake's higher-fee session) rather than only his two low-fee ones.