OCP-1Z0-051-V9.02-103题

103. Examine the structure of the TRANSACTIONS table:

name          Null          Type

TRANS_ID      NOT NULL      NUMBER(3)

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.

Answer: C

 答案解析：

此处 BETWEEN 'MONDAY' AND 'FRIDAY'是指MONDA****后面的值到FRIDAY

这里是按照字符的ASCII码来排序的，此处的排序永远为假，即输出的都是weekend。

SQL>SELECT HIREDATE,CASE

WHEN TRIM(TO_CHAR(HIREDATE,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend' ELSE 'weekday' END "Day Type"

FROM emp;

实验验证，此处使用scott的emp表下的HIREDATE字段来做测试

scott@TEST0924> SELECT HIREDATE,CASE

  2  WHEN TRIM(TO_CHAR(HIREDATE,'DAY')) IN ('SATURDAY','SUNDAY') THEN 'weekend' ELSE 'weekday' END "Day Type"

  3  FROM emp;

HIREDATE           Day Typ

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

17-DEC-80          weekday

20-FEB-81          weekday

22-FEB-81          weekend

02-APR-81          weekday

28-SEP-81          weekday

01-MAY-81          weekday

09-JUN-81          weekday

19-APR-87          weekend

17-NOV-81          weekday

08-SEP-81          weekday

23-MAY-87          weekend

03-DEC-81          weekday

03-DEC-81          weekday

23-JAN-82          weekend

14 rows selected.

scott@TEST0924> SELECT HIREDATE, CASE 

  2   WHEN TO_CHAR(HIREDATE,'DAY') BETWEEN 'MONDAY' AND 'FRIDAY' THEN 'weekday'  ELSE   'weekend'

  3  END "Day Type"FROM emp;

HIREDATE           Day Typ

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

17-DEC-80          weekend

20-FEB-81          weekend

22-FEB-81          weekend

02-APR-81          weekend

28-SEP-81          weekend

01-MAY-81          weekend

09-JUN-81          weekend

19-APR-87          weekend

17-NOV-81          weekend

08-SEP-81          weekend

23-MAY-87          weekend

03-DEC-81          weekend

03-DEC-81          weekend

23-JAN-82          weekend

14 rows selected.