Lesson 12

Create a view called EMP_VU based on the employee number, employee name, and department number from the EMP table. Change the heading for the employee name to EMPLOYEE.

Display the contents of the EMP_VU view.

Select the view name and text from the data dictionary USER_VIEWS.

Using your EMP_VU, enter a query to display all employee names and department numbers.

Create a view named DEPT20 that contains the employee number, employee name, and department number for all employees in department 20. Label the view columns EMPLOYEE_ID, EMPLOYEE, and DEPARTMENT_ID. Do not allow an employee to be reassigned to another department through the view.

Display the structure and contents of the DEPT20 view.

Attempt to reassign Smith to department 30.

Create a view called SALARY_VU based on the employee name, department name, salary, and salary grade for all employees. Label the column Employee, Department, Salary, and Grade, respectively.

Lesson 13

Create a sequence to be used with the primary key column of the DEPT table. The sequence should start at 80 and have a maximum value of 260. Have your sequence increment by ten numbers. Name the sequence DEPT_ID_SEQ.

Write a script to display the following information about your sequences: sequence name, maximum value, increment size, and the last number.

Write an interactive script to insert a row into the DEPT table. Be sure to use the sequence that you created for the ID column. Create a customized prompt to enter the department name.

Create a non-unique index on the foreign key column (deptno) in the EMP table.

Display the indexes and uniqueness that exist in the data dictionary for the EMP table.