OCP 1Z0 051 41

41. Which two statements are true regarding views? (Choose two.) 
A. A subquery that defines a view cannot include the  GROUP BY clause. 
B. A view that is created with the subquery having the DISTINCT keyword can be updated. 
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated. 
D. A data manipulation language (DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table. 

考点仍然是可更新view

SQL> create or replace view v_emp as select deptno,sum(sal) as sum_sal from emp group by deptno;
View created

a不对

SQL> create or replace view v_emp as select distinct deptno,job from emp;
View created
Executed in 0.016 seconds

SQL> update v_emp set job = 'ss' where deptno = 10;
update v_emp set job = 'ss' where deptno = 10
ORA-01732: 此视图的数据操纵操作非法

view里可以包含聚集函数，汇总语句，但有这些语句的view不能执行dml

SQL> create or replace view v_emp as select rownum as sn,empno,ename from emp;
View created
Executed in 0.016 seconds

SQL> update v_emp set ename = 'test' where empno = 7788;
update v_emp set ename = 'test' where empno = 7788
ORA-01732: 此视图的数据操纵操作非法

包含伪列的view同样不能执行dml操作

只有包含所有not null列，才可能执行insert 操作，不然会报错

SQL> alter table emp modify ename not null;
Table altered

SQL> create or replace view v_emp as select empno,deptno from emp;
View created

SQL> desc emp;
Name     Type         Nullable Default Comments 
-------- ------------ -------- ------- -------- 
EMPNO    NUMBER       Y                         
ENAME    VARCHAR2(10)                           
JOB      VARCHAR2(10) Y                         
MGR      NUMBER       Y                         
HIREDATE DATE         Y                         
SAL      NUMBER       Y                         
COMM     NUMBER       Y                         
DEPTNO   NUMBER       Y                         

SQL> insert into v_emp(empno,deptno) values(1,2);
insert into v_emp(empno,deptno) values(1,2)
ORA-01400: 无法将 NULL 插入 ("TEST"."EMP"."ENAME")

SQL> 

Answer: CD