1 / 22

COP-5725 Practice Exercises

COP-5725 Practice Exercises. Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation). M. Amanda Crick. Exercise 5.2. Problem Consider the following schema Suppliers( sid: integer , sname: string, address: string) Parts( pid: integer , pname: string, color: string)

jileen
Download Presentation

COP-5725 Practice 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. COP-5725Practice Exercises Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick

  2. Exercise 5.2 Problem • 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:

  3. Exercise 5.2 Problem 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 for which there is some supplier.

  4. Exercise 5.2 Solution for (1) SELECT DISTINCT P.pname FROM Parts P, Catalog C WHERE P.pid = C.pid

  5. Exercise 5.2 Problem 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.

  6. Exercise 5.2 Solution for (4) • 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. Exercise 5.2 Problem 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.

  8. Exercise 5.2 Solution for (6) • 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 >= ALL (SELECT C1.cost) • FROM Catalog C1 • WHERE C1.pid = P.pid)

  9. Exercise 5.2 Problem 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 a red part and a green part.

  10. Exercise 5.2 Solution for (8) 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’

  11. Exercise 5.2 Problem 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 a red part or a green part.

  12. Exercise 5.2 Solution for (9) 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’

  13. Exercise 5.4 Problem • Consider the following relational schema. An employee can work in more than one department; the pct_time field of the Works relation shows the percentage of time that a given employee works in a given department. • Emp(eid: integer, ename: string, age: integer, salary: real) • Works(eid: integer, did: integer, pct_time: integer) • Dept(did: integer, dname: string, budget: real, managerid: integer) • Write the following queries in SQL:

  14. Exercise 5.4 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) • Print the names and ages of each employee who works in both the Hardware department and the Software department.

  15. Exercise 5.4 Solution for (1) SELECT E.ename, E.age FROM Emp E, Works W1, Works W2, Dept D1, Dept D2 WHERE E.eid = W1.eid AND W1.did = D1.did AND D1.dname = ‘Hardware’ AND E.eid = W2.eid AND W2.did = D2.did AND D2.dname = ‘Software’

  16. Exercise 5.4 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) • Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.

  17. Exercise 5.4 Solution for (3) • SELECT E.ename • FROM Emp E • WHERE E.salary > ALL (SELECTD.budget • FROM Dept D, Works W • WHERE E.eid = W.eid AND D.did = W.did)

  18. Exercise 5.4 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) • Find the enames of managers who manage the departments with the largest budgets.

  19. Exercise 5.4 Solution for (5) • SELECT E.ename • FROM Emp E • WHERE E.eid IN (SELECT D.managerid • FROM Dept D • WHERE D.budget >= ALL (SELECT D2.budget • FROM Dept D2))

  20. Exercise 5.4 Problem Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct_time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) • Find the enames of managers who manage the departments with the largest budgets.

  21. Exercise 5.4 Solution for (8) SELECT M.ename FROM Emp M WHERE M.sid IN (SELECT M2.sid FROM Emp M2, Dept D WHERE M2.sid = D.managerid AND D.budget < 5000000 EXCEPT SELECT M3.sid FROM Emp M3, Dept D2 WHERE M3.sid = D2.managerid AND D2.budget <= 5000000)

  22. This is the end of the lecture! I hope you enjoyed it.

More Related