一、原题
二、题目翻译
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;
答案:BCD
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;
答案:BCD
二、题目翻译
查看表结构
显示promo names和their duration,如果PROMO_END_DATE没有值,则显示'ONGOING'。
哪个查询能得出正确的结果(选择所有正确选项)?
三、题目解析显示promo names和their duration,如果PROMO_END_DATE没有值,则显示'ONGOING'。
哪个查询能得出正确的结果(选择所有正确选项)?
A选项不正确,因为隐式转换'ONGOING'为数值类型的会报错。
E选项不正确,不能输出正确的结果。
NVL和NULLIF,COALESCE的用法,详见:
http://blog.csdn.net/holly2008/article/details/25251513
DECODE的用法,详见:
http://blog.csdn.net/holly2008/article/details/23140591
E选项不正确,不能输出正确的结果。
NVL和NULLIF,COALESCE的用法,详见:
http://blog.csdn.net/holly2008/article/details/25251513
DECODE的用法,详见:
http://blog.csdn.net/holly2008/article/details/23140591