Reference no: EM13185910

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 your command is error-free, copy and paste your statements into this assignment document.  Upload this Word document for grading.

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.
2. 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. UPDATE l_employees SET last_Name = 'Olenik' where  first_Name = 'Mike' AND last_Name = 'Olenik';
3. 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. UPDATE l_employees SET dept_code='IT' where  first_Name = 'Fred' AND last_Name = ' Campbell' and dept_code='SHP';
4. C. Etter's first name should have been Cynthia. Update the database to reflect this change. UPDATE l_employees SET first_Name = 'Cynthia' where  first_Name = 'C' AND last_Name = 'Etter';
5. 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. UPDATE l_employees SET HIRE_DATE = '13-SEP-2005' where HIRE_DATE = '12-SEP-2005';
6. 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. UPDATE l_employees SET PHONE_NUMBER ='4553' ,  CREDIT_LIMIT=35  where first_Name = 'Bill' AND last_Name = 'Bourne';
7. Bill Bourne spent more than his credit limit and was fired.  Show how you would delete him from the database. delete from l_employees where first_Name = 'Bill' AND last_Name = 'Bourne';
8. Due to budget cuts, the IT department was closed. Delete the IT department from your database. Did this work?  Why or why not? If we delete the 'IT' department from the l_departmentstable , then it will affect the l_employees table, so we have to delete the records from l_employees too
9. Explain how you could delete the IT department without deleting all the employees who work for that department. We could set  thedept_Code in l_employees table to null for the dept_Code='IT' , then delete the dept_code for this from l_departments It would look like this UPDATE l_employees SET dept_Code = null where dept_Code = 'IT'; delete  from l_departments where dept_Code = 'IT';
10. 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. update  L_FOODS set price=price+1 where SUPPLIER_ID='ASP';
11. The price of grilled steak increased by 2 dollars.  Update the database to show this change. Show your sql below. update L_FOODS set price=price+2 where  DESCRIPTION='GRILLED STEAK';
12. The lunches scheduled for the 5 of December has been moved to the 15^th of December. Update the database to show this change.  Show your sql below. update L_LUNCHES set LUNCH_DATE = LUNCH_DATE+10 where EXTRACT(Day FROM LUNCH_DATE) = 5 and EXTRACT(month FROM LUNCH_DATE) = 12 ;
13. 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.

insert into l_suppliers values('RGF','Really Good Food');

insert into l_foods (SUPPLIER_ID,PRODUCT_CODE,MENU_ITEM,DESCRIPTION,PRICE,PRICE_INCREASE)

select 'RGF' as SUPPLIER_ID, PRODUCT_CODE,MENU_ITEM,DESCRIPTION,PRICE,PRICE_INCREASE from l_foods where SUPPLIER_ID='CBC';