Database Concepts
Databases
A typical SQL query has up to three clauses:
SELECT field1, field2, ...
FROM TableName
WHERE condition;
Each line has a specific job:
| Clause | What it does |
|---|---|
| SELECT | Lists which fields (columns) to display in the result |
| FROM | Names the table to read from |
| WHERE | Filters which records (rows) to include |
The semicolon at the end is the SQL "end of statement" marker.
Common exam question
Saying what an SQL statement does
Question: State what SELECT, FROM and WHERE are each used for, or explain the purpose of SQL statements you are given (3 marks).
Asked in 2 of the 17 papers, one mark per point. For the keywords, SELECT lists the fields to be displayed, FROM names the table, and WHERE gives the condition the records must meet.
For a whole statement, say what it works out and for which records. One using SUM finds the total of the values in its field, one using COUNT finds how many records there are, and the WHERE condition that limits them to one group of records is a mark of its own. Name the real field and value in your answer ("the total stock of all the blue items"), not just "it adds them up".
SELECT every field
To return every column for every record, use the wildcard * after SELECT:
SELECT *
FROM Customers;
This returns the whole table.
SELECT specific fields
To return only certain columns, list them by name:
SELECT FirstName, LastName, Age
FROM Customers;
Only those three columns appear in the result, even if the table has many more.
SELECT with a WHERE filter
To return only certain rows, add a WHERE clause with a condition:
SELECT *
FROM Customers
WHERE Age > 25;
Only customers older than 25 are returned.
Common exam question
Completing an SQL statement
Question: Fill in the gaps in an SQL statement so that it displays, or totals, the records the question describes (2–5 marks).
Asked in 4 of the 17 papers, usually one mark per line or gap. SELECT takes only the fields asked for, FROM the table's name as printed, and WHERE the field tested, an operator and the value. In all four papers the field tested was not one displayed.
Put a text value in quotes (WHERE Colour = 'Blue';). For a yes/no field the schemes accept NOT Paid, Paid = False, Paid = No or Paid = "No". For the number of items in stock, when each record holds a quantity, SELECT needs SUM of that field: COUNT only counts the records.
Two papers then ask how a different field could give the same result: name that field and the value it is tested for (zero for an out-of-stock quantity); one scheme gives a mark for each.
A customers table to practise on
Consider this Customers table:
| ID | Name | Age | City | Country |
|---|---|---|---|---|
| 101 | Maya Hassan | 32 | Toronto | Canada |
| 102 | Liam O'Brien | 25 | Dublin | Ireland |
| 103 | Hiroshi Tanaka | 47 | Osaka | Japan |
| 104 | Priya Gupta | 19 | Mumbai | India |
Query 1: SELECT * FROM Customers; returns all five fields for all four records (the whole table).
Query 2: SELECT ID, Name, Age FROM Customers WHERE Age > 25;
| ID | Name | Age |
|---|---|---|
| 101 | Maya Hassan | 32 |
| 103 | Hiroshi Tanaka | 47 |
(Liam O'Brien is excluded because his age is exactly 25, while the query asked for strictly greater than 25. Priya Gupta is excluded because 19 < 25.)
Quick check
4 short questions on this note. See what has stuck, and where to look if something hasn't.