Part 4: Mastering the CASE Statement | SQL Challenges & Capstone
SQL Business Logic & Data Handling Practice · Part 4 · Lecture Notes
Mastering the CASE Statement
How to apply conditional business logic inside SQL queries — categorising records, creating dynamic labels, and embedding business rules directly in output.
Part 4 of 666% Complete
CASE WHEN
THEN / ELSE
Business Logic
Dynamic Labels
8 Practice Questions
Learning Objectives
Use CASE…WHEN…THEN…ELSE…END to apply conditional business logic inside SQL queries
Create dynamic classification columns that label rows based on their data values
Build multi-condition CASE statements for more granular business categorisation
Understand that CASE adds output columns without removing rows — unlike WHERE
Click any section header to open or close it
CASE adds conditional business logic directly inside SQL queries. It evaluates one or more conditions and returns a different result depending on which condition is true — similar to an IF-THEN-ELSE in other programming languages. CASE lets analysts translate raw database values into meaningful business labels without needing to post-process the data externally.
In industry, analysts use CASE to categorize customers into segments, label transactions by value tier, create readable order status labels, apply business classification rules, and build dynamic columns in reports and dashboards.
The Basic Structure
CASE opens with the keyword CASE. Each condition is a WHEN…THEN pair. ELSE handles everything that does not match any WHEN condition. END closes the statement. An AS alias after END gives the new column a readable name.
Basic CASE Syntax
SELECT
column_name,
CASE
WHEN condition THEN result
ELSE result
END AS alias_name
FROM table_name;
The simplest CASE pattern has one WHEN condition and an ELSE fallback. The database evaluates each row — if the condition is true, it returns the THEN value; otherwise it returns the ELSE value. Every row gets a classification based on its actual data.
WHEN…THEN is Evaluated in Order
If a CASE has multiple WHEN conditions, MySQL evaluates them top to bottom and returns the first one that is true. Once a match is found, the remaining conditions are skipped. Put the most specific conditions first.
Label Transactions by Value
SELECT
transaction_id,
amount,
CASE
WHEN amount >= 100 THEN ‘High Value’
ELSE ‘Standard’
END AS transaction_category
FROM transactions;
CASE is the standard tool for customer segmentation in SQL. Analysts use it to classify users by region, subscription by tier, tickets by priority, and orders by outcome — all without changing any data in the database. The classification exists only in the query output.
Multiple WHEN Conditions
Add more WHEN…THEN pairs to create more categories. CASE WHEN order_status = ‘Completed’ THEN ‘Finished Order’ WHEN order_status = ‘Pending’ THEN ‘Waiting Approval’ ELSE ‘Other Status’ END — each additional WHEN adds a new classification path.
Label Users by Region
SELECT
user_id,
country,
CASE
WHEN country = ‘Canada’ THEN ‘Domestic’
ELSE ‘International’
END AS customer_region
FROM users;
Multi-Condition Order Labels
SELECT
order_id,
order_status,
CASE
WHEN order_status = ‘Completed’ THEN ‘Finished Order’
WHEN order_status = ‘Pending’ THEN ‘Waiting Approval’
ELSE ‘Other Status’
END AS order_label
FROM orders;
CASE is how analysts embed business rules — pricing tiers, revenue thresholds, performance benchmarks — directly into SQL output. Instead of looking up rules in a separate spreadsheet and manually applying them, the logic lives in the query and applies automatically to every row.
CASE Replaces Manual Post-Processing
Before CASE, analysts would export data and apply conditional logic in Excel. With CASE, the classification happens inside the database — faster, more consistent, and reproducible every time the query runs.
Subscription Tier Classification
SELECT
subscription_id,
monthly_fee,
CASE
WHEN monthly_fee >= 40 THEN ‘Premium’
ELSE ‘Basic’
END AS subscription_level
FROM subscriptions;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Label transactions as High Value if amount >= 100, otherwise Standard, rounded to 2 decimal places
AI Prompt
Write a MySQL query to label transactions as High Value if amount is greater than or equal to 100, otherwise label them Standard rounded to 2 decimal places.
SQL Answer
SELECT
transaction_id,
ROUND(amount, 2) AS amount,
CASE
WHEN amount >= 100 THEN ‘High Value’
ELSE ‘Standard’
END AS transaction_category
FROM transactions;
Practice 2Label users as Domestic if country is Canada, otherwise International
AI Prompt
Write a MySQL query to label users as Domestic if country is Canada, otherwise label them International.
SQL Answer
SELECT
first_name,
country,
CASE
WHEN country = ‘Canada’ THEN ‘Domestic’
ELSE ‘International’
END AS customer_region
FROM users;
Practice 3Label products as Expensive if unit_price >= 100, otherwise Affordable, rounded to 2 decimal places
AI Prompt
Write a MySQL query to label products as Expensive if unit_price is greater than or equal to 100, otherwise label them Affordable rounded to 2 decimal places.
SQL Answer
SELECT
product_name,
ROUND(unit_price, 2) AS unit_price,
CASE
WHEN unit_price >= 100 THEN ‘Expensive’
ELSE ‘Affordable’
END AS product_category
FROM products;
Practice 4Label orders as Closed if order_status is Completed, otherwise Active
AI Prompt
Write a MySQL query to label orders as Closed if order_status is Completed, otherwise label them Active.
SQL Answer
SELECT
order_id,
order_status,
CASE
WHEN order_status = ‘Completed’ THEN ‘Closed’
ELSE ‘Active’
END AS order_group
FROM orders;
Practice 5Label subscriptions as Premium if monthly_fee >= 40, otherwise Standard, rounded to 2 decimal places
AI Prompt
Write a MySQL query to label subscriptions as Premium if monthly_fee is greater than or equal to 40, otherwise label them Standard rounded to 2 decimal places.
SQL Answer
SELECT
subscription_id,
ROUND(monthly_fee, 2) AS monthly_fee,
CASE
WHEN monthly_fee >= 40 THEN ‘Premium’
ELSE ‘Standard’
END AS subscription_level
FROM subscriptions;
Practice 6Label support tickets as Resolved Case if ticket_status is Resolved, otherwise Open Case
AI Prompt
Write a MySQL query to label support tickets as Resolved Case if ticket_status is Resolved, otherwise label them Open Case.
SQL Answer
SELECT
ticket_id,
ticket_status,
CASE
WHEN ticket_status = ‘Resolved’ THEN ‘Resolved Case’
ELSE ‘Open Case’
END AS ticket_group
FROM support_tickets;
Practice 7Label sessions as Mobile User if device_type is Mobile, otherwise Non-Mobile User
AI Prompt
Write a MySQL query to label sessions as Mobile User if device_type is Mobile, otherwise label them Non-Mobile User.
SQL Answer
SELECT
session_id,
device_type,
CASE
WHEN device_type = ‘Mobile’ THEN ‘Mobile User’
ELSE ‘Non-Mobile User’
END AS device_group
FROM sessions;
Practice 8Label campaigns as Email Marketing if channel is Email, otherwise Other Marketing
AI Prompt
Write a MySQL query to label campaigns as Email Marketing if channel is Email, otherwise label them Other Marketing.
SQL Answer
SELECT
campaign_name,
channel,
CASE
WHEN channel = ‘Email’ THEN ‘Email Marketing’
ELSE ‘Other Marketing’
END AS marketing_group
FROM campaigns;
Knowledge Check
Five questions — select the best answer for each.
1. What does CASE do in a SQL query?
AIt filters rows that match a condition
BIt adds conditional logic and returns different values based on which condition is true
CIt counts records grouped by a condition
DIt sorts results by a conditional value
Correct. CASE evaluates each row against one or more conditions and returns a different result depending on which condition is true — adding dynamic classification logic directly in the query.
Not quite. CASE adds conditional logic — it evaluates conditions and returns different output values for each row based on what is true.
2. What keyword closes a CASE statement in SQL?
ACLOSE
BFINISH
CEND
DSTOP
Correct. Every CASE statement must be closed with END. Forgetting END is one of the most common CASE syntax errors.
Not quite. CASE statements are closed with END — this is required and its absence causes a syntax error.
3. In a CASE with multiple WHEN conditions, what happens when more than one condition is true?
AAll matching THEN values are returned as separate rows
BThe last matching THEN value is returned
CThe first matching THEN value is returned — remaining conditions are skipped
DThe query returns an error
Correct. MySQL evaluates WHEN conditions top to bottom and returns the first one that is true. Once a match is found, all subsequent WHEN conditions are skipped for that row.
Not quite. CASE returns the first matching THEN value. Conditions are evaluated top to bottom and the first match wins — the rest are skipped.
4. What does ELSE do in a CASE statement?
AIt exits the query early
BIt handles rows where none of the WHEN conditions are true
CIt filters out NULL values
DIt is required and causes an error if omitted
Correct. ELSE is the fallback — it defines what to return for any row that does not match any WHEN condition. If ELSE is omitted and no WHEN matches, the result is NULL.
Not quite. ELSE handles rows that don’t match any WHEN condition. If ELSE is omitted and no condition matches, the result is NULL.
5. Why do analysts use CASE instead of filtering rows with WHERE?
ACASE is faster than WHERE on large tables
BCASE creates a new classification column while keeping all rows — WHERE removes rows that don’t match
CWHERE cannot be used with text conditions
DCASE automatically creates indexes on the result
Correct. WHERE is for removing rows. CASE is for adding a calculated classification column — every row is retained and gets a label based on its data. These are fundamentally different operations.
Not quite. CASE adds a classification column while keeping all rows. WHERE filters out non-matching rows. They serve completely different purposes.
Part 4 Summary
The key ideas from this lecture — keep these in mind as you move forward.
CASE allows analysts to apply conditional business logic and create dynamic categories directly inside SQL queries. Use WHEN…THEN for each condition. Use ELSE as the fallback. Close every CASE with END. Always alias the result with AS.
