Structured Query Language · Cambridge syllabus 9.4 · Lesson 4 of 4
Database Scenarios
Translate a scenario into table, fields, conditions and ordering. Identify what the user wants to see and which records qualify, then test the query against sample data including boundary values.
By the end, you can
- Translate a data requirement into an SQL query.
- Read and complete SQL scripts.
- Check whether a query produces the required records.
Applying SQL
Exam questions give a table and a requirement in English, and ask for the query. The reliable method is to translate the requirement clause by clause rather than writing the query from the start.
Ask four questions in this order. Which table holds the data, giving FROM. Which records are wanted, giving WHERE. Which fields should be shown, giving SELECT. In what order, giving ORDER BY. Then write the clauses in their proper order: SELECT, FROM, WHERE, ORDER BY.
Take show the title and price of every science book under 20 dollars, cheapest first. The table is Books; the records are those where Category = "Science" AND Price < 20; the fields are Title and Price; the order is ORDER BY Price ASC. The query follows directly from those four answers.
Completing a partly written script means reading what is already there before adding anything: the table name tells you the field names to use, and an existing clause shows the conventions being followed. Field names must match the table exactly, including capitalisation, and a name misspelt is an error rather than an empty result.
Check the query against the requirement afterwards, because the commonest mistakes are silent ones. Using OR where AND was meant returns far too many records; forgetting the quotation marks round a text value, or adding them to a number, is a syntax error; and omitting the WHERE clause returns the whole table, which looks like a working query and answers a different question.
Worth remembering
- Translate in order: which table, which records, which fields, what order.
- Write the clauses as SELECT, FROM, WHERE, ORDER BY.
- Check it back against the requirement: a missing WHERE still looks like a result.
Worked example
For “names of students scoring at least 70, alphabetically”, select Name, filter Score >= 70 and order by Name ASC.
Practice
Warm-up · not marked
Check your understanding
Answer in your own words, then compare with the worked answer. Nothing here is saved or scored.
Worked answer
Include scores below 70, exactly 70 and above 70; exactly 70 must be included.
Check your method, units and explanation against it.
Marked practice
Set 1 of 4 · easiest first · Checked as you go, with the worked answer. Sign in or create a free account to save your progress.