DOC PREVIEW
UT Dallas CS 6360 - ASSIGNMENT 2 ANSWERS

This preview shows page 1 out of 4 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 4 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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


View Full Document

UT Dallas CS 6360 - ASSIGNMENT 2 ANSWERS

Documents in this Course
Ch22(1)

Ch22(1)

44 pages

Ch21

Ch21

38 pages

Ch19

Ch19

46 pages

Ch18

Ch18

25 pages

Ch17

Ch17

21 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch05

Ch05

34 pages

Ch22

Ch22

45 pages

Ch21

Ch21

38 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch16

Ch16

17 pages

Ch15

Ch15

42 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

34 pages

Ch06

Ch06

43 pages

Ch05

Ch05

34 pages

Ch04

Ch04

39 pages

Ch03(2)

Ch03(2)

36 pages

Ch02

Ch02

33 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch03(1)

Ch03(1)

38 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch24

Ch24

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

48 pages

Ch18

Ch18

24 pages

Ch17

Ch17

22 pages

Ch08

Ch08

28 pages

Ch07

Ch07

31 pages

Ch06

Ch06

43 pages

Ch05

Ch05

39 pages

Ch04(1)

Ch04(1)

39 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

lab-manual

lab-manual

215 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch17

Ch17

25 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04

Ch04

43 pages

Ch03

Ch03

41 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Ch04(1)

Ch04(1)

43 pages

Ch07

Ch07

40 pages

Ch03

Ch03

42 pages

Ch01

Ch01

36 pages

Ch02

Ch02

38 pages

Ch05

Ch05

41 pages

Ch06

Ch06

47 pages

Ch08

Ch08

39 pages

Ch17

Ch17

25 pages

Ch18

Ch18

24 pages

Ch09

Ch09

42 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch21

Ch21

54 pages

Ch19

Ch19

51 pages

Ch18

Ch18

24 pages

Ch17

Ch17

25 pages

Ch09

Ch09

42 pages

Ch08

Ch08

39 pages

Ch07

Ch07

40 pages

Ch06

Ch06

47 pages

Ch05

Ch05

41 pages

Ch04(1)

Ch04(1)

43 pages

Ch03

Ch03

42 pages

Ch02

Ch02

38 pages

Ch01

Ch01

36 pages

Load more
Download ASSIGNMENT 2 ANSWERS
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view ASSIGNMENT 2 ANSWERS and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view ASSIGNMENT 2 ANSWERS 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?