Database Concepts
Databases
A common exam question gives a table and a SELECT query and asks which the returns. The approach:
- Look at the FROM clause to find the table.
- Look at the WHERE clause and identify the condition each record must satisfy.
- Check each record in the table against the condition.
- Keep only the records where every condition is true.
- Look at the SELECT clause and write down only the columns asked for.
Common exam question
Writing the output of an SQL query
Question: Write down the output that a given SELECT statement would produce from the table printed (2–3 marks).
Asked in 4 of the 17 papers. The marks go on the records chosen (often one per correct row) and on their order, across each line and down the page. One scheme gives its first mark for printing the columns the SELECT names, whatever rows you choose, and its last for every value right with nothing extra. Write one line per record, each value as the table prints it.
Order is where marks slip. Fields come out in the order the SELECT list names them, not the table's column order. Records keep the table's order unless there is an ORDER BY, and ORDER BY with no ASC or DESC sorts ascending: in one paper that reversed two rows of the table. Test every record against the WHERE condition exactly: >= includes the boundary value and <> means "not equal to".
Walkthrough: a Dublin or Osaka query
Using the Customers table from section 6:
SELECT *
FROM Customers
WHERE City = 'Dublin' OR City = 'Osaka';
Step by step:
- Maya Hassan:
City = 'Toronto'→ does not match either → excluded. - Liam O'Brien:
City = 'Dublin'→ matches → included. - Hiroshi Tanaka:
City = 'Osaka'→ matches → included. - Priya Gupta:
City = 'Mumbai'→ does not match either → excluded.
Result:
| ID | Name | Age | City | Country |
|---|---|---|---|---|
| 102 | Liam O'Brien | 25 | Dublin | Ireland |
| 103 | Hiroshi Tanaka | 47 | Osaka | Japan |
Walkthrough: an animal breeding query
Consider the tbl_zoo table:
| Species | InCaptivity | DailyFeedKg |
|---|---|---|
| Giraffe | Yes | 35 |
| Cheetah | Yes | 3 |
| Tortoise | Yes | 2 |
| Anaconda | No | 5 |
| Macaw | Yes | 1 |
| Bison | Yes | 25 |
| Pelican | Yes | 2 |
| Tarantula | No | 1 |
| Gorilla | Yes | 20 |
| Komodo Dragon | Yes | 4 |
A keeper wants the names of every species that is held in captivity and eats more than 10 kg of food a day:
SELECT Species
FROM tbl_zoo
WHERE InCaptivity = 'Yes'
AND DailyFeedKg > 10;
Working through each record against both conditions:
- Giraffe: in captivity and 35 > 10 → both true → included.
- Cheetah: in captivity but 3 is not > 10 → excluded.
- Tortoise: in captivity but 2 is not > 10 → excluded.
- Anaconda: not in captivity → excluded.
- Macaw, Pelican, Komodo Dragon: in captivity but their feed amounts (1, 2, 4) are not > 10 → excluded.
- Bison: in captivity and 25 > 10 → included.
- Tarantula: not in captivity → excluded.
- Gorilla: in captivity and 20 > 10 → included.
Result:
| Species |
|---|
| Giraffe |
| Bison |
| Gorilla |
Quick check
4 short questions on this note. See what has stuck, and where to look if something hasn't.