← IGCSE Computer Science Paper 2

Writing an SQL query from a requirement

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

Part of: IGCSE Computer Science 0478 — 9 Databases — Module 34, ACARA Digital Technologies Years 7–8 — Structured Data and Queries — Module 40, ACARA Digital Technologies Years 9–10 — Entities, Relationships and Queries — Module 46, QCE Digital Solutions Unit 2 — Prototype Data Solutions — Module 47, WA Computer Science ATAR Year 11 — Data Management — Module 82

Understanding Writing an SQL query from a requirement in IGCSE Computer Science Paper 2

What it does: The request asks for a total, so SUM wraps the quantity field, and WHERE limits it to product 2. ORDER BY comes last and DESC puts the highest price first. The names go after SELECT, the table after FROM, and the condition house = 'Red' goes in WHERE. "How many records" means COUNT. SUM adds up the values, so it gives the total price instead. The student must meet both conditions at the same time, so use AND. OR would include students who meet only one. Dates are stored as text, so the date value must be inside quotes. Without quotes SQL treats it as a sum.

Common mistakes: The table is called students, so the FROM line must read FROM students. A table name that does not exist makes the query fail. The keyword is misspelt. The table name must follow FROM, so the line should be FROM products.

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 Writing an SQL query from a requirement 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

Request: "List the names of students who are in the Red house." Which query is correct?

SELECT house FROM students WHERE firstName = 'Red';SELECT firstName, lastName FROM students WHERE house = 'Red';

Matches

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

↓

ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.

Applying

Meanings

Term

SELECT fields FROM table WHERE condition ORDER BY field DESC;

Meaning

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

Practice

SELECT firstName, points

Testing

Gaps

WHERE = 'Red';

Check Answer

Spots

1SELECT firstName, lastName

2FROM student

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. Request: "List the names of students who are in the Red house." Which query is correct?

A. SELECT house FROM students WHERE firstName = 'Red';

B. SELECT firstName, lastName FROM students WHERE house = 'Red'; ✓

The names go after SELECT, the table after FROM, and the condition house = 'Red' goes in WHERE.

2. Request: "How many records are in the products table with a price over 10?" Which query is correct?

A. SELECT COUNT(*) FROM products WHERE price > 10; ✓

B. SELECT SUM(price) FROM products WHERE price > 10;

"How many records" means COUNT. SUM adds up the values, so it gives the total price instead.

3. Request: "Find the total of all points for students in year 8." Which query is correct?

A. SELECT SUM(points) FROM students WHERE yearLevel = 8; ✓

B. SELECT COUNT(points) FROM students WHERE yearLevel = 8;

"The total of" means SUM. COUNT would only tell you how many records there are.

4. Request: "Show the names of products in order of price, highest first." Which ORDER BY is correct?

A. SELECT name FROM products ORDER BY price ASC;

B. SELECT name FROM products ORDER BY price DESC; ✓

"Highest first" means descending order, so use DESC. ASC puts the lowest value first.

5. Which is the correct order of the clauses when writing a query for "names of students with more than 100 points, highest points first"?

A. SELECT firstName FROM students WHERE points > 100 ORDER BY points DESC; ✓

B. SELECT firstName FROM students ORDER BY points DESC WHERE points > 100;

The clause order is SELECT, FROM, WHERE, then ORDER BY. WHERE must come before ORDER BY.

Match the Pairs

SELECT COUNT(*) FROM students WHERE house = 'Red'; → Answers 'how many records have house Red?' by counting the matching students

SELECT firstName, lastName FROM students WHERE points > 100 ORDER BY points DESC; → Lists the names of students with more than 100 points, highest points first

'The total of' a numeric field for some records needs SUM with a WHERE condition → SELECT SUM(quantity) FROM orders WHERE productId = 2;

'In order of ... highest first' is written with ORDER BY and DESC at the end of the query → SELECT name, price FROM products ORDER BY price DESC;

SELECT firstName FROM students ORDER BY points DESC WHERE house = 'Blue'; → ORDER BY is placed before WHERE. WHERE must come first, then ORDER BY at the end.

SELECT SUM(*) FROM products WHERE stock = 0; → SUM cannot add up * (all fields). To count how many records, use COUNT(*); to total, SUM needs a numeric field.

Key Term

SELECT fields FROM table WHERE condition ORDER BY field DESC;

Turn a request into one SQL statement: pick fields after SELECT, the table after FROM, conditions in WHERE, sort with ORDER BY, and use SUM or COUNT for totals and how many.

Fill in the Blanks

1. SELECT firstName, lastName FROM students WHERE house = 'Red';

Hint: Type the field name the condition tests to find students who are in Red house.

2. SELECT COUNT(*) FROM students WHERE yearLevel = 8;

Hint: Type the SQL function that counts how many records match.

3. SELECT SUM(quantity) FROM orders WHERE productId = 2;

Hint: Type the SQL function that adds up the values to give a total.

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

Hint: Type the keyword that sorts from highest to lowest.

5. SELECT name, price FROM products WHERE stock = 0;

Hint: Type the name of the table that holds the item, price and stock data.

Spot the Error

Question 1

A SELECT firstName, lastName

B FROM student

C WHERE house = 'Red';

Line B — The table is called students, so the FROM line must read FROM students. A table name that does not exist makes the query fail.

Question 2

A SELECT COUNT(*)

B FORM products

C WHERE stock = 0;

Line B — The keyword is misspelt. The table name must follow FROM, so the line should be FROM products.

Question 3

A SELECT SUM(quantity)

B FROM orders

C WHERE studentId == 1;

Line C — SQL tests equality with a single =, not ==. The line should be WHERE studentId = 1;

Question 4

A SELECT name, price

B FROM price

C WHERE category = 'Tech'

D ORDER BY price DESC;

Line B — FROM must be followed by the table name, not a field name. It should be FROM products.

Question 5

A SELECT firstName points

B FROM students

C WHERE points > 100

D ORDER BY points DESC;

Line A — Fields in a SELECT list must be separated by commas. It should be SELECT firstName, points.

Student with a class code? Join your class →