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