SQL5

 --emp works dept schema

CREATE TABLE EMP(

EID NUMBER(5),

ENAME VARCHAR2(20),

AGE NUMBER(2),

SALARY REAL

);

CREATE TABLE WORKS(

EID NUMBER (5),

DID NUMBER(5),

PCTTIME NUMBER(5)

);

CREATE TABLE DEPT(

DID NUMBER(5),

BUDGET REAL,

MANAGERID NUMBER(5)

);

-- 1. Print the names and ages of each employee who works in both the Hardware department and

-- the Software department.

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’

-- 2. For each department with more than 20 full-time-equivalent employees (i.e., where the parttime and full-time employees add up to at least that many full-time employees), print the

-- didtogether with the number of employees that work in that department.

SELECT W.DID, COUNT (W.EID)

FROM WORKS W

GROUP BY W.DID

HAVING 2000 < ( SELECT SUM (W1.PCT TIME)

FROM WORKS W1

WHERE W1.DID = W.DID )

-- 3. Print the name of each employee whose salary exceeds the budget of all of the departments that

-- he or she works in.

SELECT E.ENAME

FROM EMP E

WHERE E.SALARY > ALL (SELECT D.BUDGET

FROM DEPT D, WORKS W

WHERE E.EID = W.EID AND D.DID = W.DID)

-- 4. Find the managerids of managers who manage only departments with budgets greater than

-- $1,000,000.

SELECT DISTINCT D.MANAGERID

FROM DEPT D

WHERE 1000000 < ALL (SELECT D2.BUDGET

FROM DEPT D2

WHERE D2.MANAGERID = D.MANAGERID )

-- 5. Find the enames of managers who manage the departments with the largest budget.

SELECT E.ENAME

FROM EMP E

WHERE E.EID IN (SELECT D.MANAGERID

FROM DEPT D

WHERE D.BUDGET = (SELECT MAX (D2.BUDGET)

FROM DEPT D2))

-- 6. 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 $5,000,000.

SELECT D.MANAGERID

FROM DEPT D

WHERE 5000000 < (SELECT SUM (D2.BUDGET)

FROM DEPT D2

WHERE D2.MANAGERID = D.MANAGERID )

-- 7. Find the managerids of managers who control the largest amount.

SELECT DISTINCT TEMPD.MANAGERID

FROM (SELECT DISTINCT D.MANAGERID, SUM (D.BUDGET) AS TEMPBUDGET

FROM DEPT D

GROUP BY D.MANAGERID ) AS TEMPD

WHERE TEMPD.TEMPBUDGET = (SELECT MAX (TEMPD.TEMPBUDGET)

FROM TEMPD)