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: 3. Find the first name, middle initial and last name of all employees who work on exactly 2 projects. (2.5 points) Query in SQL: Query Output: 4. Show how you can insert the following row in the PROJECT table using SQL: (1.5 points) Pname=Audit Pnumber=40 Plocation=Houston Dnum=1 Insert Command: Screen shot of PROJECT table (to make sure that the project has been inserted): 5. Show how you can decrease the salaries of all female employees by 5% using SQL. (1.5 points) Update Command: Screen shot of EMPLOYEE table (to make sure that the salaries have been decreased): 6. Using SQL, show how you can delete dependents whose name starts with the letter A. (1.5 points) Delete command: Screen shot of DEPENDENT table (to make sure that the dependents have been deleted) One possible database state for the COMPANY relational database schema. 4 EMPLOYEE Fname Minit John B Franklin T Alicia J Jennifer S Ramesh K Joyce Ahmad v James E Lname Ssn Bdate Address Sex Salary Super_ssn Dno Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 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 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1 | 4 Dnumber DEPARTMENT Dname Research Administration Headquarters 5 1 Mgr_ssn 333445555 987654321 888665555 Mgr_start_date 1988-05-22 1995-01-01 1981-06-19 4 DEPT_LOCATIONS Dnumber Dlocation Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston 1 Pno Pnumber 1 Hours 32.5 7.5 1 Dnum 5 5 2 2 PROJECT Pname ProductX ProductY Productz Computerization Reorganization Newbenefits 3 40.0 3 WORKS ON Essn 123456789 123456789 666884444 453453453 453453453 333445555 333445555 333445555 333445555 Plocation Bellaire Sugarland Houston Stafford Houston Stafford 5 1 20.0 10 4 2 20.0 20 1 2 10.0 30 4 3 10.0 10 10.0 20 10.0 30.0 30 10 10 10.0 999887777 999887777 987987987 987987987 987654321 987654321 DEPENDENT Essn 333445555 333445555 333445555 987654321 123456789 123456789 123456789 35.0 5.0 Dependent_name Alice Theodore Joy Abner Michael Alice Elizabeth Sex F M F M M Bdate 1986-04-05 1983-10-25 1958-05-03 1942-02-28 1988-01-04 30 30 Relationship Daughter Son Spouse Spouse Son Daughter Spouse 20.0 20 15.0 F 1988-12-30 1967-05-05 888665555 20 NULL F
