首页 > 代码库 > OCP题解之SQL语法001

OCP题解之SQL语法001

2024-08-30 12:45:10   220人阅读

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 v3AS SELECT * FROM SALESWHERE cust_id = 2034WITH CHECK OPTION;B. CREATE VIEW v1AS SELECT * FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;C. CREATE VIEW v2AS SELECT prod_id, cust_id, time_id FROM SALESWHERE time_id <= SYSDATE - 2*365WITH CHECK OPTION;D. CREATE VIEW v4AS SELECT prod_id, cust_id, SUM(quantity_sold) FROM SALESWHERE time_id <= SYSDATE - 2*365GROUP BY prod_id, cust_idWITH 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

OCP题解之SQL语法001


联系
我们