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')
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;
答案:BCD
解析:题目说如果PROMO_END_DATE没有值那么显示ONGOING
A:错误,nvl参数类型必须相等
B:正确
C:正确
D:正确
E:如果promo_end_date,不为null,coalesce(promo_end_date,promo_start_date)那么返回promo_start_date,
结果是就是promo_end_date - promo_start_date
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')
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;
答案:BCD
解析:题目说如果PROMO_END_DATE没有值那么显示ONGOING
A:错误,nvl参数类型必须相等
B:正确
C:正确
D:正确
E:如果promo_end_date,不为null,coalesce(promo_end_date,promo_start_date)那么返回promo_start_date,
结果是就是promo_end_date - promo_start_date