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