You want to find all customers who placed orders with a total amount greater than 1000. Given tables:
hard📝 Application Q8 of 15
SQL - Subqueries
You want to find all customers who placed orders with a total amount greater than 1000. Given tables: customers(customer_id, name) orders(order_id, customer_id, total_amount) Which query correctly uses a subquery in the WHERE clause to achieve this?
ASELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 1000);
BSELECT name FROM customers WHERE customer_id = (SELECT customer_id FROM orders WHERE total_amount > 1000);
CSELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE total_amount > 1000);
DSELECT name FROM customers WHERE customer_id > (SELECT customer_id FROM orders WHERE total_amount > 1000);
Step-by-Step Solution
Solution:
Step 1: Understand requirement
We want customers who have orders with total_amount > 1000.
Step 2: Check subquery usage
Subquery returns customer_ids with orders > 1000. Using IN matches customers with any such order.
Step 3: Eliminate incorrect options
SELECT name FROM customers WHERE customer_id = (SELECT customer_id FROM orders WHERE total_amount > 1000); uses '=', which fails if multiple customers qualify. SELECT name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders WHERE total_amount > 1000); uses NOT IN, opposite of requirement. SELECT name FROM customers WHERE customer_id > (SELECT customer_id FROM orders WHERE total_amount > 1000); uses '>' which is invalid for this logic.
Final Answer:
SELECT name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders WHERE total_amount > 1000); -> Option A
Quick Check:
Use IN for multiple matching subquery results [OK]
Quick Trick:Use IN for subqueries returning multiple values in WHERE [OK]
Common Mistakes:
MISTAKES
Using '=' when subquery returns multiple rows
Using NOT IN instead of IN
Using comparison operators like '>' incorrectly
Master "Subqueries" in SQL
9 interactive learning modes - each teaches the same concept differently
