Year 10 · Computer Science · Summer 2

Databases and the Year 10 mock · Lesson 2 of 5

SQL: SELECT, FROM, WHERE, ORDER BY

Practical9.3 SQL: SELECT, FROM, WHERE, AND, OR9.4 SQL: ORDER BY, SUM and COUNT

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

You will learn to

Explain what SELECT, FROM and WHERE each do in a query.
Write WHERE conditions with comparison operators, AND and OR.
Sort the results with ORDER BY, using ASC or DESC.
Predict the exact output of a given query.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Success criteria

You have got it when you can say:

I can write ten past-paper queries by hand.
I can predict which records and fields a query will show.
I can sort the output by one field, highest first.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

Last lesson
The data type for 'has paid' (yes or no) is…

A TextB BooleanC Real

B. Boolean
Last unit
Which is a low-level language?

A Assembly languageB PythonC Java

A. Assembly language
Earlier this year
Which of these is lossy?

A PNGB JPEGC ZIP

B. JPEG

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Key words (1 of 4)

Database: An organised, structured store of data that a computer can search and sort.
Table: A grid of data made of rows (records) and columns (fields).
Field: One column of a table; it holds one item of data about every record, such as Minutes.
Record: One row of a table; it holds all the data about one thing, such as one bike hire.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Key words (2 of 4)

Field name: The heading at the top of a column, such as HireID; it is not a record.
Data type: The kind of data a field can hold, such as integer, real or Boolean.
Text / alphanumeric: A data type for letters, digits and symbols, such as a name or a phone number.
Integer: A data type for whole numbers, such as 55.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Key words (3 of 4)

Real: A data type for numbers with a decimal part, such as 5.50.
Date/time: A data type for a date, a time, or both, such as 05/03/2026.
SQL: Structured Query Language: the language used to ask a database for data.
Query: A question to the database, written in SQL, that returns matching data.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Key words (4 of 4)

ORDER BY: The SQL clause that sorts the output, ascending (ASC) or descending (DESC).
SUM: An SQL function that adds up the values in a numeric field.
COUNT: An SQL function that counts how many records meet a condition.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 1 of 9 · part 1 of 3

The shape of a query

SQL (Structured Query Language) is the language used to ask a database for data. A question written in SQL is called a query.

`SELECT` lists the fields you want to see. `FROM` names the table. `WHERE` gives the condition a record must meet.

The output is a new, smaller table. It has only the chosen fields, and only the records that meet the condition.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 1 of 9 · part 2 of 3

The shape of a query (continued)

Symbol	Meaning	Example
=	equal to	Station = 'Sol'
<>	not equal to	BikeType <> 'E'
>	greater than	Minutes > 30
<	less than	Cost < 1.00
>=	greater than or equal to	Minutes >= 30
<=	less than or equal to	Cost <= 2.00

SELECT HireID, Station
FROM BikeHire
WHERE Minutes > 30;

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 1 of 9 · part 3 of 3

The shape of a query (continued)

?Why?

Text values go inside single quotes, like `'Sol'`, so SQL knows where the text starts and ends. Numbers and TRUE/FALSE have no quotes.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 2 of 9 · part 1 of 3

Predicting output: one condition

Use the same three moves every time. First, filter: go down the table and tick each record that meets the WHERE condition.

Second, pick columns: keep only the fields named after SELECT, in the order they are listed.

Third, sort, if there is an ORDER BY. With no ORDER BY, keep the records in table order.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 2 of 9 · part 2 of 3

Predicting output: one condition (continued)

HireID	Minutes
H02	40
H05	55
H08	42

e.g.Example

Filter: Minutes > 30 H01 25 ✗ · H02 40 ✓ · H03 12 ✗ · H04 18 ✗ H05 55 ✓ · H06 30 ✗ · H07 8 ✗ · H08 42 ✓ Pick columns HireID and Minutes. The output is in the table below.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 2 of 9 · part 3 of 3

Predicting output: one condition (continued)

SELECT HireID, Minutes
FROM BikeHire
WHERE Minutes > 30;

!Watch out

H06 has exactly 30 minutes. 30 is not greater than 30, so H06 is not in the output. Read `>` and `>=` very carefully.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 3 of 9 · part 1 of 2

AND: both conditions must be true

AND joins two conditions. A record is shown only if both conditions are true.

AND makes the output smaller, because each record has to pass two tests.

HireID	Cost
H01	2.50
H08	4.20

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 3 of 9 · part 2 of 2

AND: both conditions must be true (continued)

e.g.Example

Records at Sol: H01 (E), H04 (M), H08 (E) H01: Sol ✓ and E ✓ → shown H04: Sol ✓ and M ✗ → not shown H08: Sol ✓ and E ✓ → shown

SELECT HireID, Cost
FROM BikeHire
WHERE Station = 'Sol' AND BikeType = 'E';

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 4 of 9 · part 1 of 3

OR: at least one condition must be true

OR joins two conditions. A record is shown if either condition is true, or both.

OR makes the output bigger, because a record has two chances to pass.

Write the full condition on both sides of OR. The field name must appear twice.

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 4 of 9 · part 2 of 3

OR: at least one condition must be true (continued)

HireID	Station
H02	Retiro
H03	Atocha
H06	Retiro
H07	Atocha

SELECT HireID, Station
FROM BikeHire
WHERE Station = 'Retiro' OR Station = 'Atocha';

9.3 SQL: SELECT, FROM, WHERE, AND, OR · The explanation · step 4 of 9 · part 3 of 3

OR: at least one condition must be true (continued)

!Watch out

`WHERE Station = 'Retiro' OR 'Atocha'` is wrong and loses the mark. Each side of OR must be a complete condition: `Station = 'Atocha'`.

The explanation · 5 more steps

More on the lesson page

The lesson page has the rest of the explanation, step by step:

ORDER BY: sorting the output
SUM: adding up a field
COUNT: how many records match
Putting it all together
Writing a query from a question

Open the lesson page

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Try it: Ask the database

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Quick quiz · 1 of 3

Text in a WHERE clause goes in…

A Single quotes: 'Red'
B Brackets
C No quotes

A. Single quotes: 'Red'

Without quotes the database looks for a field called Red.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Quick quiz · 2 of 3

The four stages of the program development life cycle are…

A Input, process, output, storage
B Analysis, design, coding, testing
C Fetch, decode, execute, store

B. Analysis, design, coding, testing

Analyse the problem, design the solution, write the code, test it.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Quick quiz · 3 of 3

Abnormal test data is…

A Data that should be rejected
B Data at the edge of the range
C Typical data

A. Data that should be rejected

Normal works, abnormal is rejected, extreme sits on the edge, boundary is either side of it.

Year 10 · Computer Science · Databases and the Year 10 mock · Lesson 2 of 5

Exit ticket

Answer on paper before you go. Then check.

A range check on age 0 to 120 would reject…

A 0B 120C 150

C. 150 0 and 120 are the edges and are allowed. 150 is outside.
Typing an email address twice to check it is…

A ValidationB A trace tableC Verification

C. Verification Validation checks the data is sensible; verification checks it was entered as intended.