Select DISTINCT E.ename from Emp E WHERE E.eid NOT IN(Select W.eid from WORKS W WHERE W.PCT=100);
3. Find the enames of managers who manage the department(s) with the smallest budget(s).
SELECT E.ename FROM Emp E WHERE E.eid IN (SELECT D.managerid FROM Dept D WHERE D.budget = (SELECT MIN (D2.budget) FROM Dept D2));
4. If a manager manages more than one department, he or she controls the sum of all the budgets for those departments. Find the managerids of managers who control more than $10 million.
SELECT D.managerid FROM Dept D WHERE 10000000 < (SELECT SUM (D2.budget) FROM Dept D2 WHERE D2.managerid = D.managerid ) ;
5. For each department, print the department name and the minimum, maximum, and average age of the employees for each department
pending
Problem 2.
MovieStar(SNo, sname, gender),
Director(DNo, dname, ddob, dcity)
Movie(MNo, DNo, title, genre, year, length, STNo, profit)
Studio(STNo, sname)
StarsIn(SNo, MNo, wage, firstrole)
1. Find the names of stars who have appeared in at least one movie where the profits were greater than 100M
Select sname from MovieStar where SNo in (Select SI.SNo from Movie M,StarsIn SI where SI.MNo=M.MNo and profit>100000000)