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;

答案: AB
考點:透過檢視對基表進行insert操作時，基表中帶有not null約束的列須出現在檢視中。
關鍵字：SELECT * FROM

【解析實驗】

題意要求可以對檢視進行所有DML操作（insert 、update、delete），
如果透過檢視對基表進行insert操作，建立檢視的語句必須包含基表中所有帶有not null約束限制的列
本題基表上各列均有not null約束限制，所以建立檢視時必須包含基表所有列。CD選項錯誤.

語法上看ABC選項檢視均可以正常建立，但C選項檢視v2不可以進行insert操作。

對C選項中檢視v2執行insert操作時，相當於對基表SALES部分列執行insert操作，其他列由於有not null約束，會報錯。
SH@PROD>insert into v2 values (20,2411,to_date('26-JUN-98','DD-MON-RR'));

D選項帶有group by子句，使用了sum函式，是複雜檢視，不能對複雜檢視進行DML操作更新基表。D錯誤
D選項本身語法也有錯誤， SUM(quantity_sold) 後需要使用別名才能正常建立檢視

【OCP】Oracle 11g OCP 1Z0-051 002

相關文章