Part 1: Mastering the WHERE Command | SQL Challenges & Capstone
SQL Challenges & Capstone · Part 1 · Lecture Notes
Mastering the WHERE Command
How to filter table rows using conditions — comparison operators, AND/OR logic, IN, BETWEEN, LIKE, and NULL checks.
Part 1 of 616% Complete
WHERE
Comparison Operators
AND / OR
IN, BETWEEN, LIKE
IS NULL
8 Practice Questions
Learning Objectives
Use WHERE to filter table rows by a specific condition
Apply comparison operators (=, !=, >, <) to numeric and text values
Combine multiple conditions using AND and OR
Use IN, BETWEEN, and LIKE for efficient multi-value, range, and pattern filtering
Identify missing data using IS NULL and IS NOT NULL
Click any section header to open or close it
WHERE filters records and returns only rows that meet a specific condition. Without WHERE, every query returns all rows in the table. WHERE is what makes SQL analytical — it lets you ask precise questions about your data instead of retrieving everything at once.
In industry, analysts use WHERE to isolate customers from a specific region, filter transactions above a threshold, identify active users, find high-value sales, and clean datasets by excluding incomplete records.
The Basic Structure
SELECT names what columns you want. FROM names the table. WHERE adds the filter condition. The database evaluates every row and returns only those where the condition is true.
Basic WHERE Syntax
SELECT column_name
FROM table_name
WHERE condition;
Comparison operators compare a column value against a specific value. They are the most common WHERE conditions in analytical SQL work.
The Four Core Operators
= matches exactly. != excludes a value. > returns values greater than the threshold. < returns values less than the threshold. You can also use >= and <= to include the boundary value.
Filter Transactions Greater Than 100
SELECT *
FROM transactions
WHERE amount > 100;
Combined with SELECT *, this returns every column for all transactions where the amount exceeds 100. Changing the operator to = would return only exact matches; != would exclude that value; < would return all smaller amounts.
AND and OR allow you to combine multiple WHERE conditions into a single filter. AND is the stricter operator — both conditions must be true for a row to be returned. OR is more inclusive — a row is returned if either condition is true.
AND vs OR
Use AND when you want to narrow down — every condition must match. Use OR when you want to widen results — any matching condition is enough. Mixing both is common; use parentheses to control the order of evaluation.
AND — Canada Retail Users Only
SELECT *
FROM users
WHERE country = ‘Canada’
AND user_type = ‘Retail’;
OR — Completed or Pending Orders
SELECT *
FROM orders
WHERE order_status = ‘Completed’
OR order_status = ‘Pending’;
IN, BETWEEN, and LIKE are shorthand operators that make common filtering patterns much easier to write. Each one replaces a chain of OR conditions with a single, readable clause.
When to Use Each
IN replaces multiple OR conditions for the same column. BETWEEN filters a numeric or date range inclusively. LIKE searches for patterns — use % as a wildcard that matches any sequence of characters.
IN — Multiple Countries
SELECT *
FROM users
WHERE country IN (‘Canada’, ‘United States’, ‘United Kingdom’);
BETWEEN — Amount Range
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
WHERE amount BETWEEN 50 AND 150;
LIKE — Name Starts With A
SELECT *
FROM users
WHERE first_name LIKE ‘A%’;
NULL represents missing or unknown data in a database. You cannot use = or != to check for NULL — those operators don’t work with missing values. SQL requires the specific keywords IS NULL and IS NOT NULL.
NULL Is Not Zero or Empty
NULL means no value exists at all — it is not the same as 0, an empty string, or a space. A subscription with end_date IS NULL has no end date recorded, which typically means it is still active. WHERE end_date = NULL will never return any results — always use IS NULL.
Find Active Subscriptions — No End Date
SELECT *
FROM subscriptions
WHERE end_date IS NULL;
Find Closed Tickets — Resolution Date Present
SELECT *
FROM support_tickets
WHERE resolved_date IS NOT NULL;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL; a correct result earns a congratulations message.
Practice 1Show all users from Canada
AI Prompt
Write a MySQL query to show all users where country is Canada.
SQL Answer
SELECT *
FROM users
WHERE country = ‘Canada’;
Practice 2Show transactions greater than 100 rounded to 2 decimal places
AI Prompt
Write a MySQL query to show transactions where amount is greater than 100 rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
WHERE amount > 100;
Practice 3Show users from Canada with user_type Retail
AI Prompt
Write a MySQL query to show users where country is Canada and user_type is Retail.
SQL Answer
SELECT *
FROM users
WHERE country = ‘Canada’
AND user_type = ‘Retail’;
Practice 4Show orders that are Completed or Pending
AI Prompt
Write a MySQL query to show orders where order_status is Completed or Pending.
SQL Answer
SELECT *
FROM orders
WHERE order_status = ‘Completed’
OR order_status = ‘Pending’;
Practice 5Show users from Canada, United States, or United Kingdom
AI Prompt
Write a MySQL query to show users where country is Canada, United States, or United Kingdom.
SQL Answer
SELECT *
FROM users
WHERE country IN (‘Canada’, ‘United States’, ‘United Kingdom’);
Practice 6Show transactions between 50 and 150 rounded to 2 decimal places
AI Prompt
Write a MySQL query to show transactions where amount is between 50 and 150 rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount
FROM transactions
WHERE amount BETWEEN 50 AND 150;
Practice 7Show users whose first_name starts with A
AI Prompt
Write a MySQL query to show users where first_name starts with A.
SQL Answer
SELECT *
FROM users
WHERE first_name LIKE ‘A%’;
Practice 8Show subscriptions where end_date is NULL
AI Prompt
Write a MySQL query to show subscriptions where end_date is NULL.
SQL Answer
SELECT *
FROM subscriptions
WHERE end_date IS NULL;
Knowledge Check
Five questions — select the best answer for each.
1. What does the WHERE clause do in a SQL query?
AIt selects which columns to return
BIt sorts the results in order
CIt filters rows based on a condition
DIt groups records into categories
Correct. WHERE evaluates each row against a condition and returns only the rows where that condition is true.
Not quite. WHERE filters rows — it evaluates each row against a condition and returns only those where the condition is true.
2. Which operator finds all transactions where amount is greater than 50?
Aamount = 50
Bamount != 50
Camount < 50
Damount > 50
Correct. The > operator returns rows where the column value is strictly greater than the specified number.
Not quite. The > (greater than) operator returns rows where the value exceeds the threshold: WHERE amount > 50.
3. What is the correct way to check for a NULL value in a column?
AWHERE end_date = NULL
BWHERE end_date IS NULL
CWHERE end_date == NULL
DWHERE end_date NOT NULL
Correct. NULL cannot be tested with = or != — SQL requires the IS NULL keyword specifically for checking missing values.
Not quite. NULL values require IS NULL — you cannot use = to compare with NULL. WHERE end_date IS NULL is the correct syntax.
4. Which query correctly returns users from Canada, USA, or UK using IN?
AWHERE country = ‘Canada’ OR ‘USA’ OR ‘UK’
BWHERE country IN ‘Canada’, ‘USA’, ‘UK’
CWHERE country IN (‘Canada’, ‘United States’, ‘United Kingdom’)
DWHERE IN country (‘Canada’, ‘United States’, ‘United Kingdom’)
Correct. IN takes a comma-separated list of values inside parentheses. Each value must be a properly quoted string.
Not quite. IN requires the column name first, then IN, then the list of values in parentheses with quotes: WHERE country IN (‘Canada’, ‘United States’, ‘United Kingdom’).
5. What does LIKE ‘A%’ match?
AAny value that ends with the letter A
BAny value that equals exactly ‘A%’
CAny value that starts with the letter A
DAny value containing the letter A anywhere
Correct. The % wildcard matches any sequence of characters after A — so LIKE ‘A%’ returns any value that begins with A, regardless of what follows.
Not quite. In LIKE patterns, % is a wildcard matching any characters. ‘A%’ means starts with A — the % matches everything after.
Part 1 Summary
The key ideas from this lecture — keep these in mind as you move forward.
WHERE filters datasets so analysts can focus only on relevant business records and conditions. Use = for exact matches, > and < for thresholds, AND/OR to combine conditions, IN for multiple values, BETWEEN for ranges, LIKE for patterns, and IS NULL to find missing data.
WHERE=!=><ANDORINBETWEENLIKEIS NULLIS NOT NULLFilterCondition
