Nested CASE expressions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Nested CASE in SELECT

Given the table Employees with columns id, department, and salary, what is the output of this query?

SELECT id, department, salary,
 CASE
   WHEN department = 'Sales' THEN
     CASE WHEN salary > 5000 THEN 'High Sales' ELSE 'Low Sales' END
   ELSE 'Other' END AS category
FROM Employees
WHERE id = 3;

SQL

CREATE TABLE Employees (id INT, department VARCHAR(20), salary INT);
INSERT INTO Employees VALUES (1, 'Sales', 6000), (2, 'HR', 4000), (3, 'Sales', 4500), (4, 'IT', 7000);

A [{"id":3,"department":"Sales","salary":4500,"category":"Low Sales"}]

B [{"id":3,"department":"Sales","salary":4500,"category":"High Sales"}]

C []

D [{"id":3,"department":"Sales","salary":4500,"category":"Other"}]

💡 Hint

Look carefully at the nested CASE conditions and salary value for id 3.

The outer CASE checks if department is 'Sales'. For id 3, it is 'Sales'. Then the inner CASE checks if salary > 5000. Salary is 4500, so it returns 'Low Sales'.

📝 Syntax

intermediate

Identify the Syntax Error in Nested CASE

Which option contains a syntax error in the nested CASE expression below?

SELECT id,
 CASE WHEN score > 80 THEN
   CASE WHEN grade = 'A' THEN 'Excellent'
   ELSE 'Good' END
 ELSE 'Average' END AS performance
FROM Results;

A Missing END for outer CASE

B Missing THEN keyword in inner CASE

C All syntax is correct

D Missing END for inner CASE

💡 Hint

Count the number of CASE and END keywords carefully.

Both inner and outer CASE expressions have matching THEN and END keywords. The syntax is correct.

❓ optimization

advanced

Optimizing Nested CASE Expressions

You have this nested CASE expression:

CASE
  WHEN status = 'Active' THEN
    CASE WHEN score >= 90 THEN 'Top Performer' ELSE 'Active Member' END
  WHEN status = 'Inactive' THEN 'No Access'
  ELSE 'Unknown' END

Which option is the most efficient equivalent expression?

A CASE WHEN status = 'Active' AND score >= 90 THEN 'Top Performer' WHEN status = 'Active' THEN 'Active Member' WHEN status = 'Inactive' THEN 'No Access' ELSE 'Unknown' END

B CASE WHEN score >= 90 THEN 'Top Performer' WHEN status = 'Active' THEN 'Active Member' WHEN status = 'Inactive' THEN 'No Access' ELSE 'Unknown' END

C CASE WHEN status = 'Active' THEN 'Active Member' WHEN score >= 90 THEN 'Top Performer' WHEN status = 'Inactive' THEN 'No Access' ELSE 'Unknown' END

D CASE WHEN status = 'Inactive' THEN 'No Access' WHEN status = 'Active' THEN CASE WHEN score >= 90 THEN 'Top Performer' ELSE 'Active Member' END ELSE 'Unknown' END

💡 Hint

Try to combine conditions to avoid nested CASE.

Option A combines the nested CASE into a single CASE with combined conditions, reducing complexity and improving readability.

🔧 Debug

advanced

Debugging Unexpected Output from Nested CASE

Given this query:

SELECT id,
 CASE WHEN region = 'North' THEN
   CASE WHEN sales > 1000 THEN 'High' ELSE 'Low' END
 ELSE 'Unknown' END AS sales_level
FROM SalesData
WHERE id = 5;

The output is:

{"id":5,"sales_level":"Unknown"}

But the region for id 5 is 'North' and sales is 1200. What is the likely cause?

A The sales value is stored as text, causing comparison failure

B The column name 'region' is misspelled or does not exist

C The WHERE clause filters out the row with id 5

D The nested CASE syntax is incorrect

💡 Hint

Check column names carefully and case sensitivity.

If 'region' column is misspelled or missing, the CASE condition fails and ELSE 'Unknown' is returned.

🧠 Conceptual

expert

Understanding Nested CASE Evaluation Order

Consider this nested CASE expression:

CASE
  WHEN condition1 THEN
    CASE
      WHEN condition2 THEN 'Result A'
      ELSE 'Result B'
    END
  ELSE 'Result C'
END

Which statement best describes how SQL evaluates this expression?

A SQL evaluates condition2 first; if true, then evaluates condition1; otherwise returns 'Result C'

B SQL evaluates condition1 and condition2 independently and concatenates results

C SQL evaluates both condition1 and condition2 simultaneously and returns the first true result

D SQL evaluates condition1 first; if true, then evaluates condition2; otherwise returns 'Result C'

💡 Hint

Think about how nested CASE expressions work step-by-step.

The outer CASE checks condition1 first. Only if condition1 is true does it evaluate the inner CASE with condition2. Otherwise, it returns the ELSE value of the outer CASE.