One test is rarely enough
A pupil wants a cold drink that costs between 100 and 150 pence. Another wants a snack that is not crisps.
Each wish is two or three tests joined together. Small code words can join tests.
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 |
| Condition | a test that is true or false for each row |
| BETWEEN | tests that a value is inside a range, edges included |
| AND | true only when both tests are true |
| OR | true when at least one test is true |
| NOT | turns true into false and false into true |
| Brackets | round ( ) marks that make SQL work out one part first |
The cells students fill in are left blank in this preview.
By the end of this lesson
Note
I can combine tests with BETWEEN, AND, OR, NOT and brackets to pick the right rows.
Starter
Have a go
Starter
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
BETWEEN picks a range
BETWEEN 100 AND 150 keeps every value from 100 up to 150. Both edges are included, so 100 and 150 pass.
It is the same as writing >= 100 AND <= 150, but shorter and easier to read.
Try it: a range of prices
SELECT name, price_pence FROM products
WHERE price_pence BETWEEN 100 AND 150;
-- The same test written the long way:
SELECT name, price_pence FROM products
WHERE price_pence >= 100 AND price_pence <= 150;
AND, OR and NOT
| Word | Keeps a row when | Example | Rows kept |
|---|---|---|---|
| `AND` | both tests are true | `category = 'drink' AND stock > 30` | Cola, Water |
| `OR` | one or both tests are true | `category = 'fruit' OR category = 'snack'` | Apple, Banana, Crisps, Cookie |
| `NOT` | the test is false | `NOT category = 'lunch'` | Everything but lunch items |
The cells students fill in are left blank in this preview.
Try it: join tests
SELECT name FROM products WHERE category = 'drink' AND stock > 30;
SELECT name FROM products WHERE category = 'fruit' OR category = 'snack';
SELECT name FROM products WHERE NOT category = 'lunch';
-- Change AND to OR in the first query. What changes?
Check
Have a go
Check
4 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.