Question
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 |
Name | ID | Section |
|
|
|
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started