The manager asks in plain English
Nobody asks for "an INNER JOIN with a GROUP BY". The shop manager says: "Which products sell best?"
Your job is to turn that sentence into code that runs. This lesson gives you a method for it.
Key words
| Word | What it means |
|---|---|
| Database | an organised store of tables that you can ask questions |
| SQL | the language used to ask a database for data |
| Query | one question written in SQL |
| Requirement | what the answer has to show, in the asker's words |
| Clause | one part of a query, such as WHERE or ORDER BY |
| Test the query | run it and check the rows against what you expect |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can turn a plain question into a query with several clauses, run it, and check the answer.
Starter
Have a go
Starter
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.
Five steps from question to query
- Say what the answer shows: which columns, one row for what.
- Find the tables that hold those columns. Join them if there are two.
- Filter: which rows count? That is WHERE.
- Group or count if the answer is a summary.
- Sort so the answer is easy to read.
Then run it and check a few rows by hand.
Plain words to clauses
| The question says | Clause | Example |
|---|---|---|
| "Show me the names and prices" | SELECT | `SELECT name, price_pence` |
| "from the products list" | FROM | `FROM products` |
| "only the drinks" | WHERE | `WHERE category = 'drink'` |
| "for each category" | GROUP BY | `GROUP BY category` |
| "how many" | COUNT | `COUNT(*)` |
| "biggest first" | ORDER BY ... DESC | `ORDER BY items DESC` |
| "with the product name" | JOIN | `INNER JOIN products ON ...` |
The cells students fill in are left blank in this preview.
Try it: a query built in steps
-- Question: how many sales did each payment type have, most first?
SELECT paid_by, COUNT(*) AS sales
FROM sales
GROUP BY paid_by
ORDER BY sales DESC;
-- Now change it: only sales of 2 or more items (qty).
Check the steps
Have a go
Check the steps
3 questionsHave a go: your answers are marked here and then forgotten. Sign in if you would like your score kept, and to pick up where you left off.Sign in to keep your score
Your answers are marked here and are not kept. A college account keeps every student’s marks.