Consider the following schema Supplierssid integer sname str

Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

The Catalog relation lists the prices charged for parts by Suppliers. Write the following
queries in SQL:

a. The following query returns:

SELECT DISTINCT P.pname
FROM Parts P, Catalog C
WHERE P.pid = C.pid

b. Find the snames of suppliers who supply every part.

c. The following query returns:

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

d. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

e. The following query returns:
SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > ( SELECT AVG (C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid )

f. The following query returns:
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)

g. Find the sids of suppliers who supply only red parts.

h. The following query returns:
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’

i. Find the sids of suppliers who supply a red part or a green part.

j. The following query returns:
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’)

k. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.

Solution

a. distinct pnames of parts for which there is some supplier.

b.select s.sname
from suppliers s
where not exists
((select p.pid from parts p)
minus --EXCEPT
(select c.pid
from catalog c
where c.sid=s.sid));

c.sname of suppliers that supply every red part

d. select p.pid,p.pname from parts p, suppliers s, catalog c where
p.pid=c.pid and s.sid=c.sid and s.sname like \'Acme%\'
minus
(select p.pid,p.pname from parts p, suppliers s, catalog c where
p.pid=c.pid and s.sid=c.sid and s.sname not like \'Acme%\');
OR this query also works
SELECT P.pname
FROM Parts P, Catalog C, Suppliers S
WHERE P.pid = C.pid AND C.sid = S.sid
AND S.sname = \'Acme Widget Suppliers\'
AND NOT EXISTS ( SELECT *
FROM Catalog C1, Suppliers S1
WHERE P.pid = C1.pid AND C1.sid = S1.sid AND
S1.sname <> \'Acme Widget Suppliers\' );

e. sids of suppliers who charge more for some
part than the average cost of that part.

f.For each part, the sname of suppliers who charge
the most for that part.

g. SELECT DISTINCT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT * FROM Parts P, Catalog C1 WHERE P.pid = C.pid AND P.color <> \'red\' AND C.sid = C1.sid)

i.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\'
j.for every supplier who supplies only green parts, print sname and number of parts supplied.
k.
select s.sname, p.pname, c.cost
from catalog c, suppliers s, parts p
where c.sid=s.sid and c.pid=p.pid and c.cost=
(select max(c2.cost) from catalog c2
where c2.sid=c.sid)
and c.sid in
-- See 5.2.8: sid of suppliers who supply red parts and green parts.
(select s.sid from suppliers s, catalog c, parts p
where
s.sid=c.sid and c.pid=p.pid and p.color=\'Red\'
intersect
select s.sid from suppliers s, catalog c, parts p
where
s.sid=c.sid and c.pid=p.pid and p.color=\'Green\');