Which orders still need sending?
You start a support job at a small online coffee shop. A customer rings to ask where their order is.
Your manager says: "Check which orders are still open." The shop keeps every order in a database. You could scroll through them all. Or you could ask the database one short question.
By the end of this lesson
Note
I can use SQL to find the rows that match a condition.
Key words
| Word | What it means | Example |
|---|---|---|
| Table | data stored in rows and columns | |
| Row | one record, such as one order | |
| Column | one detail every row has | |
| Query | a question you ask a database | |
| Condition | a test each row passes or fails | status = 'open' |
| SQL | the language for asking a database questions |
The cells students fill in are left blank in this preview.
SELECT says which columns you want
A database keeps data in tables. The shop has a table called orders. Each row is one order.
A query starts with SELECT. After it you name the columns you want, with commas between them. FROM says which table to read. A star, *, means every column. A query only reads. It never changes the table.
Try it: choose the columns
-- Every column of every order.
SELECT * FROM orders;
-- Only the two columns a delivery driver needs.
SELECT customer, city FROM orders;
A WHERE clause filters rows
SELECT picks the columns. WHERE picks the rows.
After WHERE you write a condition. The database tests every row against it. It keeps the rows that pass and drops the rest. So WHERE status = 'open' keeps only the orders whose status is open.
Try it: only the open orders
-- Only the orders that have not been sent yet.
SELECT id, customer, total FROM orders
WHERE status = 'open';
-- Change 'open' to 'sent', then press Run again.
Worked example: WHERE status = 'open'
| Order | status | Kept? |
|---|---|---|
| 101 | sent | No |
| 102 | open | Yes |
| 103 | open | Yes |
| 104 | sent | No |
| 105 | open | Yes |
| 106 | refunded | No |
| 107 | open | Yes |
The cells students fill in are left blank in this preview.
A common mistake
Warning
Many people think WHERE city = Leeds works. It does not. Text values need single quotes: city = 'Leeds'. Without quotes the database looks for a column called Leeds. Numbers need no quotes: total > 50.
AND and OR join conditions
Real questions often have two parts. "Open orders over 50 pounds" is two tests.
AND keeps a row only when both tests pass. OR keeps a row when either test passes. For numbers you can use > for more than and < for less than.
Try it: two tests at once
-- Open orders worth more than 50 pounds.
SELECT id, customer, total FROM orders
WHERE status = 'open' AND total > 50;
-- Orders going to Leeds or to Cardiff.
SELECT id, customer, city FROM orders
WHERE city = 'Leeds' OR city = 'Cardiff';