Here is the structure and data of the CUST_TRANS table:
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A.
SELECT transdate + ’10’ FROM cust_trans;
B.
SELECT * FROM cust_trans WHERE transdate = ’01-01-07′;
C.
SELECT transamt FROM cust_trans WHERE custno > ’11’;
D.
SELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
E.
SELECT custno + ‘A’ FROM cust_trans WHERE transamt > 2000;
SELECT transdate + ’10′ FROM cust_trans;
SELECT transamt FROM cust_trans WHERE custno > ’11′;
SELECT * FROM cust_trans WHERE transdate=’01-JANUARY-07′;
can 11 be in single quotes?
given that Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table
then
why not B is correct?
“B” is correct only if the parameter NLS_DATE_FORMAT = ‘DD/MM/RRRR’.
In this case, the default date format dd-mm-rr, i.e, NLS_DATE_FORMAT = ‘DD-MON-RR’.
For test, execute ” alter session set NLS_DATE_FORMAT = ‘DD-MON-RR’ “
If you look at how data are displayed you can see ’01-JAN-07′. It seems that in the seesion is nls_date_format=’DD-MON-RR’ and then you get ORA-01843 – “not a valid monht”
I think that the question is a typo. Should be. Dates are stored in the default date format dd-MON-rr
Look at Question #93
Same Q but the default date format is dd-mon-rr
Home > Oracle Questions & Answers > 1Z0-051 > Which SQL statements would execute successfully?
NOT
Home > Oracle Questions & Answers > 1Z0-051 (v.1) > Which three SQL statements would execute successfully?
SQL> alter session set nls_date_format=’dd-mm-rr’;
Session altered.
SQL> select * from cust_trans where transdate=’01-01-07′;
CU TRANSDAT TRANSAMT
— ——– ———-
11 01-01-07 1000
SQL> select * from cust_trans where transdate=’01-JANUARY-07′;
CU TRANSDAT TRANSAMT
— ——– ———-
11 01-01-07 1000
Both B and D works for the above session parameter! can somebody explain why?
Yop, for me too. And couple of others that I tried without using TO_DATE, which I normaly use always! Looks like implicit conversion works better and better in Oracle which is cool except for some testing questions. 🙂
why the Answer E is not correct?
That’s bec you can´t add a string to a number
It will give an error as “Invalid Number”
Why A. is correct? I thought we can add number to date type but can we add a string to date type? Am I missing something? Thank you
Yes you can add number in date .
Have a look on below link
https://community.oracle.com/thread/58184?tstart=0
blabla
Hurrah, that’s what I was seeking for, what a stuff! existing here at this blog, thanks admin of this web site.|
IF ‘NLS_DATE_FORMAT’ = DD-MM-RR CORRECT QUESTIONS -> A,B,C,D
IF ‘NLS_DATE_FORMAT’ = DD-MON-RR CORRECT QUESTIONS -> A,C,D
SQL> select value from v$nls_parameters where parameter=’NLS_DATE_FORMAT’;
VALUE
—————————————————————-
DD-MM-RR
A)
SQL> SELECT hire_date + ’10’ FROM employees;
HIRE_DAT
——–
27-09-87
27-02-96
B)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
C)
SQL> SELECT first_name FROM employees WHERE custno > ’11’;
FIRST_NAME
——————–
eva
D)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JAN-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
OR
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JANEIRO-90′;
FIRST_NAME HIRE_DAT
——————– ——–
Alexander 03-01-90
SQL> select value from v$nls_parameters where parameter=’NLS_DATE_FORMAT’;
VALUE
—————————————————————-
DD-MON-RR
A)
SQL> SELECT hire_date + ’10’ FROM employees;
HIRE_DATE
———
27-SET-87
27-FEV-96
B)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′;
SELECT first_name,hire_date FROM employees WHERE hire_date=’03-01-90′
*
ERRO na linha 1:
ORA-01843: nÒo Ú um mÛs vßlido
C)
SQL> SELECT first_name FROM employees WHERE custno > ’11’;
FIRST_NAME
——————–
eva
D)
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JAN-90′;
FIRST_NAME HIRE_DATE
——————– ———
Alexander 03-JAN-90
OR
SQL> SELECT first_name,hire_date FROM employees WHERE hire_date=’03-JANEIRO-90′;