OCP-1Z0-051-V9.02-94题

94. Examine the structure of the PROGRAMS table:

name            Null        Type

PROG_ID         NOT NULL    NUMBER(3)

PROG_COST                  NUMBER(8,2)

START_DATE      NOT NULL    DATE

END_DATE                   DATE

Which two SQL statements would execute successfully? (Choose two.)

A. SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE)

FROM programs;

B. SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))

FROM programs;

C. SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')

FROM programs;

D. SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')

FROM programs;

Answer: AD

 

本题主要考NVL的参数的数据类型是否相同的问题。

NVL函数见http://blog.csdn.net/rlhua/article/details/11805803

 

1、根据题意创建PROGRAMS表

sh@TEST0910> create table PROGRAMS

  2  (prog_id number(3) not null,

  3  prog_cost number(8,2),

  4  start_date date not null,

  5  end_date date);

 

Table created.

2、插入一行数据测试用。

 

sh@TEST0910> select * from  PROGRAMS;

 

   PROG_ID  PROG_COST START_DAT END_DATE

---------- ---------- --------- ---------

         1       10.1 18-SEP-13 19-SEP-13

 

 

3、开始测试：

A答案：

sh@TEST0910> SELECT NVL(ADD_MONTHS(END_DATE,1),SYSDATE) FROM programs;

 

 

NVL(ADD_M

---------

19-OCT-13

 

ADD_MONTHS returns the date date plus integer months.

ADD_MONTHS 返回的是数据类型是Date和sysdate类型一致，故正确。

 

B答案：

sh@TEST0910> SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))

  2  FROM programs;

SELECT TO_DATE(NVL(SYSDATE-END_DATE,SYSDATE))

               *

ERROR at line 1:

ORA-01858: a non-numeric character was found where a numeric was expected

 

sh@TEST0910> select SYSDATE-END_DATE from  programs;

 

SYSDATE-END_DATE

----------------

       .9937963

 

SYSDATE-END_DATE 返回的是一个数值类型，与sysdate类型不一致。故错误。

 

C答案：

 

sh@TEST0910> SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')

  2   FROM programs;

SELECT NVL(MONTHS_BETWEEN(start_date,end_date),'Ongoing')

                                               *

ERROR at line 1:

ORA-01722: invalid number

 

sh@TEST0910> select MONTHS_BETWEEN(start_date,end_date) from  programs;

 

MONTHS_BETWEEN(START_DATE,END_DATE)

-----------------------------------

                         -.03225806

 

MONTHS_BETWEEN(start_date,end_date)返回是数值类型，与'Ongoing'类型不一致，故错误

 

使用to_char使他们类型一致，就是正确的了

sh@TEST0910> SELECT NVL( to_char(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')

  2  FROM programs;

 

NVL(TO_CHAR(MONTHS_BETWEEN(START_DATE,EN

----------------------------------------

-.03225806451612903225806451612903225806

 

D答案：NVL内的参数一致，故正确。

sh@TEST0910> SELECT NVL(TO_CHAR(MONTHS_BETWEEN(start_date,end_date)),'Ongoing')

  2   FROM programs;

 

NVL(TO_CHAR(MONTHS_BETWEEN(START_DATE,EN

----------------------------------------

-.03225806451612903225806451612903225806