OCP 1Z0 051 104

来源：互联网发布：家庭教育网络平台编辑：程序博客网时间：2024/05/14 13:03

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;

还是隐式转换的问题

SQL> SELECT promo_name,  2         to_char(nvl(promo_end_date - promo_start_date, 'ONGOING')) AS duration  3    FROM (SELECT promo_name,  4                 p.promo_end_date   AS promo_end_date,  5                 p.promo_begin_date AS promo_start_date  6            FROM sh.promotions p  7           WHERE rownum <= 5) promos  8  /SELECT promo_name,       to_char(nvl(promo_end_date - promo_start_date, 'ONGOING')) AS duration  FROM (SELECT promo_name,               p.promo_end_date   AS promo_end_date,               p.promo_begin_date AS promo_start_date          FROM sh.promotions p         WHERE rownum <= 5) promosORA-01722: invalid number

字符串放前面就不会报错，不要看数据，这儿只是演示顺序

SQL> SELECT promo_name,  2         to_char(nvl('ONGOING', promo_end_date - promo_start_date)) AS duration  3    FROM (SELECT promo_name,  4                 p.promo_end_date   AS promo_end_date,  5                 p.promo_begin_date AS promo_start_date  6            FROM sh.promotions p  7           WHERE rownum <= 5) promos;PROMO_NAME                     DURATION------------------------------ ----------------------------------------NO PROMOTION #                 ONGOINGnewspaper promotion #16-108    ONGOINGpost promotion #20-232         ONGOINGnewspaper promotion #16-349    ONGOINGinternet promotion #14-471     ONGOING5 rows selectedExecuted in 0.031 seconds

所以D是对的

SQL> SELECT promo_name,  2         decode(promo_end_date - promo_start_date,  3                NULL,  4                'ONGOING',  5                promo_end_date - promo_start_date)  6    FROM (SELECT promo_name,  7                 p.promo_end_date   AS promo_end_date,  8                 p.promo_begin_date AS promo_start_date  9            FROM sh.promotions p 10           WHERE rownum <= 5) promos;PROMO_NAME                     DECODE(PROMO_END_DATE-PROMO_ST------------------------------ ----------------------------------------NO PROMOTION #                 0newspaper promotion #16-108    31post promotion #20-232         61newspaper promotion #16-349    62internet promotion #14-471     295 rows selected

BC都是显示转换

SQL> SELECT promo_name,  2         coalesce(to_char(promo_end_date - promo_start_date), 'ONGOING') AS duration  3    FROM (SELECT promo_name,  4                 p.promo_end_date   AS promo_end_date,  5                 p.promo_begin_date AS promo_start_date  6            FROM sh.promotions p  7           WHERE rownum <= 5) promos;PROMO_NAME                     DURATION------------------------------ ----------------------------------------NO PROMOTION #                 0newspaper promotion #16-108    31post promotion #20-232         61newspaper promotion #16-349    62internet promotion #14-471     295 rows selectedSQL> SQL> SELECT promo_name,  2         nvl(to_char(promo_end_date - promo_start_date), 'ONGOING') AS duration  3    FROM (SELECT promo_name,  4                 p.promo_end_date   AS promo_end_date,  5                 p.promo_begin_date AS promo_start_date  6            FROM sh.promotions p  7           WHERE rownum <= 5) promos;PROMO_NAME                     DURATION------------------------------ ----------------------------------------NO PROMOTION #                 0newspaper promotion #16-108    31post promotion #20-232         61newspaper promotion #16-349    62internet promotion #14-471     295 rows selected

E逻辑不对

Answer: BCD

0 0