COP-5725 PRACTICE EXERCISES Chapter 5: Queries, Triggers,

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) Catalog(sid: integer, pid: integer, cost: real) The Catalog relation lists the prices charged for parts by Suppliers. Write the following queries in SQL:

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) 1. Find the pnames of parts for which there is some supplier.

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

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) 4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.

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

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) 6. For each part, find the sname of the supplier who charges the most for that part.

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)

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) 8. Find the sids of suppliers who supply a red part and a green part.

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’

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) 9. Find the sids of suppliers who supply a red part or a green part.

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’

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:

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) 1. Print the names and ages of each employee who works in both the Hardware department and the Software department.

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’

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) 3. Print the name of each employee whose salary exceeds the budget of all of the departments that he or she works in.

Exercise 5.4 Solution for (3)

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) 5. Find the enames of managers who manage the departments with the largest budgets.

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

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) 8. Find the enames of managers who manage the departments with the largest budgets.

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)

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