Database
Font Paragraph Styles Lab 3 WORKS ON table to ensure that the entries were deleted successfully. 4) Write an SQL query that uses a LEFT OUTER JOIN to show the first name of the employee and the name of the department he/she manages. (2 points) Due Date: On or Before Thursday February 25, 2021 Target CLO(s) Reference Chapter 6 Hand in the query command and the output to make sure that, in addition to mangers, non-managers are also listed. Objective(s) To practice the INSERT, UPDATE, DELETE, and SELECT commands of SQL DML. 6 5) Write an SQL query that uses GROUP BY to show the social security number of the employee and the number of dependents he/she has ordered by the number of dependents in ascending order. That is, the query should produce the following output: (2 points) Name SOCIAL_SECURITY_NUMBER NUMBER_OF_DEPENDENTS ID Section 1 Instructions: 987654321 123456789 333445555 3 Log in to Oracle SQL Developer and answer the following questions. 3 Hand in the query command and the output to make sure that it produces the above output. 1) Using SQL's INSERT command, insert a new employee using the following data: (2 points) Fname=Richard; Minit=K:Lname=Marini:SSN=653298653;Bdate='12- DEC-63;Address=98 Oak forest, Katy, TX:Sex=M:Salary=37000;Super_ssn=987654321; Dno=1 Hand in the INSERT command and show the updated content of the EMPLOYEE table to ensure that the employee was inserted successfully. 2) Using SQL's UPDATE command, write a single UPDATE command to decrease the number of hours of all employees who work on project number 1 or project number 10 by 2 hours. (2 points) Hand in the UPDATE command and show the updated content of the WORKS ON table to ensure that the update was done successfully. 3) Use SQL's DELETE command to delete all entries in the WORKS_ON table with Pno=30. (2 points) English illnited States 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
