Examine the structure of the EMPLOYEES table:
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER
Foreign key to DEPARTMENT_ID column
of the DEPARTMENTS table
You need to create a view called EMP_VU that allows the users to insert rows through the view.
Which SQL statement, when used to create the EMP_VU view, allows the users to insert rows?
A. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
B. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
department_id
FROM employees
WHERE mgr_id IN (102, 120);
C. CREATE VIEW emp_vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id;
D. CREATE VIEW emp_vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees;
Answer: B
这道题目为什么是B呢?视图怎么可以allows the users to insert rows?
------解决方案--------------------------------------------------------
job _id要求not null,所以A错。
C中有聚合函数,所以错。
D这种方法连view都建不起,何谈insert了。