Assignment 2 Query1 Retrieve each department whose average employee salary is more than $30,000. SELECT D.DNAME,COUNT(*) AS NUMBER_OF_EMPLOYEES FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DNO=D.DNO GROUP BY D.DNAME HAVING AVG(SALARY)>30000; Query2 Same as a, except output the number of male employees instead of the number of employees. SELECT D.DNAME AS DEPARTMENT_NAME,COUNT(*) AS NUMBER_OF_MALE_EMPLOYEES FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DNO=D.DNO WHERE E.SEX='M' AND E.DNO IN (SELECT D.DNO FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DNO=D.DNO GROUP BY D.DNO HAVING AVG(SALARY)>30000) GROUP BY D.DNAME; Query 3 Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. SELECT E.FNAME||' '||E.LNAME AS FULL_NAME FROM EMPLOYEE E WHERE E.DNO IN (SELECT E.DNO FROM EMPLOYEE E WHERE E.SALARY= (SELECT MAX(SALARY) FROM EMPLOYEE E ) ); Query4 Retrieve the names of employees who make at least $10,000 more than the employee who is paid the least in the company. SELECT E.FNAME||' '||E.LNAME AS FULL_NAME FROM EMPLOYEE E WHERE E.SALARY>(SELECT MIN(SALARY) FROM EMPLOYEE E)+10000; Query5 Retrieve the names of employees who is making least in their departments and have more than one dependent. SELECT E.LNAME||' '||E.FNAME AS FULL_NAME FROM EMPLOYEE E INNER JOIN (SELECT DNO,MIN(SALARY) AS MINSAL FROM EMPLOYEE GROUP BY DNO ) MS ON E.DNO=MS.DNO AND E.SALARY=MS.MINSAL INNER JOIN (SELECT ESSN FROM DEPENDENT GROUP BY ESSN HAVING COUNT(ESSN)>1 ) DEP ON E.SSN=DEP.ESSN; A view that has the department name, manager name and manager salary for every department. CREATE VIEW VIEW1 AS SELECT D.DNAME AS DEPARTMENT_NAME,E.LNAME||' '||E.FNAME AS MANAGER_NAME, E.SALARY AS SALARY FROM DEPARTMENT D INNER JOIN EMPLOYEE E ON D.MGRSSN=E.SSN ORDER BY D.DNAME; A view that has the department name, its manager's name, number of employees working in that department, and the number of projects controlled by that department (for each department). CREATE VIEW VIEW2 AS SELECT PR.DNAME AS DEPARTMENT_NAME,PR.LNAME||' '||PR.FNAME AS MANAGER_NAME,NUMBER_OF_EMPLOYEES,NUMBER_OF_PROJECTS FROM (SELECT D.DNAME,E.LNAME,E.FNAME,COUNT(P.DNO) AS NUMBER_OF_PROJECTS FROM DEPARTMENT D LEFT JOIN PROJECT P ON D.DNO=P.DNO INNER JOIN EMPLOYEE E ON D.MGRSSN=E.SSN GROUP BY D.DNAME,E.LNAME,E.FNAME ) PR INNER JOIN (SELECT D.DNAME,COUNT(E.SSN) AS NUMBER_OF_EMPLOYEES FROM EMPLOYEE E INNER JOIN DEPARTMENT D ON E.DNO=D.DNO GROUP BY D.DNAME ) EM ON PR.DNAME=EM.DNAME; A view that has the project name, controlling department name, number of employees working on the project, and the total hours per week they work on the project (for each project) CREATE VIEW VIEW3 AS SELECT P.PNAME,D.DNAME,COUNT(W.SSN) AS NUMBER_OF_EMPLOYEES,SUM(HOURS) FROM PROJECT P INNER JOIN DEPARTMENT D ON P.DNO=D.DNO LEFT JOIN WORKS_ON W ON P.PNO=W.PNO GROUP BY P.PNAME, D.DNAME; A view that has the project name, controlling department name, number of employees, and total hours worked per week on the project for each project with more than one employee working on it. CREATE VIEW VIEW4 AS SELECT P.PNAME,D.DNAME,COUNT(W.SSN) AS NUMBER_OF_EMPLOYEES,SUM(HOURS) FROM PROJECT P INNER JOIN DEPARTMENT D ON P.DNO=D.DNO INNER JOIN WORKS_ON W ON P.PNO=W.PNO GROUP BY P.PNAME, D.DNAME HAVING COUNT(W.SSN)>1; A view that has the employee name, employee salary, department that the employee works in,department manager name, manager salary, and average salary for the department. CREATE VIEW VIEW5 AS SELECT E1.LNAME||' '||E1.FNAME AS EMPLOYEE_NAME,E1.SALARY AS EMPLOYEE_SALARY,ADS.DNAME AS DEPARTMENT, E2.LNAME||' '||E2.FNAME AS MANAGER_NAME,E2.SALARY AS MANAGER_SALARY,AVG_DEPT_SALARY FROM EMPLOYEE E1 LEFT JOIN DEPARTMENT D1 ON E1.DNO=D1.DNO LEFT JOIN EMPLOYEE E2 ON D1.MGRSSN=E2.SSN LEFT JOIN (SELECT E.DNO,D.DNAME,D.MGRSSN,CAST(AVG(SALARY) AS DECIMAL(25,2)) AS AVG_DEPT_SALARY FROM EMPLOYEE E JOIN DEPARTMENT D ON E.DNO=D.DNO GROUP BY E.DNO, D.MGRSSN, D.DNAME ) ADS ON E1.DNO=ADS.DNO;
View Full Document
Unlocking...