← IGCSE Computer Science Paper 2

Working out the output of an SQL statement

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

Part of: IGCSE Computer Science 0478 — 9 Databases — Module 32, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 38, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 36, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 102, WA Computer Science ATAR Year 11 — Data Management — Module 84

Understanding Working out the output of an SQL statement in IGCSE Computer Science Paper 2

What it does: No student has more than 500 points, so no row passes WHERE and the output table is empty. Each row only needs to pass one of the two tests, so WHERE keeps it. SELECT then shows only firstName. WHERE removes every row that fails the condition, so only the 2 rows with Year = 10 are shown. DESC means descending order, so the largest value comes first. AND needs both conditions to be true. OR is the one that needs just one. OR keeps a row if at least one condition is true. A single House value cannot be both Red and Blue.

Common mistakes: Text values must be in quotes. Without them Red is read as a field name, so no rows can be kept. It should be WHERE house = 'Red'. SQL tests equality with a single =. The double == is not valid, so the WHERE step fails. It should be WHERE stock = 0.

Class: _____________________ · Name: _____________________ · Date: _________

Free printable worksheet with full answer key — or add this module to a class and your students practise it interactively.

🖨 Print worksheet + answer key Add Working out the output of an SQL statement to a class →

This module's key terms are also in a printable crossword (Modules 525–538) and word search (Modules 500–538).

Take this worksheet further — free with Cloodit

Each concept is reinforced from every angle — learned, then applied, then tested.

Learning

Duo

Which order do you follow to work out a query's output from a table?

Keep rows that pass WHERE, sort with ORDER BY, then show the SELECT fieldsShow the SELECT fields, then sort with ORDER BY, then apply WHERE

Matches

SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Red';

↓

WHERE is placed after ORDER BY. The clause order is wrong, so the query will not run. WHERE must come first to pick the rows before they are sorted.

Applying

Meanings

Term

WHERE, ORDER BY, SELECT

Meaning

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

Practice

SELECT firstName, points

Testing

Gaps

points > 100;

Check Answer

Spots

2FROM students

3WHERE house = Red

Cloodit turns curriculum-aligned coding content into journeys students work through one biome at a time.

Students earn collectable badges

Over 100 of them, across seven natural biomes.

Study Buddy

Students build a real study resource

Turns their work into revision material ready for Flash Cards and Exam study.

Diamond Dash — one of Cloodit's live, competitive Sprint games

Students lock in with Sprints

Live, competitive practice for the start of the lesson or a teacher-led lesson finisher.

A Cloodit certificate of achievement, ready to personalise and print

Custom printable certificates

Honour your top performers, or encourage a student who's trying hard — ready to print in seconds.

Add to class →

Quick Questions

1. Which order do you follow to work out a query's output from a table?

A. Show the SELECT fields, then sort with ORDER BY, then apply WHERE

B. Keep rows that pass WHERE, sort with ORDER BY, then show the SELECT fields ✓

First pick the rows that pass WHERE, then sort them with ORDER BY, and last show only the fields named in SELECT.

2. A table has 6 records and exactly 2 have Year = 10. How many rows does SELECT Name FROM Students WHERE Year = 10; output?

A. 6 rows

B. 2 rows ✓

WHERE removes every row that fails the condition, so only the 2 rows with Year = 10 are shown.

3. For SELECT Name, Score FROM Results ORDER BY Score DESC; which row appears first in the output?

A. The row with the highest Score ✓

B. The row with the lowest Score

DESC means descending order, so the largest value comes first.

4. For SELECT Name FROM Pupils WHERE Age > 15; the pupils are Ana (14), Ben (16), Cal (17). What is the output?

A. Ben and Cal under the heading Name ✓

B. Ana, Ben and Cal under the heading Name

Only Ben (16) and Cal (17) pass Age > 15. Ana is 14, so she is removed by WHERE.

5. For SELECT Name FROM Pupils WHERE Age > 15 AND House = 'Red'; which rows pass the WHERE?

A. Only rows where both conditions are true ✓

B. Rows where at least one condition is true

AND needs both conditions to be true. OR is the one that needs just one.

Match the Pairs

SELECT firstName FROM students WHERE house = 'Red' ORDER BY points DESC; → Keeps only the Red house rows, sorts them from highest points to lowest, then shows just the firstName column.

SELECT COUNT(*) FROM students WHERE yearLevel = 7; → Keeps only the Year 7 rows, then outputs a single number: how many rows passed the WHERE condition.

If no row passes the WHERE condition, the output shows the field headings but no records. → SELECT firstName FROM students WHERE points > 500;

With OR, a row is kept if either condition is true. Rows are picked first, then the chosen fields are shown. → SELECT firstName FROM students WHERE house = 'Red' OR yearLevel = 7;

SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Red'; → WHERE is placed after ORDER BY. The clause order is wrong, so the query will not run. WHERE must come first to pick the rows before they are sorted.

SELECT COUNT(*) FROM students WHERE yearLevel = 7 AND yearLevel = 8; → AND needs both conditions to be true, but no student is in Year 7 and Year 8 at once. No row passes, so the count is 0. OR should be used to count both years.

Key Term

WHERE, ORDER BY, SELECT

To find a query's output, first keep the rows that pass WHERE, then sort them with ORDER BY, then show only the SELECT fields or the SUM or COUNT result.

Fill in the Blanks

1. SELECT firstName, points FROM students WHERE points > 100;

Hint: Type the clause keyword that keeps only the rows passing a condition, which is applied first when tracing the query.

2. SELECT firstName, points FROM students ORDER BY points DESC;

Hint: Type the keyword that sorts the output with the largest value at the top.

3. SELECT firstName FROM students WHERE house = 'Red' AND points > 100;

Hint: Type the operator that keeps a row only if both conditions are true.

4. SELECT COUNT(*) FROM students WHERE house = 'Blue';

Hint: Type the function that outputs how many rows passed the WHERE condition.

5. SELECT firstName, points FROM students WHERE house = 'Red' ORDER BY points DESC;

Hint: Type the two-word clause that sorts the rows left after the WHERE filter.

Spot the Error

Question 1

A SELECT firstName, house

B FROM students

C WHERE house = Red

D ORDER BY firstName;

Line C — Text values must be in quotes. Without them Red is read as a field name, so no rows can be kept. It should be WHERE house = 'Red'.

Question 2

A SELECT name, price

B FROM products

C WHERE stock == 0

D ORDER BY name;

Line C — SQL tests equality with a single =. The double == is not valid, so the WHERE step fails. It should be WHERE stock = 0.

Question 3

A SELECT firstName, points

B FROM students

C WHERE yearLevel = 8

D ORDER points DESC;

Line D — The sorting clause is ORDER BY, not ORDER. Without BY the sort step cannot be applied. It should be ORDER BY points DESC;

Question 4

A SELECT SUM points

B FROM students

C WHERE house = 'Blue';

Line A — SUM needs brackets round the field it adds up. The result is the total of points for the rows that pass WHERE. It should be SELECT SUM(points).

Question 5

A SELECT COUNT(*)

B FORM students

C WHERE points > 100;

Line B — The keyword is FROM, not FORM. FROM names the table whose rows WHERE then filters. It should be FROM students.

Student with a class code? Join your class →