TOPICS (Click to Navigate)
Thursday, 28 January 2016
SQL Exercise 6 - Solved
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”.
WHERE city = “Chandigargh”;
b) Find the name of the manufacturer that supplies the largest quantity of any product.
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’;
SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...
Advanced concepts in DBMS Advanced Database Topics (Click on the links to navigate) Advanced Concepts in D...
Set of solved exercises in Normalization / Normalization Solved Examples / How to find candidate keys, and primary keys in database? /...
Query Processing in DBMS / Steps involved in Query Processing in DBMS / How is a query gets processed in a Database Management System? / Q...