Examine the structure of the TRANSACTIONS table:
name          Null          Type
TRANS_ID     NOT NULL     NUMBER(3) 
The safer , easier way to help you pass any IT exams.
54 / 99
CUST_NAME                  VARCHAR2(30)
TRANS_DATE                 DATE
TRANS_AMT                  NUMBER(10,2)
You want to display the transaction date and specify whether it is a weekday or weekend.
Evaluate the following two queries:
SQL>SELECT TRANS_DATE,CASE
WHEN TRIM(TO_CHAR(trans_date,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend'
ELSE 'weekday'
END "Day Type"
FROM transactions;
SQL>SELECT TRANS_DATE, CASE
WHEN TO_CHAR(trans_date,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'
ELSE  'weekend'
END "Day Type"FROM transactions;
Which statement is true regarding the above queries?
A. Both give wrong results.
B. Both give the correct  result.
C. Only the first query gives the correct result.
D. Only the  second query gives the correct result.

答案C

TO_CHAR是把日期或者数字转换为字符，to_date把数字转换成日期
SELECT to_date(SYSDATE,'hh24') FROM dual ---错
SELECT to_date(SYSDATE,'hh24') FROM dual --对

SELECT to_char(SYSDATE,'day') FROM dual 输出结果为小写
SELECT to_char(SYSDATE,'DAY') FROM dual 输出结果为大写

星期是不能用BETWEEN取区间
SELECT to_char(t,'DAY') FROM t
 WHERE to_char(t,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY'

没有结果返回


--------------------------------

NVL (expr1, expr2)->expr1为NULL，返回expr2；不为NULL，返回expr1。注意两者的类型要一致
NVL2 (expr1, expr2, expr3) ->expr1不为NULL，返回expr2；为NULL，返回expr3。expr2和expr3类型不同的话，expr3会转换为expr2的类型
NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

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;
Answer: D