Exercises Supplierssid integer sname string address string Partspid

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find the names of suppliers who supply some red part. SELECT S. sname FROM Suppliers S, Parts P, Catalog C WHERE P. color=’red’ AND C. pid=P. pid AND C. sid=S. sid

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find pairs of sids such that the supplier with the first sid charges more for some part than the supplier with the second sid. SELECT C 1. sid, C 2. sid FROM Catalog C 1, Catalog C 2 WHERE C 1. pid = C 2. pid AND C 1. sid = C 2. sid AND C 1. cost > C 2. cost

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find the sids of suppliers who supply some red part or are at 221 Packer Street. SELECT S. sid FROM Suppliers S WHERE S. address = ‘ 221 Packer street’ OR S. sid IN (SELECT C. sid FROM Parts P, Catalog C WHERE P. color=’red’ AND P. pid = C. pid )

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find the sids of suppliers who supply every part. SELECT C. sid FROM Catalog C WHERE NOT EXISTS (SELECT P. pid FROM Parts P WHERE NOT EXISTS (SELECT C 1. sid FROM Catalog C 1 WHERE C 1. sid = C. sid AND C 1. pid = P. pid))

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham. SELECT C. pid FROM Catalog C, Suppliers S WHERE S. sname = ‘Yosemite Sham’ AND C. sid = S. sid AND C. cost ≥ ALL (SELECT C 2. cost FROM Catalog C 2, Suppliers S 2 WHERE S 2. sname = ‘Yosemite Sham’ AND C 2. sid = S 2. sid)

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • Find the pnames of parts supplied by Acme Widget Suppliers and no one else. 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 C 1, Suppliers S 1 WHERE P. pid = C 1. pid AND C 1. sid = S 1. sid AND S 1. sname <> ‘Acme Widget Suppliers’ )

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • 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). SELECT DISTINCT C. sid FROM Catalog C WHERE C. cost > ( SELECT AVG (C 1. cost) FROM Catalog C 1 WHERE C 1. pid = C. pid )

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • 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 (C 1. cost) FROM Catalog C 1 WHERE C 1. pid = P. pid)

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • 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’ )

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • For every supplier that supplies more than 1 part, print the name of the supplier and the total number of parts that she supplies. SELECT S. sname, COUNT(*) as Part. Count FROM Suppliers S, Parts P, Catalog C WHERE P. pid = C. pid AND C. sid = S. sid GROUP BY S. sname, S. sid HAVING COUNT (*) > 1

Exercises Suppliers(sid: integer, sname: string, address: string) Parts(pid: integer, pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real) • For every supplier, print the name and price of the most expensive part that she supplies. SELECT S. sname, MAX(C. cost) as Max. Cost FROM Suppliers S, Parts P, Catalog C WHERE P. pid = C. pid AND C. sid = S. sid GROUP BY S. sname, S. sid