Thursday, January 28, 2016

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,
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 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,, 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

Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

data recovery