Oracle Database Part9 Interview Questions and Answers (Page 9)

Below are some important Oracle Database interview questions which are asked in most MNC company interviews for beginners or professionals.

81. How To Write Date and Time Literals in Oracle?
Date and time literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format 03-OCT-07
SELECT TIMESTAMP '0227-01-31 09:26:50.124' FROM DUAL 31-JAN-07 09.26.50.124000000 AM
-- This is ANSI format

82. How To Write Date and Time Interval Literals in Oracle?
Date and time interval literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' + INTERVAL '123-2' YEAR(3) TO MONTH FROM DUAL
-- 123 years and 2 months is added to 2002-10-03 03-DEC-25
SELECT DATE '2002-10-03' + INTERVAL '123' YEAR(3) FROM DUAL
-- 123 years is added to 2002-10-03 03-OCT-25

SELECT DATE '2002-10-03' + INTERVAL '299' MONTH(3) FROM DUAL
-- 299 months years is added to 2002-10-03 03-SEP-27

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12:10.222' DAY TO SECOND(3) FROM DUAL 04-FEB-97 02.39.00.346000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '4 5:12' DAY TO MINUTE FROM DUAL 04-FEB-97 02.38.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400 5' DAY(3) TO HOUR FROM DUAL 07-MAR-98 02.26.50.124000000 PM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '400' DAY(3) FROM DUAL 07-MAR-98 09.26.50.124000000 AM

SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '11:12:10.2222222' HOUR TO SECOND(7) FROM DUAL
31-JAN-97 08.39.00.346222200 PM

83. How To Convert Numbers to Characters in Oracle?
You can convert numeric values to characters by using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(4123.4570) FROM DUAL 123.457
SELECT TO_CHAR(4123.457, '$9,999,999.99') FROM DUAL $4,123.46
SELECT TO_CHAR(-4123.457, '9999999.99EEEE') FROM DUAL -4.12E+03

84. How To Convert Characters to Numbers in Oracle?
You can convert characters to numbers by using the TO_NUMBER() function as shown in the following examples:
SELECT TO_NUMBER('4123.4570') FROM DUAL 4123.457
SELECT TO_NUMBER(' $4,123.46','$9,999,999.99') FROM DUAL 4123.46
SELECT TO_NUMBER(' -4.12E+03') FROM DUAL -4120

85. How To Convert Dates to Characters in Oracle?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
-- SYSDATE returns the current date 07-MAY-2006

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD') FROM DUAL;
2006/05/07

SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
MAY 07, 2006

SELECT TO_CHAR(SYSDATE, 'fmMONTH DD, YYYY') FROM DUAL;
May 7, 2006

SELECT TO_CHAR(SYSDATE, 'fmDAY, MONTH DD, YYYY') FROM DUAL;
SUNDAY, MAY 7, 2006

86. How To Convert Characters to Dates in Oracle?
You can convert dates to characters using the TO_DATE() function as shown in the following examples:
SELECT TO_DATE('07-MAY-2006', 'DD-MON-YYYY') FROM DUAL;
07-MAY-06

SELECT TO_DATE('2006/05/07 ', 'YYYY/MM/DD') FROM DUAL;
07-MAY-06

SELECT TO_DATE('MAY 07, 2006', 'MONTH DD, YYYY') FROM DUAL;
07-MAY-06

SELECT TO_DATE('May 7, 2006', 'fmMONTH DD, YYYY') FROM DUAL;
07-MAY-06

SELECT TO_DATE('SUNDAY, MAY 7, 2006', 'fmDAY, MONTH DD, YYYY') FROM DUAL;
07-MAY-06

87. How To Convert Times to Characters in Oracle?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
04:49:49

SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS.FF') FROM DUAL;
-- Error: SYSDATE has no fractional seconds

SELECT TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF9') FROM DUAL;
16:52:57.847000000

SELECT TO_CHAR(SYSDATE, 'SSSSS') FROM DUAL;
-- Seconds past midnight
69520
88. How To Convert Characters to Times in Oracle?
You can convert dates to characters using the TO_CHAR() function as shown in the following examples:

SELECT TO_CHAR(TO_DATE('04:49:49', 'HH:MI:SS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
-- Default date is the first day of the current month
01-MAY-2006 04:49:49

SELECT TO_CHAR(TO_TIMESTAMP('16:52:57.847000000', 'HH24:MI:SS.FF9'), 'DD-MON-YYYY HH24:MI:SS.FF9')
FROM DUAL;
01-MAY-2006 16:52:57.847000000

SELECT TO_CHAR(TO_DATE('69520', 'SSSSS'), 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
01-MAY-2006 19:18:40

89. What Is NULL value in Oracle?
NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most operations has special rules when NULL is involved. The tutorial exercise below shows you some examples:
SET NULL 'NULL'; -- Make sure NULL is displayed
SELECT NULL FROM DUAL;
N
-
N
U
L
L

SELECT NULL + NULL FROM DUAL;
NULL+NULL
----------
NULL

SELECT NULL + 7 FROM DUAL;
NULL+7
----------
NULL

SELECT NULL * 7 FROM DUAL;
NULL*7
----------
NULL

SELECT NULL || 'A' FROM DUAL;
N
-
A

SELECT NULL + SYSDATE FROM DUAL;
NULL+SYSD
---------
NULL

90. How To Use NULL as Conditions in Oracle?
If you want to compare values against NULL as conditions, you should use the "IS NULL" or "IS NOT NULL" operator. Do not use "=" or "<>" against NULL. The sample script below shows you some good examples:
SELECT 'A' IS NULL FROM DUAL;
-- Error: Boolean is not data type.
-- Boolean can only be used as conditions

SELECT CASE WHEN 'A' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE

SELECT CASE WHEN '' IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN 0 IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE

SELECT CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE

SELECT CASE WHEN 'A' = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Do not use "="
FALSE

SELECT CASE WHEN 'A' <> NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Do not use "<>"
FALSE

SELECT CASE WHEN NULL = NULL THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Do not use "="
FALSE

More Questions & Answers :-

Page1  Page2  Page3  Page4  Page5  Page6  
Page7  Page8  Page9  Page10