Note: Question 2 and 4 have been answered CIS 250 Unit 2 Assignment 1 Directions

Question

Note: Question 2 and 4 have been answered
CIS 250 Unit 2 Assignment 1
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 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

Explanation / Answer

1.SQL> CREATE THE L_EMPLOYEES TABLE
CREATE TABLE L_EMPLOYEES
( EMPLOYEE_ID NUMBER(3),
FIRST_NAME VARCHAR2(10),
LAST_NAME VARCHAR2(20),
CREDIT_LIMIT NUMBER(4,2),
PHONE_NUMBER VARCHAR2(4));

table created.

SQL> INSERT INTO L_EMPLOYEES VALUES (201, 'JANE', 'SMITH',’15.00’, '7890' );
one row inserted.
SQL> INSERT INTO L_EMPLOYEES VALUES (202, 'MARTHA', 'WOODS', ’20.25’, '00');
one row inserted.
SQL> INSERT INTO L_EMPLOYEES VALUE (203, 'BILL', 'BOURNE', ‘00’,’00’);
one row inserted.
SAL> INSERT INTO L_EMPLOYEES VALUES (204, 'MIKE', 'OLENI',’33.33’, '3333');
one row inserted.
SQL> INSERT INTO L_EMPLOYEES VALUES (205, 'C', 'ETTER', ‘43’,'00');
one row inserted.
SQL>commit;

//creating sequence
SQL> CREATE SEQUENCE SEQ_EMPLOYEE_ID
START WITH 206
INCREMENT BY 1;
SQL>commit;

SQL> ALTER TABLE L_EMPLOYEES
ADD Manager_Name VARCHAR(20));
table altered.
SQL>commit;

SQL> INSERT INTO L_EMPLOYEES VALUES (206, 'ROSE', 'HAMBY', ’14.00’,'3433',’SUSAN BROWN’);
one row inserted.
SQL>commit;

SQL> INSERT INTO L_DEPARTMENT VALUES ( 'IT',’INFORMATION TECHNOLOGY’);
one row inserted.
SQL>commit;

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.