Question

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 bySuppliers. Write the following queries in SQL:
1) Find the pnames of parts supplied by Acme Widget Suppliers andno one else.2) Find the sids of suppliers who charge more for some partthan the average cost of that part (averaged over all the supplierswho supply that part).3) For each part, find the sname of the supplier who chargesthe most for that part.4) Find the sids of suppliers who supply only red parts.

Answer 1

(a) Find the names of suppliers who supply some yellow part.
RA: sname(Suppliers on (color=0yellow0(Parts) on Catalog))
SQL:
SELECT S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’yellow’

(b) Find the sids of suppliers who supply some green part but not ared part.
RA: sid(Suppliers on (color=0green0(Parts) on Catalog))−
sid(Suppliers on (color=0red0(Parts) on Catalog))
SQL:
SELECT S.sid
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’green’
EXCEPT
SELECT T.sid
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’

(c) Find the sids and snames of suppliers who supply a’bolt’ whose price is under 100
dollars or whose color is red.
There is ambiguity here too. Some of you may interpret the questionto mean a bolt
whose price is under 100 dollars or a bolt whose color is red. Someof you may interpret
it to mean a bolt whose price is under 100 dollars or a red part. Ihave given points to
both these versions.
RA: sid,sname(Suppliers on((pname=0bolt0^cost<100)_(color=0red0)(Parts onCatalog)))
RA: sid,sname(Suppliers on((pname=0bolt0^(cost<100_color=0red0))(Parts onCatalog)))
SQL:
SELECT S.sid, S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND C.cost < 100
UNION
SELECT T.sid, T.sname
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’

SELECT S.sid, S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND C.cost < 100
UNION
SELECT T.sid, T.sname
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.pname =’bolt’ AND P.color = ’red’

(d) Find the sids of suppliers who supply all parts.
RA: sid((sid,pid(Catalog))/(pid(Parts))
SQL:
SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (( SELECT P.pid
FROM Parts P )
EXCEPT
(SELECT C1.pid
FROM Catalog C1
WHERE C1.sid = C.sid ))

(e) Find pids of parts supplied by at least two differentsuppliers
RA: (CatPairs(1 !sid1, 2 !pid1, 3 !cost1, 4 !sid2, 5 !pid2, 6!cost2), Catalog×
Catalog)
pid1((pid1=pid2)^(sid16=sid2)CatPairs)
SQL:
SELECT P.pid
FROM Catalog P, Catalog C
WHERE P.pid = C.pid AND P.sid <> C.sid

(f) Find the names of suppliers who supply some brown part.
RA: sname(Suppliers on (color=0brown0(Parts) on Catalog))
SQL:
SELECT S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’brown’
(g) Find the sids of suppliers who supply some yellow part but nota red part.
RA: sid(Suppliers on (color=0yellow0(Parts) on Catalog))−
sid(Suppliers on (color=0red0(Parts) on Catalog))
SQL:
SELECT S.sid
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.color =’yellow’
EXCEPT
SELECT T.sid
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’red’


(h) Find the sids and snames of suppliers who supply a’nut’ whose price is under 10 dollars
or whose color is pink.
There is ambiguity here too. Some of you interpret the question asa nut whose price is
under 10 dollars or a nut whose color is pink. Some of youinterpret it as a nut whose
price is under 10 dollars and a part whose color is pink. Bothversions are treated as
correct answers.
RA: sid,sname(Suppliers on((pname=0nut0^cost<10)_(color=0pink0)(Parts onCatalog)))
RA: sid,sname(Suppliers on((pname=0nut0^(cost<10_color=0pink0))(Parts onCatalog)))
SQL:
SELECT S.sid, S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND C.cost < 10
UNION
SELECT T.sid, T.sname
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.color =’pink’
SELECT S.sid, S.sname
FROM Suppliers S, Parts P, Catalog C
WHERE S.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND C.cost < 10
UNION
SELECT T.sid, T.sname
FROM Suppliers T, Parts P, Catalog C
WHERE T.sid = C.sid AND P.pid = C.pid AND P.pname =’nut’ AND P.color = ’pink’