SQL: SELECT, FROM, WHERE and ORDER BY 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 3 of 4 in Databases; follows Primary keys; leads into SQL: SUM, COUNT, AND and OR

Central model

Fig. 1: each line of a query does one job

Cooperative structure

Cooperative Concept Mapping

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

SELECT and FROM: In the lab, build a query on the Books table: tick fields for SELECT and run it.
Choosing records with WHERE: WHERE compares a field with a value.
Sorting with ORDER BY: ORDER BY comes last.

Key points to leave on the board

SELECT fields, FROM table, WHERE condition, ORDER BY field ASC or DESC.
Output: test every record against WHERE, one row at a time, and tick the ones that match.
Then show only the SELECTed fields, and sort if there is an ORDER BY.
Watch < versus <=, and put text values in quotes.

The core visual model

One picture every idea returns to

SQL (structured query language) is used to search a database. SELECT lists the fields to show. FROM names the table. WHERE gives a condition, so only the records that meet it are shown. ORDER BY sorts the results by a field, ASC (ascending: A to Z, smallest first) or DESC (descending: Z to A, largest first). To find the output, check every record against the WHERE condition, keep the matching ones, show only the SELECTed fields, then sort.

Fig. 1: each line of a query does one job

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, Ask the table a question.. A games shop wants every racing game, cheapest first. 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: SELECT and FROM	In the lab, build a query on the Books table: tick fields for SELECT and run it. Project tab 2 of the lab (SQL query builder) and let a student drive it while the class predicts.	Whole class, then pairs
13 to 21 min	Teach and check: Choosing records with WHERE	WHERE compares a field with a value. Model the worked example Text condition on the board, one line at a time. Your turn: Write a query to show the Title and Genre of every game that can be played online.	Pairs
21 to 30 min	Teach and check: Sorting with ORDER BY	ORDER BY comes last. 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 WHERE then ORDER BY on the board, one line at a time. Your turn: Give the output of: SELECT GameID, Title FROM Games WHERE Year > 2020 ORDER BY Title ASC;	Cooperative Concept Mapping
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, 19 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 Sorting with ORDER BY, 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

Cooperative Concept Mapping

Teams receive the key terms on cards and arrange them on a large sheet, drawing and labelling the links. Compare maps between teams before showing the model map.

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
SELECT names fields, not records. "SELECT Racing" is wrong: the word Racing is a value in the Genre field, so it belongs in a WHERE condition.	Return to SELECT and FROM: ask a pair to state the correct version and show it on the lab before moving on.
Read 30 would leave out Dragon Isle at exactly 30.00; WHERE Price >= 30 includes it.	00; WHERE Price >= 30 includes it.
The keywords must be in this order: SELECT, FROM, WHERE, ORDER BY.	And ORDER BY Price DESC sorts the output, it does not choose which records appear.

Worksheet

SQL: SELECT, FROM, WHERE and ORDER BY: worksheet

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

NameClassDate

Q1 · write[3 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 display the Title and Price of every Puzzle game.

Q2 · trace[3 marks]

Using the GAMES table above, give the output of this query.

SELECT GameID, Price
FROM Games
WHERE Online = FALSE
ORDER BY Price ASC;

Q3 · write[4 marks]

Write an SQL query on the GAMES table to display the Title and Year of every game released before 2021, newest first. Then give its output.

Q4 · analysis[3 marks]

A student wants the titles of all Racing games, most expensive first, and writes:

SELECT Title
FROM Games
ORDER BY Price DESC
WHERE Genre = 'Racing';

Identify the error, write the corrected query and give its output.

Q5 · write[4 marks]

Complete the query so that it displays the Title and Genre of every game costing 25.00 or more, in alphabetical order of title.

SELECT ........ , Genre
........ Games
WHERE Price ........ 25
ORDER BY Title ........ ;

Q6 · explain[2 marks]

Explain the purpose of the WHERE and ORDER BY parts of an SQL query.

Answer key and marking notes

Q1 · 3 marks

SELECT Title, Price
FROM Games
WHERE Genre = 'Puzzle';

Q2 · 3 marks

Online = FALSE: G02 40.00, G05 30.00, G06 15.00.
Sorted by Price, smallest first.
G06 15.00; G05 30.00; G02 40.00.

Q3 · 4 marks

SELECT Title, Year FROM Games
WHERE Year < 2021
ORDER BY Year DESC;
Output: Dragon Isle 2020; Castle Quest 2019; Block Town 2018.

Q4 · 3 marks

The WHERE clause must come before ORDER BY.
SELECT Title FROM Games WHERE Genre = 'Racing' ORDER BY Price DESC;
Output: Speed Kings; Star Racer.

Q5 · 4 marks

Title
FROM
>=
ASC (output: Castle Quest Adventure; Dragon Isle Adventure; Speed Kings Racing; Star Racer Racing).

Q6 · 2 marks

WHERE sets a condition (search criteria) so that only records meeting it are included in the output.
ORDER BY sorts the records in the output by a given field, in ascending or descending order.

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

In an SQL query, which keyword is followed by the name of the table to search?

AFROM
BSELECT
CWHERE
DORDER BY

Q21 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 = 'Action';

ANight Patrol; Iron Coast
BF02; F04
CAction; Action
DNight Patrol; Iron Coast; Red Harbour

Q31 mark

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

SELECT Title
FROM Films
WHERE Minutes > 104;

ANight Patrol; Iron Coast; Red Harbour
BNight Patrol; Iron Coast; Lost Valley; Red Harbour
COcean Deep; Moon Garden; Lost Valley; Paper Kites
DIron Coast; Red Harbour; Night Patrol

Q41 mark

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

SELECT Title, Rental
FROM Films
WHERE Subtitled = TRUE
ORDER BY Rental ASC;

ARed Harbour 4.00; Ocean Deep 4.50; Iron Coast 5.00; Moon Garden 5.50
BMoon Garden 5.50; Iron Coast 5.00; Ocean Deep 4.50; Red Harbour 4.00
COcean Deep 4.50; Moon Garden 5.50; Iron Coast 5.00; Red Harbour 4.00
DLost Valley 2.50; Night Patrol 3.00; Paper Kites 3.50; Red Harbour 4.00

Q51 mark

A student wants the titles of all Animation films, longest first, and writes:

SELECT Title
FROM Films
ORDER BY Minutes DESC
WHERE Genre = 'Animation';

Which statement about the query is correct?

AWHERE must come before ORDER BY; corrected, it outputs Paper Kites; Moon Garden
BMinutes must be selected to sort by it; corrected, it outputs Moon Garden; Paper Kites
CDESC must be ASC to put the longest first; corrected, it outputs Moon Garden; Paper Kites
DGenre must be selected to test it; corrected, it outputs Paper Kites; Moon Garden

Q61 mark

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

SELECT FilmID
FROM Films
WHERE Minutes < 100
ORDER BY Title DESC;

AF06; F01; F03
BF03; F01; F06
CF01; F06; F03
DF01; F03; F06

Quiz key

Q1: A

FROM names the table, for example FROM Films. SELECT lists the fields to show. WHERE gives the condition that chooses which records appear. ORDER BY names the field used to sort the results.

Q2: A

Only F02 and F04 have Genre = 'Action', and SELECT Title shows just their titles: Night Patrol and Iron Coast. F02 and F04 are the FilmIDs, but FilmID is not selected. Showing Action twice displays the Genre field, which was not selected either. Red Harbour is a Drama film, so it does not meet the condition.

Q3: A

Minutes > 104 keeps F02 (118), F04 (132) and F07 (121), shown in table order. Including Lost Valley treats > as >=, but 104 is not greater than 104. The four short films are the records that do not match (Minutes <= 104). Listing Iron Coast first sorts by Minutes, but the query has no ORDER BY.

Q4: A

Subtitled = TRUE keeps F01 (4.50), F03 (5.50), F04 (5.00) and F07 (4.00); ASC sorts the rental prices smallest first: 4.00, 4.50, 5.00, 5.50. Starting with Moon Garden is DESC order. Ocean Deep, Moon Garden, Iron Coast, Red Harbour is table order, ignoring ORDER BY. The last list sorts the cheapest films in the whole table, ignoring the WHERE condition.

Q5: A

The clauses must appear in the order SELECT, FROM, WHERE, ORDER BY, so WHERE has to move above ORDER BY. The Animation films are Moon Garden (88) and Paper Kites (92); longest first gives Paper Kites then Moon Garden. A field can be used in ORDER BY or WHERE without being selected, so neither Minutes nor Genre needs adding to SELECT. DESC (largest first) is already right for longest first; ASC would put the shortest first.

Q6: A

Minutes < 100 keeps F01 Ocean Deep (95), F03 Moon Garden (88) and F06 Paper Kites (92). Sorting the titles Z to A gives Paper Kites, Ocean Deep, Moon Garden, so the FilmIDs come out F06, F01, F03. F03, F01, F06 is A to Z (ASC). F01, F06, F03 sorts by Minutes, longest first. F01, F03, F06 is table order, ignoring ORDER BY.

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: SELECT, FROM, WHERE and ORDER BY 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.