ASSIGNMENT 2 AVALJOT KHURANA 2021269440 1 Write following queries in SQL 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 b Same as a except output the number of male employees instead of the number of employees c Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees d Retrieve the names of employees who make at least 10 000 more than the employee who is paid the least in the company e Retrieve the names of employees who is making least in their departments and have more than one dependent Solve using correlated nested queries A SELECT D DNAME COUNT AS NO OF EMPLOYEES FROM DEPARTMENT D EMPLOYEE E WHERE D DNO E DNO GROUP BY D DNAME HAVING AVG E SALARY 30000 B SELECT DNAME COUNT FROM DEPARTMENT D EMPLOYEE E WHERE D DNO E DNO AND E SEX M AND E DNO IN SELECT DNO FROM EMPLOYEE GROUP BY DNO HAVING AVG SALARY 30000 GROUP BY DNAME C SELECT FNAME LNAME FROM DEPARTMENT D EMPLOYEE E WHERE D DNO E DNO AND E DNO IN SELECT DNO FROM EMPLOYEE WHERE SALARY SELECT MAX SALARY FROM EMPLOYEE D SELECT FNAME LNAME FROM EMPLOYEE WHERE SALARY SELECT MIN SALARY FROM EMPLOYEE 10000 E SELECT E FNAME E LNAME FROM EMPLOYEE E DEPARTMENT D WHERE E DNO D DNO AND E SALARY SELECT MIN EM SALARY FROM EMPLOYEE EM WHERE EM DNO D DNO AND E SSN IN SELECT DP ESSN FROM EMPLOYEE EMP DEPENDENT DP WHERE DP ESSN EMP SSN GROUP BY DP ESSN HAVING COUNT 1 2 Specify following views in SQL Solve questions using correlated nested queries except a a A view that has the department name manager name and manager salary for every department 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 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 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 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 A CREATE VIEW DEPARTMENT MANAGER AS SELECT DNAME FNAME LNAME SALARY FROM EMPLOYEE E DEPARTMENT D WHERE D MGRSSN E SSN B CREATE VIEW DEPARTMENT DETAILS AS SELECT DNAME FNAME LNAME SELECT COUNT FROM EMPLOYEE EM WHERE D DNO EM DNO GROUP BY D DNO AS NO OF EMPLOYEES SELECT COUNT FROM PROJECT P WHERE D DNO P DNO GROUP BY D DNO AS NO OF PROJECT FROM EMPLOYEE E DEPARTMENT D WHERE D MGRSSN E SSN C CREATE VIEW PROJECT DETAILS AS SELECT P PNAME D DNAME SELECT COUNT FROM WORKS ON WO WHERE WO PNO P PNO GROUP BY PNO AS NO OF EMPLOYEES SELECT SUM WO HOURS FROM WORKS ON WO WHERE WO PNO P PNO GROUP BY PNO AS HOURS PER WEEK FROM PROJECT P DEPARTMENT D WHERE P DNO D DNO D CREATE VIEW PROJECT DETAILS 1 AS SELECT P PNAME D DNAME SELECT COUNT FROM WORKS ON WO WHERE WO PNO P PNO GROUP BY PNO AS NO OF EMPLOYEES SELECT SUM WO HOURS FROM WORKS ON WO WHERE WO PNO P PNO GROUP BY PNO HAVING COUNT PNO 1 AS HOURS PER WEEK FROM PROJECT P DEPARTMENT D WHERE P DNO D DNO E CREATE VIEW DEPARTMENT DETAILS 1 AS SELECT E FNAME E LNAME E SALARY D DNAME SELECT EM FNAME FROM EMPLOYEE EM WHERE D MGRSSN EM SSN AS MANAGER FNAME SELECT EM LNAME FROM EMPLOYEE EM WHERE D MGRSSN EM SSN AS MANAGER LNAME SELECT EM SALARY FROM EMPLOYEE EM WHERE D MGRSSN EM SSN AS MANAGER SALARY SELECT AVG SALARY FROM EMPLOYEE EMP WHERE D DNO EMP DNO GROUP BY D DNO AS AVRAGE SALARY FROM EMPLOYEE E DEPARTMENT D WHERE D DNO E DNO 3 Specify following queries in relational algebra a Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ProductX project b List the names of all employees who have a dependent with the same first name as themselves c Find the names of all employees who are directly supervised by Franklin Wong d For each project list the project name and the total hours per week by all employees spent on that project e Retrieve the names of all employees who work on every project f Retrieve the names of all employees who do not work on any project g For each department retrieve the department name and the average salary of all employees working in that department h Retrieve the average salary of all female employees i Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston j List the last names of all department managers who have no dependents The following symbols are used for SELECT for PROJECT for EQUI JOIN for NATURAL JOIN f for FUNCTION for DIVISION for SET DIFFERENCE A R1 FNAME LNAME DNO 5 EMPLOYEE DEPARTMENT R2 PNAME Productx R1 PROJECT R3 HOURS 10 R2 WORKS ON RESULT FNAME LNAME R3 B R1 EMPLOYEES SSN ESSN AND FNAME DEPNAME DEPENDENT RESULT FNAME LNAME R1 C R1 SSN FNAME Frankin AND LNAME Wong EMPLOYEE R2 EMPLOYEE SUPERSSN SSN R1 Result FNAME LNAME R2 D R1 PNO TOT HRS PNO f SUM HOURS WORKS ON Result PNAME TOT HRS R1 PNO PNUMBER PROJECT E R1 PNO SSN PNO ESSN WORKS ON R2 PNO PNUMBER PROJECT R3 FNAME LNAME R1 R2 Result FNAME LNAME EMPLOYEE R3 F R1 SSN EMPLOYEE R2 SSN ESSN WORKS ON R3 R1 R2 Result FNAME LNAME EMPLOYEE R3 G R1 EMPLOYEE DEPARTMENT R2 DNAME f AVG SALARY R1 H Result f AVG SALARY SEX F EMPLOYEE I R1 SSN ESSN WORKS ON PNO PNUMBER PLOCATION Houston PROJECT R2 DNO DEPARTMENT DNO PLOCATION Houston DEPARTMENT R3 SSN EMPLOYEE R2 R4 R1 R3 Result FNAME LNAME ADDRESS EMPLOYEE R2 J R1 EMPLOYEES SSN MGRSSN DEPARTMENT R2 R1 SSN ESSN DEPENDENT R3 R1 R2 RESULT LNAME R3
View Full Document
Unlocking...