Premium

Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)



Question : What value is returned after executing the following statement?
TO_CHAR(sysdate, 'Month DD, YYYY')


: What value is returned after executing the following statement?
1. '07 09, 2015'
2. 'July 9, 2015'
3. 'Jul 09, 2015'
4. 'July 09, 2015'


Correct Answer : Get Lastest Questions and Answer :

Explanation: The following are date examples for the TO_CHAR function.

TO_CHAR(sysdate, 'yyyy/mm/dd')
Result: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')
Result: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')
Result: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'
You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'





Question : Which of the following query will sort the days of the week in order

A. SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day"
FROM emp
ORDER BY "Day";

B. SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day"
FROM emp
ORDER BY "Day";

: Which of the following query will sort the days of the week in order
1. A
2. B
3. Both A and B
4. None of A and B


Correct Answer : Get Lastest Questions and Answer :

Explanation: SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day"
FROM emp
ORDER BY "Day";
In the above SQL, the fmDay format mask used in the TO_CHAR function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day"
FROM emp
ORDER BY "Day";





Question : What value is returned after executing the following statement?
TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')


: What value is returned after executing the following statement?
1. '13-DEC-03 10.13.18.000000000 '
2. '13-DEC-03 10.13.18 AM'
3. '13-DEC-03 10.13.18.000000000 AM'
4. '13-DEC-03 22.13.18.000000000 PM'


Correct Answer : Get Lastest Questions and Answer :

Explanation: The syntax for the Oracle/PLSQL TO_TIMESTAMP function is:

TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )
Parameters or Arguments

string1 is the string that will be converted to a timestamp.

format_mask is optional. This is the format that will be used to convert string1 to a timestamp.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter Explanation
YYYY 4-digit year
MM Month (01-12; JAN = 01).
MON Abbreviated name of month.
MONTH Name of month, padded with blanks to length of 9 characters.
DD Day of month (1-31).
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
Let's look at some Oracle TO_TIMESTAMP function examples and explore how to use the TO_TIMESTAMP function in Oracle/PLSQL.

For example:

TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')
would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')
would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.




Related Questions

Question : Select the clauses of the SELECT statement which help for selection and projection?
 : Select the clauses of the SELECT statement which help for selection and projection?
1. SELECT, FROM
2. ORDER BY, WHERE
3. Access Mostly Uused Products by 50000+ Subscribers
4. SELECT, ORDER BY


Question : Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM HADOOPEXAM:
 : Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM HADOOPEXAM:
1. WHERE JOIN_DATE IN ('02-JUN-2004');
2. WHERE USER_ID IN ('1000','4000','2000');
 3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE RANK BETWEEN 0.1 AND 0.5;



Question : Choose the WHERE clause that extracts the USER_NAME values containing the character literal "ab" from the DEPARTMENTS table.
The SELECT and FROM clauses are SELECT USER_NAME FROM DEPARTMENTS:
 : Choose the WHERE clause that extracts the USER_NAME values containing the character literal
1. WHERE USER_NAME IN ('%a%b');
2. WHERE USER_NAME LIKE '%ab%';
 3. Access Mostly Uused Products by 50000+ Subscribers
4. WHERE USER_NAME CONTAINS 'a%b';




Question : Select the statement about the ORDER BY clause which is not correct.
 : Select the statement about the ORDER BY clause which is not correct.
1. When using the ORDER BY clause, it always appears as the last clause in a SELECT statement.
2. The ORDER BY clause may appear in a SELECT statement that does not contain a WHERE clause.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Positional sorting is accomplished by specifying the numeric position of a column as it appears in the SELECT list, in the ORDER BY clause.





Question : If a table is created without specifying a schema, in which schema will it be?
: If a table is created without specifying a schema, in which schema will it be?
1. It will be an orphaned table, without a schema.
2. The creation will fail.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be in the PUBLIC schema.


Question : How can you delete the values from one column of every row in a table? (Choose the best answer.)
 : How can you delete the values from one column of every row in a table? (Choose the best answer.)
1. Use the DELETE COLUMN command.
2. Use the TRUNCATE COLUMN command.
 3. Access Mostly Uused Products by 50000+ Subscribers
4. Use the DROP COLUMN command.