SQL aggregate functions, views and transactions (HL) – IB Computer Science exam questions with mark scheme

1HardHL only4 marks

The table below stores exam results.

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 that outputs the StudentID and average Score of every student who has at least two results and an average score above 70. [4]

SELECT StudentID, AVG(Score) FROM Result (accept AVERAGE(Score), the IB guide's wording; allow an alias, e.g. AS AvgScore)(1)
GROUP BY StudentID(1)
HAVING COUNT(*) >= 2 (accept COUNT(Score), COUNT(Subject), or > 1)(1)
AND AVG(Score) > 70 in the HAVING clause (reject if either aggregate condition is placed in WHERE)(1)
Answer: SELECT StudentID, AVG(Score) FROM Result GROUP BY StudentID HAVING COUNT(*) >= 2 AND AVG(Score) > 70; output (1, 71.5), (3, 86.0)
Examiner tip: Conditions on aggregates go in HAVING; WHERE cannot use AVG or COUNT.

2CoreHL only3 marks

A school library records book loans in the table below. DaysLate is the number of days a book was returned after its due date.

Loan
LoanID	BookID	MemberID	DaysLate
101	B4	M2	0
102	B7	M5	3
103	B4	M1	0
104	B2	M2	6
105	B9	M3	1
106	B7	M2	0
107	B2	M4	9

State the output of each of the following SQL queries.

(a) SELECT COUNT(*) FROM Loan WHERE DaysLate > 0; [1]

(b) SELECT MAX(DaysLate) FROM Loan; [1]

(a) 4 (loans 102, 104, 105 and 107)(1)
(b) 9(1)
(c) 6 (0 + 6 + 0; do not accept 3, which counts M2's loans instead of summing)(1)
Answer: (a) 4 (b) 9 (c) 6
Examiner tip: COUNT counts rows that pass the WHERE condition, SUM adds the values: do not swap them in (c).

3CoreHL only4 marks

A climbing centre stores details of its members in a table Member with fields MemberID, JoinDate, Age and FeesPaid.

For each requirement below, identify the SQL aggregate function that should be used.

(a) The number of members in the table. [1]

(b) The date on which the longest-standing member joined. [1]

(d) The mean age of the members. [1]

(a) COUNT (accept COUNT(*) or COUNT(MemberID))(1)
(b) MIN (accept MIN(JoinDate); MAX = 0, since the earliest date is the smallest value)(1)
(c) SUM (accept SUM(FeesPaid))(1)
(d) AVG (accept AVERAGE, the IB guide's wording, or AVG(Age); do not accept MEAN)(1)
Answer: (a) COUNT (b) MIN (c) SUM (d) AVG / AVERAGE
Examiner tip: The earliest date is the smallest date value, so it needs MIN, not MAX.

Stuck on SQL aggregate functions, views and transactions (HL)?

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

4CoreHL only4 marks

An online shop uses transactions to keep its database accurate.

(a) State the four properties represented by the acronym ACID. [2]

(b) Outline the purpose of the transaction control command COMMIT. [1]

(a) Any two of Atomicity, Consistency, Isolation, Durability correct(1)
(a) All four correct (Atomicity, Consistency, Isolation, Durability; accept in any order; ignore minor spelling errors)(1)
(b) Makes all changes in the transaction permanent / saves them to the database (so other users can see them)(1)
(c) Undoes / cancels all changes made since BEGIN TRANSACTION, returning the database to its state before the transaction (allow "to the last commit")(1)
Answer: (a) Atomicity, Consistency, Isolation, Durability (b) makes the transaction's changes permanent (c) undoes all of the transaction's changes
Examiner tip: ROLLBACK undoes the whole transaction, not just the last statement.

5HardHL only5 marks

A homeware shop uses two tables:

Product
ProductID	Name	Category	Price
P1	Kettle	Kitchen	24.00
P2	Toaster	Kitchen	31.50
P3	Desk lamp	Lighting	18.00
P4	Floor lamp	Lighting	55.00
P5	Rug	Decor	42.00

OrderLine
OrderID	ProductID	Quantity
1	P1	2
1	P3	1
2	P4	1
3	P2	2
3	P5	1
4	P3	4
5	P1	1

Construct an SQL query that outputs each Category and its total sales revenue (quantity × price), for categories whose total revenue is more than 100. The output must list the category with the highest revenue first. [5]

SELECT Category, SUM(Quantity * Price) (allow table prefixes and an alias such as AS Revenue)(1)
FROM Product JOIN OrderLine ON Product.ProductID = OrderLine.ProductID (accept INNER JOIN; accept the join written as a WHERE condition with both tables in FROM)(1)
GROUP BY Category(1)
HAVING SUM(Quantity * Price) > 100 (accept HAVING Revenue > 100 if alias defined; reject the condition placed in WHERE)(1)
ORDER BY SUM(Quantity * Price) DESC (accept ORDER BY alias DESC)(1)
Answer: SELECT Category, SUM(Quantity * Price) AS Revenue FROM Product JOIN OrderLine ON Product.ProductID = OrderLine.ProductID GROUP BY Category HAVING SUM(Quantity * Price) > 100 ORDER BY Revenue DESC; output: Lighting 145.00, Kitchen 135.00
Examiner tip: The revenue condition is on a total, so it belongs in HAVING after GROUP BY, not in WHERE.

6StandardHL only5 marks

A bank table Account holds three rows: account A with balance 500, account B with balance 200 and account C with balance 50. The following three transactions run one after another.

-- Transaction 1
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 150 WHERE AccNo = 'A';
UPDATE Account SET Balance = Balance + 150 WHERE AccNo = 'B';
COMMIT;

-- Transaction 2
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance - 400 WHERE AccNo = 'B';
-- the application now reads B's balance:
-- if it is negative it issues ROLLBACK, otherwise COMMIT

-- Transaction 3
BEGIN TRANSACTION;
UPDATE Account SET Balance = Balance + 100 WHERE AccNo = 'C';
UPDATE Account SET Balance = Balance - 100 WHERE AccNo = 'A';
COMMIT;

(a) State the final balance of each of accounts A, B and C. [3]

(b) Explain how the ROLLBACK in Transaction 2 maintains the integrity of the data. [2]

(a) A = 250(1)
(a) B = 350 (do not accept −50, which ignores the ROLLBACK)(1)
(a) C = 150(1)
(b) ROLLBACK undoes the −400 update, so B is restored to its committed value of 350 / the transaction has no effect (all or nothing: atomicity)(1)
(b) so the database is never left in an invalid state that breaks the rule that balances cannot be negative (consistency) (accept: no partial / incorrect change is saved or seen by other users)(1)
Answer: (a) A 250, B 350, C 150 (b) the failed withdrawal is undone completely, so B never stores an invalid negative balance
Examiner tip: Changes made after BEGIN TRANSACTION are discarded by ROLLBACK, so trace B back to its value at the last COMMIT.

7HardHL only6 marks

A chain of 60 school canteens stores every sale in a table that now holds about 40 million rows. Head office wants a dashboard showing total sales per canteen per week. The sales data is loaded into the database each night.

(a) Distinguish between a virtual view and a materialized (snapshot) view. [4]

(b) Suggest, with a reason, which type of view head office should use for the dashboard. [2]

(a) A virtual view stores only the query (definition); no data is stored(1)
(a) its query is re-run against the base tables each time the view is used, so it always shows current data(1)
(a) A materialized view stores the result of the query physically (as a table) on disk(1)
(a) so it is faster to read but uses extra storage and must be refreshed, and can be out of date (stale) between refreshes (accept any one of these consequences)(1)
(b) Materialized view(1)
(b) because recalculating the weekly totals over 40 million rows on every dashboard load would be slow, and since data only arrives nightly the view can be refreshed after the nightly load without becoming out of date (reason must link to the scenario)(1)
Answer: (a) virtual: stored query, re-run every time, always current; materialized: stored result, fast but needs storage and refresh, may be stale (b) materialized, refreshed after the nightly load
Examiner tip: For a suggest question, the reason must use the context: large table and data that only changes overnight.

8StandardHL only5 marks

A school's database holds a table Staff with the fields StaffID, Name, Department, Email, Salary and Active. Active is 'Y' for staff who currently work at the school and 'N' otherwise.

Reception staff need to look up the name, department and email address of current staff only. They must not be able to see salaries.

(a) Construct an SQL statement that creates a view called ReceptionDirectory to meet this requirement. [3]

(b) Outline two benefits of giving reception staff access to the view rather than to the Staff table. [2]

(a) CREATE VIEW ReceptionDirectory AS(1)
(a) SELECT Name, Department, Email FROM Staff (no Salary; SELECT * = 0 for this mark)(1)
(a) WHERE Active = 'Y' (accept double quotes)(1)
(b) Security / privacy: sensitive fields such as Salary are hidden; users only see the columns and rows they need (accept access control can be granted on the view alone)(1)
(b) Simplicity: users query one simple object without needing to write the column list or the Active = 'Y' filter themselves / the base table can change structure without changing what reception uses (any one further distinct benefit; do not accept 'always up to date', as the Staff table is equally current)(1)
Answer: (a) CREATE VIEW ReceptionDirectory AS SELECT Name, Department, Email FROM Staff WHERE Active = 'Y'; (b) hides Salary (security); simpler for users, who do not need to write the filter themselves
Examiner tip: Name the columns explicitly: SELECT * would expose Salary and defeat the purpose of the view.

9HardHL only5 marks

A cinema asks customers to rate a film from 1 to 5. Customers may skip the question, in which case Rating is stored as NULL.

Survey
ResponseID	Rating
1	5
2	4
3	NULL
4	5
5	3
6	NULL
7	5
8	5

(a) State the value returned by each of the following. [3]

SELECT COUNT(*) FROM Survey;
SELECT COUNT(Rating) FROM Survey;
SELECT AVG(Rating) FROM Survey;

(b) A manager calculates the mean rating as SUM(Rating) / COUNT(*) and gets a different answer from AVG(Rating). Explain why the two values differ. [2]

(a) COUNT(*) = 8(1)
(a) COUNT(Rating) = 6(1)
(a) AVG(Rating) = 4.5 (accept 4.50 / 4.5000; do not accept 3.375)(1)
(b) Aggregate functions such as AVG (and SUM, COUNT(column)) ignore NULL values, so AVG divides 27 by 6(1)
(b) COUNT(*) counts every row including those with NULL Rating, so the manager divides 27 by 8, giving 3.375 (a lower value) (accept 3 if integer division by the DBMS is described)(1)
Answer: (a) 8, 6, 4.5 (b) AVG ignores NULLs (27 / 6 = 4.5) but COUNT(*) includes them (27 / 8 = 3.375)
Examiner tip: COUNT(*) counts rows; COUNT(column) and AVG skip NULLs.

10StretchHL only6 marks

A concert venue's database stores the number of unsold tickets for a show in a field Remaining, which is 12. Two booking agents, X and Y, sell tickets at the same time without using transactions. The diagram shows the order in which their operations are carried out.

(a) State the value of Remaining after T6, and the value it should have. [2]

(b) Identify the ACID property that has not been maintained. [1]

(c) Explain how running each sale as a transaction with BEGIN TRANSACTION and COMMIT would prevent this problem. [3]

(a) 7 is stored(1)
(a) It should be 4 (12 − 3 − 5); X's sale of 3 tickets has been lost (lost update)(1)
(b) Isolation (accept consistency only if linked to the incorrect total; atomicity / durability = 0)(1)
(c) Each sale (read, calculate, write) is grouped into one transaction that runs in isolation: the DBMS locks the record when X's transaction starts(1)
(c) Y's transaction must wait (cannot read or write Remaining) until X's transaction has finished with COMMIT(1)
(c) so Y then reads the committed value 9 and writes 4: the result is the same as if the sales ran one after another (serially) (accept: if a conflict occurs, one transaction is rolled back and retried)(1)
Answer: (a) 7; should be 4 (b) isolation (c) locking makes Y wait until X commits, so Y reads 9 and stores 4
Examiner tip: Say what the second transaction actually does differently (it waits, then reads the committed value), not just that transactions are "safer".

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