Year 10 · Computer Science · Summer 2

Databases and the Year 10 mock · Lesson 3 of 5

SUM and COUNT; validation in databases

Exam practice9.4 SQL: ORDER BY, SUM and COUNT9.2 Data types, validation and primary keys

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

You will learn to

Use SUM to total a numeric field and COUNT to count records.
Suggest a validation check for each field in a table, and justify it.
Answer past-paper database questions under time.

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

Success criteria

You have got it when you can say:

I can write a query that totals a field for records that meet a condition.
I can write a COUNT query and predict its answer.
I can name a suitable check for each field and say why.

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

Retrieval starter

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

Last lesson
A function differs from a procedure because it…

A Returns a valueB Has a nameC Can have parameters

A. Returns a value
Last unit
The bootloader…

A Prints documentsB Loads the operating system from storage into RAM at start-upC Translates Python

B. Loads the operating system from storage into RAM at start-up
Earlier this year
Abnormal test data is…

A Data that should be rejectedB Data at the edge of the rangeC Typical data

A. Data that should be rejected

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

Key words (1 of 5)

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 3 of 5

Key words (2 of 5)

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.
Character: A data type for exactly one letter, digit or symbol, such as E or M.

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

Key words (3 of 5)

Boolean: A data type with only two possible values, TRUE or FALSE.
Integer: A data type for whole numbers, such as 55.
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.

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

Key words (4 of 5)

Primary key: A field whose value is unique for every record, so it identifies each record.
Validation: An automatic check that data is sensible and follows the rules before it is accepted.
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 3 of 5

Key words (5 of 5)

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.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 1 of 9 · part 1 of 3

ORDER BY: sorting the output

ORDER BY sorts the output by one field. It always comes last in the query.

ASC means ascending: smallest to largest, A to Z, oldest date to newest.

DESC means descending: largest to smallest, Z to A, newest date to oldest.

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 1 of 9 · part 2 of 3

ORDER BY: sorting the output (continued)

HireID	Minutes
H02	40
H04	18
H07	8

e.g.Example

Filter BikeType = 'M': H02 (40), H04 (18), H07 (8) Sort by Minutes, largest first: 40, 18, 8

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 1 of 9 · part 3 of 3

ORDER BY: sorting the output (continued)

SELECT HireID, Minutes
FROM BikeHire
WHERE BikeType = 'M'
ORDER BY Minutes DESC;

?Why?

If you leave out ASC or DESC, the database sorts in ascending order. Writing ASC anyway makes your answer clear to the examiner.

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 2 of 9 · part 1 of 2

SUM: adding up a field

SUM adds up all the values in a numeric field, for the records that meet the condition.

The output is one single number, not a list of records.

Only use SUM on integer or real fields. You cannot add up text.

e.g.Example

Members (Member = TRUE): H01, H03, H04, H06 Costs: 2.50 + 1.20 + 0.90 + 3.00 2.50 + 1.20 = 3.70 3.70 + 0.90 = 4.60 4.60 + 3.00 = 7.60 Output: 7.60

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 2 of 9 · part 2 of 2

SUM: adding up a field (continued)

SELECT SUM(Cost)
FROM BikeHire
WHERE Member = TRUE;

!Watch out

Put the field name inside the brackets: `SUM(Cost)`. Writing `SUM Cost` or `SUM(Member)` will not earn the mark.

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 3 of 9 · part 1 of 2

COUNT: how many records match

COUNT counts how many records meet the condition. It does not add the values.

The output is one single whole number.

Here we write `COUNT(HireID)`. You may also see `COUNT(*)`, which counts every matching record in the same way.

e.g.Example

Electric bikes: H01, H03, H05, H06, H08 Count them: 1, 2, 3, 4, 5 Output: 5

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 3 of 9 · part 2 of 2

COUNT: how many records match (continued)

SELECT COUNT(HireID)
FROM BikeHire
WHERE BikeType = 'E';

?Why?

SUM answers "how much?". COUNT answers "how many?". Read the question for those words to choose the right one.

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 4 of 9 · part 1 of 3

Putting it all together

A full query can use WHERE with AND, then ORDER BY. Work through it with the three moves: filter, pick columns, sort.

Then test yourself in the toy below. Its BikeHire table has different fields: BikeID, Type, Station, Hours and Cost. Type a query, predict the output on paper first, then press Run query.

Try the example buttons, then change `ASC` to `DESC`, or `AND` to `OR`. Watch how the output changes.

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 4 of 9 · part 2 of 3

Putting it all together (continued)

Station	Minutes	Cost
Moncloa	55	5.50
Retiro	40	2.00
Sol	42	4.20

e.g.Example

Filter: non-members (H02, H05, H07, H08) whose cost is 2.00 or more H02 2.00 ✓ · H05 5.50 ✓ · H07 0.40 ✗ · H08 4.20 ✓ Pick columns: Station, Minutes, Cost Sort by Station A to Z: Moncloa, Retiro, Sol

9.4 SQL: ORDER BY, SUM and COUNT · The explanation · step 4 of 9 · part 3 of 3

Putting it all together (continued)

SELECT Station, Minutes, Cost
FROM BikeHire
WHERE Cost >= 2.00 AND Member = FALSE
ORDER BY Station ASC;

There is something to try for this step in the IGCSE library.

9.2 Data types, validation and primary keys · The explanation · step 8 of 9 · part 1 of 2

Validation in a database

Validation is an automatic check that data is sensible before the database accepts it. It does not prove the data is correct.

You met these checks in topic 7: range, length, type, presence, format and check digit.

In a database, each check is set on a field. The database then refuses bad data when someone types it in.

9.2 Data types, validation and primary keys · The explanation · step 8 of 9 · part 2 of 2

Validation in a database (continued)

Field	Check	Rule
Minutes	Range	Between 1 and 240
Minutes	Type	Must be an integer
HireID	Format	The letter H followed by two digits
HireID	Length	Exactly 3 characters
Station	Presence	Must not be left empty

?Why?

A wrong value, like 5500 minutes, would ruin every total and count. Stopping it at the door is easier than finding it later.

The explanation · 4 more steps

More on the lesson page

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

Writing a query from a question
The six data types
Choosing a data type: the tricky cases
The primary key

Open the lesson page

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

Quick quiz · 1 of 3

In a trace table you write a new row…

A Only at the end
B Only for OUTPUT
C Each time a value changes

C. Each time a value changes

One column per variable, one row per change.

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

Quick quiz · 2 of 3

A diamond in a flowchart means…

A A decision
B A process
C Input or output

A. A decision

Rectangle: process. Parallelogram: input or output. Diamond: a question with yes and no.

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

Quick quiz · 3 of 3

Bubble sort stops when…

A Half the list is sorted
B A whole pass makes no swaps
C The first swap happens

B. A whole pass makes no swaps

No swaps in a pass means everything is in order.

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

Exit ticket

Answer on paper before you go. Then check.

FOR i ← 1 TO 10 … NEXT i runs the loop…

A 9 timesB 10 timesC 11 times

B. 10 times 1 to 10 inclusive. Python's range(1, 11) does the same.
A REPEAT … UNTIL loop…

A Always runs at least onceB May run zero timesC Runs for ever

A. Always runs at least once The test is at the end, so the body runs first. WHILE tests first and may run zero times.