cop-5725 practice exercises chapter 5: queries, triggers, constraints (up to, but not including...
TRANSCRIPT
![Page 1: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/1.jpg)
COP-5725PRACTICE EXERCISES
Chapter 5: Queries, Triggers, Constraints
(up to, but not including aggregation)
M. Amanda Crick
![Page 2: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/2.jpg)
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:
![Page 3: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/3.jpg)
Exercise 5.2
ProblemSuppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)
1. Find the pnames of parts for which there is some supplier.
![Page 4: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/4.jpg)
Exercise 5.2
Solution for (1)
SELECT DISTINCT P.pnameFROM Parts P, Catalog CWHERE P.pid = C.pid
![Page 5: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/5.jpg)
Exercise 5.2
ProblemSuppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)
4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
![Page 6: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/6.jpg)
Exercise 5.2
Solution for (4)
SELECT P.pnameFROM Parts P, Catalog C, Suppliers SWHERE P.pid = C.pid AND C.sid = S.sidAND 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’ )
![Page 7: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/7.jpg)
Exercise 5.2
ProblemSuppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)
6. For each part, find the sname of the supplier who charges the most for that part.
![Page 8: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/8.jpg)
Exercise 5.2
Solution for (6)
SELECT P.pid, S.snameFROM Parts P, Suppliers S, Catalog CWHERE C.pid = P.pidAND C.sid = S.sidAND C.cost >= ALL (SELECT C1.cost)
FROM Catalog C1 WHERE C1.pid = P.pid)
![Page 9: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/9.jpg)
Exercise 5.2
ProblemSuppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)
8. Find the sids of suppliers who supply a red part and a green part.
![Page 10: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/10.jpg)
Exercise 5.2
Solution for (8)
SELECT DISTINCT C.sidFROM Catalog C, Parts PWHERE C.pid = P.pid AND P.color = ‘Red’INTERSECTSELECT DISTINCT C1.sidFROM Catalog C1, Parts P1WHERE C1.pid = P1.pid AND P1.color = ‘Green’
![Page 11: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/11.jpg)
Exercise 5.2
ProblemSuppliers(sid: integer, sname: string, address: string)Parts(pid: integer, pname: string, color: string)Catalog(sid: integer, pid: integer, cost: real)
9. Find the sids of suppliers who supply a red part or a green part.
![Page 12: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/12.jpg)
Exercise 5.2
Solution for (9)
SELECT DISTINCT C.sidFROM Catalog C, Parts PWHERE C.pid = P.pid AND P.color = ‘Red’UNIONSELECT DISTINCT C1.sidFROM Catalog C1, Parts P1WHERE C1.pid = P1.pid AND P1.color = ‘Green’
![Page 13: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/13.jpg)
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:
![Page 14: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/14.jpg)
Exercise 5.4
ProblemEmp(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)
1. Print the names and ages of each employee who works in both the Hardware department and the Software department.
![Page 15: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/15.jpg)
Exercise 5.4
Solution for (1)
SELECT E.ename, E.ageFROM Emp E, Works W1, Works W2, Dept D1, Dept D2WHERE 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’
![Page 16: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/16.jpg)
Exercise 5.4
ProblemEmp(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)
3. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.
![Page 17: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/17.jpg)
Exercise 5.4
Solution for (3)
![Page 18: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/18.jpg)
Exercise 5.4
ProblemEmp(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)
5. Find the enames of managers who manage the departments with the largest budgets.
![Page 19: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/19.jpg)
Exercise 5.4
Solution for (5)
SELECT E.enameFROM Emp EWHERE E.eid IN (SELECT D.managerid
FROM Dept DWHERE D.budget >= ALL (SELECT D2.budget
FROM Dept D2))
![Page 20: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/20.jpg)
Exercise 5.4
ProblemEmp(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)
8. Find the enames of managers who manage the departments with the largest budgets.
![Page 21: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/21.jpg)
Exercise 5.4
Solution for (8)
SELECT M.enameFROM Emp MWHERE M.sid IN (SELECT M2.sid
FROM Emp M2, Dept DWHERE M2.sid = D.managerid AND D.budget
< 5000000EXCEPTSELECT M3.sidFROM Emp M3, Dept D2WHERE M3.sid = D2.managerid ANDD2.budget <= 5000000)
![Page 22: COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers, Constraints (up to, but not including aggregation) M. Amanda Crick](https://reader033.vdocuments.us/reader033/viewer/2022061521/56649c785503460f9492d8ac/html5/thumbnails/22.jpg)
This is the end of the lecture!I hope you enjoyed it.