IGCSE · Practice questions · SQL: SUM, COUNT, AND and OR

Count it, sum it, check it.

Six original Cambridge-style questions on SUM, COUNT, AND and OR against a single table, with worked solutions.

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

Keep these straight

One record at a time.

SUM(field) adds the values; COUNT(field) counts the matching records. Each gives one value.
AND: both conditions true. OR: at least one true; a record is never output twice.
Full conditions: Price > 10 AND Price < 30, never Price > 10 AND < 30.
Method: tick each record against each condition, then apply SUM, COUNT or ORDER BY.

Write

[4 marks]

The table GAMES is shown below.

GameID	Title	Genre	Price	Year	Online
G01	Star Racer	Racing	25.00	2021	TRUE
G02	Castle Quest	Adventure	40.00	2019	FALSE
G03	Puzzle Pop	Puzzle	10.00	2023	TRUE
G04	Speed Kings	Racing	35.00	2022	TRUE
G05	Dragon Isle	Adventure	30.00	2020	FALSE
G06	Block Town	Puzzle	15.00	2018	FALSE

Write an SQL query to find the total price of all the games that can be played online, and give its output.

SELECT SUM(Price) ✓
FROM Games ✓
WHERE Online = TRUE; ✓
Output: 25.00 + 10.00 + 35.00 = 70.00. ✓

Write

[4 marks]

Write an SQL query on the GAMES table to count the games released after 2019 that cost less than 35.00. Give its output.

SELECT COUNT(GameID) FROM Games ✓
WHERE Year > 2019 ✓
AND Price < 35; ✓
Output: 3 (G01, G03 and G05; G04 costs 35.00, which is not less than 35). ✓

Trace

[3 marks]

Give the output of this query on the GAMES table.

SELECT Title, Price
FROM Games
WHERE Genre = 'Racing' OR Genre = 'Puzzle'
ORDER BY Price DESC;

Matching records: Star Racer 25.00, Puzzle Pop 10.00, Speed Kings 35.00, Block Town 15.00. ✓
Sorted by Price, largest first. ✓
Speed Kings 35.00; Star Racer 25.00; Block Town 15.00; Puzzle Pop 10.00. ✓

Analysis

[3 marks]

Give the output of each query on the GAMES table and explain why they differ.

SELECT COUNT(GameID) FROM Games
WHERE Genre = 'Adventure' AND Year < 2020;

SELECT COUNT(GameID) FROM Games
WHERE Genre = 'Adventure' OR Year < 2020;

AND query: 1 (only Castle Quest is Adventure and before 2020). ✓
OR query: 3 (Castle Quest, Dragon Isle, Block Town). ✓
AND needs both conditions to be true; OR needs only one, so it matches more records. ✓

Analysis

[2 marks]

A student wants the total price of every game in the GAMES table and writes SELECT COUNT(Price) FROM Games; Give the output of this query and correct it.

Output: 6 (the number of records), not the total price. ✓
Correct query: SELECT SUM(Price) FROM Games; which outputs 155.00. ✓

Explain

[2 marks]

Explain the difference between SUM and COUNT in SQL.

SUM adds together the values in a numeric field for the records that match. ✓
COUNT gives the number of records that match the condition. ✓

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.