CASE WHEN expression in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE WHEN with numeric ranges

Consider the following SQL query:

SELECT id, score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    ELSE 'F'
  END AS grade
FROM students;

What will be the output for a student with score = 85?

MySQL

CREATE TABLE students (id INT, score INT);
INSERT INTO students VALUES (1, 85);

A {"id":1,"score":85,"grade":"C"}

B {"id":1,"score":85,"grade":"A"}

C {"id":1,"score":85,"grade":"B"}

D {"id":1,"score":85,"grade":"F"}

💡 Hint

Check the conditions in order and see which range 85 fits into.

The CASE WHEN checks conditions from top to bottom. Since 85 is not >= 90 but is >= 80, it matches the second WHEN and returns 'B'.

❓ query_result

intermediate

CASE WHEN with NULL values

Given the table:

CREATE TABLE employees (id INT, department VARCHAR(20));
INSERT INTO employees VALUES (1, 'Sales'), (2, NULL);

What is the output of this query?

SELECT id,
  CASE WHEN department IS NULL THEN 'No Dept' ELSE department END AS dept_status
FROM employees ORDER BY id;

MySQL

CREATE TABLE employees (id INT, department VARCHAR(20));
INSERT INTO employees VALUES (1, 'Sales'), (2, NULL);

A [{"id":1,"dept_status":null},{"id":2,"dept_status":"No Dept"}]

B [{"id":1,"dept_status":"Sales"},{"id":2,"dept_status":null}]

C [{"id":1,"dept_status":"No Dept"},{"id":2,"dept_status":"No Dept"}]

D [{"id":1,"dept_status":"Sales"},{"id":2,"dept_status":"No Dept"}]

💡 Hint

Remember how CASE WHEN handles NULL with IS NULL condition.

The CASE WHEN checks if department IS NULL. For id=2, department is NULL, so it returns 'No Dept'. For id=1, it returns the actual department 'Sales'.

📝 Syntax

advanced

Identify the syntax error in CASE WHEN

Which of the following CASE WHEN expressions is syntactically correct in MySQL?

A CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END

B CASE score WHEN > 50 THEN 'Pass' ELSE 'Fail' END

C CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail'

D CASE score WHEN score > 50 THEN 'Pass' ELSE 'Fail' END

💡 Hint

Check the syntax for CASE WHEN with and without expression.

Option A uses the correct syntax: CASE WHEN condition THEN result ELSE result END. Option A and D misuse the CASE expression syntax. Option A misses END keyword.

❓ optimization

advanced

Optimizing CASE WHEN for multiple conditions

You want to assign categories based on age:

SELECT age,
  CASE
    WHEN age < 13 THEN 'Child'
    WHEN age < 20 THEN 'Teen'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS category
FROM people;

Which of the following changes improves readability and maintains correctness?

A Keep original CASE WHEN with ordered conditions from smallest to largest

B Use nested CASE WHEN inside ELSE for each range

C Use CASE age WHEN < 13 THEN 'Child' WHEN < 20 THEN 'Teen' WHEN < 65 THEN 'Adult' ELSE 'Senior' END

D Use multiple separate IF statements instead of CASE WHEN

💡 Hint

Think about how CASE WHEN evaluates conditions in order.

Option A is best because CASE WHEN evaluates conditions top to bottom, so ordering from smallest to largest age ranges is clear and correct. Option A is invalid syntax. Option A complicates unnecessarily. Option A is not SQL standard.

🧠 Conceptual

expert

Understanding CASE WHEN evaluation order and NULL handling

Given the query:

SELECT value,
  CASE
    WHEN value > 10 THEN 'High'
    WHEN value <= 10 THEN 'Low'
    ELSE 'Unknown'
  END AS category
FROM data;

What will be the category for a row where value is NULL?

A 'Low'

B 'Unknown'

C 'High'

D NULL

💡 Hint

Remember how comparisons with NULL behave in SQL CASE WHEN.

In SQL, any comparison with NULL returns UNKNOWN, so neither WHEN condition matches. Therefore, ELSE clause is used, returning 'Unknown'.

Practice

1. What does the CASE WHEN expression do in a MySQL query?

easy

A. It creates a new table in the database.

B. It sorts the results in ascending order.

C. It deletes rows that meet a condition.

D. It returns different values based on specified conditions.

Solution

1. Step 1: Understand the purpose of CASE WHEN

   The CASE WHEN expression checks conditions and returns values accordingly.

2. Step 2: Compare with other options

   Creating tables, deleting rows, or sorting are different SQL operations, not done by CASE WHEN.

3. Final Answer:

   It returns different values based on specified conditions. -> Option D

4. Quick Check:

   CASE WHEN = conditional value return [OK]

Hint: CASE WHEN returns values based on conditions, like IF-THEN-ELSE [OK]

Common Mistakes:

• Thinking CASE WHEN creates or deletes tables
• Confusing CASE WHEN with ORDER BY sorting
• Assuming CASE WHEN changes data permanently

2. Which of the following is the correct syntax for a simple CASE WHEN expression in MySQL?

easy

A. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

B. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END FROM students;

C. SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' FROM students;

D. SELECT CASE score > 50 THEN 'Pass' ELSE 'Fail' END students;

Solution

1. Step 1: Check the correct CASE WHEN syntax

   The correct syntax includes CASE WHEN condition THEN result ELSE result END.

2. Step 2: Identify syntax errors in other options

   The other options miss keywords or punctuation: some omit WHEN, others forget END, or misplace FROM.

3. Final Answer:

   SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; -> Option A

4. Quick Check:

   Correct CASE WHEN syntax = SELECT CASE WHEN score > 50 THEN 'Pass' ELSE 'Fail' END FROM students; [OK]

Hint: CASE WHEN needs WHEN, THEN, ELSE, and END keywords [OK]

Common Mistakes:

• Omitting WHEN keyword
• Forgetting END at the end
• Misplacing FROM clause

3. Given the table employees with a column salary, what will this query return?

SELECT name, CASE WHEN salary >= 5000 THEN 'High' ELSE 'Low' END AS salary_level FROM employees;

medium

A. An error because CASE WHEN cannot be used in SELECT.

B. Only employees with salary greater than 5000 will be shown.

C. A list of employee names with 'High' or 'Low' based on salary threshold 5000.

D. A list of salaries labeled as 'High' or 'Low' without employee names.

Solution

1. Step 1: Understand the CASE WHEN usage in SELECT

   The query labels each employee's salary as 'High' if >= 5000, else 'Low'.

2. Step 2: Check what columns are selected

   It selects employee names and the computed salary_level, so all employees appear.

3. Final Answer:

   A list of employee names with 'High' or 'Low' based on salary threshold 5000. -> Option C

4. Quick Check:

   CASE WHEN labels salaries, all employees listed [OK]

Hint: CASE WHEN in SELECT returns labels per row without filtering [OK]

Common Mistakes:

• Thinking CASE WHEN filters rows instead of labeling
• Assuming query returns error
• Ignoring selected columns

4. Identify the error in this query:

SELECT name, CASE salary > 10000 THEN 'Rich' ELSE 'Average' END AS status FROM customers;

medium

A. Missing WHEN keyword after CASE.

B. Missing END keyword at the end.

C. Incorrect alias usage for CASE expression.

D. Using ELSE without THEN is invalid.

Solution

1. Step 1: Review CASE WHEN syntax

   CASE must be followed by WHEN before condition; here WHEN is missing.

2. Step 2: Check other parts of the query

   END keyword is present, alias is correct, ELSE is properly used after THEN.

3. Final Answer:

   Missing WHEN keyword after CASE. -> Option A

4. Quick Check:

   CASE requires WHEN after CASE [OK]

Hint: Always write CASE WHEN, not CASE condition directly [OK]

Common Mistakes:

• Omitting WHEN keyword
• Confusing ELSE and THEN order
• Forgetting END keyword

5. You want to categorize products by price into 'Cheap' (price < 20), 'Moderate' (20 ≤ price ≤ 100), and 'Expensive' (price > 100). Which CASE WHEN expression correctly implements this?

hard

A. CASE WHEN price < 20 THEN 'Cheap' WHEN price < 100 THEN 'Moderate' ELSE 'Expensive' END

B. CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

C. CASE WHEN price < 20 THEN 'Cheap' WHEN price > 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END

D. CASE WHEN price <= 20 THEN 'Cheap' WHEN price > 20 AND price < 100 THEN 'Moderate' ELSE 'Expensive' END

Solution

1. Step 1: Analyze the price ranges needed

   'Cheap' is price less than 20, 'Moderate' is price between 20 and 100 inclusive, 'Expensive' is above 100.

2. Step 2: Check each option's conditions

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END correctly uses price < 20 for 'Cheap', price >= 20 and <= 100 for 'Moderate', else 'Expensive'. Others have incorrect boundaries.

3. Final Answer:

   CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END -> Option B

4. Quick Check:

   Correct range checks = CASE WHEN price < 20 THEN 'Cheap' WHEN price >= 20 AND price <= 100 THEN 'Moderate' ELSE 'Expensive' END [OK]

Hint: Use inclusive ranges carefully with AND and >=, <= operators [OK]

Common Mistakes:

• Overlapping or missing ranges
• Using incorrect comparison operators
• Not covering all possible values