Simple CASE syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Simple CASE with numeric grades

Given the table Grades with a column score containing numeric values, what is the output of this query?

SELECT score, CASE score WHEN 90 THEN 'A' WHEN 80 THEN 'B' ELSE 'F' END AS grade FROM Grades;

SQL

CREATE TABLE Grades(score INT);
INSERT INTO Grades VALUES (90), (80), (70);

A [{"score":90,"grade":"A"},{"score":80,"grade":"B"},{"score":70,"grade":"F"}]

B [{"score":90,"grade":"A"},{"score":80,"grade":"F"},{"score":70,"grade":"F"}]

C [{"score":90,"grade":"F"},{"score":80,"grade":"B"},{"score":70,"grade":"F"}]

D [{"score":90,"grade":"A"},{"score":80,"grade":"B"},{"score":70,"grade":"B"}]

💡 Hint

Simple CASE compares the expression to each WHEN value exactly.

The CASE expression compares the score to each WHEN value. 90 matches 'A', 80 matches 'B', and 70 matches none, so ELSE 'F' applies.

🧠 Conceptual

intermediate

Understanding Simple CASE vs Searched CASE

Which statement correctly describes the difference between Simple CASE and Searched CASE expressions in SQL?

A Simple CASE requires ELSE clause; Searched CASE does not.

B Simple CASE evaluates multiple conditions independently; Searched CASE compares one expression to multiple values.

C Simple CASE can only be used with numeric values; Searched CASE only with strings.

D Simple CASE compares one expression to multiple values; Searched CASE evaluates multiple conditions independently.

💡 Hint

Think about how the CASE expression compares or evaluates conditions.

Simple CASE compares one expression to multiple values. Searched CASE evaluates independent conditions in WHEN clauses.

📝 Syntax

advanced

Identify the syntax error in this Simple CASE query

Which option contains a syntax error in the Simple CASE expression?

SQL

SELECT id, CASE WHEN status = 'active' THEN 'A' WHEN status = 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

A SELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

B SELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END state FROM Users;

C ;sresU MORF etats SA DNE 'U' ESLE 'I' NEHT 'evitcani' NEHW 'A' NEHT 'evitca' NEHW sutats ESAC ,di TCELES

D ELECT id, CASE status WHEN 'active' THEN 'A' WHEN 'inactive' THEN 'I' ELSE 'U' END AS state FROM Users;

💡 Hint

Check the FROM clause syntax carefully.

Option A is missing the FROM keyword before the table name, causing a syntax error.

❓ optimization

advanced

Optimizing Simple CASE for performance

You have a large table with a column category containing values 'A', 'B', 'C', or others. Which Simple CASE query is most efficient to categorize rows as 'Group1' for 'A' and 'B', 'Group2' for 'C', and 'Other' otherwise?

A SELECT category, CASE WHEN category IN ('A','B') THEN 'Group1' WHEN category = 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

B SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'B' THEN 'Group1' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

C SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

D SELECT category, CASE category WHEN 'A' THEN 'Group1' WHEN 'B' THEN 'Group2' WHEN 'C' THEN 'Group2' ELSE 'Other' END AS group_name FROM table;

💡 Hint

Simple CASE compares one expression to values; using multiple WHENs for same output is valid.

Option B uses Simple CASE correctly with multiple WHENs for 'Group1'. Option B uses Searched CASE, which may be less efficient. Options C and D misclassify or omit 'B'.

🔧 Debug

expert

Debugging unexpected output in Simple CASE

Given this query:

SELECT id, CASE status WHEN 'active' THEN 'Active' WHEN 'inactive' THEN 'Inactive' ELSE 'Unknown' END AS state FROM Users;

Why might rows with status = NULL show 'Unknown' instead of NULL?

A Because NULL is treated as a string 'NULL' in Simple CASE, matching ELSE.

B Because NULL values are automatically converted to 'Unknown' by the database engine.

C Because Simple CASE compares using equality, NULL never matches any WHEN value, so ELSE applies.

D Because the query syntax is invalid and defaults to ELSE.

💡 Hint

Think about how NULL behaves in comparisons.

NULL is not equal to any value, so Simple CASE never matches WHEN clauses for NULL, resulting in ELSE output.

Practice

1. What does the Simple CASE expression do in SQL?

easy

A. It loops through rows and updates values conditionally.

B. It compares one expression to multiple values and returns a result for the first match.

C. It creates a new table based on conditions.

D. It deletes rows that match a condition.

Solution

1. Step 1: Understand the purpose of Simple CASE

   Simple CASE compares one expression against multiple possible values.

2. Step 2: Identify the result behavior

   It returns the result for the first matching value it finds.

3. Final Answer:

   It compares one expression to multiple values and returns a result for the first match. -> Option B

4. Quick Check:

   Simple CASE = compare expression to values [OK]

Hint: Simple CASE matches one value to many options [OK]

Common Mistakes:

• Thinking it loops through rows
• Confusing with IF statements
• Assuming it creates or deletes tables

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

easy

A. CASE expression WHEN value THEN result ELSE default END

B. IF expression THEN result ELSE default END

C. CASE WHEN expression THEN result END

D. SWITCH expression WHEN value THEN result END

Solution

1. Step 1: Recall Simple CASE syntax structure

   The correct syntax is: CASE expression WHEN value THEN result ELSE default END.

2. Step 2: Compare options

   CASE expression WHEN value THEN result ELSE default END matches this syntax exactly, others use incorrect keywords or structure.

3. Final Answer:

   CASE expression WHEN value THEN result ELSE default END -> Option A

4. Quick Check:

   Simple CASE syntax = CASE expr WHEN val THEN res ELSE def END [OK]

Hint: Simple CASE uses 'CASE expr WHEN val THEN res' format [OK]

Common Mistakes:

• Using WHEN without expression after CASE
• Confusing with IF or SWITCH syntax
• Omitting END keyword

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

SELECT EmployeeName,
 CASE DepartmentID
   WHEN 1 THEN 'HR'
   WHEN 2 THEN 'Sales'
   ELSE 'Other'
 END AS DeptName
FROM Employees;

medium

A. Employee names with department names 'HR', 'Sales', or 'Other' based on DepartmentID.

B. Only employees from HR and Sales departments.

C. An error because ELSE is missing.

D. Employee names with numeric DepartmentID values.

Solution

1. Step 1: Understand the CASE expression in the query

   The CASE checks DepartmentID: 1 returns 'HR', 2 returns 'Sales', others return 'Other'.

2. Step 2: Analyze the SELECT output

   The query returns EmployeeName and DeptName with the mapped department names or 'Other'.

3. Final Answer:

   Employee names with department names 'HR', 'Sales', or 'Other' based on DepartmentID. -> Option A

4. Quick Check:

   CASE maps DepartmentID to names [OK]

Hint: CASE returns first matching value or ELSE default [OK]

Common Mistakes:

• Thinking ELSE is required (it's optional)
• Assuming only HR and Sales appear
• Expecting numeric DepartmentID instead of names

4. Identify the error in this Simple CASE query:

SELECT ProductName,
 CASE ProductCategory
   WHEN 'A' THEN 'Category A'
   WHEN 'B' 'Category B'
   ELSE 'Other'
 END AS CategoryName
FROM Products;

medium

A. CASE expression should be inside parentheses.

B. Missing END keyword at the end.

C. Incorrect use of ELSE keyword.

D. Missing THEN keyword after WHEN 'B'.

Solution

1. Step 1: Review each WHEN clause syntax

   WHEN clauses must have THEN keyword before the result. The second WHEN is missing THEN.

2. Step 2: Check other parts of the query

   END keyword is present, ELSE is correctly used, parentheses are not required around CASE expression.

3. Final Answer:

   Missing THEN keyword after WHEN 'B'. -> Option D

4. Quick Check:

   WHEN must be followed by THEN [OK]

Hint: Each WHEN must be followed by THEN [OK]

Common Mistakes:

• Forgetting THEN after WHEN
• Thinking END is optional
• Adding unnecessary parentheses

5. You want to categorize sales amounts into 'Low', 'Medium', and 'High' using Simple CASE. Which query correctly assigns:
- 'Low' for 0
- 'Medium' for 1
- 'High' for 2 or more (default)?

SELECT SaleID,
 CASE SaleAmount
   WHEN 0 THEN 'Low'
   WHEN 1 THEN 'Medium'
   ELSE 'High'
 END AS SaleCategory
FROM Sales;

hard

A. Fails because ELSE cannot be used for multiple values.

B. Incorrect because Simple CASE cannot handle ranges like 2 or more.

C. Correctly categorizes sales as described.

D. Syntax error due to missing WHEN for 2.

Solution

1. Step 1: Understand Simple CASE limitations

   Simple CASE matches exact values; ELSE handles all other cases.

2. Step 2: Analyze the query logic

   WHEN 0 returns 'Low', WHEN 1 returns 'Medium', ELSE covers 2 or more as 'High'.

3. Final Answer:

   Correctly categorizes sales as described. -> Option C

4. Quick Check:

   ELSE covers all unmatched values [OK]

Hint: Use ELSE for all other values in Simple CASE [OK]

Common Mistakes:

• Thinking ELSE can't cover multiple values
• Trying to use ranges in Simple CASE
• Expecting WHEN for every possible value