You have tables: Products(product_id, category_id, price) Categories(category_id, category_name) Which query correctly finds product_id of products in categories 'Furniture' or 'Appliances'?
ASELECT product_id FROM Products WHERE category_id IN (SELECT category_id FROM Categories WHERE category_name IN ('Furniture', 'Appliances'));
BSELECT product_id FROM Products WHERE category_id = (SELECT category_id FROM Categories WHERE category_name = 'Furniture' OR category_name = 'Appliances');
CSELECT product_id FROM Products WHERE category_id IN (SELECT category_name FROM Categories WHERE category_name = 'Furniture' OR category_name = 'Appliances');
DSELECT product_id FROM Products WHERE category_id IN (SELECT category_id FROM Categories WHERE category_name = 'Furniture' AND category_name = 'Appliances');
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
Find product_ids where category_id matches categories named 'Furniture' or 'Appliances'.
Step 2: Analyze options
SELECT product_id FROM Products WHERE category_id IN (SELECT category_id FROM Categories WHERE category_name IN ('Furniture', 'Appliances')); correctly uses IN with a subquery returning category_ids for both categories.
Step 3: Identify errors in other options
SELECT product_id FROM Products WHERE category_id = (SELECT category_id FROM Categories WHERE category_name = 'Furniture' OR category_name = 'Appliances'); uses = with subquery returning multiple rows (error). SELECT product_id FROM Products WHERE category_id IN (SELECT category_name FROM Categories WHERE category_name = 'Furniture' OR category_name = 'Appliances'); compares category_id with category_name (wrong column). SELECT product_id FROM Products WHERE category_id IN (SELECT category_id FROM Categories WHERE category_name = 'Furniture' AND category_name = 'Appliances'); uses AND which cannot be true simultaneously.
Final Answer:
Option A -> Option A
Quick Check:
Use IN with subquery returning multiple matching category_ids [OK]
Quick Trick:Use IN with subquery returning multiple values for OR conditions [OK]
Common Mistakes:
MISTAKES
Using = instead of IN for multiple values
Comparing incompatible columns
Using AND instead of OR in subquery
Master "Subqueries" in SQL
9 interactive learning modes - each teaches the same concept differently
