SQL CASE WHEN with Challenge Questions

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;

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;

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;

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;

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;

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

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.

SELECT 
first_name,
last_name,
is_active,
CASE is_active
	WHEN 1 THEN "ACTIVE"
    WHEN 0 THEN "INACTIVE"
END AS status_label
 FROM employees;

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.

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.

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.

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.

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.

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.

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.

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.

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%.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.