You want to display thedate, time, and transaction amount of transactions that where done before 12

noon. The value zeroshould be displayed for transactions where the transaction amount has not been

entered.

Which query gives therequired result

A. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),

TO_CHAR(trans_amt,'$99999999D99')

FROM transactions

WHERETO_NUMBER(TO_DATE(trans_date,'hh24')) < 12 ANDCOALESCE(trans_amt,NULL)<>NULL;

B. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),

NVL(TO_CHAR(trans_amt,'$99999999D99'),0)

FROM transactions

WHERETO_CHAR(trans_date,'hh24') < 12;

C. SELECTTO_CHAR(trans_date,'dd-mon-yyyy hh24:mi:ss'),

COALESCE(TO_NUMBER(trans_amt,'$99999999.99'),0)

FROM transactions

WHERETO_DATE(trans_date,'hh24') < 12;

D. SELECT TO_DATE(trans_date,'dd-mon-yyyy hh24:mi:ss'),

NVL2(trans_amt,TO_NUMBER(trans_amt,'$99999999.99'),0)

FROM transactions

WHERETO_DATE(trans_date,'hh24') < 12;

Answer: B

解析：题目的意思找出12点之前的事务的时间以及rans_amt，如果为空就显示为0

12点之前，正确的表示：

TO_CHAR(trans_date,'hh24')< 12

TO_DATE(trans_date,'hh24')中日期格式不正确 C D错误

如果为空就显示为0，A选项没有处理

103. Examine thestructure of the TRANSACTIONS table:

name Null Type

TRANS_ID NOT NULLNUMBER(3)

CUST_NAME VARCHAR2(30)

TRANS_DATE DATE

TRANS_AMT NUMBER(10,2)

You want to display thetransaction date and specify whether it is a weekday or weekend.

eva luate the followingtwo queries:

SQL>SELECTTRANS_DATE,CASE

WHENTRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'

ELSE 'weekday'

END "Day Type"

FROM transactions;

SQL>SELECTTRANS_DATE, CASE

WHENTO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'

ELSE 'weekend'

END "DayType"FROM transactions;

Which statement is trueregarding the above queries

A. Both give wrongresults.

B. Both give the correctresult.

C. Only the first querygives the correct result.

D. Only the second querygives the correct result.

Answer: C

解析：

BETWEEN 'MONDAY' AND'FRIDAY' 这里错误，不会按星期的顺序去一一比较

104. Examine thestructure of the PROMOS table:

name Null Type

PROMO_ID NOT NULLNUMBER(3)

PROMO_NAME VARCHAR2(30)

PROMO_START_DATE NOTNULL DATE

PROMO_END_DATE DATE

You want to generate areport showing promo names and their duration (number of days). If the

PROMO_END_DATE has notbeen entered, the message 'ONGOING' should be displayed.

Which queries give thecorrect output (Choose all that apply.)

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

FROM promos;

B. SELECTpromo_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;

Answer: BCD

解析：

A选项，nvl中两个表达式数据类型不一样，也不能隐式转换

E选项，coalesce(promo_end_date,promo_start_date) 返回第一个非空值，

但是有可能其中一个是空值

105. Examine thestructure of the PROMOS table:

name Null Type

PROMO_ID NOT NULLNUMBER(3)

PROMO_NAME VARCHAR2(30)

PROMO_START_DATE NOTNULL DATE

PROMO_END_DATE NOT NULLDATE

You want to display thelist of promo names with the message 'Same Day' for promos that started and

ended on the same day.