（104）Examine the structure of the PROMOS table:

name Null Type

PROMO_ID NOT NULL NUMBER(3)

PROMO_NAME VARCHAR2(30)

PROMO_START_DATE NOT NULL DATE

PROMO_END_DATE DATE

You want to generate a report showing promo names and their duration (number of days).

If the PROMO_END_DATE has not been entered, the message 'ONGOING' should be displayed.

Which queries give the correct output? (Choose all that apply.)

A.

SELECT promo_name, TO_CHAR(NVL(promo_end_date -promo_start_date,'ONGOING'))

FROM promos;

B.

SELECT promo_name, COALESCE(TO_CHAR(promo_end_date - promo_start_date),'ONGOING')

FROM promos;

C.

SELECT promo_name, NVL(TO_CHAR(promo_end_date -promo_start_date),'ONGOING')

FROM promos;

D.

SELECT promo_name, DECODE(promo_end_date - promo_start_date, NULL, 'ONGOING',promo_end_date - promo_start_date)

FROM promos;

E.

SELECT promo_name, decode(coalesce(promo_end_date,promo_start_date), null,'ONGOING', promo_end_date - promo_start_date)

FROM promos;

答案：（B、C、D）

A（X），都是因为数据类型转换的原因

E（X），因为逻辑不对,不会显示ONGOING

create table promos

(

promo_id number(3) not null,

promo_name varchar2(30),

promo_start_date date not null,

promo_end_date date

);

insert into promos values(1, 'Promo_Name_1', to_date('2014-03-01', 'YYYY-MM-DD'), to_date('2014-03-03', 'YYYY-MM-DD'));

insert into promos values(2, 'Promo_Name_2', to_date('2014-03-02', 'YYYY-MM-DD'), null);

SELECT promo_name,

DECODE(promo_end_date - promo_start_date, NULL, 'ONGOING',promo_end_date - promo_start_date) as res

FROM promos;

ITPub博客

OCP 1Z0-051-104