COMPLEX SQL Answers to Assignment 2 Question 1 a For each department whose average employee salary is more than 30 000 retrieve the department name and the number of employees working for that department SELECT DNAME COUNT FROM DEPARTMENT EMPLOYEE WHERE DNUMBER DNO GROUP BY DNAME HAVING AVG SALARY 30000 b Suppose we want the number of male employees in each department making more than 30000 in average SELECT DNAME COUNT FROM DEPARTMENT EMPLOYEE WHERE DNUMBER DNO AND GENDER M AND DNO IN SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING AVG SALARY 30000 GROUP BY DNAME c Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees SELECT Fname Lname FROM EMPLOYEE WHERE Dno SELECT Dno FROM EMPLOYEE WHERE Salary SELECT MAX Salary FROM EMPLOYEE d Retrieve the names of employees who make at least 10 000 more than the employee who is paid the least in the company SELECT Fname Lname FROM EMPLOYEE WHERE Salary 10000 SELECT MIN Salary FROM EMPLOYEE e Retrieve the names of employees who is making least in their departments and have more than one dependent SELECT Fname Lname FROM EMPLOYEE E WHERE Salary SELECT MIN Salary FROM Employee E2 WHERE E Dno E2 Dno AND Select Count FROM Dependent D WHERE E Ssn D Essn 1 Question 2 a A view that has the department name manager name and manager salary for every department CREATE VIEW V1 AS SELECT Dname Fname Lname Salary FROM Department Employee WHERE D Mgrssn E Ssn b 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 V2 AS SELECT Dname Fname Lname SELECT COUNT FROM Employee E1 WHERE E1 Dno D Dno AS Num Employees SELECT COUNT FROM PROJECT P WHERE P Dno D Dno AS Num Projects FROM Department D Employee E WHERE D Mgrssn E Ssn c 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 V3 AS SELECT Pname Dname SELECT COUNT Ssn FROM WORKS ON W1 WHERE W1 Pno P Pno AS Num Employees SELECT SUM Hours FROM WORKS ON W2 WHERE W2 Pno P Pno AS Total Hours FROM Project P Department D WHERE P Dno D Dno d 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 V4 AS SELECT Pname Dname SELECT COUNT Ssn FROM WORKS ON W1 WHERE W1 Pno P Pno AS Num Employees SELECT SUM Hours FROM WORKS ON W2 WHERE W2 Pno P Pno AS Total Hours FROM Project P Department D WHERE P Dno D Dno AND SELECT COUNT Ssn FROM WORKS ON W3 WHERE W3 Pno P Pno 1 OR CREATE VIEW V4 AS SELECT Pname Dname SELECT COUNT Ssn FROM WORKS ON W1 WHERE W1 Pno P Pno AS Num Employees SELECT SUM Hours FROM WORKS ON W2 WHERE W2 Pno P Pno AS Total Hours FROM Project P Department D WHERE P Dno D Dno AND P Pno IN SELECT Pno FROM WORKS ON GROUP BY Pno HAVING Count 1 e 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 V5 AS SELECT Dname E Fname E Lname E Salary M Fname M Lname M Salary SELECT AVG Salary FROM Employee E1 WHERE E1 Dno E DNo AS Avg Dept Salary FROM Employee E Department D Employee M WHERE E Dno D Dno AND D Mgrssn M Ssn Employees who have two or more dependents SELECT Lname Fname FROM Employee WHERE SELECT COUNT FROM Dependent WHERE Essn Ssn 2 RELATIONAL ALGEBRA 1 Retrieve the name and address of all employees who work for the Research department 2 Retrieve the names of employees who have no dependents 3 List the names of managers who have at least one dependent Exercises 6 16 d For each project list the project name and the total hours per week by all employees spent on that project PROJ HOURS PNO TOT HRS PNO f SUM HOURS WORKS ON RESULT P PNAME TOT HRS PROJ HOURS J PNO PNUMBER PROJECT Exercises 6 16 g For each department retrieve the department name and the average salary of employees working in that department DEPT AVG SALS DNUMBER AVG SAL DNO f AVG SALARY EMPLOYEE RESULT P DNUMBER AVG SAL DEPT AVG SALS DEPARTMENT Exercises 6 16 h Retrieve the average salary of all female employees RESULT AVG F SAL f AVG SALARY s GENDER F EMPLOYEE
View Full Document
Unlocking...