1Z0-051-2题

2. View the Exhibit to examine the description for the SALES table.
Which views can have all DML operations performed on it? (Choose all that apply.) 

A. CREATE VIEW v3
AS SELECT * FROM SALES   
WHERE cust_id = 2034   
WITH CHECK OPTION;
B. CREATE VIEW v1
AS SELECT * FROM SALES   
WHERE time_id <= SYSDATE - 2*365   
WITH CHECK OPTION;
C. CREATE VIEW v2
AS SELECT prod_id, cust_id, time_id FROM SALES   
WHERE time_id <= SYSDATE - 2*365   
WITH CHECK OPTION;
D. CREATE VIEW v4
AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALES   
WHERE time_id <= SYSDATE - 2*365   
GROUP BY prod_id, cust_id   
WITH CHECK OPTION;
Answer: AB

解析：
注意本地题干：Which views can have all DML operations performed on it?

SQL> create table SALES(prod_id number not null,CUST_ID number not null,time_id date not null,channel_id number not null,promo_id number not null,quantity number(10,2) not null); 
SQL> CREATE VIEW v3 AS SELECT * FROM SALES WHERE cust_id=2034 WITH CHECK OPTION;

View created.

SQL> CREATE VIEW v1 AS SELECT * FROM SALES WHERE time_id<=SYSDATE - 2*365 WITH CHECK OPTION;

 

View created.
 

C答案其他列不能为空
D答案sum(quantity_sold)后面需要加别名
SQL> create view v4 as select prod_id,cust_id,sum(quantity_SOLD) sumqty from sales where time_id<=sysdate-2*365 group by prod_id,cust_id with check option;

View created.