ORDER BY, AND/OR, COUNT and SUM · Year 9 Databases & SQL, lesson 4 · KS3 Computing

Read the map from the bottom. Year 7 starts on the left, and the line climbs through Years 8 and 9 into IGCSE, then on to IB in the sixth form.

Dashed rings mark this half-term's units. Taught half-terms are ticked in the year plans.

Follow a thread

Pick a thread to number its stops in teaching order, or select any stop to see what it builds on and what it sets up.

Skip the map, go to the year plans

KS3 Computing IGCSE 0478 Paper 1 Paper 2 Both papers

IB DP CS Paper 1 (Theme A) Paper 2 (Theme B) IA

KS3 year plan

When each Year 7–9 unit is taught. Select a unit to see its lessons.

IGCSE year plan

Cambridge IGCSE Computer Science (0478) across Years 10 and 11, 3 lessons a week. Select a topic to see what it covers and open its lessons.

IB year plan

IB Diploma Computer Science for the 2027 exams, across Years 12 and 13, 4 lessons a week. It follows the IB's suggested order: A1 to A3 and then programming (B1 to B3) in Year 12; the internal assessment, A4 Machine learning and the Paper 1 case study in Year 13. HL extensions are taught alongside each topic.

New free lessons, worksheets and revision sheets for Computer Science, sent to your inbox.

For teachers and students, Year 7 to A Level
Only what's new and useful
Unsubscribe from any email

ORDER BY, AND/OR, COUNT and SUM (assessed)

Year 9 · Databases & SQL · Lesson 4 of 5On device

What you'll learn

Sort query results with ORDER BY ... ASC or DESC
Combine conditions with AND and OR
Use COUNT and SUM to answer questions about a table

Open the slides

Extra resources

W3Schools SQL try-it editor

ORDER BY, AND/OR, COUNT and SUM (assessed)

Year 9 · Databases & SQL · Lesson 4 of 5On device

NameFormDate

SQL query task (20 marks)

A. What does it return? (5 marks)

Table name: Songs. Use it for every question.

SongID	Title	Genre	Year	Plays
S01	Night Drive	Pop	2023	120
S02	Rocket Run	Rock	2021	85
S03	Kite Season	Pop	2024	200
S04	Ocean Blue	Jazz	2022	40
S05	Level Up	Rock	2024	150
S06	Snow Day	Pop	2021	95
S07	Midnight Sax	Jazz	2024	60
S08	Final Boss	Rock	2022	110

SELECT Title FROM Songs WHERE Genre = 'Rock' ORDER BY Plays DESC;
SELECT COUNT(*) FROM Songs WHERE Year = 2024;
SELECT SUM(Plays) FROM Songs WHERE Genre = 'Pop';
SELECT Title FROM Songs WHERE Genre = 'Jazz' OR Plays > 150; (2 marks)

B. Write the query (8 marks, 2 each)

Show the Title and Year of every song, oldest first.
Show the Title of Pop songs with more than 100 plays.
Count how many Rock songs there are.
Find the total plays for songs from 2022 or 2024.

C. Find and fix (4 marks)

Each query has one error. Write the corrected query.

SELECT Title FROM Songs WHERE Genre = Jazz;
SELECT Title FROM Songs ORDER Plays DESC;
SELECT SUM(Title) FROM Songs;
SELECT Title FROM Songs WHERE Plays > 100 AND Plays < 50;

D. Stretch (3 marks)

The music club wants the Title and Plays of Rock songs from 2022 onwards, most played first. Write the query and give its result.

KS3 year plan

IGCSE year plan

GCSE year plan

IB year plan

A Level year plan

Get free resources