Oracle Database SQL — Question 206

Examine this schema information:

1. EMPLOYEES.DEPARTMENT_ID has a foreign key referencing DEPARTMENTS.DEPARTMENT_ID.
2. EMP_VIEW is based on the EMPLOYEES and DEPARTMENTS tables.
3. EMP_VIEW has columns EMPLOYEE_ID, EMPLOYEE_NAME and DEPARTMENT_NAME.

You must add a new column, MANAGER_ID, from the EMPLOYEES table, to the view, showing each employee’s manager.

Which statement will do this?

Answer options

• A. ALTER VIEW emp_view ADD (employee.manager_id);
• B. ALTER VIEW emp_view MODIFY ( SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d - WHERE e.department_id = d.department_id);
• C. ALTER VIEW emp_view ADD (SELECT manager_id FROM employees);
• D. CREATE OR REPLACE VIEW emp_view AS SELECT employee_id, employee_name, department_name, manager_id FROM employees e, departments d - WHERE e.department_id = d.department_id;

Correct answer: D

Explanation

The correct answer is D because it creates or replaces the existing view with the new SELECT statement that includes the MANAGER_ID. Options A and C incorrectly attempt to add the column without redefining the view's structure, while B incorrectly uses MODIFY, which is not the correct syntax for adding a column in this context.