Given a company database schemas as below: EMPLOYEE Frame Minit Lname San Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr start date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Ess Dependent name Sex Bdate Relationship Figure 3.7 Referential integrity constants disp on the COMPANY relational databe schema: 1. Retrieve the number of female employees in the company. 2. Retrieve the average salary, minimum and maximum salary of the male employees. 3. For each employee, list his/her name along with the project names he/she is working on. 4. For each project, list its name and controlling department name. 5. For each project, list its name, the controlling department name, and controlling department manager name. 6. Retrieve the names of employees who work on the project "ProjectX". 7. Retrieve the first name and last name of the male employee who has the highest salary. 8. Retrieve the first names and last names of the employees who DON'T work on any projects. 9. Retrieve the names and SSNs of employees who more than one dependent. 10. For each employee, list his/her name along with his/her department manager name. 2 EMPLOYEE Minit Sen B T Frame John Franklin Alicia Jennifer Ramesh Joyce Ahmad James Lrame Bdate Address Sex Salary Super_san Dno Smith 1234567891085-01-09 791 Fondren, Houston, TX M 30000 1333445565 5 Wong 333445555 1965-12-08 638 Voss, Houston, TX M 40000 888685555 5 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX 25000 987654321 Wallace 9876543211941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Narayan 666884444 1962-09-16 975 Fire Oak, Humble, TX M 38000 383445665 5 English 453453453 1972-07-31 5631 Rico, Houston, TX 25000 333445665 5 Jabbar 987987987 1989-03-29 980 Dallas, Houston, TX M 25000 987654321 888665858 1937-11-10 450 Stone, Houston, TX M M 55000 NULL S K v E 3 4 Borg Dnumber DEPARTMENT Dname Research Administration Headquarters 5 Marsan 333445555 987654321 888665555 Mgr start date 1988-05-22 1995-01-01 4 DEPT LOCATIONS Dnumber Dlocation Houston 4 Stafford 5 Beliaire 5 Sugarland Houston 1981-08-19 cdn.com/blackboard.learn.xythos.prod/5789239d39013/10715090?X-Blackboard-E 2 1 2 Dnumber DEPARTMENT Dname Research Administration Headquarters Mgr_sn 333445556 Mor_start_date 1988-05-22 1995-01-01 987654321 DEPT_LOCATIONS Dnumber Dlocation Houston Stafford Bellaire 5 Sugarland 5 Houston 888665555 1981-08-19 Pro Pnumber Hours 32.5 Drum 1 WORKS ON Essn 123456789 123456789 666884444 453453453 1 7.5 2 PROJECT Pname ProductX Product Productz Computerization Reorganization Newbenefits 40.0 all 3 Plocation Bellaire Sugarland Houston Stafford Houston Stafford Alolchon 20.0 10 453453453 20.0 20 1 333445555 10.0 30 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT Essa 333445555 20 10.0 Dependent name Sex Bdate 999887777 30 30.0 Alice F 1986-04-05 999887777 10 100 333445555 333445555 333445555 Theodore M Relationship Daughter Son Spouse Spouse 987987987 10 35.0 F 1983 10-25 1958-06-09 1942-02-28 Joy Abner 987987987 30 987654321 M 987654321 30 20.0 Michael M 1988-01-04 Son 123456789 123456789 8765432 20 15.0 Alice 1988-12-30 Daughter Spouse B68885555 20 NULL 123456789 Elbeth 1967-05-05
