SQL Exercise 6 - Solved



SQL Exercises for Beginners / Simple SQL Exercises with Answers / SQL Exercises for simple Table creation and SELECT queries / Solved SQL exercises / SQL solved exercises with subqueries



Consider the following relations; primary key attributes are underlined.

Product(productCode, productName, productCategory, productDescription)
Manufacturer(manuCode, manufacturerName, city, Phone)
Supply(manuCode, productCode, storeID, wholesaleUnitPrice, quantity)
Store(storeID, storeName, phoneNumber , city)

Write the SQL queries to answer the following questions. You can assume appropriate data types for the columns. Note: do not use the aggregation functions in SQL for these questions.

a) Find names of all stores that are in the city of “Chandigargh”.

SELECT storeName
FROM Store
WHERE city = “Chandigargh”;

b) Find the name of the manufacturer that supplies the largest quantity of any product.

SELECT manufacturerName
FROM supply
WHERE qty >= ALL (SELECT quantity FROM supply);

c) Find the names and the cities of all manufactures that supply any product of more than 100 units whose wholesale unit price is greater than 50.

SELECT m.manufacturerName, m.city
FROM Manufacturer m, Supply s
WHERE m.manuCode = s.manuCode
AND quantity > 100
AND wholesaleUnitPrice > 50;

d) Find the names of store-manufacturer pairs where the store and the manufacturer in each pair is located in the same city and there is a supply record of the manufacturer whose total cost (i.e. unit price multiplied by quantity) is greater than 10,000.

SELECT s.storeName, m.manufacturerName
FROM Store s, Manufacturer m, Supply sp
WHERE m.manuCode = sp.manuCode
AND s.storeID = sp.storeID
AND m.city = s.city
AND sp.wholesaleUnitPrice*sp.quantity > 10000;

e) Find the store name, city, and product name of all the products whose wholesale unit price is less than 100 and the city is not ‘Chennai’.

SELECT s.storeName, s.city, p.productName
FROM Store s, Supply sp, Product p
WHERE s.storeID = sp.storeID
AND p.productCode = sp.productCode
AND wholesaleUnitPrice < 100
AND city NOT LIKE ‘Chennai’;




 

No comments:

Post a Comment