You want to classify products based on Price and Stock using nested CASE. If Price > 100 and Stock > 50, label 'Premium-Available'. If Price > 100 and Stock ≤ 50, label 'Premium-Limited'. Otherwise, label 'Standard'. Which SQL snippet correctly implements this?
ACASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END
BCASE WHEN Price > 100 AND Stock > 50 THEN 'Premium-Available' WHEN Price > 100 AND Stock <= 50 THEN 'Premium-Limited' ELSE 'Standard' END
CCASE WHEN Price > 100 THEN 'Premium' ELSE CASE WHEN Stock > 50 THEN 'Available' ELSE 'Limited' END END
DCASE WHEN Price > 100 THEN CASE Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END
Step-by-Step Solution
Solution:
Step 1: Understand the nested conditions
First check if Price > 100, then inside check Stock > 50 or not.
Step 2: Match correct syntax and logic
CASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END correctly nests CASE inside THEN with proper WHEN, THEN, ELSE, and END keywords matching the problem.
Final Answer:
CASE WHEN Price > 100 THEN CASE WHEN Stock > 50 THEN 'Premium-Available' ELSE 'Premium-Limited' END ELSE 'Standard' END -> Option A
Quick Check:
Nested CASE matches Price then Stock checks [OK]
Quick Trick:Check outer condition first, nest inner CASE for second condition [OK]
Common Mistakes:
Using AND instead of nested CASE
Missing WHEN in inner CASE
Incorrect ELSE placement
Master "CASE Expressions" in SQL
9 interactive learning modes - each teaches the same concept differently
