Specify the following queries on the COMPANY relational database schema shown in Figure 5.5 using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state in Figure 5.6.

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.

Figure 5.5

Figure 5.6

Transcribed Image Text:

Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Minit Lname Sen Bdate Address Sex Salary Super_ssn Dno B 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 333445555 1955-12-08 638 Voss, Houston, TX 999887777 1968-01-19 3321 Castle, Spring, TX John Smith Franklin Wong M 40000 888665555 5 Alicia Zelaya 25000 987654321 43000 888665555 F 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 4 K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M English 453453453 1972-07-31 5631 Rice, Houston, TX 987987987 1969-03-29 980 Dallas, Houston, TX 38000 333445555 25000 333445555 Ramesh Joyce A F Ahmad V M 25000 98B7654321 Jabbar James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT LOCATIONS Dname Dnumber Mgr_san Mgr_start_date Dnumber Dlocation Research 5 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 888665555 1981-06-19 5 Bellaire 5 Sugarland 5 Houston WORKS ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 32.5 ProductX Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 40.0 Productz 3 Houston 453453453 20.0 Computerization 10 Stafford 453453453 2 20.0 Reorganization 20 Houston 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent name Sex Bdate Relationship Daughter 999887777 30 30.0 333445555 Alice F 1986-04-05 999887777 10 10.0 333445555 Theodore 1983-10-25 Son 987987987 10 35.0 333445555 Joy 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse