Question

Lesson 10Create the DEPARTMENT tables based on the following:

Column Name   ID      Name

Data Type     Number Varchar2

Length        7      25

Populate the DEPARTMENT table with data from the DEPT table. Include only columns that you need.

Create the EMPLOYEE table based on the following table chart:

Column Name    ID      LAST_NAME     FIRST_NAME     DEPT_ID

Data Type      Number Varchar2      Varchar2       Number

Length         7       25            25             7

Modify the EMPLOYEE table to allow for longer employee last names. Confirm your modification.

Confirm that both the DEPARTMENT and EMPLOYEE tables are stored in the data dictionary. (Hint: USER_TABLES)

Create the EMPLOYEE2 table based on the structure of the EMP table. Include only the EMPO, ENAME, DEPTNO columns. Name the columns in your new table ID, LAST_NAME, and DEPT_ID, respectively.

Drop the EMPLOYEE table.

Rename the EMPLOYEE2 table to EMPLOYEE.

Add a comment to the DEPARTMENT and EMPLOYEE table definitions describing the tables. Confirm your additions in the data dictionary.

Drop the LAST_NAME column from the EMPLOYEE table. Confirm your modification by checking the description of the table.

Create the EMPLOYEE2 table based on the structure of the EMP table. Include only the EMPNO, ENAME, DEPT_ID columns. Name the columns in your new table ID, LAST_NAME, and DEPT_ID, respectively. Mark the DEPT_ID column in the EMPLOYEE2 table as UNUSED. Confirm your modification by checking the description of the table.

Drop all the UNUSED columns from the EMPLOYEE2 table. Confirm your modifications by checking the description of the table.

Lesson 11

Add a table-level PRIMARY KEY constraint to the EMPLOYEE table using the ID column. The constraint should be named at creation.

Create a PRIMARY KEY constraint on the DEPARTMENT table using the ID column. The constraint should be named at creation.

Add a foreign key reference on the EMPLOYEE table that will ensure that the employee is not assigned to a nonexistent department.

Confirm that the constraints were added by querying USER_CONSTRAINTS. Note the types and names of the constraints and include them in your statement comments.

Display the object names and types from the USER_OBJECTS data dictionary view for EMPLOYEE and DEPARTMENT tables. You will want to format the columns for readability. Provide comments that include the new tables and indexes created.

Modify the EMPLOYEE table by adding a SALARY column of NUMBER data type, precision 7.

HERE IS THE SCRIPT TO BUILD THE ORIGINAL TABLE:

SET ECHO OFF

SET FEEDBACK OFF

PROMPT Creating the MY_EMPLOYEE table. Please wait...

CREATE TABLE my_employee

(id NUMBER(4)

CONSTRAINT my_employee_id_nn NOT NULL,

last_name VARCHAR2(25),

first_name VARCHAR2(25),

userid VARCHAR2(8),

salary NUMBER(9,2))

/

PROMPT Table MY_EMPLOYEE has been created.

SET FEEDBACK ON

SET ECHO ON

Answer 1

Program :