Database I Lab 3 Due Date On or Before Thursday February 25, 2021 Objective(s) Target CLO(s) Reference To practice the INSERT , UPDATE , DELETE , and SELECT commands of SQL DML 6 Chapter 6 Instructions Log in to Oracle SQL Developer and answer the following questions Using SQLs 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 Using SQLs 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 Use SQLs DELETE command to delete all entries in the WORKS ON table with Pno 30 (2 points) Hand in the DELETE command and show the updated content of the WORKS ON table to ensure that the entries were deleted successfully 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) Hand in the query command and the output to make sure that, in addition to mangers, non managers are also listed 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) SOCIAL SECURITY NUMBER NUMBER OF DEPENDENTS 987654321 1 123456789 3 333445555 3 Hand in the query command and the output to make sure that it produces the above output

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 26, 2024

Database I Lab 3 Due Date: On or Before Thursday February 25, 2021 Objective(s) Target CLO(s) Reference To practice the INSERT , UPDATE , DELETE

Database I

Lab 3

Due Date: On or Before Thursday February 25, 2021

Objective(s)	Target CLO(s)	Reference
To practice the INSERT, UPDATE, DELETE, and SELECT commands of SQL DML.	6	Chapter 6

Instructions:

Using SQLs 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.

Using SQLs 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.

Use SQLs DELETE command to delete all entries in the WORKS_ON table with Pno=30. (2 points)

Hand in the DELETE command and show the updated content of the WORKS_ON table to ensure that the entries were deleted successfully.

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)

Hand in the query command and the output to make sure that, in addition to mangers, non-managers are also listed.

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)

SOCIAL_SECURITY_NUMBER NUMBER_OF_DEPENDENTS

----------------------------------------------------------------------------

987654321 1

123456789 3

333445555 3

Hand in the query command and the output to make sure that it produces the above output.