Consider the following schema (Database tables): a) Suppliers ( sid: integer , s
ID: 3575846 • Letter: C
Question
Consider the following schema (Database tables):
a) Suppliers (sid: integer, sname: string, address: string)
The "sid" is supplier's Id and "sname" is supplier's name
b) Parts (pid: integer, pname: string, color: string)
"pid" is part Id and "pname" means part name
c) Catalog (sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers.
Write the following queries in SQL:
1. Find the pnames of parts for which there is some supplier.
2. Find the snames of suppliers who supply every part.
3. Find the snames of suppliers who supply every red part.
4. Find the pnames of parts supplied by "Awesome" Suppliers and no one else.
5. Find the sids of suppliers who charge more for some part than the average cost of that part (averaged over all the suppliers who supply that part).
6. For each part, find the sname of the supplier who charges the most for that part.
7. Find the sids of suppliers who supply only red parts.
8. Find the sids of suppliers who supply a red part and a green part.
9. Find the sids of suppliers who supply a red part or a green part.
10. For every supplier that only supplies green parts, print the name of the supplier and the total number of parts that she supplies.
Save the queries in a text file having ".sql" extension. Prefix each question no with "//" as shown in example below
Example:
// 1
Select * from employee;
Explanation / Answer
// 1 The pnames of parts for which there is some supplier is
SELECT DISTINCT P.pname
FROM Parts P, Catalog C
WHERE P.pid = C.pid
// 2 The snames of suppliers who supply every part is
SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS (( SELECT P.pid
FROM Parts P )
EXCEPT
( SELECT C.pid
FROM Catalog C
WHERE C.sid = S.sid ))
// 3 The snames of suppliers who supply every red part is
SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS (( SELECT P.pid
FROM Parts P
WHERE P.color = 'Red')
EXCEPT
( SELECT C.pid
FROM Catalog C ,PARTS P)
WHERE C.sid = S.sid AND
C.pid = P.pid AND P.color = 'Red'))
// 4 The pnames of parts supplied by "Awesome" Suppliers and no one else is
SELECT P.pname
FROM Parts P, Catalog C, Suppliers S
WHERE P.pid = C.pid AND C.sid = S.sid
AND S.sname = ‘Awesome Suppliers’
AND NOT EXISTS ( SELECT *
FROM Catalog C1, Suppliers S1
WHERE P.pid = C1.pid AND C1.sid = S1.sid AND
S1.sname <> ‘Awesome Suppliers’)
// 5 The sids of suppliers who charge more for some part than the average cost of that part is
SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > ( SELECT AVG (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid )
// 6 For each part, the sname of the supplier who charges the most for that part is
SELECT P.pid, S.sname
FROM Parts P, Suppliers S, Catalog C
WHERE C.pid = P.pid
AND C.sid = S.sid
AND C.cost = (SELECT MAX (C1.cost)
FROM Catalog C1
WHERE C1.pid = P.pid)
// 7 The sids of suppliers who supply only red parts is
SELECT DISTINCT C.sid
FROM Catalog C
WHERE NOT EXISTS ( SELECT *
FROM Parts P
WHERE P.pid = C.pid AND P.color <> ‘Red’)
// 8 The sids of suppliers who supply a red part and a green part is
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid
AND P.color = ‘Red’
INTERSECT
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’
// 9 The sids of suppliers who supply a red part or a green part is
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid
AND P.color = ‘Red’
UNION
SELECT DISTINCT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color = ‘Green’
// 10 For every supplier that only supplies green parts, the name of the supplier and the total number of parts that she supplies is
SELECT S.sname, COUNT(*) as PartCount
FROM Suppliers S, Parts P, Catalog C
WHERE P.pid = C.pid AND C.sid = S.sid
GROUP BY S.sname, S.sid
HAVING EVERY (P.color=’Green’)