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...