Create a view called EMP_VU based on the employee number, employee name, and dep

Question

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_UV view.

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

Using your EMP_UV, 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_UV based on the employee name, department name, salary, and salary grade for all employees. Label the column Employee, Department, Salary, and Grade, respectively.

Create a sequence to be used with the primary key column of the DEPARTMENT 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 DEPARTMENT 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 EMPLOYEE table.

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

Explanation / Answer

Answer)

1. 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.

CREATE VIEW EMP_UV AS

SELECT employee_number, employee_name EMPLOYEE, department_number

FROM EMP;

2. Display the contents of the EMP_UV view.

select * from EMP_UV;

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

select view_name, text from user_views where view_name = 'EMP_UV';

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

select employee_name, department_number from EMP_UV;

5. 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.

CREATE VIEW DEPT20 AS

SELECT employee_number EMPLOYEE_ID, employee_name EMPLOYEE, department_number DEPARTMENT_ID

FROM EMP where department_number=20

WITH CHECK OPTION;

6. Display the structure and contents of the DEPT20 view.

desc DEPT20;

select * from DEPT20;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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