Ten thousand rows, one answer
The school shop sells hundreds of items a week. The manager wants to know which products are nearly sold out.
Reading every row by eye takes hours. One short line of code gives the answer in a blink.
Key words
| Word | What it means | Example |
|---|---|---|
| Database | an organised store of tables that you can ask questions | |
| SQL | the language used to ask a database for data | SELECT name FROM products; |
| Query | one question written in SQL | |
| Table | a grid of data: rows go across and columns go down | |
| Column | one field in every row, such as price | |
| SELECT | the SQL word that says which columns to show | |
| FROM | the SQL word that says which table to read | |
| WHERE | the SQL word that keeps only the rows that pass a test |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can write a query with SELECT, FROM and WHERE to get exactly the rows and columns I need.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Try it: read a whole table
-- The star means every column.
SELECT * FROM products;
-- Now ask for only two columns. Remove the two dashes to run it.
-- SELECT name, price_pence FROM products;
The shape of a query
A query has clauses in a fixed order. Write SELECT first, then FROM, then WHERE.
SELECT lists the columns. FROM names the table. WHERE keeps only the rows that pass a test. End the query with a semicolon.
The tests you can write in WHERE
| Test | Meaning | Example | Rows kept |
|---|---|---|---|
| `=` | equal to | `category = 'drink'` | Cola, Orange juice, Water |
| `<>` | not equal to | `category <> 'drink'` | Everything but the drinks |
| `<` | less than | `stock < 10` | Banana, Cookie, Chicken wrap, Cheese toastie |
| `>` | greater than | `price_pence > 200` | Cheese roll, Chicken wrap, Cheese toastie |
| `<=` | less than or equal | `stock <= 8` | Chicken wrap, Cookie, Banana, Cheese toastie |
| `>=` | greater than or equal | `price_pence >= 250` | Cheese roll, Chicken wrap, Cheese toastie |
The cells students fill in are left blank in this preview.
Try it: keep only some rows
SELECT name, stock FROM products WHERE stock < 10;
-- Text goes in single quotes.
SELECT name, price_pence FROM products WHERE category = 'lunch';
-- Change the number 10 and run the first query again.
Check
Have a go
Check
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.