OCP 1Z0 051 39

39. Evaluate the following command:  
CREATE TABLE employees 
(employee_id      NUMBER(2) PRIMARY KEY,  
last_name        VARCHAR2(25) NOT NULL,  
department_id    NUMBER(2)NOT NULL,  
job_id           VARCHAR2(8),  
salary        NUMBER(10,2)); 
You issue the following command to create a view that displays the IDs and last names of the sales staff 
in the organization: 
CREATE OR REPLACE VIEW sales_staff_vu AS
SELECT employee_id, last_name,job_id
FROM employees
WHERE job_id LIKE 'SA_%'
WITH CHECK OPTION;
Which two statements are true regarding the above view? (Choose two.) 
A. It allows you to insert rows into the EMPLOYEES table . 
B. It allows you to delete details of the existing sales staff from the EMPLOYEES table. 
C. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table. 
D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in 
multitable INSERT statements. 

SQL> CREATE TABLE employees  2  (employee_id      NUMBER(10) PRIMARY KEY,  3  last_name        VARCHAR2(25) NOT NULL,  4  department_id    NUMBER(10)NOT NULL,  5  job_id           VARCHAR2(20),  6  salary        NUMBER(10,2));Table createdSQL> insert into employees(employee_id,last_name,department_id,job_id,salary)  2  select employee_id,last_name,department_id,job_id,salary from hr.employees  3  WHERE department_id IS NOT NULL;106 rows insertedSQL> SQL> CREATE OR REPLACE VIEW sales_staff_vu AS  2  SELECT employee_id, last_name,job_id  3  FROM employees  4  WHERE job_id LIKE 'SA_%'  5  WITH CHECK OPTION;View createdSQL> select count(*) from sales_staff_vu;  COUNT(*)----------        34

因有非空列department_id不在view中，所以不能insert.

SQL> insert into sales_staff_vu values(35,'test','no job');insert into sales_staff_vu values(35,'test','no job')ORA-01400: 无法将 NULL 插入 ("TEST"."EMPLOYEES"."DEPARTMENT_ID")

SQL> delete from sales_staff_vu where rownum <=1;1 row deleted

c这种情况前面讲过

SQL> update sales_staff_vu set job_id = 'new job';update sales_staff_vu set job_id = 'new job'ORA-01402: 视图 WITH CHECK OPTION where 子句违规

D from the view,view做为源取数据？那insert到哪个表？

Answer: BD