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.)
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_sold number(10,2) not null);
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
CD选项，所创建视图不包含所有字段，视图不包含字段时默认为空，而SALES表所有字段皆不可为空，因此CD选项所创建的视图无法完成插入操作。
SQL> insert into v2 values(1,2,sysdate);
insert into v2 values(1,2,sysdate)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."SALES"."CHANNEL_ID")
而V3视图就可以
SQL> insert into v3 values(1,2034,sysdate,1,1,1); 1 row created. SQL> commit; Commit complete. SQL> select * from v3; PROD_ID CUST_ID TIME_ID CHANNEL_ID PROMO_ID QUANTITY_SOLD
---------- ---------- ------------------ ---------- ---------- -------------
1 2034 03-JAN-17 1 1 1
5. Which SQL statements would display the value 1890.55 as $1,890.55? (Choose three .)
A. SELECT TO_CHAR(1890.55,'$0G000D00')
FROM DUAL;
B. SELECT TO_CHAR(1890.55,'$9,999V99')
FROM DUAL;
C. SELECT TO_CHAR(1890.55,'$99,999D99')
FROM DUAL;
D. SELECT TO_CHAR(1890.55,'$99G999D00')
FROM DUAL;
E. SELECT TO_CHAR(1890.55,'$99G999D99')
FROM DUAL;
Answer: ADE
注意字母类比如D，与符号类，比如”,”，只能出现一个，不能同时出现，因此排除C选项。
SQL> select TO_CHAR(1890.55,'$99,999D99') from dual;
select TO_CHAR(1890.55,'$99,999D99') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model