OCP 1Z0 051 105

105. 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    NOT NULL    DATE 
You want  to display  the  list of promo names with  the message  'Same Day'  for promos  that started and 
ended on the same day. 
Which query gives the correct output? 
A. SELECT promo_name, NVL(NULLIF(promo_start_date, promo_end_date), 'Same Day') 
FROM promos; 
B. SELECT promo_name, NVL(TRUNC(promo_end_date - promo_start_date), 'Same Day') 
FROM promos; 
C. SELECT promo_name, NVL2(TO_CHAR(TRUNC(promo_end_date-promo_start_date)), NULL,'Same 
Day') 
FROM promos; 
D. SELECT promo_name, DECODE((NULLIF(promo_start_date, promo_end_date)), NULL,'Same day') 
FROM promos; 

A 'Same Day' 不能转换为date
B 'Same Day' 不能转换为number
C 起止时间相同时 trunc结果为0，不是空

SQL> SELECT promo_name,
  2         promo_end_date,
  3         promo_start_date,
  4         trunc(promo_end_date - promo_start_date) AS trunc
  5    FROM (SELECT promo_name,
  6                 p.promo_end_date   AS promo_end_date,
  7                 p.promo_begin_date AS promo_start_date
  8            FROM sh.promotions p
  9           WHERE rownum <= 5) promos;
PROMO_NAME                     PROMO_END_DATE PROMO_START_DATE      TRUNC
------------------------------ -------------- ---------------- ----------
NO PROMOTION #                 9999-1-1       9999-1-1                  0
newspaper promotion #16-108    2001-1-23      2000-12-23               31
post promotion #20-232         1998-11-25     1998-9-25                61
newspaper promotion #16-349    1998-9-10      1998-7-10                62
internet promotion #14-471     2000-3-26      2000-2-26                29
5 rows selected

SQL> SELECT promo_name,
  2         promo_start_date,
  3         promo_end_date,
  4         decode((nullif(promo_start_date, promo_end_date)), NULL, 'Same day') AS same
  5    FROM (SELECT promo_name,
  6                 p.promo_end_date   AS promo_end_date,
  7                 p.promo_begin_date AS promo_start_date
  8            FROM sh.promotions p
  9           WHERE rownum <= 5) promos;
PROMO_NAME                     PROMO_START_DATE PROMO_END_DATE SAME
------------------------------ ---------------- -------------- --------
NO PROMOTION #                 9999-1-1         9999-1-1       Same day
newspaper promotion #16-108    2000-12-23       2001-1-23      
post promotion #20-232         1998-9-25        1998-11-25     
newspaper promotion #16-349    1998-7-10        1998-9-10      
internet promotion #14-471     2000-2-26        2000-3-26      
5 rows selected

Answer: D