Specify the following queries on the COMPANY relational database schema shown in Figure 3.5, using the relational operators discussed in this chapter. Also show the result of each query as it would apply to the database state of Figure 3.6.
- List the managers who are controlling more than 2 projects.
- List the names of employees all of whose dependents were born before 1990.
- Retrieve the average salary of employees who work totally no less than 30 hours on projects.
Extracted text: EMPLOYEE Fname Minit Lname Ssn Address Sex Super_ssn Dno Bdate Salary DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONSs Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours Figure 5.5 Schema diagram for the COMPANY relational DEPENDENT Essn Dependent_name Sex Bdate Relationship database schema.
Extracted text: Figure 5.6 One possible database state for the COMPANY relational database schema EMPLOYEE Fname Super san DnD 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 M 40000 888665555 Minit Lname San Bdate Addross Sar Salary John B. Smith 333445555 1955-12-08 638 Voes, Houston, TX Zelaya 999887ITI 1968-01-19 3321 Castle, Spring, TX Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX Franklin Wong Alicia 25000 987654321 Jenniler S F 43000 888665555 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M Joyce English 453453453 1972-07-31 5631 Rice, Houston, TX V Jabbar E Borg 38000 333445565 F 25000 333445555 M 25000 987654321 A 5 Ahmad 987987987 1969-03-29 980 Dallas, Houston, TX James 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 DEPT LOCATIONS Dlocaion DEPARTMENT Dramber 1 Drumber Mgr start date Dname Mgr_san Rosearch 5 333445555 1988-05-22 Houston Administration 4 987654321 1995-01-01 4 Stafford Hoadquartors 888665555 1981-06-19 Belaire 5 Sugarland Houston 5 WORKS ON PROJECT Essn Pno Hours Phame Pnumber Plocation Dnum 123456789 1 325 ProductX Bellaire 5 123456789 2 75 ProducfY 2 Sugarland 5 666884444 3. 40.0 Product 3 Hounton 5 453453453 20.0 Computorization Roorganiration 10 Stafford 4 453453453 2 20.0 20 Houston 1 333445555 2 10.0 Newbenelits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent name Sox Relationshp 1986 04-05 Daughter Bdate 999887777 30 30.0 333445555 Alice F 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 10 35.0 1958-05-03 Spoune 1942-02-28 Spouse 333445555 Joy 30 5.0 987654321 Abner M 987654321 30 20.0 123456789 Michael M 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