--Micah Acinapura
--5.4

--1)

SELECT DISTINCT ename, age
FROM emp E, works W, dept D
WHERE E.eid = W.eid AND W.did = (SELECT did
				   			     FROM Dept
							     WHERE dname = 'Hardware'
							     AND E.eid in(SELECT E.eid 
							   	    		  FROM emp E, works W, dept D 
						   					  WHERE E.eid = W.eid AND W.did = 
						   						(SELECT did
				   			   					FROM Dept
							   					WHERE dname = 'Software'
							   					)));
							   					
							   					
							   					
--2)
SELECT did, count(works.eid)
FROM works
GROUP BY did
HAVING (SUM(works.pct_time) > 200);

--3)
SELECT ename
FROM emp, dept d, works
WHERE emp.eid = works.eid AND salary > (SELECT MAX(budget)
										FROM d);


--4)
SELECT DISTINCT managerid
FROM Dept
WHERE budget > 1000000;

--5)
SELECT DISTINCT ename
FROM emp, dept
WHERE emp.eid = (SELECT managerid 
				 FROM dept
				 WHERE budget = (SELECT MAX(budget)
				 				 FROM dept, works
				 				 WHERE dept.did = works.eid 
				 				 AND works.eid = emp.eid));


--6)
SELECT managerid
FROM( SELECT managerid, SUM(budget)
	  FROM( SELECT managerid, budget 
	        FROM Dept
	        GROUP BY managerid, budget
	       ) AS foo
	  GROUP BY managerid
	) AS bar;

--7)
SELECT managerid, MAX(sum)
FROM( SELECT managerid, SUM(budget)
	  FROM( SELECT managerid, budget 
	        FROM Dept
	        GROUP BY managerid, budget
	       ) AS foo
	  GROUP BY managerid
	) AS bar;