SQL (Structured Query Language) is the language for asking a database questions. A query names the columns you want (SELECT), the table they come from (FROM) and, if you like, which rows to keep (WHERE) and in which order to show them (ORDER BY).
The tasks use a table called customers with the columns name, city and country. Try the queries for real in the SQL sandbox: its office supplies shop has exactly this table, with eight customers (and two more columns, id and company). The exercise has four tasks and takes about 25 minutes.
A worked example
Question: "Which customers live in Germany, in alphabetical order?"
SELECT name, city
FROM customers
WHERE country = 'Germany'
ORDER BY name;Read it as a sentence: show name and city, from the customers table, only where the country is Germany, sorted by name. In the shop database it returns three rows: Anna Berger (Hamburg), Emma Schulz (Hamburg) and Farid Haddad (Berlin). Text values such as 'Germany' go in single quotes; numbers do not.
Common mistakes
Text without quotes, or with the wrong ones. Write
'Germany'in single quotes. In standard SQL, double quotes are for names of columns and tables.Upper and lower case in values. In SQLite,
country = 'germany'finds nothing, because the data says 'Germany'. Keywords like SELECT can be written in any case.Clauses in the wrong order.
ORDER BYbeforeWHEREis an error. The order block above is the order SQL expects.A count in WHERE. Conditions on COUNT, SUM or AVG go into HAVING.
= NULL. An empty value is found with
IS NULL, never with= NULL.
For fast finishers
Run these in the sandbox (Office supplies shop) and note the answers:
Which customers live in a city that starts with B?
How many different cities are in the table?
Which customers do not live in Germany? Sort them by name.
Which customers work for a company with "Trading" in its name?
Extension solutions
Farid Haddad (Berlin):
SELECT name, city FROM customers WHERE city LIKE 'B%';Seven:
SELECT COUNT(DISTINCT city) FROM customers;(Hamburg appears twice.)Ben Okafor, Carla Mendes, David Lee, Greta Nilsson and Hannah Wolf:
SELECT name FROM customers WHERE country <> 'Germany' ORDER BY name;Anna Berger and Emma Schulz, both at Northwind Trading:
SELECT name, company FROM customers WHERE company LIKE '%Trading%';
Teacher notes
How to run it. Show the worked example in the sandbox first, then learners do the four tasks alone. Learners who finish open the sandbox and try the extension. With a class link (unfold "Use it with a class" below the exercise) you collect a result code from each learner and see the class average per task in Class results.
Typical mistakes. WHERE placed after ORDER BY; LIKE '%Ham' for "starts with"; AND used where IN or OR is needed; GROUP BY typed as one word in the blanks (the gap expects "GROUP BY" with a space).
Follow-up questions.
Why does SQL need both WHERE and HAVING?
What happens with
ORDER BY country, name? (Sorted by country, and by name within each country.)How would you find customers with no city entered?
Differentiation. Easier: give the order of clauses on a card and skip the HAVING questions. Harder: continue with the first two shop tasks in the sandbox, which check the answer automatically, then with the joins exercise.
