CMPSCI 445 — SQL Practice Problems
Consider the following schema: •
Suppliers(sid: integer, sname: varchar(50), address: varchar(60))
•
Parts(pid: integer, pname: varchar(50), color: varchar(20))
•
Catalog(sid: integer, pid: integer, cost: real)
Write SQL expressions for each of the following queries. (a) Find the distinct distinct pnames of all parts. SELECT SELECT DISTINCT DISTINCT pname pname FROM Parts; Parts;
(b) Find the distinct distinct pnames of parts for which there is some supplier. supplier. SELECT SELECT DISTINCT DISTINCT P.pname P.pname FROM FROM Parts Parts P, Catal Catalog og C WHERE WHERE P.pid P.pid = C.pid C.pid; ;
(c) Find the distinct distinct pnames of all parts sold (by some supplier) supplier) for less than 5.50 SELECT SELECT DISTINCT DISTINCT P.pname P.pname FROM FROM Parts Parts P, Catal Catalog og C WHERE WHERE P.pid P.pid = C.pid C.pid AND C.cost<5.50; C.cost<5.50;
(d) Find the distinct snames of all suppliers suppliers who sell ’Acme Widget Widget Washers’. Washers’. SELECT SELECT DISTINCT DISTINCT S.sname S.sname FROM FROM Suppl Supplie iers rs S, Catal Catalog og C, Parts Parts P WHERE WHERE S.sid S.sid = C.sid C.sid AND C.pid=P.pid C.pid=P.pid AND P.pname P.pname=’Ac =’Acme me Widget Widget Washer’; Washer’;
(e) Find the sids of suppliers suppliers who supply supply a red part and a green part. part.
1
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’;
(f) Find the sids of suppliers who supply a red part or a green part. 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’;
(g) Find the price of the least expensive red part. SELECT MIN(C.cost) FROM Catalog C, Parts P WHERE C.pid = P.pid AND P.color = ’Red’;
(h) For every supplier, print the name of the supplier and the total number of parts that he or she supplies. SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Catalog C WHERE C.sid = S.sid GROUP BY S.sname, S.sid
(i) For every supplier who supplies at least 1 red part, print the name of the supplier and the total number of red parts that he or she supplies. SELECT S.sname, COUNT(*) as PartCount FROM Suppliers S, Catalog C, Parts P WHERE C.sid = S.sid and P.pid = C.pid and P.color = ’Red’ GROUP BY S.sname, S.sid
(j) Find the sids of suppliers who supply only red parts. SELECT DISTINCT C.sid FROM Catalog C WHERE NOT EXISTS ( SELECT * FROM Parts P WHERE P.pid = C.pid AND P.color <> Red )
2
(k) For each part, find the sname of the supplier who charges the most for that part. 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 )
(l) Find the snames of suppliers who supply every part. 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 ) )
(m) Find the snames of suppliers who supply every red part. 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 ) )
3
