First questions to the data · Lesson 3 of 20

WHERE: filtering rows

Comparisons, AND/OR, IN, BETWEEN, LIKE, and the NULL trap.

10 minutes3 games

WHERE picks rows (just as SELECT picks columns). The database tests the condition on every row and keeps only those where it's true.

SQL

SELECT name, city, xp
FROM students
WHERE city = 'Cairo';

Condition	Meaning
xp = 90	equals
xp <> 90	not equal (or `!=`)
xp >= 150	greater than or equal
xp BETWEEN 100 AND 300	100 to 300, both ends included
city IN ('Cairo', 'Giza')	one of the list
name LIKE 'K%'	starts with K (`%` = any characters)
xp IS NULL	has no value

Combine conditions with AND (both must hold) and OR (either is enough). When mixing them, use brackets: AND binds before OR, like multiplication before addition.

SQL

SELECT name, city, xp
FROM students
WHERE xp > 100
  AND (city = 'Cairo' OR city = 'Giza');

The NULL trap

NULL means "unknown", not zero and not empty text. Youssef's xp is NULL. Run these two and compare:

SQL

SELECT name FROM students WHERE xp = NULL;

SELECT name FROM students WHERE xp IS NULL;

What's the output?

SELECT name
FROM students
WHERE xp BETWEEN 150 AND 300;

What will this code print?

Spot the bug

This query should find students with no XP yet, but it returns nothing. Where's the bug?

Exercise

Get name, city and xp for students in Alexandria or Giza who also have 150 XP or more.

Expected result:

name | city | xp
Karim | Alexandria | 150
Nour | Giza | 410
Ali | Giza | 180

SQL

SELECT name, city, xp
FROM students
WHERE ;

Learn more from

Played 0 of 3 games

SQL