Which statement is true regarding the above queries?

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?

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.

Show Hint

Leave a Reply 10

Your email address will not be published. Required fields are marked *

Daniel

Daniel

Both give the correct result, I have run them..

Reply

Justyna

Justyna

my tests:
select start_date,to_char(start_date,’DAY’),
case
when to_char(start_date,’DAY’) between ‘MONDAY’ and ‘FRIDAY’ then ‘weekday’
else ‘weekend’
end “Dzien”
from ord_items
order by to_char(start_date,’DAY’);

START_DATE TO_CHAR(START_DATE,’DAY’) Dzien
———- ————————- ——-
20-AUG-12 MONDAY weekend
13-AUG-12 MONDAY weekend
04-AUG-12 SATURDAY weekend
04-AUG-12 SATURDAY weekend
12-AUG-12 SUNDAY weekend
04-SEP-12 TUESDAY weekend
29-AUG-12 WEDNESDAY weekend

7 rows selected

There is no letters in the alphabet between M and F (rather between F and M) and there is no TRIM function used.

select start_date,to_char(start_date,’DAY’),
case
when trim(to_char(start_date,’DAY’)) in (‘SATURDAY’,’SUNDAY’) then ‘weekend’
else ‘weekday’
end “Dzien”
from ord_items
order by to_char(start_date,’DAY’);

START_DATE TO_CHAR(START_DATE,’DAY’) Dzien
———- ————————- ——-
20-AUG-12 MONDAY weekday
13-AUG-12 MONDAY weekday
04-AUG-12 SATURDAY weekend
04-AUG-12 SATURDAY weekend
12-AUG-12 SUNDAY weekend
04-SEP-12 TUESDAY weekday
29-AUG-12 WEDNESDAY weekday

7 rows selected

Reply

Ritam Tiwari

Ritam Tiwari

thanks…good explanation

Reply

charu

charu

can any one explain how trim is used here. Trim is used to remove character at head or trail or both only know

Reply

Sayed

Sayed

TRIM removes the leading empty spaces. MONDAY AND WEDNESDAY have different size. So TRIM makes both the days like Monday, 15. Wednesday, 15. Without TRIM it looks Monday , 15. Wednesday, 15.

Reply

charu

charu

Thanks Mr.Sayed

Reply

john

john

iam still not getting ur exaplantion..plz explain with example .

Reply

john

john

syed plz explain why trim is ued..with example.

Reply

Bartosz

Bartosz

both are correct

Reply