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.