You want to find all suppliers who have supplied products costing more than 500. Given tables:
hard📝 Application Q8 of 15
SQL - Subqueries
You want to find all suppliers who have supplied products costing more than 500. Given tables: Suppliers(id, name) Products(id, supplier_id, price) Which query correctly uses EXISTS to achieve this?
ASELECT name FROM Suppliers s WHERE EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.id AND p.price > 500);
BSELECT name FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.id AND p.price > 500);
CSELECT name FROM Suppliers s WHERE EXISTS (SELECT * FROM Products p WHERE p.price > 500);
DSELECT name FROM Suppliers s WHERE EXISTS (SELECT 1 FROM Products p WHERE p.price > 500);
Step-by-Step Solution
Solution:
Step 1: Understand the requirement
We want suppliers who have at least one product priced over 500.
Step 2: Analyze each option
SELECT name FROM Suppliers s WHERE EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.id AND p.price > 500); correctly correlates Products with Suppliers and filters by price. SELECT name FROM Suppliers s WHERE NOT EXISTS (SELECT 1 FROM Products p WHERE p.supplier_id = s.id AND p.price > 500); uses NOT EXISTS, which is opposite. SELECT name FROM Suppliers s WHERE EXISTS (SELECT * FROM Products p WHERE p.price > 500); lacks correlation to supplier. SELECT name FROM Suppliers s WHERE EXISTS (SELECT 1 FROM Products p WHERE p.price > 500); also lacks correlation, so it checks if any product is over 500 regardless of supplier.
Final Answer:
Option A -> Option A
Quick Check:
EXISTS must correlate subquery to outer query [OK]
Quick Trick:EXISTS subquery must correlate to outer query [OK]
Common Mistakes:
MISTAKES
Missing correlation between tables
Using NOT EXISTS instead of EXISTS
Ignoring price condition in subquery
Master "Subqueries" in SQL
9 interactive learning modes - each teaches the same concept differently
