Filtering with WHERE

So far every query returned all the rows. Add a WHERE clause to keep only the rows that match a condition:

SELECT name, score FROM student WHERE score >= 80;

Only rows where the condition is true are returned.

You can compare with:

• = equal to
• <> not equal to
• < less than, > greater than
• <= at most, >= at least

Numbers are written plainly (80). Text must go in single quotes ('11B'):

SELECT name FROM student WHERE form = '11B';

Question. Show the name and form of every student who is not in form 11A.

Answer. "Not equal to" is <>. The form is text, so '11A' goes in single quotes:

SELECT name, form FROM student WHERE form <> '11A';

name  | form
Jamal | 11B
Tom   | 11B

Check each row against the test: Mei, Sara and Lin are in 11A, so form <> '11A' is false for them and they are left out.

• Text without quotes. WHERE form = 11A fails with unrecognized token: "11A". Write WHERE form = '11A'.
• => is not an operator. Write >= (and <=): the = always comes second.
• WHERE goes after FROM. SELECT name WHERE score > 80 FROM student is a syntax error.