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’;

Go back to SQL Exercise home page.