Product Dojo is a members' platform. Sign in to open the lessons, clear
stages, and earn the certificate for each one. Your rank and credentials
stay with your account across every device you use.
Free to join, through GitHub, Google or LinkedIn. No password to set.
Not unlocked yet
Stages open in order. Clear the one before this to come back here.
A table with a million rows is not useful. A table with the nineteen rows
that answer your question is. WHERE is the clause that gets you
from one to the other, and getting its logic right is the difference between
a report that is correct and a report that merely looks correct.
Ready?
Step-by-step lessons
Choosing Your Rows
Four short lessons: how a filter is actually evaluated, why text needs single quotes, the trap in mixing AND with OR, and the three shorthands that make a long condition readable.
1
WHERE Is Asked Once per Row
WHERE takes a condition and applies it to every row
independently. If the condition is true for that row, the row survives.
If not, it is dropped. There is no memory between rows and no order to
it — each row is judged alone.
SELECT name
FROM users
WHERE is_active = 1;
Note the single =. Many languages use == for
comparison because they need = for assignment; SQL has no
assignment here, so one equals sign is the comparison and there is
nothing to confuse it with.
WHERE goes after FROM and before
ORDER BY. It also runs before the SELECT list is
computed, which is why you cannot use a column alias you defined in
SELECT inside your WHERE — the alias does not exist yet.
Fails
WHERE yearly > 100
Where yearly is an alias defined in the SELECT list. WHERE runs first, so the name is unknown.
Works
WHERE price * 12 > 100
Repeat the expression. WHERE can compute anything from the table's own columns.
Quick check
A table has 24 orders. WHERE amount > 100 returns 6. What happened to the other 18?
2
Single Quotes for Text, Nothing for Numbers
Text literals go in single quotes. Numbers go bare.
Double quotes mean something entirely different in SQL — they name a
column or a table — which is why WHERE plan = "free" tends
to fail with a message about a column called free.
WHERE plan = 'free' -- text, single quotes
WHERE amount > 100 -- number, no quotes
WHERE is_active = 1 -- a flag stored as a number
The comparison operators are the ones you would expect, with one
surprise: "not equal" is written <> in standard SQL.
!= works in most databases including this one, but
<> is the portable spelling.
Text comparison is exact. 'free' and
'Free' are different strings in most databases. When a
filter you are sure about returns nothing, the first thing to check is
the exact stored spelling — which is what SELECT DISTINCT
in stage 1-04 is for.
=
Equal
One equals sign, not two.
<>
Not equal
The portable spelling. != also works nearly everywhere.
>=
At least
Includes the boundary. > does not.
''
Quoting a quote
'O''Brien' — double the apostrophe to include one.
Quick check
WHERE country = "UK" returns an error about a column named UK. Why?
3
AND Binds Tighter Than OR
AND needs both sides true. OR needs either.
Simple enough alone — and the source of a great many quietly wrong
reports the moment they are mixed, because AND is evaluated
first, exactly like multiplication before addition.
WHERE plan = 'pro' OR plan = 'team' AND is_active = 1
-- what SQL actually reads:
WHERE plan = 'pro' OR (plan = 'team' AND is_active = 1)
That query returns every pro user including the churned ones, which is
almost certainly not what was intended. The fix is brackets, and the
habit worth building is bracketing any mixed condition even when
you have worked out that you do not need to.
WHERE (plan = 'pro' OR plan = 'team') AND is_active = 1
Everyday example, the shopping list
"Get bread or milk and eggs." Two people will read that two different
ways and one of them comes home wrong. Brackets are how you stop
having the argument — with SQL, and arguably at the shop.
Quick check
How many users does WHERE plan = 'free' OR plan = 'pro' AND is_active = 0 return, given 5 free users (2 inactive), and 4 pro users (all active)?
4
Three Shorthands Worth Knowing Immediately
IN replaces a chain of ORs against the same
column. It is shorter, it reads better, and adding a fourth allowed value
costs one comma rather than another clause.
WHERE category = 'addon' OR category = 'service'
WHERE category IN ('addon', 'service') -- the same thing
BETWEEN is a range, and it includes both
ends. That inclusiveness is fine on numbers and a genuine trap
on dates and timestamps, where BETWEEN '2024-01-01' AND
'2024-01-31' silently excludes everything that happened during the
31st if the column carries a time as well as a date.
LIKE matches text by pattern. % stands for any
run of characters, _ for exactly one.
WHERE name LIKE 'Pro%' -- starts with Pro
WHERE name LIKE '%Seat' -- ends with Seat
WHERE name LIKE '%data%' -- contains data
A leading wildcard is slow
LIKE 'Pro%' can use an index — the database knows where to
start looking. LIKE '%Pro' cannot, because a match could
begin anywhere, so it has to read every row. On twelve rows this is
invisible. On twelve million it is the difference between a query and a
coffee break.
Quick check
Which orders does WHERE amount BETWEEN 9 AND 29 include?
Write it yourself
Query Lab
Four queries that cut a table down to the rows you actually want. The checks compare your result against the expected one, so a filter that is nearly right will say so.
0 of 4 cleared
Real SQLite runs right here in your browser — nothing to install, nothing
sent to a server. The database is rebuilt from scratch before every single
run, so nothing you write can break it and nothing carries over between
exercises.
The Northwind Analytics databaseA small SaaS product's database: who signed up, what they subscribed to, what they bought, and what they did in the app.
Loading the tables…
01
Keep only some rows
To do
WHERE is the filter. It sits after FROM, and
it is checked once per row: rows where the condition is true come back,
rows where it is not are dropped.
SELECT name FROM users WHERE is_active = 1;
Note the single =. SQL is not a programming language here —
one equals sign is the comparison, and there is no assignment to confuse
it with.
Your task: return the name and
plan of every user whose is_active is
1.
query.sql
SQLiteCtrl↵ to run
Hint
Add one line: WHERE is_active = 1 — no quotes, because is_active holds a number rather than text.
Output
02
Matching text
To do
Text goes in single quotes. Double quotes mean
something else in SQL (they name a column or table), so a query written
with them will usually fail in a confusing way.
WHERE plan = 'free'
The comparison is exact. 'free' and 'Free' are
different strings to most databases, so when a filter mysteriously
returns nothing, checking the exact stored spelling is the first move.
Your task: return the name and
country of every user on the free plan.
query.sql
SQLiteCtrl↵ to run
Hint
WHERE plan = 'free' — single quotes around the text, and lower case, because that is exactly how it is stored.
Output
03
Two conditions at once
To do
AND requires both sides to be true; OR
requires either. Mixing them without brackets is the classic way to get
a wrong answer that looks plausible, so bracket anything you are not
completely sure about.
WHERE status = 'paid' AND amount > 100
The comparison operators are the ones you would expect:
=, <> (not equal), <,
>, <=, >=.
Your task: return order_id,
amount and status for every order that is paid
and worth more than 100.
query.sql
SQLiteCtrl↵ to run
Hint
Two conditions joined by AND: status = 'paid' AND amount > 100. Strictly more than 100, so an order of exactly 100 would not count.
Output
04
One of several values
To do
IN replaces a chain of ORs. These two say the
same thing, and the second is far easier to read and to change later:
WHERE category = 'addon' OR category = 'service' WHERE category IN ('addon', 'service')
Two more shorthands worth having now. BETWEEN 10 AND 50
covers a range and includes both ends. LIKE
'Pro%' matches text by pattern, where % means "any
run of characters".
Your task: return name,
category and price for every product that is
an addon or a service.
query.sql
SQLiteCtrl↵ to run
Hint
WHERE category IN ('addon', 'service') — brackets around the list, single quotes around each value, comma between them.
Output
Boss round
Assignment: The Exceptions Report
One graded query combining two conditions. Pass every check and the stage is cleared.
This one is graded. You can see 2
of the checks up front, and 2
stay hidden until you run. The hidden ones test the same job from angles
you have not been shown, so code that genuinely solves the problem clears
this and code shaped around the visible examples does not.
Pass them all and the stage is yours.
The exceptions report
To do
Finance wants every order that needs a human to look at it. An order
qualifies if it did not complete — anything whose status
is not paid — or if it did complete but for a trivial amount,
under 15 dollars, because those are usually a mistake.
Your task: from orders, return
order_id, user_id, amount and
status for every order matching either condition.
Seven orders qualify. Read the two conditions carefully before you write
them: one of them is about status, the other is about amount, and they are
joined by or, not and.
query.sql
SQLiteCtrl↵ to run
Hint
status <> 'paid' OR amount < 15 — <> is "not equal". An order that is both unpaid and small still appears once, not twice.
Output
Lock it in
Guess the Term
Read the clues and name the concept. The fewer clues you need, the more points you score.