SQL, lesson 2
A table can hold thousands of rows, and you rarely want all of them. WHERE adds a condition.
SQLite checks the condition for each row and keeps only the rows where it is true.
WHERE comes after FROM. The columns you test do not have to be the columns you print.
| Operator | Meaning |
|---|---|
= | equal (SQLite also accepts ==) |
<> or != | not equal |
2 exercises to try what you just read.
<, <=, >, >= | less / greater than |
Text comparisons with = are exact and case-sensitive: 'Tea' = 'tea' is false. Put text in
single quotes, numbers without quotes.
Join conditions with AND (both must hold) and OR (at least one must hold). Flip one with
NOT. AND binds more tightly than OR, so use parentheses whenever you mix them; it saves you
from a bug and tells the reader what you meant.
x BETWEEN 10 AND 20 means x >= 10 AND x <= 20. Both ends are included.x IN ('red', 'blue') is true when x matches any value in the list.x LIKE 'Ch%' matches text patterns: % stands for any run of characters (even none), and
_ stands for exactly one character. In SQLite, LIKE ignores the case of English letters.NULL means "no value here". It is not zero and not empty text, and it is not equal to
anything, not even another NULL. So email = NULL is never true. Use IS NULL and
IS NOT NULL instead:
This is one of the most common mistakes in SQL, so it is worth remembering early.
The table snacks has the columns name, category and price:
Print the name and price of every snack in the category sweet that costs less than 2,
in the order they are stored. Expected output:
The table members has the columns name, email and city. Some members never gave an
email address, so their email is NULL:
Print the name and email of members who have an email address and live in Leeds
or York, in the order they are stored. Expected output:
SELECT name, price FROM snacks WHERE price < 2;SELECT name FROM snacks
WHERE category = 'fruit' AND (price < 1 OR stock > 50);SELECT name FROM snacks WHERE name LIKE '%choc%'; -- contains "choc", any case
SELECT name FROM snacks WHERE category IN ('nuts', 'fruit');SELECT name FROM customers WHERE email IS NULL;name | category | price
Apple | fruit | 0.5
Chocolate bar | sweet | 1.5
Trail mix | nuts | 3.0
Banana | fruit | 0.4
Gummy bears | sweet | 2.2
Salted almonds | nuts | 2.8
Toffee | sweet | 0.9Chocolate bar|1.5
Toffee|0.9name | email | city
Ava | ava@example.com | Leeds
Ben | NULL | York
Chloe | chloe@example.com | Bath
Dev | dev@example.com | York
Elena | NULL | Leeds
Finn | finn@example.com | HullAva|ava@example.com
Dev|dev@example.com