Keep only the rows that pass
Filter rows with WHERE and a comparison. About 7 minutes.
WHERE keeps the rows that pass
The library desk gets a question: which books came out after 1950? You don't want every row, only the ones that pass a test.
WHERE is that test. Write it after FROM, and the database checks each row against it. A row that passes is kept, and a row that fails is dropped. Only then does SELECT pick its columns from the rows that stayed.
SELECT title FROM books WHERE year > 1950;
| id | title | genre | year |
|---|---|---|---|
| 1 | Gitanjali | Poetry | 1910 |
| 2 | Train to Pakistan | Novel | 1956 |
| 3 | The Guide | Novel | 1958 |
| 4 | Madhushala | Poetry | 1935 |
Each row is tested against year > 1950, one at a time.
The tests you can write
A test compares a column with a value. You can ask for equal, not equal, smaller, larger, at most or at least. The symbols for each are in the box below.
Text goes inside single quotes, and numbers don't. Text is matched exactly, so Poetry and poetry are different values. Equal is one equals sign, not two.
WHERE genre = 'Poetry' -- equal WHERE copies >= 3 -- at least WHERE genre <> 'Novel' -- not equal WHERE year < 1950 -- smaller
Which rows does WHERE price >= 100 keep?
- ARows priced 100 or more
- BOnly rows priced above 100
- CRows priced 100 or less
Show the answer
Rows priced 100 or more. >= means greater than or equal to, so 100 itself passes.
The shortlist
The applicants table has one row per applicant. Show the name of every applicant whose score is above 60.
applicants(id, name, city, score)
Up to 2,000 applicants