Q1.
a)
b)
( ( )) ( )
c)
d)
e)
Q2.
a)
CREATE TABLE Works(
eid INTEGER,
did INTEGER,
pct_time INTEGER,
PRIMARY KEY(eid,did),
FOREIGN KEY eid REFERENCES Emp,
FOREIGN KEY did REFERENCES Dept
)
b)
SELECT E.ename
FROM Emp E, Works W, Dept D, Emp M
WHERE E.eid=W.eid AND W.did=D.did AND D.managerid=M.eid AND M.ename=’Steve Smith’;
c)
SELECT E1.age
FROM Emp E1
WHERE E1.eid NOT IN (
SELECT E.eid
FROM Emp E, Works W, Dept D
WHERE E.eid=W.eid AND W.did=Dept.did AND D.budget < 20000 ) d) SELECT E.age FROM Emp E WHERE E.salary = (SELECT MAX(salary) FROM Emp) e) SELECT D.did, AVG(E.salary) FROM Emp E, Works W, Dept D WHERE E.eid=W.eid AND W.did=D.did AND E.age < 45 GROUP BY D.did HAVING 10 <= (SELECT COUNT(*) FROM Works W1 WHERE W1.did=D.did ) f) SELECT E.ename FROM Emp E WHERE NOT EXISTS( SELECT D.did FROM Dept D MINUS SELECT D1.did FROM Dept D1, Works W WHERE D1.did=W.did and W.eid=E.eid ) g) SELECT TMP.dname FROM (SELECT D.did, D.dname, AVG(E.salary) AS avgsal FROM Dept D, Emp E, Works W WHERE D.did=W.did AND W.eid=E.eid GROUP BY D.did, D.dname ) AS TMP WHERE TMP.avgsal = (SELECT MAX(avgsal) FROM TMP);