Database

Using the COMPANY schema, express the following operations in SQL. For your SELECT operations, make sure that they always work without modification even if the underlying tables change via insert/update/delete operations.

1. Find the first name, middle initial and last name of all female employees. (1 point)

Query in SQL:

Query Output:

2. Produce a relation showing the department name and the name of the project controlled by that department. (2 points)

Query in SQL:

Query Output:

Figure 5.6 One possible database state for the COMPANY relational database scher Son B EMPLOYEE Frame Minit John Franklin Alicia Jennifer S Ramesh K Joyce A Ahmad V James E Lname Edate Address Sex Salary Superson Ono Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Wong 333445555 1855-12-08 638 Voss, Houston, TX M 40000 688665555 5 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Wallace 9876543211941-06-20 291 Berry Bellaire, TX F 43000 B88665555 4 Narayan 666884444 1962-09-15 975 Fire Oak Humble, TX M 38000 333445555 5 English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Jabbar 987987967 1969-03-29 980 Dallas, Houston, TX M 25000 087654321 4 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT Dname Research Administration Headquarters Dnumber 5 Mous 333445555 987654321 888665555 Motstart_date 1988-05-22 1995-01-01 1981-06-19 4 DEPT_LOCATIONS Dnumber Dlocation 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 WORKS ON Fan Pro Pnumber Drum 5 1 1 PROJECT Prame ProductX Product Productz Computerization Reorganisation Newbenefits 2 3 Hours 32.5 7.5 40.0 20.0 20.0 10.0 10.0 5 5 3 Plocation Bellaire Sugarland Houston Stallone Houston Stafford 10 2 + 20 30 2 3 DEPENDENT 123456789 123456789 666804444 453453453 453453453 333445555 333445555 333445555 333445555 999887777 998887777 987987987 987987987 987654321 987654321 888666666 10.0 10.0 30.0 Ess Sex - 9889999818 F 10.0 35.0 5.0 333445555 333445555 333445555 987654321 123456789 123456789 123456789 Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth M F M M Bdate 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 1988-12-30 1967-05-05 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 20.0 15.0 F NULL F