Database

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):

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