SQL: SUM, COUNT, AND and OR lesson pack

At a glance

The shape of the lesson

Topic

Databases

Syllabus reference

Cambridge IGCSE Computer Science 0478, 9 (4)

Level

IGCSE

Duration

45 minutes, lab-led; stretches to a 60 minute block (see the contingency notes)

Position in topic

Lesson 4 of 4 in Databases; follows SQL: SELECT, FROM, WHERE and ORDER BY

Central model

Fig. 1: three records match the condition; COUNT gives 3 and SUM adds their prices

Cooperative structure

Think-Pair-Share

Assessment

Stage 1 check and the exam-style plenary from the student lesson, plus the worksheet as the exit task

Learning objectives

By the end of the lesson, learners can

Section by section

SUM and COUNT: In the lab, run a SUM or COUNT query on the Books table and compare it with the same query without them: the list of rows collapses into one number.
Two conditions: AND and OR: Write the full condition on each side of AND or OR: Genre = 'Racing' AND Price > 30, not Genre = 'Racing' AND > 30.
Putting it together: Exam questions combine everything: a function, two conditions and sometimes a sort.

Key points to leave on the board

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.

The core visual model

One picture every idea returns to

SUM(field) adds up the values in a numeric field for every record that matches the WHERE condition. COUNT(field) gives the number of records that match. Both return a single value, not a list. In a WHERE clause, AND keeps a record only if both conditions are true; OR keeps a record if at least one condition is true. A record that meets both OR conditions is still output only once.

Fig. 1: three records match the condition; COUNT gives 3 and SUM adds their prices

Lesson sequence

Forty-five minutes, phase by phase

Time	Phase	What happens in the room	Grouping
0 to 5 min	Hook: predict	Open with the question in the title, Many rows, one answer.. How many games can be played online? Put the figure on the board: it is the one picture every idea in the lesson returns to.	Whole class, then pairs
5 to 13 min	Teach and check: SUM and COUNT	In the lab, run a SUM or COUNT query on the Books table and compare it with the same query without them: the list of rows collapses into one number. Project tab 2 of the lab (SQL query builder) and let a student drive it while the class predicts. Model the worked example SUM on the board, one line at a time. Your turn: Give the output of: SELECT SUM(Price) FROM Games;	Whole class, then pairs
13 to 21 min	Teach and check: Two conditions: AND and OR	Write the full condition on each side of AND or OR: Genre = 'Racing' AND Price > 30, not Genre = 'Racing' AND > 30. Model the worked example AND on the board, one line at a time. Your turn: Give the output of: SELECT COUNT(GameID) FROM Games WHERE Price > 20 AND Year > 2019;	Pairs
21 to 30 min	Teach and check: Putting it together	Exam questions combine everything: a function, two conditions and sometimes a sort. Project tab 3 of the lab (What will this query output?) and let a student drive it while the class predicts. Model the worked example SUM with AND on the board, one line at a time. Your turn: Complete the query to count the Adventure games that cost more than 35.00, and give its output. SELECT........(GameID) FROM Games WHERE Genre = 'Adventure'........ Price > 35;	Think-Pair-Share
30 to 36 min	Check	The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong.	Individual, then whole class
36 to 43 min	Plenary	The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording.	Individual, random call
43 to 45 min	Exit and homework	Set the worksheet below (6 questions, 18 marks). Learners self-assess against the objectives before they leave.	Individual

Timing and contingency

Protect the parts that carry the learning

The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.

Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.

Cut first if the build overruns: the last check in Putting it together, never the check or the plenary.

In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.

Running the cooperative task

Think-Pair-Share

Pose the question, give thirty seconds of silent thinking, then a minute to compare with a partner before a few pairs share with the class. The silent minute is the part that cannot be cut.

The full structure, with its variations and the other twelve, is on the cooperative-learning page.

Examiner traps to pre-empt

What to head off, and how

Trap learners fall into	Teaching move that pre-empts it
SUM adds the values; COUNT counts the records.	COUNT(Price) on the whole GAMES table gives 6, not 155.00.
AND gives the same or fewer records than either condition alone; OR gives the same or more.	If your AND answer has more rows than one of its conditions, recheck it.
When asked for the output of a SUM or COUNT query, give one value.	Listing the matching records instead earns no mark.

Worksheet

SQL: SUM, COUNT, AND and OR: worksheet

6 questions, 18 marks. Written in the style of the papers; no past paper question is reproduced.

NameClassDate

Q1 · 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.

Q2 · 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.

Q3 · 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;

Q4 · 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;

Q5 · 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.

Q6 · explain[2 marks]

Explain the difference between SUM and COUNT in SQL.

Answer key and marking notes

Q1 · 4 marks

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

Q2 · 4 marks

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).

Q3 · 3 marks

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.

Q4 · 3 marks

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.

Q5 · 2 marks

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

Q6 · 2 marks

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

Quiz

6 multiple-choice questions on this lesson

From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.

Assign in the app Run as a class game Topic question bank

Q11 mark

What does COUNT(FilmID) return in an SQL query with a WHERE condition?

Athe number of records that meet the condition
Bthe total of the FilmID values that meet the condition
Ca list of the FilmIDs that meet the condition
Dthe largest FilmID that meets the condition

Q21 mark

The table FILMS stores details of films that can be rented. What is the output of this query?

SELECT SUM(Rental)
FROM Films
WHERE Genre = 'Animation';

A9.00
B2
C4.50
DMoon Garden; Paper Kites

Q31 mark

The table FILMS stores details of films that can be rented. What is the output of this query?

SELECT COUNT(FilmID)
FROM Films
WHERE Subtitled = FALSE;

A3
B4
C7
D9.00

Q41 mark

The table FILMS stores details of films that can be rented. What is the output of this query?

SELECT Title
FROM Films
WHERE Genre = 'Documentary' AND Rental > 3.00;

AOcean Deep
BOcean Deep; Lost Valley
CLost Valley
DOcean Deep; Moon Garden; Iron Coast; Lost Valley; Paper Kites; Red Harbour

Q51 mark

The table FILMS stores details of films that can be rented. What is the output of this query?

SELECT COUNT(FilmID)
FROM Films
WHERE Minutes > 115 OR Subtitled = TRUE;

Q61 mark

The table FILMS stores details of films that can be rented. A manager wants the total rental price of all the Action films and all the Drama films together. Which query gives it?

ASELECT SUM(Rental) FROM Films WHERE Genre = 'Action' OR Genre = 'Drama';
BSELECT SUM(Rental) FROM Films WHERE Genre = 'Action' AND Genre = 'Drama';
CSELECT COUNT(Rental) FROM Films WHERE Genre = 'Action' OR Genre = 'Drama';
DSELECT SUM(Rental) FROM Films WHERE Genre = 'Action' OR 'Drama';

Quiz key

Q1: A

COUNT gives a single number: how many records match the WHERE condition. Adding values together is what SUM does (and FilmIDs such as F01 cannot be added anyway). A list of matching FilmIDs would come from SELECT FilmID, not COUNT. Finding the largest value is not one of the syllabus functions.

Q2: A

The Animation films are F03 (5.50) and F06 (3.50); SUM adds them: 5.50 + 3.50 = 9.00. The answer 2 is the COUNT of those records, not the total. 4.50 is their average, which SUM does not give. Listing the titles ignores the SUM function: the query returns a single value.

Q3: A

Subtitled is FALSE for F02, F05 and F06, so COUNT gives 3. The answer 4 counts the subtitled (TRUE) films instead. 7 counts every record, ignoring the WHERE condition. 9.00 is the SUM of the matching Rental values (3.00 + 2.50 + 3.50), but COUNT gives how many records, not a total.

Q4: A

The Documentary films are F01 (4.50) and F05 (2.50); only F01 also has Rental > 3.00, so the output is Ocean Deep. Listing both Documentary films ignores the second condition. Lost Valley alone has failed the Rental test. The long list is the result of OR, which keeps a record that passes either test.

Q5: A

Minutes > 115 gives F02, F04 and F07; Subtitled = TRUE gives F01, F03, F04 and F07. With OR, a record is counted once if it passes either test: F01, F02, F03, F04, F07, so 5. Adding 3 + 4 = 7 counts F04 and F07 twice. 2 counts only the records passing both tests (AND). 3 counts just the first condition.

Q6: A

OR keeps a record whose Genre is Action or whose Genre is Drama (F02, F04, F07), and SUM adds their Rental values: 3.00 + 5.00 + 4.00 = 12.00. With AND no record can be both genres, so nothing matches. COUNT gives how many films match (3), not their total price. Each condition must be complete: OR 'Drama' on its own does not compare Genre with anything.

Lab and demo

What to project

The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.

Tab 2SQL query builder Tab 3What will this query output?

Slides

Slides for this lesson

A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.

Also for this topic

Everything else you can use

Student lessonSQL: SUM, COUNT, AND and OR Practice pageWorked solutions, reveal one at a time Topic question bankEvery MCQ for Databases Revision notesOne page per lesson, with the traps Notes PDFPrint the topic notes Topic planDatabases: every lesson in order

Original work by the TheLucidSTEM team. The worksheet items are written in the style of the papers; no past paper question is reproduced.