SQL: SELECT, FROM, WHERE & ORDER BY — Cambridge IGCSE CS (0478)

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · SQL scripts to query data Paper 2 · Algorithms, Programming and Logic

By the end of this lesson you can:

Write an SQL query with SELECT, FROM, WHERE and ORDER BY, including SELECT *.
Use the comparison operators = > < >= <= <> with values written to match the field's data type.
Show exactly what a given query outputs, in the right order.

Textbook: Chapter 9, §9.1.4 (pp. 344–347)

Recap / Warm-Up 5 min

So far: a table of records and fields, each field with a data type, and one primary key. Now we ask the table questions.

Quick starter

Here is the table for this lesson — a film-rental shop's stock. State its primary key and the data type of Price.

Table FILM (10 records, 7 fields)
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F01	Ocean Deep	Documentary	2021	95	3.99	4
F02	Robot Rescue	Animation	2019	88	2.99	6
F03	The Last Orbit	Sci-Fi	2023	132	5.49	3
F04	Laugh Out Loud	Comedy	2018	101	1.99	5
F05	Storm Chasers	Action	2022	117	4.49	2
F06	Star Academy	Sci-Fi	2020	124	3.49	5
F07	Pizza Party	Comedy	2023	92	4.99	7
F08	Silent Forest	Drama	2017	110	1.49	1
F09	Space Pups	Animation	2022	84	3.99	8
F10	Second Chance	Drama	2021	106	2.49	3

Reveal the answer

Primary key: FilmID (unique for every film). Price is real (it has decimals).

🧠 Key Concept 14 min

1 · SQL and SQL scripts

SQL (Structured Query Language) is the standard language for getting information out of a database. A list of SQL commands saved to do one job is an SQL script. It can be run again whenever needed.

Real world. A shop assistant only needs the titles in stock. A manager wants every detail of the dramas. Both ask the same table — each with a different query.

2 · The four clauses

Clause	What it does	Needed?
`SELECT`	Chooses which fields (columns) to show. A query always starts with it.	Always
`FROM`	Names the table to use.	Always
`WHERE`	Keeps only the records (rows) that match a condition.	Optional
`ORDER BY`	Sorts the results by a field, alphabetically or numerically.	Optional

A query ends with a semicolon ;. SELECT * means "show all fields".

The four clauses as a pipeline. Each one narrows or reorders the data until only the result set is left.

3 · Conditions and comparison operators

Operator	Meaning	Example
`=`	equal to	`Genre = 'Drama'`
`>`	greater than	`Minutes > 120`
`<`	less than	`Price < 3.00`
`>=`	greater than or equal to	`ReleaseYear >= 2022`
`<=`	less than or equal to	`Copies <= 2`
`<>`	not equal to	`Genre <> 'Comedy'`

4 · Write values to match the data type

Field type	How to write the value	Example
Text	In single quotes	`'Space Pups'`
Character	In single quotes	`'M'`
Boolean	No quotes	`TRUE`
Integer / real	No quotes	`2022` · `3.99`
Date/time	In single quotes	`'2024-03-10'`

Beyond the syllabus (not examined): Microsoft Access writes dates between hashes, e.g. #10/03/2024#, and accepts double quotes for text. The exam expects single quotes.

5 · A first query, and its output

SELECT FilmID, Title, Minutes
FROM FILM
WHERE Minutes > 120;

FILM — kept rows in bold, chosen fields shaded
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F01	Ocean Deep	Documentary	2021	95	3.99	4
F02	Robot Rescue	Animation	2019	88	2.99	6
F03	The Last Orbit	Sci-Fi	2023	132	5.49	3
F04	Laugh Out Loud	Comedy	2018	101	1.99	5
F05	Storm Chasers	Action	2022	117	4.49	2
F06	Star Academy	Sci-Fi	2020	124	3.49	5
F07	Pizza Party	Comedy	2023	92	4.99	7
F08	Silent Forest	Drama	2017	110	1.49	1
F09	Space Pups	Animation	2022	84	3.99	8
F10	Second Chance	Drama	2021	106	2.49	3

Result set — 2 rows
FilmID	Title	Minutes
F03	The Last Orbit	132
F06	Star Academy	124

6 · ORDER BY

ORDER BY Title sorts in ascending order: A→Z, or smallest→largest. Ascending is the default.
ORDER BY Minutes DESC sorts in descending order: Z→A, or largest→smallest.
ORDER BY Genre, Title sorts by Genre first; Title only breaks ties.

Without ORDER BY, records come out in the order they are stored in the table.

Worked Example 12 min

(a) "List the title and price of every comedy."

SELECT Title, PriceThe question asks for two fields only, separated by a comma.
FROM FILMName the table the data lives in.
WHERE Genre = 'Comedy';Genre is text, so the value goes in single quotes. The semicolon ends the query.
Scan Genre row by row: F04 and F07 match. Copy their Title and Price, in table order.No ORDER BY, so stored order is kept.

SELECT Title, Price
FROM FILM
WHERE Genre = 'Comedy';

Result set — 2 rows
Title	Price
Laugh Out Loud	1.99
Pizza Party	4.99

(b) "Show recent films (2022 or later), newest first."

SELECT Title, ReleaseYearShow the year so the order can be checked.
FROM FILM
WHERE ReleaseYear >= 2022"2022 or later" includes 2022, so use >= not >. ReleaseYear is an integer: no quotes.
ORDER BY ReleaseYear DESC, Title;DESC puts the newest first. Two films share 2023 and two share 2022, so Title sorts each tie A→Z.
Matching rows: F03 (2023), F05 (2022), F07 (2023), F09 (2022). Sort them.2023s first: Pizza Party before The Last Orbit (P before T). Then 2022s: Space Pups before Storm Chasers (Sp before St).

SELECT Title, ReleaseYear
FROM FILM
WHERE ReleaseYear >= 2022
ORDER BY ReleaseYear DESC, Title;

Result set — 4 rows
Title	ReleaseYear
Pizza Party	2023
The Last Orbit	2023
Space Pups	2022
Storm Chasers	2022

(c) "Show every detail of the dramas."

SELECT *"Every detail" means all fields — the asterisk saves listing all seven.
FROM FILM
WHERE Genre = 'Drama';

SELECT *
FROM FILM
WHERE Genre = 'Drama';

Result set — 2 rows
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F08	Silent Forest	Drama	2017	110	1.49	1
F10	Second Chance	Drama	2021	106	2.49	3

Try It Yourself 12 min

🟢 Easy

Goal: Write a query to show the Title and Copies of every film with 2 copies or fewer. Then show its output.

🟡 Medium

Goal: Write a query to list the Title and Genre of films under 3.00, in alphabetical order of title. Show the output.

🔴 Stretch

Goal: Write a query listing every field of all films that are not Sci-Fi, longest first. How many rows does it return, and which title is last?

Hint

Use <> in the WHERE clause and DESC on Minutes. Two Sci-Fi films are removed.

📝 Exam Practice 10 min

A garden centre stores its stock in a table, PLANT. Questions 1–3 use this table.

Table PLANT (8 records, 6 fields)
PlantCode	PlantName	PlantType	HeightCm	Price	Stock
PL01	Sunflower	Flower	180	3.50	40
PL02	Basil	Herb	30	2.00	25
PL03	Tomato	Vegetable	120	2.75	0
PL04	Lavender	Herb	60	4.25	18
PL05	Rose	Flower	90	6.99	12
PL06	Mint	Herb	40	1.80	30
PL07	Pepper	Vegetable	70	3.10	0
PL08	Tulip	Flower	45	1.20	55

Show[2]

Show the output from this query.

SELECT PlantName, Price
FROM PLANT
WHERE Stock = 0;

Mark scheme

Tomato 2.75 (1).
Pepper 3.10 (1).
Only these two fields shown; no other records.

Show[3]

Show the output from this query.

SELECT PlantCode, PlantName
FROM PLANT
WHERE HeightCm > 60
ORDER BY HeightCm DESC;

Mark scheme

Correct four records only: PL01, PL03, PL05, PL07 (1).
Only PlantCode and PlantName shown (1).
In the order PL01 Sunflower, PL03 Tomato, PL05 Rose, PL07 Pepper (1).

Result set — 4 rows
PlantCode	PlantName
PL01	Sunflower
PL03	Tomato
PL05	Rose
PL07	Pepper

Write[4]

Write an SQL query to display the name and height of every herb, in alphabetical order of name.

Mark scheme

SELECT PlantName, HeightCm
FROM PLANT
WHERE PlantType = 'Herb'
ORDER BY PlantName;

SELECT PlantName, HeightCm (1).
FROM PLANT (1).
WHERE PlantType = 'Herb' (1).
ORDER BY PlantName — accept ASC (1).

Output: Basil 30, Lavender 60, Mint 40.

Recap & Key Terms 3 min

SELECT picks fields, FROM names the table, WHERE keeps matching records and ORDER BY sorts them. Text and dates go in quotes; numbers do not. End with a semicolon.

SQL: Structured Query Language — the standard language for writing scripts that get information from a database.
SQL script: A list of SQL commands that perform a task, often saved in a file so it can be reused.
SELECT: Fetches the specified fields (columns) from a table; SELECT * fetches all fields.
FROM: Identifies the table to use.
WHERE: Includes only the records (rows) that match a given condition.
ORDER BY: Sorts the results by a given field, alphabetically or numerically; ascending unless DESC is given.

Homework 1 min

Task (≤ 15 min): Using the PLANT table, show the output of this query.

SELECT PlantName, Stock
FROM PLANT
WHERE Price >= 3.00
ORDER BY Stock;

Model answer

Result set — 4 rows
PlantName	Stock
Pepper	0
Rose	12
Lavender	18
Sunflower	40

Four plants cost 3.00 or more (Sunflower, Lavender, Rose, Pepper). Sorted by stock, smallest first.

🎯 Syllabus & Goals 3 min

Cambridge 9.1 · SQL scripts to query data Paper 2 · Algorithms, Programming and Logic

By the end of this lesson you can:

Write an SQL query with SELECT, FROM, WHERE and ORDER BY, including SELECT *.
Use the comparison operators = > < >= <= <> with values written to match the field's data type.
Show exactly what a given query outputs, in the right order.

Textbook: Chapter 9, §9.1.4 (pp. 344–347)

Recap / Warm-Up 5 min

So far: a table of records and fields, each field with a data type, and one primary key. Now we ask the table questions.

Quick starter

Here is the table for this lesson — a film-rental shop's stock. State its primary key and the data type of Price.

Table FILM (10 records, 7 fields)
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F01	Ocean Deep	Documentary	2021	95	3.99	4
F02	Robot Rescue	Animation	2019	88	2.99	6
F03	The Last Orbit	Sci-Fi	2023	132	5.49	3
F04	Laugh Out Loud	Comedy	2018	101	1.99	5
F05	Storm Chasers	Action	2022	117	4.49	2
F06	Star Academy	Sci-Fi	2020	124	3.49	5
F07	Pizza Party	Comedy	2023	92	4.99	7
F08	Silent Forest	Drama	2017	110	1.49	1
F09	Space Pups	Animation	2022	84	3.99	8
F10	Second Chance	Drama	2021	106	2.49	3

Reveal the answer

Primary key: FilmID (unique for every film). Price is real (it has decimals).

🧠 Key Concept 14 min

1 · SQL and SQL scripts

Real world. A shop assistant only needs the titles in stock. A manager wants every detail of the dramas. Both ask the same table — each with a different query.

2 · The four clauses

Clause	What it does	Needed?
`SELECT`	Chooses which fields (columns) to show. A query always starts with it.	Always
`FROM`	Names the table to use.	Always
`WHERE`	Keeps only the records (rows) that match a condition.	Optional
`ORDER BY`	Sorts the results by a field, alphabetically or numerically.	Optional

A query ends with a semicolon ;. SELECT * means "show all fields".

The four clauses as a pipeline. Each one narrows or reorders the data until only the result set is left.

3 · Conditions and comparison operators

Operator	Meaning	Example
`=`	equal to	`Genre = 'Drama'`
`>`	greater than	`Minutes > 120`
`<`	less than	`Price < 3.00`
`>=`	greater than or equal to	`ReleaseYear >= 2022`
`<=`	less than or equal to	`Copies <= 2`
`<>`	not equal to	`Genre <> 'Comedy'`

4 · Write values to match the data type

Field type	How to write the value	Example
Text	In single quotes	`'Space Pups'`
Character	In single quotes	`'M'`
Boolean	No quotes	`TRUE`
Integer / real	No quotes	`2022` · `3.99`
Date/time	In single quotes	`'2024-03-10'`

Beyond the syllabus (not examined): Microsoft Access writes dates between hashes, e.g. #10/03/2024#, and accepts double quotes for text. The exam expects single quotes.

5 · A first query, and its output

SELECT FilmID, Title, Minutes
FROM FILM
WHERE Minutes > 120;

FILM — kept rows in bold, chosen fields shaded
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F01	Ocean Deep	Documentary	2021	95	3.99	4
F02	Robot Rescue	Animation	2019	88	2.99	6
F03	The Last Orbit	Sci-Fi	2023	132	5.49	3
F04	Laugh Out Loud	Comedy	2018	101	1.99	5
F05	Storm Chasers	Action	2022	117	4.49	2
F06	Star Academy	Sci-Fi	2020	124	3.49	5
F07	Pizza Party	Comedy	2023	92	4.99	7
F08	Silent Forest	Drama	2017	110	1.49	1
F09	Space Pups	Animation	2022	84	3.99	8
F10	Second Chance	Drama	2021	106	2.49	3

Result set — 2 rows
FilmID	Title	Minutes
F03	The Last Orbit	132
F06	Star Academy	124

6 · ORDER BY

ORDER BY Title sorts in ascending order: A→Z, or smallest→largest. Ascending is the default.
ORDER BY Minutes DESC sorts in descending order: Z→A, or largest→smallest.
ORDER BY Genre, Title sorts by Genre first; Title only breaks ties.

Without ORDER BY, records come out in the order they are stored in the table.

Worked Example 12 min

(a) "List the title and price of every comedy."

SELECT Title, PriceThe question asks for two fields only, separated by a comma.
FROM FILMName the table the data lives in.
WHERE Genre = 'Comedy';Genre is text, so the value goes in single quotes. The semicolon ends the query.
Scan Genre row by row: F04 and F07 match. Copy their Title and Price, in table order.No ORDER BY, so stored order is kept.

SELECT Title, Price
FROM FILM
WHERE Genre = 'Comedy';

Result set — 2 rows
Title	Price
Laugh Out Loud	1.99
Pizza Party	4.99

(b) "Show recent films (2022 or later), newest first."

SELECT Title, ReleaseYearShow the year so the order can be checked.
FROM FILM
WHERE ReleaseYear >= 2022"2022 or later" includes 2022, so use >= not >. ReleaseYear is an integer: no quotes.
ORDER BY ReleaseYear DESC, Title;DESC puts the newest first. Two films share 2023 and two share 2022, so Title sorts each tie A→Z.
Matching rows: F03 (2023), F05 (2022), F07 (2023), F09 (2022). Sort them.2023s first: Pizza Party before The Last Orbit (P before T). Then 2022s: Space Pups before Storm Chasers (Sp before St).

SELECT Title, ReleaseYear
FROM FILM
WHERE ReleaseYear >= 2022
ORDER BY ReleaseYear DESC, Title;

Result set — 4 rows
Title	ReleaseYear
Pizza Party	2023
The Last Orbit	2023
Space Pups	2022
Storm Chasers	2022

(c) "Show every detail of the dramas."

SELECT *"Every detail" means all fields — the asterisk saves listing all seven.
FROM FILM
WHERE Genre = 'Drama';

SELECT *
FROM FILM
WHERE Genre = 'Drama';

Result set — 2 rows
FilmID	Title	Genre	ReleaseYear	Minutes	Price	Copies
F08	Silent Forest	Drama	2017	110	1.49	1
F10	Second Chance	Drama	2021	106	2.49	3

Try It Yourself 12 min

🟢 Easy

Goal: Write a query to show the Title and Copies of every film with 2 copies or fewer. Then show its output.

🟡 Medium

Goal: Write a query to list the Title and Genre of films under 3.00, in alphabetical order of title. Show the output.

🔴 Stretch

Goal: Write a query listing every field of all films that are not Sci-Fi, longest first. How many rows does it return, and which title is last?

Hint

Use <> in the WHERE clause and DESC on Minutes. Two Sci-Fi films are removed.

📝 Exam Practice 10 min

A garden centre stores its stock in a table, PLANT. Questions 1–3 use this table.

Table PLANT (8 records, 6 fields)
PlantCode	PlantName	PlantType	HeightCm	Price	Stock
PL01	Sunflower	Flower	180	3.50	40
PL02	Basil	Herb	30	2.00	25
PL03	Tomato	Vegetable	120	2.75	0
PL04	Lavender	Herb	60	4.25	18
PL05	Rose	Flower	90	6.99	12
PL06	Mint	Herb	40	1.80	30
PL07	Pepper	Vegetable	70	3.10	0
PL08	Tulip	Flower	45	1.20	55

Show[2]

Show the output from this query.

SELECT PlantName, Price
FROM PLANT
WHERE Stock = 0;

Mark scheme

Tomato 2.75 (1).
Pepper 3.10 (1).
Only these two fields shown; no other records.

Show[3]

Show the output from this query.

SELECT PlantCode, PlantName
FROM PLANT
WHERE HeightCm > 60
ORDER BY HeightCm DESC;

Mark scheme

Correct four records only: PL01, PL03, PL05, PL07 (1).
Only PlantCode and PlantName shown (1).
In the order PL01 Sunflower, PL03 Tomato, PL05 Rose, PL07 Pepper (1).

Result set — 4 rows
PlantCode	PlantName
PL01	Sunflower
PL03	Tomato
PL05	Rose
PL07	Pepper

Write[4]

Write an SQL query to display the name and height of every herb, in alphabetical order of name.

Mark scheme

SELECT PlantName, HeightCm
FROM PLANT
WHERE PlantType = 'Herb'
ORDER BY PlantName;

SELECT PlantName, HeightCm (1).
FROM PLANT (1).
WHERE PlantType = 'Herb' (1).
ORDER BY PlantName — accept ASC (1).

Output: Basil 30, Lavender 60, Mint 40.

Recap & Key Terms 3 min

SELECT picks fields, FROM names the table, WHERE keeps matching records and ORDER BY sorts them. Text and dates go in quotes; numbers do not. End with a semicolon.

SQL: Structured Query Language — the standard language for writing scripts that get information from a database.
SQL script: A list of SQL commands that perform a task, often saved in a file so it can be reused.
SELECT: Fetches the specified fields (columns) from a table; SELECT * fetches all fields.
FROM: Identifies the table to use.
WHERE: Includes only the records (rows) that match a given condition.
ORDER BY: Sorts the results by a given field, alphabetically or numerically; ascending unless DESC is given.

Homework 1 min

Task (≤ 15 min): Using the PLANT table, show the output of this query.

SELECT PlantName, Stock
FROM PLANT
WHERE Price >= 3.00
ORDER BY Stock;

Model answer

Result set — 4 rows
PlantName	Stock
Pepper	0
Rose	12
Lavender	18
Sunflower	40

Four plants cost 3.00 or more (Sunflower, Lavender, Rose, Pepper). Sorted by stock, smallest first.