SUM and COUNT; validation in databases

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.

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

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.

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

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.

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

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.

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.

Writing a query from a question

Exam questions describe what is needed in words. Turn each part of the sentence into one line of SQL.

"Show the hire ID and station" → SELECT. "from the bike-hire table" → FROM. "of hires under 20 minutes or from Moncloa" → WHERE. "highest hire ID first" → ORDER BY … DESC.

Copy field names and the table name exactly as they appear in the table.

SELECT HireID, Station
FROM BikeHire
WHERE Minutes < 20 OR Station = 'Moncloa'
ORDER BY HireID DESC;

The six data types

Every field has a data type. The data type tells the database what kind of data the field may hold.

Cambridge uses six data types for databases. Learn them with an example from `BikeHire`.

Choosing a data type: the tricky cases

Ask two questions. Will I do maths with it? Is it a whole number or does it need decimals?

A phone number is text, not integer. It can start with 0 or +34, and you never add phone numbers together.

A field with only yes/no answers is Boolean. A field that always holds one single symbol, like a grade `A` or a size `M`, is character.

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.

The primary key

A primary key is a field that holds a unique value for every record. No two records can have the same value in that field.

It lets the database pick out exactly one record, with no confusion.

To choose one, look for a field where no value can ever repeat, and that is never empty. ID fields are made for this.