SQL CASE WHEN with Challenge Questions

by

What is CASE WHEN?

CASE WHEN is SQL’s way of writing if/else logic inside a query. It lives inside SELECT and returns a value based on conditions. No new table. No function. Just inline logic.

SELECT
    column,
    CASE
        WHEN condition THEN 'result'
        WHEN condition THEN 'result'
        ELSE 'default'
    END AS alias
FROM table;
SQL

Types of cases

Searched case

Evaluates conditions one by one. First true condition wins.

SELECT
    first_name,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'Senior'
        WHEN salary >= 60000 THEN 'Mid'
        WHEN salary >= 40000 THEN 'Junior'
        ELSE 'Ungraded'
    END AS grade
FROM employees;
SQL

Simple case

SELECT
    order_id,
    status,
    CASE status
        WHEN 'delivered'  THEN 'Done'
        WHEN 'shipped'    THEN 'On the way'
        WHEN 'pending'    THEN 'Waiting'
        WHEN 'cancelled'  THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders;
SQL

CASE inside ORDER BY

You can use CASE to define a custom sort order – not just alphabetical or numeric.

SELECT order_id, status
FROM orders
ORDER BY
    CASE status
        WHEN 'pending'   THEN 1
        WHEN 'shipped'   THEN 2
        WHEN 'delivered' THEN 3
        WHEN 'cancelled' THEN 4
    END;
SQL

CASE with computed columns together

CASE and arithmetic work together naturally.

SELECT
    first_name,
    salary,
    CASE
        WHEN salary IS NULL   THEN 0
        WHEN salary > 90000   THEN salary * 1.05
        WHEN salary > 60000   THEN salary * 1.08
        ELSE                       salary * 1.10
    END AS new_salary
FROM employees;
SQL

Key rules to remember

  • Conditions are checked top to bottom – first match wins, rest are skipped
  • CASE returns one value per row
  • Always close with END
  • ELSE is optional but always write it
  • NULL does not match any WHEN – handle it explicitly if needed
  • You can nest CASE inside CASE – but keep it readable

PRACTICE QUESTIONS

Download SQL FILe

Beginner CASE WHEN Questions

Q1.
From employees, show first name, last name, and a column called status_label that shows Active if is_active = 1 and Inactive if is_active = 0.

Answer:
SELECT 
first_name,
last_name,
is_active,
CASE is_active
	WHEN 1 THEN "ACTIVE"
    WHEN 0 THEN "INACTIVE"
END AS status_label
 FROM employees;
SQL
Watch Here

Q2.
From orders, show order id, status, and a column called status_display that shows Done for delivered, In Transit for shipped, Waiting for pending, and Cancelled for cancelled.

Answer
SELECT 
order_id,
status,
CASE status
     WHEN 'delivered' THEN 'done'
    WHEN 'shipped' THEN 'in transit'
    WHEN 'pending' THEN 'waiting'
    WHEN 'cancelled' THEN 'Cancelled'
END AS status_display
FROM orders;
SQL

Q3.
From products, show product name, unit price, and a column called price_range that shows Budget if under 50, Mid if between 50 and 200, and Premium if above 200.

Answer:
SELECT product_name,
unit_price,
CASE
	WHEN unit_price < 50 THEN 'BUDGET'
    WHEN unit_price <=200 THEN 'MID'
    WHEN unit_price > 200 THEN 'PREMIUM'
END AS price_range
FROM products;
SQL

Q4.
From employees, show full name, department, and a column called team that shows Tech for Engineering, Revenue for Sales and Marketing, and Support for everything else.

Answer:
SELECT 
CONCAT(first_name, ' ', last_name) AS full_name,
department,
CASE
	WHEN department = 'Engineering' THEN 'Tech'
    WHEN department IN ('Sales','Marketing') THEN
    'Revenue'
    ELSE 'Support'
END AS team
FROM employees;
SQL

Q5.
From products, show product name, stock qty, and a column called stock_status that shows Low if stock is under 50, OK if between 50 and 200, and High if above 200.

Answer

SELECT 
product_name,
stock_qty,
CASE
    WHEN stock_qty < 50 THEN 'Low'
    WHEN stock_qty <=200 THEN 'Ok'
    ELSE 'High'
END AS stock_status
FROM products;
SQL

Q6.
From employees, show full name, salary, and a column called salary_band — Low under 55,000, Mid between 55,000 and 85,000, High above 85,000.

Answer
SELECT 
    CONCAT_WS(" ", first_name, last_name) AS full_name,
    salary,
    CASE
        WHEN salary < 55000 THEN 'LOW'
        WHEN salary BETWEEN 55000 AND 85000 THEN 'MID'
        ELSE 'HIGH'
    END AS salary_band
FROM employees;
SQL

Q7.
From orders, show order id, shipping fee, and a column called delivery_type — Free Delivery if shipping fee is 0, Paid Delivery if above 0.

Answer
SELECT 
order_id,
shipping_fee,
CASE shipping_fee
	WHEN  0 THEN 'FREE DELIVERY'
    ELSE 'PAID DELIVERY'
END AS delivery_type
FROM orders;
SQL

Q8.
From customers, show full name, country, and a column called region — Americas for USA, Canada, Brazil. Europe for UK, France, Germany, Italy, Norway, Sweden, Czech Rep, Romania. Asia for Japan, South Korea, India. Other for everything else.

Answer
SELECT 
CONCAT_WS(" ",first_name,last_name) AS full_name,
country,
CASE
	WHEN country IN ('USA', 'Canada', 'Brazil') THEN 'Americas'
    WHEN country In ('UK', 'France', 'Germany', 'Italy', 'Norway',
    'Sweden', 'Czech Rep', 'Romania') THEN 'Europe'
    WHEN country In ('Japan', 'South Korea', 'India') THEN 'Asia'
    ELSE 'Other'
END AS region
FROM customers;
SQL

Intermediate CASE WHEN QUESTIONS

Q9.
From employees, show full name, department, salary, and a column called bonus — Engineering gets 15% of salary, Sales gets 12%, everyone else gets 10%. Show the bonus amount as a computed value. Handle NULL salary — return 0.

Answer
SELECT 
CONCAT(first_name, ' ',last_name) AS full_name,
department,
salary,
CASE
	WHEN salary IS NULL THEN 0
	WHEN department = 'Engineering' THEN salary*0.15
    WHEN department = 'Sales' THEN salary* 0.12
    ELSE salary * 0.10
END AS bonus
 from employees;
SQL

Q10.
From products, show product name, unit price, cost price, gross profit (computed), and a column called margin_health — Excellent if margin is above 60%, Good between 40–60%, Thin below 40%.

Answer
SELECT product_name, unit_price, cost_price,
unit_price - cost_price as gross_proft,
(unit_price - cost_price)/unit_price as margin,
CASE
	WHEN (unit_price - cost_price)/unit_price > 0.60 THEN 'Excellent'
    WHEN (unit_price - cost_price)/unit_price BETWEEN 0.40 AND 0.60 THEN 
    'Good'
    ELSE 'THIN'
END AS margin_health
 from products;
SQL

Q11.
From employees, show full name, hire date, and a column called seniority — hired before 2019 is Veteran, 2019 to 2021 is Experienced, 2022 onwards is New Hire.

Answer
SELECT 
concat(first_name,' ',last_name) as full_name,
hire_date,
YEAR(hire_date) as year,
CASE
	WHEN YEAR(hire_date) < 2019 THEN 'veteran'
    WHEN YEAR(hire_date) BETWEEN 2019 AND 2021 THEN 'Experienced'
    ELSE 'New hire'
END As seniority
 from employees;
SQL
FunctionPurposeExampleResult
YEAR(date)Extract yearYEAR('2024-07-20')2024
MONTH(date)Extract month numberMONTH('2024-07-20')7
MONTHNAME(date)Month nameMONTHNAME('2024-07-20')July
DAY(date)Day of monthDAY('2024-07-20')20
DAYNAME(date)Day nameDAYNAME('2024-07-20')Saturday
DAYOFWEEK(date)Day number (1=Sunday)DAYOFWEEK('2024-07-20')7
DAYOFYEAR(date)Day number in yearDAYOFYEAR('2024-07-20')202
WEEK(date)Week numberWEEK('2024-07-20')29
WEEKDAY(date)Weekday (0=Monday)WEEKDAY('2024-07-20')5
QUARTER(date)Quarter of yearQUARTER('2024-07-20')3
HOUR(time)HourHOUR('15:30:45')15
MINUTE(time)MinuteMINUTE('15:30:45')30
SECOND(time)SecondSECOND('15:30:45')45

Q12.
From order_items, show item id, quantity, unit price, line total (computed), and a column called order_size — line total under 100 is Small, 100–500 is Medium, above 500 is Large.

Answer
SELECT 
item_id,
unit_price,quantity,
unit_price * quantity as line_total,

CASE
	WHEN unit_price * quantity < 100 THEN 'small'
    WHEN unit_price * quantity <= 500 THEN 'medium'
    ELSE 'large'
END AS order_size
 from order_items;
SQL

Q13.
From employees, show full name, salary, annual salary (computed), and a column called tax_band — annual salary under 600,000 is Basic Rate, 600,000–900,000 is Higher Rate, above 900,000 is Top Rate. Handle NULL — show No Data.

Answer
SELECT 
concat(first_name,' ' ,last_name) as full_name,
salary,
salary * 12 AS annual_salary,
CASE
	WHEN salary IS NULL THEN 'nodata'
	WHEN salary*12 < 600000 THEN 'basic'
    WHEN salary * 12 < 900000 THEN 'higher'
    ELSE 'top'
END AS tax_band
 from employees;
SQL

Q14.
From orders, show order id, status, shipping fee, and a column called priority — pending with shipping fee above 0 is Urgent, shipped is Watch, delivered is Closed, cancelled is Archived, everything else is Review.

Answer
SELECT 
order_id, status, shipping_fee,
CASE 
	WHEN status = 'pending' AND shipping_fee > 0 THEN 'urgent'
    WHEN status = 'shipped' THEN 'watch'
    WHEN status = 'delivered' THEN 'closed'
    WHEN status = 'cancelled' THEN 'archived'
    ELSE 'review'
END as priority
 from orders;
SQL

Q15.
From products, show product name, category, unit price, and a column called vat_rate — Electronics gets 20% VAT, Furniture gets 5%, Accessories gets 0%. Show the actual VAT amount as a computed column called vat_amount.

Answer
SELECT 
product_name, category, unit_price,
CASE
	WHEN category = 'Electronics' THEN 20
    WHEN category = 'Furniture' THEN 5
    WHEN category = 'Accessories' THEN 0
END as vat_rate,
CASE
	WHEN category = 'Electronics' THEN unit_price * 0.2
    WHEN category = 'Furniture' THEN unit_price * 0.04
    WHEN category = 'Accessories' THEN 0
END as vat_amount
FROM products;
SQL

Q16.
From employees, show full name, department, job title, and a column called role_type — if job title contains Manager or Lead or CMO or CFO show Leadership, if it contains Senior show Senior IC, if it contains Junior or Intern show Junior, everything else show IC.

Answer
SELECT
    CONCAT_WS(' ', first_name, last_name) AS full_name,
    department,
    job_title,
    CASE
        WHEN job_title LIKE '%Manager%'
          OR job_title LIKE '%Lead%'
          OR job_title IN ('CMO', 'CFO')
            THEN 'Leadership'

        WHEN job_title LIKE '%Senior%'
            THEN 'Senior IC'

        WHEN job_title LIKE '%Junior%'
          OR job_title LIKE '%Intern%'
            THEN 'Junior'

        ELSE 'IC'
    END AS role_type
FROM employees;
SQL

HARD Case When Questions

Q17.
From employees, show full name, department, salary, and a column called revised_salary — apply these raises: Engineering gets 10%, Sales gets 8%, Marketing gets 7%, Finance gets 6%, HR gets 5%. If salary is NULL, return 0. Order by revised_salary descending.

Answer:
SELECT concat_ws(" ",first_name,last_name) as full_name,
department, salary,
CASE
    WHEN salary IS NULL THEN 0
    WHEN department = 'Engineering' THEN salary + salary * 0.1
    WHEN department = 'Sales' THEN salary + salary * 0.08
    WHEN department = 'Marketing' THEN salary + salary * 0.07
    WHEN department = 'Finance' THEN salary + salary * 0.06
    WHEN department = 'HR' THEN salary + salary * 0.05
    ELSE salary
END AS revised_salary
from employees
ORDER BY revised_salary DESC;
SQL

Q18.
From products, show product name, unit price, stock qty, and a column called action — if stock is under 20 and unit price is above 300 show Urgent Restock, if stock is under 50 show Restock Soon, if stock is above 400 show Overstock, everything else show OK. Order by stock qty ascending.

Answer:
SELECT
    product_name,
    unit_price,
    stock_qty,
    CASE
        WHEN stock_qty < 20 AND unit_price > 300 THEN 'Urgent Restock'
        WHEN stock_qty < 50 THEN 'Restock Soon'
        WHEN stock_qty > 400 THEN 'Overstock'
        ELSE 'OK'
    END AS action
FROM products
ORDER BY stock_qty ASC;
SQL

Q19.
From order_items, show item id, order id, unit price, quantity, line total (computed), and a column called discount_applied — line total above 500 gets 10% off shown as discount_amount, otherwise 0. Also show the final price after discount as final_price.

Answer:
SELECT
    item_id,
    order_id,
    unit_price,
    quantity,
    line_total,
    CASE
        WHEN line_total > 500 THEN line_total * 0.10
        ELSE 0
    END AS discount_applied,
    CASE
        WHEN line_total > 500 THEN line_total * 0.90
        ELSE line_total
    END AS final_price
FROM (
    SELECT
        item_id,
        order_id,
        unit_price,
        quantity,
        unit_price * quantity AS line_total
    FROM order_items
) t;
SQL

Q20.
From employees, show full name, department, salary, annual salary (computed), and TWO CASE columns — experience_level based on hire date (Veteran/Experienced/New Hire from Q11) AND pay_grade based on salary band (Low/Mid/High from Q6). Order by annual salary descending, NULLs last.

Q21.
From products, show product name, category, unit price, cost price, gross profit (computed), margin percentage (computed and rounded to 1 decimal), and a column called pricing_strategy — margin above 70% is Premium Pricing, 50–70% is Value Pricing, 30–50% is Competitive, below 30% is Loss Leader. Order by margin percentage descending.

Q22.
From employees, show full name, job title, department, salary, and a column called recommended_action — salary IS NULL and is_active = 1 shows Set Salary, salary under 52,000 and is_active = 1 shows Review Pay, is_active = 0 shows Offboarded, salary above 100,000 shows Retain, everything else shows No Action. Order by employee_id.

Q23.
From orders, show order id, order date, status, shipping fee, and a column called fulfilment_score — delivered with free shipping scores 5, delivered with paid shipping scores 4, shipped scores 3, pending scores 2, cancelled scores 1. Order by fulfilment_score descending, then order date ascending.

Q24.
From employees, show full name, department, salary, annual salary (computed), bonus amount using department-based bonus rates from Q9 (handle NULL), and a column called total_compensation which is annual salary plus bonus. Order by total_compensation descending, NULLs last.

Q25.
From products, show product name, category, unit price, and build a single column called display_tag that combines category and price range in one label — for example Electronics — Premium, Accessories — Budget, Furniture — Mid. Use CASE for the price range part and string concat to combine it with category. Order by category, then unit price ascending.

Leave a Comment