Refer to the tables. Which products are selected by the query below?
The Correct Answer and Explanation is:
✅ Correct Answer:
Onesies set, Sunsuit, Romper
📘 Explanation:
The SQL query provided is:
SELECT ProductName
FROM Product
WHERE SupplierId IN (
SELECT SupplierId
FROM Supplier
WHERE CountryId = 2
);
🔍 Step-by-step Breakdown:
1. Understand the Subquery:
The subquery:
SELECT SupplierId FROM Supplier WHERE CountryId = 2
will return all SupplierIds where the CountryId is 2.
Looking at the Supplier table, the suppliers from CountryId = 2 are:
- SupplierID = 2 (Oshkosh Bgosh)
- SupplierID = 4 (SugarPlum)
- SupplierID = 5 (Rosebuds for Girls)
- SupplierID = 7 (Periwinkle)
So, the result of the subquery is:
2, 4, 5, 7
2. Apply the Result to the Main Query:
Now, look at the Product table and select rows where SupplierId is IN (2, 4, 5, 7).
From the Product table:
| ProductName | SupplierId |
|---|---|
| Onesies set | 2 |
| Sunsuit | 2 |
| Romper | 4 |
| Pajama set | 3 ❌ |
| Shorts set | 3 ❌ |
So, only the following products have SupplierId in (2, 4, 5, 7):
- Onesies set (SupplierId 2)
- Sunsuit (SupplierId 2)
- Romper (SupplierId 4)
3. Final Answer:
✅ Onesies set, Sunsuit, Romper
📌 Summary:
The query filters all products whose supplier is located in a country with CountryId = 2. By checking the supplier IDs that meet this criterion and matching them to the products, we accurately retrieve the names of the products supplied by those vendors. This is a classic example of using a subquery to filter a parent table using a foreign key relationship.
