1 / 11

Exercises

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

meli
Download Presentation

Exercises

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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 C1.sid, C2.sid FROM Catalog C1, Catalog C2 WHERE C1.pid = C2.pid AND C1.sid = C2.sid AND C1.cost > C2.cost

  3. 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 )

  4. 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 C1.sid FROM Catalog C1 WHERE C1.sid = C.sid AND C1.pid = P.pid))

  5. 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 C2.cost FROM Catalog C2, Suppliers S2 WHERE S2.sname = ‘Yosemite Sham’ AND C2.sid = S2.sid)

  6. 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 C1, Suppliers S1 WHERE P.pid = C1.pid AND C1.sid = S1.sid AND S1.sname <> ‘Acme Widget Suppliers’ )

  7. 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 (C1.cost) FROM Catalog C1 WHERE C1.pid = C.pid )

  8. 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 (C1.cost) FROM Catalog C1 WHERE C1.pid = P.pid)

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

  10. 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 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 COUNT (*) > 1

  11. 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 MaxCost FROM Suppliers S, Parts P, Catalog C WHERE P.pid = C.pid AND C.sid = S.sid GROUP BY S.sname, S.sid

More Related