Searched CASE syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of Searched CASE with multiple conditions

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

SELECT id, salary,
 CASE
   WHEN salary > 70000 THEN 'High'
   WHEN salary >= 40000 AND salary <= 70000 THEN 'Medium'
   ELSE 'Low'
 END AS SalaryLevel
FROM Employees
WHERE department = 'Sales';

Assume the Employees table has these rows:

id | salary | department
1  | 80000  | Sales
2  | 50000  | Sales
3  | 30000  | Sales
4  | 60000  | HR

SQL

SELECT id, salary,
 CASE
   WHEN salary > 70000 THEN 'High'
   WHEN salary >= 40000 AND salary <= 70000 THEN 'Medium'
   ELSE 'Low'
 END AS SalaryLevel
FROM Employees
WHERE department = 'Sales';

A [{"id":1,"salary":80000,"SalaryLevel":"High"},{"id":2,"salary":50000,"SalaryLevel":"Low"},{"id":3,"salary":30000,"SalaryLevel":"Low"}]

B [{"id":1,"salary":80000,"SalaryLevel":"High"},{"id":2,"salary":50000,"SalaryLevel":"Medium"},{"id":3,"salary":30000,"SalaryLevel":"Low"}]

C [{"id":1,"salary":80000,"SalaryLevel":"Medium"},{"id":2,"salary":50000,"SalaryLevel":"Medium"},{"id":3,"salary":30000,"SalaryLevel":"Low"}]

D [{"id":1,"salary":80000,"SalaryLevel":"High"},{"id":2,"salary":50000,"SalaryLevel":"Medium"}]

💡 Hint

Remember the searched CASE evaluates conditions in order and stops at the first true condition.

The CASE expression checks salary > 70000 first, so id=1 gets 'High'. Then salary between 40000 and 70000 is 'Medium' for id=2. The ELSE covers salaries below 40000, so id=3 gets 'Low'. Only employees in Sales department are included.

🧠 Conceptual

intermediate

Understanding evaluation order in Searched CASE

In a searched CASE expression, why is the order of WHEN conditions important?

Consider:

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  WHEN score >= 70 THEN 'C'
  ELSE 'F'
END

What happens if the order is changed to check score >= 70 first?

A The first matching condition is used, so if <code>score &gt;= 70</code> is first, all scores 70 and above get 'C', ignoring higher grades.

B The CASE expression evaluates all conditions and returns the highest grade matching the score.

C The CASE expression will cause a syntax error if conditions are reordered.

D The order does not matter; all conditions are checked and the best match is returned.

💡 Hint

Think about how CASE stops checking conditions once one is true.

Searched CASE evaluates conditions top to bottom and returns the result of the first true condition. If a broader condition like score >= 70 is first, it matches many scores and later conditions are ignored.

📝 Syntax

advanced

Identify the syntax error in this searched CASE query

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

SELECT id,
 CASE
   WHEN age > 60 THEN 'Senior'
   WHEN age >= 18 THEN 'Adult'
   ELSE 'Minor'
 END AS AgeGroup
FROM Users;

A CASE WHEN age > 60 THEN 'Senior' WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END

B CASE WHEN age > 60 THEN 'Senior' WHEN age >= 18 THEN 'Adult' ELSE 'Minor' END AS AgeGroup

C DNE 'roniM' ESLE 'tludA' NEHT 81 => ega NEHW 'roineS' NEHT 06 > ega NEHW ESAC

D CASE age WHEN > 60 THEN 'Senior' WHEN >= 18 THEN 'Adult' ELSE 'Minor' END

💡 Hint

Check the syntax for searched CASE vs simple CASE.

Option D incorrectly uses searched CASE syntax with a simple CASE style (CASE age WHEN > 60 ...), which is invalid. The searched CASE requires WHEN conditions with full boolean expressions.

❓ optimization

advanced

Optimizing searched CASE for performance

You have a large table Orders with a total_amount column. You want to classify orders as 'Small', 'Medium', or 'Large' using searched CASE:

CASE
  WHEN total_amount < 100 THEN 'Small'
  WHEN total_amount < 500 THEN 'Medium'
  ELSE 'Large'
END

Which approach can improve query performance when filtering by these categories?

A Rewrite the CASE as multiple OR conditions in the WHERE clause without indexing.

B Use nested CASE expressions inside the query to reduce evaluation time.

C Create a computed column with the CASE expression and index it, then filter on that column.

D Remove the CASE expression and filter only by total_amount ranges in the WHERE clause.

💡 Hint

Think about indexing and how it helps filtering.

Creating a computed column with the CASE expression and indexing it allows the database to quickly filter by category without recalculating CASE for every row.

🔧 Debug

expert

Debugging unexpected output from searched CASE

A developer writes this query:

SELECT product_id,
 CASE
   WHEN stock < 10 THEN 'Low'
   WHEN stock < 50 THEN 'Medium'
   WHEN stock < 100 THEN 'High'
   ELSE 'Very High'
 END AS StockLevel
FROM Inventory;

They notice that products with stock 5 are labeled 'Low' as expected, but products with stock 30 are labeled 'Low' instead of 'Medium'. What is the most likely cause?

A The stock column is stored as text, so comparisons behave unexpectedly.

B The CASE conditions are evaluated in order, so stock < 10 matches first, but 30 is not less than 10, so it should be 'Medium'.

C There is a hidden trigger changing stock values before query execution.

D The query is missing parentheses around conditions causing wrong evaluation.

💡 Hint

Check data types and how comparisons work.

If stock is stored as text, '30' < '10' can be false in string comparison because '3' is greater than '1' in ASCII order, causing wrong CASE branch to match.

Practice

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

easy

A. Deletes rows from a table

B. Joins two tables together

C. Creates a new table

D. Returns different results based on multiple conditions

Solution

1. Step 1: Understand the purpose of searched CASE

   The searched CASE expression evaluates multiple conditions and returns a result for the first true condition.

2. Step 2: Compare with other SQL operations

   Unlike joins, deletes, or table creation, CASE is used to return values conditionally within queries.

3. Final Answer:

   Returns different results based on multiple conditions -> Option D

4. Quick Check:

   Searched CASE = conditional results [OK]

Hint: Think of CASE as SQL's if-else for conditions [OK]

Common Mistakes:

• Confusing CASE with JOIN operations
• Thinking CASE modifies tables
• Assuming CASE deletes data

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

easy

A. CASE WHEN condition THEN result ELSE default END

B. CASE condition WHEN THEN result ELSE END

C. CASE THEN condition WHEN result ELSE END

D. CASE WHEN THEN condition ELSE result END

Solution

1. Step 1: Recall the searched CASE syntax

   The correct syntax starts with CASE, followed by WHEN condition THEN result, optional ELSE, and ends with END.

2. Step 2: Check each option

   Only CASE WHEN condition THEN result ELSE default END follows the correct order: CASE WHEN condition THEN result ELSE default END.

3. Final Answer:

   CASE WHEN condition THEN result ELSE default END -> Option A

4. Quick Check:

   Syntax = CASE WHEN ... THEN ... ELSE ... END [OK]

Hint: Remember: WHEN condition THEN result, ELSE optional, END last [OK]

Common Mistakes:

• Mixing order of WHEN and THEN
• Omitting END keyword
• Placing ELSE before THEN

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

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

medium

A. Returns only salaries greater than 5000

B. Shows an error because ELSE is missing

C. Labels each salary as 'High', 'Medium', or 'Low' based on value

D. Labels all salaries as 'Low'

Solution

1. Step 1: Analyze the CASE conditions

   The CASE checks if Salary > 5000, then returns 'High'; else if Salary > 3000, returns 'Medium'; otherwise 'Low'.

2. Step 2: Understand the query output

   Each row shows Salary and its label based on these conditions, so salaries are categorized correctly.

3. Final Answer:

   Labels each salary as 'High', 'Medium', or 'Low' based on value -> Option C

4. Quick Check:

   CASE categorizes salaries correctly [OK]

Hint: Read CASE conditions top to bottom, first true wins [OK]

Common Mistakes:

• Thinking ELSE is required and missing causes error
• Assuming query filters rows instead of labeling
• Ignoring order of WHEN conditions

4. Identify the error in this searched CASE expression:

SELECT Name, CASE WHEN Age > 18 THEN 'Adult' WHEN Age < 18 THEN 'Minor' END AS AgeGroup FROM People;

medium

A. Incorrect use of THEN keyword

B. No error, query is correct

C. No END keyword to close CASE

D. Missing ELSE clause for other cases

Solution

1. Step 1: Check CASE syntax completeness

   The CASE has WHEN conditions, THEN results, and ends with END keyword properly.

2. Step 2: Consider ELSE clause necessity

   ELSE is optional; if no ELSE, NULL is returned for unmatched cases, which is valid.

3. Final Answer:

   No error, query is correct -> Option B

4. Quick Check:

   ELSE optional, END required, syntax correct [OK]

Hint: ELSE is optional; END is mandatory [OK]

Common Mistakes:

• Assuming ELSE is always required
• Forgetting END keyword
• Misplacing THEN keyword

5. You want to classify products by price range using searched CASE. Which query correctly assigns 'Cheap' for prices below 50, 'Affordable' for prices between 50 and 150, and 'Expensive' for prices above 150?

hard

A. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

B. SELECT ProductName, CASE WHEN Price > 150 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

C. SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price > 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

D. SELECT ProductName, CASE WHEN Price BETWEEN 50 AND 150 THEN 'Cheap' WHEN Price < 50 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products;

Solution

1. Step 1: Match conditions to price ranges

   Price < 50 should be 'Cheap', 50 to 150 'Affordable', above 150 'Expensive'.

2. Step 2: Verify each option's logic

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; correctly orders conditions and labels prices as required. Others mislabel or misorder conditions.

3. Final Answer:

   SELECT ProductName, CASE WHEN Price < 50 THEN 'Cheap' WHEN Price BETWEEN 50 AND 150 THEN 'Affordable' ELSE 'Expensive' END AS PriceRange FROM Products; -> Option A

4. Quick Check:

   Conditions match price ranges correctly [OK]

Hint: Order CASE conditions from smallest to largest range [OK]

Common Mistakes:

• Mixing condition order causing wrong labels
• Using wrong comparison operators
• Misplacing BETWEEN ranges