How can you combine CASE in WHERE clause with AND to filter Orders where if priority is 'High', amount > 500 and status is 'Open', else amount <= 500 or status is 'Closed'?
hard📝 Application Q9 of 15
SQL - CASE Expressions
How can you combine CASE in WHERE clause with AND to filter Orders where if priority is 'High', amount > 500 and status is 'Open', else amount <= 500 or status is 'Closed'?
ASELECT * FROM Orders WHERE CASE WHEN priority = 'High' THEN amount > 500 ELSE amount <= 500 END AND CASE WHEN priority = 'High' THEN status = 'Open' ELSE status = 'Closed' END;
BSELECT * FROM Orders WHERE (CASE WHEN priority = 'High' THEN amount > 500 AND status = 'Open' ELSE amount <= 500 OR status = 'Closed' END);
CSELECT * FROM Orders WHERE CASE WHEN priority = 'High' THEN amount > 500 OR status = 'Open' ELSE amount <= 500 AND status = 'Closed' END;
DSELECT * FROM Orders WHERE CASE priority = 'High' THEN amount > 500 AND status = 'Open' ELSE amount <= 500 OR status = 'Closed' END;
Step-by-Step Solution
Solution:
Step 1: Understand logical conditions
For priority 'High', both amount > 500 AND status = 'Open' must be true.
Step 2: Check SELECT * FROM Orders WHERE CASE WHEN priority = 'High' THEN amount > 500 ELSE amount <= 500 END AND CASE WHEN priority = 'High' THEN status = 'Open' ELSE status = 'Closed' END; structure
SELECT * FROM Orders WHERE CASE WHEN priority = 'High' THEN amount > 500 ELSE amount <= 500 END AND CASE WHEN priority = 'High' THEN status = 'Open' ELSE status = 'Closed' END; uses two CASE expressions combined with AND, correctly applying conditions.
Step 3: Identify errors in other options
B and D misuse AND/OR inside single CASE; C swaps AND/OR incorrectly.
Final Answer:
SELECT * FROM Orders WHERE CASE WHEN priority = 'High' THEN amount > 500 ELSE amount <= 500 END AND CASE WHEN priority = 'High' THEN status = 'Open' ELSE status = 'Closed' END; -> Option A
Quick Check:
Use multiple CASE for complex AND/OR logic [OK]
Quick Trick:Use multiple CASE for combining AND/OR in WHERE [OK]
Common Mistakes:
Mixing AND and OR inside one CASE incorrectly
Omitting WHEN keyword
Using CASE without END
Master "CASE Expressions" in SQL
9 interactive learning modes - each teaches the same concept differently
