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.

Practice

1. What is the main purpose of using nested CASE expressions in SQL?

easy

A. To check multiple conditions inside another condition for detailed results

B. To create new tables from existing data

C. To delete rows based on conditions

D. To join two tables together

Solution

1. Step 1: Understand the role of CASE expressions

   CASE expressions allow conditional logic to return different values based on conditions.

2. Step 2: Recognize nested CASE usage

   Nested CASE means putting one CASE inside another to check conditions within conditions for more detailed categorization.

3. Final Answer:

   To check multiple conditions inside another condition for detailed results -> Option A

4. Quick Check:

   Nested CASE = detailed condition checks [OK]

Hint: Nested CASE means CASE inside CASE for detailed checks [OK]

Common Mistakes:

• Confusing CASE with JOIN operations
• Thinking CASE creates or deletes tables
• Ignoring the need to close each CASE with END

2. Which of the following is the correct syntax for a nested CASE expression in SQL?

easy

A. CASE WHEN condition1 THEN IF condition2 THEN result1 ELSE result2 END ELSE result3 END

B. CASE WHEN condition1 THEN CASE condition2 THEN result1 ELSE result2 END ELSE result3 END

C. IF condition1 THEN CASE WHEN condition2 THEN result1 ELSE result2 END ELSE result3 END

D. CASE WHEN condition1 THEN CASE WHEN condition2 THEN result1 ELSE result2 END ELSE result3 END

Solution

1. Step 1: Review correct CASE syntax

   CASE expressions use WHEN ... THEN ... ELSE ... END structure; nested CASE must follow this pattern inside THEN.

2. Step 2: Identify correct nested CASE

   CASE WHEN condition1 THEN CASE WHEN condition2 THEN result1 ELSE result2 END ELSE result3 END correctly nests a CASE inside THEN with proper WHEN, THEN, ELSE, and END keywords.

3. Final Answer:

   CASE WHEN condition1 THEN CASE WHEN condition2 THEN result1 ELSE result2 END ELSE result3 END -> Option D

4. Quick Check:

   Nested CASE syntax = CASE WHEN condition1 THEN CASE WHEN condition2 THEN result1 ELSE result2 END ELSE result3 END [OK]

Hint: Nested CASE uses WHEN-THEN inside THEN with END for each CASE [OK]

Common Mistakes:

• Using IF instead of CASE inside SQL
• Missing END keyword for inner CASE
• Incorrect keyword order in nested CASE

3. Given the table Employees with column Salary, what will this query return?

SELECT Salary, CASE WHEN Salary > 70000 THEN CASE WHEN Salary > 90000 THEN 'High' ELSE 'Medium' END ELSE 'Low' END AS SalaryLevel FROM Employees;

medium

A. Labels all salaries above 70000 as 'High' and others as 'Low'

B. Labels salaries above 90000 as 'High', between 70001 and 90000 as 'Medium', others as 'Low'

C. Labels all salaries below 70000 as 'Medium' and others as 'High'

D. Returns an error due to incorrect CASE syntax

Solution

1. Step 1: Analyze outer CASE condition

   If Salary > 70000, then check inner CASE; else label 'Low'.

2. Step 2: Analyze inner CASE condition

   Inside outer WHEN true, if Salary > 90000 then 'High', else 'Medium'.

3. Final Answer:

   Labels salaries above 90000 as 'High', between 70001 and 90000 as 'Medium', others as 'Low' -> Option B

4. Quick Check:

   Nested CASE categorizes salary levels correctly [OK]

Hint: Check outer condition first, then inner CASE for detailed labels [OK]

Common Mistakes:

• Ignoring inner CASE and labeling all above 70000 as same
• Confusing syntax causing errors
• Misreading comparison operators

4. Identify the error in this nested CASE SQL snippet:

SELECT CASE WHEN score > 80 THEN CASE score > 90 THEN 'Excellent' ELSE 'Good' END ELSE 'Average' END AS Grade FROM Results;

medium

A. Using wrong comparison operator

B. Missing END keyword for outer CASE

C. Missing WHEN keyword before inner condition

D. Incorrect ELSE placement inside inner CASE

Solution

1. Step 1: Check inner CASE syntax

   Inner CASE lacks WHEN before condition; it should be 'CASE WHEN score > 90 THEN ...'

2. Step 2: Verify overall CASE structure

   Outer CASE has proper WHEN and END; error is only in inner CASE missing WHEN keyword.

3. Final Answer:

   Missing WHEN keyword before inner condition -> Option C

4. Quick Check:

   Inner CASE needs WHEN before condition [OK]

Hint: Every CASE condition must start with WHEN [OK]

Common Mistakes:

• Forgetting WHEN in inner CASE
• Assuming END is missing when it is present
• Misplacing ELSE inside CASE

5. You want to classify products based on Price and Stock using nested CASE. If Price > 100 and Stock > 50, label 'Premium-Available'. If Price > 100 and Stock ≤ 50, label 'Premium-Limited'. Otherwise, label 'Standard'. Which SQL snippet correctly implements this?

hard

A. CASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END

B. CASE WHEN Price > 100 AND Stock > 50 THEN 'Premium-Available' WHEN Price > 100 AND Stock <= 50 THEN 'Premium-Limited' ELSE 'Standard' END

C. CASE WHEN Price > 100 THEN 'Premium' ELSE CASE WHEN Stock > 50 THEN 'Available' ELSE 'Limited' END END

D. CASE WHEN Price > 100 THEN CASE Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END

Solution

1. Step 1: Understand the nested conditions

   First check if Price > 100, then inside check Stock > 50 or not.

2. Step 2: Match correct syntax and logic

   CASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END correctly nests CASE inside THEN with proper WHEN, THEN, ELSE, and END keywords matching the problem.

3. Final Answer:

   CASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END -> Option A

4. Quick Check:

   Nested CASE matches Price then Stock checks [OK]

Hint: Check outer condition first, nest inner CASE for second condition [OK]

Common Mistakes:

• Using AND instead of nested CASE
• Missing WHEN in inner CASE
• Incorrect ELSE placement