Consider the following schema: Suppliers(sid: integer, sname: string, address: s
ID: 3794443 • Letter: C
Question
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.
Explanation / Answer
a) It returns the distinct part names which are charged by the suppliers.
b) SELECTS S.sname FROM Suppliers S where S.sid = (SELECT C.sid FROM Catalog C WHERE NOT EXISTS (SELECT P.pid FROM Parts P WHERE NOT EXISTS (SELECT C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid)))
c) The first subquery will return all the partid whose color is red from parts table. The Second subquery will return the partid from catalog table which are charged by the suppliers. Here 'Except' clause is used which returns the rows from the first select statement which are not in second select statement. So the output of the subquery are the partid's from parts table whose color is red and which are not charged by the supplier. The main query will return suppliers name who supply parts other than the red parts which are charged.
d) SELECT p.pname from parts p where p.pid = (SELECT c.pid from catalog c,suppliers s where c.sid=s.sid and s.sname='Acne Widget Suppliers')