CASE in WHERE clause in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of CASE in WHERE clause filtering

Consider a table Employees with columns id, department, and salary. What rows will be returned by this query?

SELECT id, department, salary FROM Employees WHERE CASE WHEN department = 'Sales' THEN salary > 50000 ELSE salary > 70000 END;

SQL

CREATE TABLE Employees (id INT, department VARCHAR(20), salary INT);
INSERT INTO Employees VALUES
(1, 'Sales', 60000),
(2, 'Sales', 40000),
(3, 'HR', 75000),
(4, 'HR', 65000),
(5, 'IT', 80000);

A Rows with id 3, 4, and 5

B Rows with id 1, 2, 3, and 5

C Rows with id 2, 4, and 5

D Rows with id 1, 3, and 5

💡 Hint

Think about how the CASE expression evaluates the condition for each department.

The CASE expression checks if the department is 'Sales'. For 'Sales', it filters salaries > 50000 (id 1 passes, id 2 fails). For others, it filters salaries > 70000 (id 3 and 5 pass, id 4 fails).

📝 Syntax

intermediate

Identify syntax error in CASE usage in WHERE clause

Which option contains a syntax error when using CASE in the WHERE clause?

SELECT * FROM Products WHERE CASE category WHEN 'Electronics' THEN price < 100 ELSE price < 50 END;

A CASE category WHEN 'Electronics' THEN price < 100 ELSE price < 50 END

B CASE WHEN category = 'Electronics' THEN price < 100 ELSE price < 50 END

C CASE category WHEN 'Electronics' THEN price < 100 ELSE price < 50

D CASE WHEN category = 'Electronics' THEN price < 100 ELSE price < 50 END;

💡 Hint

Check if the CASE expression is properly closed.

Option C is missing the END keyword to close the CASE expression, causing a syntax error.

❓ optimization

advanced

Optimizing CASE in WHERE clause for performance

You have a large Orders table with columns order_id, status, and amount. You want to filter orders where if status is 'completed', amount > 100, else amount > 200. Which query is likely more efficient?

A SELECT * FROM Orders WHERE status = 'completed' AND amount > 100 AND amount > 200;

B SELECT * FROM Orders WHERE (status = 'completed' AND amount > 100) OR (status != 'completed' AND amount > 200);

C SELECT * FROM Orders WHERE amount > 100 OR amount > 200;

D SELECT * FROM Orders WHERE CASE WHEN status = 'completed' THEN amount > 100 ELSE amount > 200 END;

💡 Hint

Think about how the database can use indexes with simple conditions.

Option B uses explicit AND/OR conditions which can better use indexes. Option B uses CASE which may prevent index use. Options C and D do not correctly implement the logic.

🧠 Conceptual

advanced

Understanding CASE evaluation in WHERE clause

What does the CASE expression return in the WHERE clause to filter rows?

SELECT * FROM Customers WHERE CASE WHEN age >= 18 THEN TRUE ELSE FALSE END;

A It returns TRUE or FALSE to decide if the row is included

B It returns the age value for filtering

C It returns NULL for rows where age < 18

D It returns a string 'TRUE' or 'FALSE' which is always true

💡 Hint

Remember WHERE expects a condition that evaluates to TRUE or FALSE.

The CASE expression returns a boolean TRUE or FALSE. WHERE includes rows where the condition is TRUE.

🔧 Debug

expert

Debugging unexpected results with CASE in WHERE clause

Given this query:

SELECT * FROM Sales WHERE CASE WHEN region = 'North' THEN amount > 1000 ELSE amount > 500 END = TRUE;

Why might this query return no rows even though data exists?

A Because the CASE expression returns NULL for some rows causing the condition to fail

B Because the CASE expression returns boolean but comparing with = TRUE is redundant and may cause issues

C Because the syntax of CASE in WHERE clause is invalid

D Because amount column has NULL values causing errors

💡 Hint

Consider how NULL values affect boolean expressions in SQL.

If amount is NULL, the CASE expression returns NULL, and comparing NULL = TRUE yields FALSE, so those rows are excluded.

Practice

1. What does using CASE in a WHERE clause allow you to do in SQL?

easy

A. Sort the results in multiple ways

B. Create new columns in the result set

C. Apply different filter conditions based on row values

D. Join tables conditionally

Solution

1. Step 1: Understand the role of CASE in WHERE

   The CASE expression in WHERE lets you choose which condition to apply depending on data values in each row.

2. Step 2: Compare with other options

   Creating new columns is done with SELECT, sorting with ORDER BY, and joining with JOIN, not CASE in WHERE.

3. Final Answer:

   Apply different filter conditions based on row values -> Option C

4. Quick Check:

   CASE in WHERE = conditional filtering [OK]

Hint: CASE in WHERE picks filters by row values [OK]

Common Mistakes:

• Thinking CASE creates new columns
• Confusing CASE with ORDER BY
• Assuming CASE controls JOIN logic

2. Which of the following is the correct syntax to use CASE inside a WHERE clause?

easy

A. WHERE CASE age WHEN > 18 THEN 'adult' ELSE 'minor' END = 'adult'

B. WHERE CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END = 'adult'

C. WHERE CASE WHEN age > 18 THEN adult ELSE minor END

D. WHERE CASE age > 18 THEN 'adult' ELSE 'minor' END = 'adult'

Solution

1. Step 1: Review correct CASE syntax

   The correct CASE syntax uses WHEN condition THEN result ELSE result END. WHERE CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END = 'adult' follows this exactly.

2. Step 2: Identify syntax errors in other options

   Options B, C, and D misuse CASE syntax by missing WHEN or ELSE keywords or incorrect placement.

3. Final Answer:

   WHERE CASE WHEN age > 18 THEN 'adult' ELSE 'minor' END = 'adult' -> Option B

4. Quick Check:

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

Hint: CASE needs WHEN and THEN keywords in WHERE [OK]

Common Mistakes:

• Omitting WHEN keyword
• Using CASE without END
• Placing conditions incorrectly

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

SELECT * FROM Employees WHERE CASE WHEN salary >= 50000 THEN 1 ELSE 0 END = 1;

medium

A. Rows where salary is less than 50000

B. Rows where salary is exactly 50000

C. All rows regardless of salary

D. Rows where salary is 50000 or more

Solution

1. Step 1: Understand the CASE condition

   The CASE returns 1 if salary is 50000 or more, else 0.

2. Step 2: Apply the WHERE filter

   The WHERE clause keeps rows where CASE result equals 1, so salary must be at least 50000.

3. Final Answer:

   Rows where salary is 50000 or more -> Option D

4. Quick Check:

   salary >= 50000 = filtered rows [OK]

Hint: CASE returns 1 for salary ≥ 50000, filter keeps those rows [OK]

Common Mistakes:

• Thinking it returns rows below 50000
• Confusing equality with greater than
• Ignoring ELSE result

4. Identify the error in this query using CASE in WHERE clause:

SELECT * FROM Orders WHERE CASE order_status WHEN 'shipped' THEN TRUE ELSE FALSE;

medium

A. Missing END keyword for CASE expression

B. Incorrect use of TRUE/FALSE in CASE

C. CASE cannot be used in WHERE clause

D. Missing THEN keyword in CASE

Solution

1. Step 1: Check CASE syntax completeness

   The CASE expression must end with END keyword, which is missing here.

2. Step 2: Verify other parts

   TRUE/FALSE usage is valid, CASE can be used in WHERE, and THEN keyword is present.

3. Final Answer:

   Missing END keyword for CASE expression -> Option A

4. Quick Check:

   CASE ... END required [OK]

Hint: Always close CASE with END in WHERE [OK]

Common Mistakes:

• Forgetting END keyword
• Thinking CASE can't be in WHERE
• Misplacing THEN keyword

5. You want to filter Products so that if category is 'Electronics', only products with stock > 0 are shown; otherwise, show products with price < 100. Which WHERE clause using CASE is correct?

hard

A. WHERE CASE WHEN category = 'Electronics' THEN stock > 0 ELSE price < 100 END = TRUE

B. WHERE CASE category WHEN 'Electronics' THEN stock > 0 ELSE price < 100 = TRUE

C. WHERE CASE WHEN category = 'Electronics' THEN stock > 0 ELSE price < 100

D. WHERE CASE category WHEN 'Electronics' THEN stock > 0 ELSE price < 100

Solution

1. Step 1: Understand CASE returns boolean condition

   The CASE must return TRUE or FALSE to filter rows properly.

2. Step 2: Check syntax and comparison

   WHERE CASE WHEN category = 'Electronics' THEN stock > 0 ELSE price < 100 END = TRUE correctly uses WHEN condition THEN boolean expression ELSE boolean expression END = TRUE to filter rows.

3. Step 3: Identify why others are wrong

   Options A, B, and D miss the END keyword for the CASE expression, causing syntax errors.

4. Final Answer:

   WHERE CASE WHEN category = 'Electronics' THEN stock > 0 ELSE price < 100 END = TRUE -> Option A

5. Quick Check:

   CASE returns boolean, compare with TRUE in WHERE [OK]

Hint: Compare CASE result to TRUE in WHERE for boolean filters [OK]

Common Mistakes:

• Missing END keyword
• Using CASE with simple WHEN without condition
• Returning non-boolean values in CASE