05.02.Employee_Queries.txt

-- 1. Make a list of all project numbers for projects that involve 
-- an employee whose last name is ‘Scott’,
-- either as a worker or as a manager of the department that controls the project.

SELECT P.PNO
    FROM PROJECT P, DEPARTMENT D, EMPLOYEE E
    WHERE E.SSN = D.MGRSSN
    AND P.DNO = D.DNO
    AND E.LNAME='SCOTT'
    UNION
    SELECT WO.PNO
    FROM WORKS_ON WO, EMPLOYEE E
    WHERE WO.SSN = E.SSN
    AND E.LNAME = 'SCOTT';


-- 2. Show the resulting salaries if every employee working
-- on the ‘IoT’ project is given a 10 percent raise.

SELECT E.FNAME, E.LNAME, 1.1*E.SALARY AS INC_SAL
    FROM EMPLOYEE E, WORKS_ON WO, PROJECT P
    WHERE E.SSN = WO.SSN
    AND WO.PNO = P.PNO
    AND P.PNAME='IOT';

-- 3. Find the sum of the salaries of all employees of the ‘Accounts’ department,
-- as well as the maximum salary, the minimum salary,
-- and the average salary in this department

SELECT SUM(SALARY), MAX(SALARY), MIN(SALARY), AVG(SALARY)
    FROM EMPLOYEE E, DEPARTMENT D
    WHERE E.DNO = D.DNO
    AND D.DNAME = 'ACCOUNTS';


-- 4. Retrieve the name of each employee who works on all the projects
-- Controlled by department number 5 (use NOT EXISTS operator).

SELECT E.FNAME, E.LNAME FROM EMPLOYEE E
    WHERE NOT EXISTS (
    SELECT PNO FROM PROJECT P
    WHERE DNO = 5
    MINUS
    SELECT PNO FROM WORKS_ON WO
    WHERE WO.SSN = E.SSN);


-- 5. For each department that has more than five employees,
-- retrieve the department number and the number of its employees
-- who are making more than Rs. 6, 00,000.

SELECT E.DNO, COUNT(E.DNO)
    FROM EMPLOYEE E
    WHERE E.SALARY > 600000
    GROUP BY E.DNO
    HAVING E.DNO IN (SELECT E1.DNO FROM EMPLOYEE E1
    GROUP BY E1.DNO HAVING COUNT(*) > 5);