Loading

Database - CIS250 Unit 2 A new manager was recently hired along with 5 employees who will work under her Write insert statements to insert the data into the tables l_employees and l_department

Availability: In stock

$30.00
Already Bought 7 times.

Database - A new manager was recently hired along with 5 employees who will work under her Write insert statements to insert the data into the tables l_employees and l_department

Double click on above image to view full picture

Zoom Out
Zoom In

More Views

Quick Overview

Directions


Modify Oracle table data using the correct SQL statements.   You must create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command).  Once the  commands are error-free, copy and paste the statements into this document


Modification Specifics:


A new manager was recently hired along with 5 employees who will work under her.


The President of our company has created a new department for her to head as well.


You can assume that all the new employees were hired today.


Make sure your changes get saved to the database.


Information:


 


New Employees (using table l_employees):










































First Name



Last Name



Credit Limit



Phone Number



Jane



Smith



15.00



7890



Martha



Woods



20.25



 



Bill



Bourne



 



 



Mike



Oleni



33.33



3333



C



Etter



43



 



 


New Manager (using table l_employees):


Rose Hamby is the manager of all the employees above.  Her information is below:




















First Name



Last Name



Credit Limit



Phone Number



Manager Name



Rose



Hamby



14.00



3433



Susan Brown



 


 


 


The new department that was created for Rose is as follows (using table l_department):














Department Code



Department Name



IT



Information Technology



 



  1. Write insert statements to insert the data into the tables above.  Show all the insert statements you used, along with the order they were run in.   Use the seq_employee_id SEQUENCE to get the employee_id.  Do not hard-code the manger_id value in your insert statements.


 



  1. Mike Olenik’s name was spelled incorrectly. Change his record so that his last name is spelled correctly.(Olenik). Make sure your changes get saved to the database.


 



  1. Fred Campbell asked for a transfer from the Shipping department to the IT department.  His request was approved.  Update the database to reflect this change.


 



  1. C. Etter’s first name should have been Cynthia. Update the database to reflect this change.


 



  1. For some unknown reason, HR decided to change the hire date of all the employees that were added today, to tomorrow (09/13/2005).  Update the database to reflect this change.  Make sure your changes get saved to the database.


 



  1. Bill Bourne has now received a phone number and credit limit.  They are 4553 and $35.00 respectively.  Update the database to reflect this change.


 



  1. Bill Bourne spent more than his credit limit and was fired.  Show how you


            would delete him from the database.


 



  1. Due to budget cuts, the IT department was closed. Delete the IT department from your database. Did this work?  Why or why not?


 


 



  1. Explain how you could delete the IT department without deleting all the employees who work for that department.


 


 



  1. Supplier ASP’s has decided to raise their prices.  They raised the price of all their foods by a $1.  Update the database to show this change.


 



  1. The price of grilled steak increased by 2 dollars.  Update the database to show this change. Show your sql below.


 



  1. The lunches scheduled for the 5 of December has been moved to the 15th of December. Update the database to show this change.  Show your sql below.


 



  1.  A new supplier, RGF (Really Good Food) has been approved. RGF supplys all the same items as CBC, but 50 cents cheaper.  Show the insert statements required to enter this information into the l_foods and l_suppliers table.


 


 

$30.00

Details

Directions

Modify Oracle table data using the correct SQL statements.   You must create the statements in Oracle by using the following steps (SQL > SQL commands > Enter Command).  Once the  commands are error-free, copy and paste the statements into this document

Modification Specifics:

A new manager was recently hired along with 5 employees who will work under her.

The President of our company has created a new department for her to head as well.

You can assume that all the new employees were hired today.

Make sure your changes get saved to the database.

Information:

 

New Employees (using table l_employees):

First Name

Last Name

Credit Limit

Phone Number

Jane

Smith

15.00

7890

Martha

Woods

20.25

 

Bill

Bourne

 

 

Mike

Oleni

33.33

3333

C

Etter

43

 

 

New Manager (using table l_employees):

Rose Hamby is the manager of all the employees above.  Her information is below:

First Name

Last Name

Credit Limit

Phone Number

Manager Name

Rose

Hamby

14.00

3433

Susan Brown

 

 

 

The new department that was created for Rose is as follows (using table l_department):

Department Code

Department Name

IT

Information Technology

 

  1. Write insert statements to insert the data into the tables above.  Show all the insert statements you used, along with the order they were run in.   Use the seq_employee_id SEQUENCE to get the employee_id.  Do not hard-code the manger_id value in your insert statements.

 

  1. Mike Olenik’s name was spelled incorrectly. Change his record so that his last name is spelled correctly.(Olenik). Make sure your changes get saved to the database.

 

  1. Fred Campbell asked for a transfer from the Shipping department to the IT department.  His request was approved.  Update the database to reflect this change.

 

  1. C. Etter’s first name should have been Cynthia. Update the database to reflect this change.

 

  1. For some unknown reason, HR decided to change the hire date of all the employees that were added today, to tomorrow (09/13/2005).  Update the database to reflect this change.  Make sure your changes get saved to the database.

 

  1. Bill Bourne has now received a phone number and credit limit.  They are 4553 and $35.00 respectively.  Update the database to reflect this change.

 

  1. Bill Bourne spent more than his credit limit and was fired.  Show how you

            would delete him from the database.

 

  1. Due to budget cuts, the IT department was closed. Delete the IT department from your database. Did this work?  Why or why not?

 

 

  1. Explain how you could delete the IT department without deleting all the employees who work for that department.

 

 

  1. Supplier ASP’s has decided to raise their prices.  They raised the price of all their foods by a $1.  Update the database to show this change.

 

  1. The price of grilled steak increased by 2 dollars.  Update the database to show this change. Show your sql below.

 

  1. The lunches scheduled for the 5 of December has been moved to the 15th of December. Update the database to show this change.  Show your sql below.

 

  1.  A new supplier, RGF (Really Good Food) has been approved. RGF supplys all the same items as CBC, but 50 cents cheaper.  Show the insert statements required to enter this information into the l_foods and l_suppliers table.

 

 

Additional Information

Programming Language Database & SQL

Product Tags

Use spaces to separate tags. Use single quotes (') for phrases.