Multiple choice technology databases

You created a view called EMP_DEPT_VU that contains three columns from the EMPLOYEES and DEPARTMENTS tables: EMPLOYEE_ID, EMPLOYEE_NAME AND DEPARTMENT_NAME. The DEPARTMENT_ID column of the EMPLOYEES table is the foreign key to the primary key DEPARTMENT_ID column of the DEPARTMENTS table. You want to modify the view by adding a fourth column, MANAGER_ID of NUMBER data type from the EMPLOYEES tables. How can you accomplish this task?

  1. ALTER VIEW emp_dept_vu (ADD manager_id NUMBER);

  2. MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER);

  3. ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id;

  4. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To add a column to an existing view in Oracle, you cannot use ALTER VIEW or MODIFY VIEW. The view must be recreated with the new column using the CREATE OR REPLACE VIEW statement along with the updated query select list.

AI explanation

To modify the view by adding a fourth column, MANAGER_ID, from the EMPLOYEES table, you need to use the CREATE OR REPLACE VIEW statement.

The correct answer is:

D. CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id;

Explanation: Option A) ALTER VIEW emp_dept_vu (ADD manager_id NUMBER); - This option is incorrect because the ALTER VIEW statement does not have an ADD clause to add columns to an existing view.

Option B) MODIFY VIEW emp_dept_vu (ADD manager_id NUMBER); - This option is incorrect because there is no MODIFY VIEW statement in SQL to modify the structure of a view.

Option C) ALTER VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employee e, departments d WHERE e.department_id = d.department_id; - This option is incorrect because the ALTER VIEW statement does not allow you to change the SELECT statement of the view.

Option D) CREATE OR REPLACE VIEW emp_dept_vu AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d WHERE e.department_id = d.department_id; - This option is correct because the CREATE OR REPLACE VIEW statement allows you to create or modify a view. In this case, it creates a view called emp_dept_vu with the specified columns and queries the EMPLOYEES and DEPARTMENTS tables to retrieve the required data.

Therefore, the correct answer is option D.

Keep practicing — related questions