SQL: SELECT, FROM, WHERE, ORDER BY

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.

SELECT HireID, Station
FROM BikeHire
WHERE Minutes > 30;

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.

SELECT HireID, Minutes
FROM BikeHire
WHERE Minutes > 30;

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.

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

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.

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

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;