You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables:
hard📝 Application Q15 of 15
PostgreSQL - Subqueries in PostgreSQL
You want to find all customers who have placed orders for products in the 'Electronics' category. Given tables: customers(id, name) orders(id, customer_id, product_id) products(id, name, category) Which query correctly uses a subquery with IN to get these customers?
ASELECT name FROM customers WHERE id IN (SELECT id FROM products WHERE category = 'Electronics');
BSELECT name FROM customers WHERE id = IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));
CSELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics'));
DSELECT name FROM customers WHERE id IN (SELECT product_id FROM orders WHERE customer_id IN (SELECT id FROM products WHERE category = 'Electronics'));
Step-by-Step Solution
Solution:
Step 1: Identify the correct subquery for products in 'Electronics'
The innermost subquery selects product IDs where category is 'Electronics'.
Step 2: Use these product IDs to find matching orders
The middle subquery selects customer IDs from orders with those product IDs.
Step 3: Select customer names whose IDs match those customer IDs
The main query selects customer names where their ID is in the list from the orders subquery.
Final Answer:
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE category = 'Electronics')); -> Option C
Quick Check:
Nested IN subqueries filter customers by product category [OK]
Quick Trick:Use nested IN subqueries to filter step-by-step [OK]
Common Mistakes:
Using = IN instead of IN
Mixing up customer_id and product_id columns
Selecting wrong columns in subqueries
Master "Subqueries in PostgreSQL" in PostgreSQL
9 interactive learning modes - each teaches the same concept differently
