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.

1. 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.

2. 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.

3. 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.

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)

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

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)

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.