1. What Is SQL Standard?
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
SQL became an ANSI standard, called SQL-87, in 1986.
ISO made a major revision, called SQL-92, in 1992.
The latest revision is SQL:2008.
2. How Many Categories of Data Types?
Oracles supports the following categories of data types:
◉ Oracle Built-in Datatypes.
◉ ANSI, DB2, and SQL/DS Datatypes.
◉ User-Defined Types.
◉ Oracle-Supplied Types.
3. What Are the Oracle Built-in Data Types?
There are 20 Oracle built-in data types, divided into 6 groups:
◉ Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
◉ Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
◉ Long and Row Datatypes - LONG, LONG RAW, RAW
◉ Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
◉ Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
◉ Row ID Datatypes - ROWID, UROWID
4. What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
◉ CHAR's size is specified in bytes by default.
◉ NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
◉ NCHAR stores characters in Unicode.
5. What Are the Differences between CHAR and VARCHAR2?
The main differences between CHAR and VARCHAR2 are:
◉ CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
◉ VARCHAR2 stores values in variable lengths. Values are not padded with any characters.
6. What Are the Differences between NUMBER and BINARY_FLOAT?
The main differences between NUMBER and BINARY_FLOAT are:
◉ NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
◉ BINARY_FLOAT stores values as single precision floating-point numbers.
7. What Are the Differences between DATE and TIMESTAMP?
The main differences between DATE and TIMESTAMP are:
◉ DATE stores values as century, year, month, date, hour, minute, and second.
◉ TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.
8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
◉ INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
◉ INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.
9. What Are the Differences between BLOB and CLOB?
The main differences between BLOB and CLOB are:
◉ BLOB stores values as LOB (Large OBject) in bitstreams.
◉ CLOB stores values as LOB (Large OBject) in character steams.
10. What Are the ANSI Data Types Supported in Oracle?
The following ANSI data types are supported in Oracle:
◉ CHARACTER(n) / CHAR(n)
◉ CHARACTER VARYING(n) / CHAR VARYING(n)
◉ NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
◉ NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n)
◉ NUMERIC(p,s)
◉ DECIMAL(p,s)
◉ INTEGER / INT
◉ SMALLINT
◉ FLOAT
◉ DOUBLE PRECISION
◉ REAL
11. How To Write Text Literals?
There are several ways to write text literals as shown in the following samples:
SELECT 'FYICenter.com' FROM DUAL -- The most common format
FYICenter.com
SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped
It's Sunday!
SELECT N'Allo, C''est moi.' FROM DUAL -- National chars
Allo, C'est moi.
SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter
It's Sunday!
12. How To Write Numeric Literals?
Numeric literals can coded as shown in the following samples:
SELECT 255 FROM DUAL -- An integer
255
SELECT -6.34 FROM DUAL -- A regular number
-6.34
SELECT 2.14F FROM DUAL -- A single-precision floating point
2.14
SELECT -0.5D FROM DUAL -- A double-precision floating point
-0.5
13. How To Write Date and Time Literals?
Date and time literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format
03-OCT-02
SELECT TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL
31-JAN-97 09.26.50.124000000 AM
-- This is ANSI format
14. How To Write Date and Time Interval Literals?
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
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97 09.27.20.247500000 AM
15. How To Convert Numbers to Character Strings?
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
16. How To Convert Characters to Numbers?
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
17. How To Convert Dates to Characters?
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
18. How To Convert Character Strings to Dates?
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
19. How To Convert Times to Character Strings?
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
20. How To Convert Character Strings to Times?
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
21. What Is NULL Value?
NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most opertations 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
22. How To Use NULL as Conditions?
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
23. How To Concatenate Two Text Values?
There are two ways to concatenate two text values together:
◉ CONCAT() function.
◉ '||' operation.
Here is some examples on how to use them:
SELECT 'FYI' || 'Center' || '.com' FROM DUAL;
FYICenter.com
SELECT CONCAT('FYICenter','.com') FROM DUAL;
FYICenter.com
24. How To Increment Dates by 1?
If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:
SELECT TO_DATE('30-APR-06') + 1 FROM DUAL;
-- Adding 1 day to a date
01-MAY-06
SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06')
FROM DUAL;
-- Taking date differences
1
SELECT SYSTIMESTAMP + 1 FROM DUAL;
-- The number you add is always in days.
08-MAY-06
SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3')
FROM DUAL;
-- Error: Adding 1 to a timestamp makes it a date.
25. How To Calculate Date and Time Differences?
If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
DAY(9) TO SECOND FROM DUAL;
-- 39901 days and some seconds
39901 7:26:7.0
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
DAY(9) TO SECOND
FROM DUAL;
-- 39901 days and some fractional seconds
39901 7:26:7.723000000
26. How To Use IN Conditions?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the script below:
SELECT CASE WHEN 3 IN (1,2,3,5) THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE
SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
27. How To Use LIKE Conditions?
LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:
◉ '_' is used in the pattern to match any one character.
◉ '%' is used in the pattern to match any zero or more characters.
◉ ESCAPE clause is used to provide the escape character in the pattern.
The following script provides you some good pattern matching examples:
SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Case sensitive by default
FALSE
SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
28. How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().
The following script provides you some good examples:
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*fyi.*',
'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',
'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.
SQL became an ANSI standard, called SQL-87, in 1986.
ISO made a major revision, called SQL-92, in 1992.
The latest revision is SQL:2008.
2. How Many Categories of Data Types?
Oracles supports the following categories of data types:
◉ Oracle Built-in Datatypes.
◉ ANSI, DB2, and SQL/DS Datatypes.
◉ User-Defined Types.
◉ Oracle-Supplied Types.
3. What Are the Oracle Built-in Data Types?
There are 20 Oracle built-in data types, divided into 6 groups:
◉ Character Datatypes - CHAR, NCHAR, NVARCHAR2, VARCHAR2
◉ Number Datatypes - NUMBER, BINARY_FLOAT, BINARY_DOUBLE
◉ Long and Row Datatypes - LONG, LONG RAW, RAW
◉ Datetime Datatypes - DATE, TIMESTAMP, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
◉ Large Object Datatypes - BLOB, CLOB, NCLOB, BFILE
◉ Row ID Datatypes - ROWID, UROWID
4. What Are the Differences between CHAR and NCHAR?
Both CHAR and NCHAR are fixed length character data types. But they have the following differences:
◉ CHAR's size is specified in bytes by default.
◉ NCHAR's size is specified in characters by default. A character could be 1 byte to 4 bytes long depending on the character set used.
◉ NCHAR stores characters in Unicode.
5. What Are the Differences between CHAR and VARCHAR2?
The main differences between CHAR and VARCHAR2 are:
◉ CHAR stores values in fixed lengths. Values are padded with space characters to match the specified length.
◉ VARCHAR2 stores values in variable lengths. Values are not padded with any characters.
6. What Are the Differences between NUMBER and BINARY_FLOAT?
The main differences between NUMBER and BINARY_FLOAT are:
◉ NUMBER stores values as fixed-point numbers using 1 to 22 bytes.
◉ BINARY_FLOAT stores values as single precision floating-point numbers.
7. What Are the Differences between DATE and TIMESTAMP?
The main differences between DATE and TIMESTAMP are:
◉ DATE stores values as century, year, month, date, hour, minute, and second.
◉ TIMESTAMP stores values as year, month, day, hour, minute, second, and fractional seconds.
8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
The main differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND are:
◉ INTERVAL YEAR TO MONTH stores values as time intervals at the month level.
◉ INTERVAL DAY TO SECOND stores values as time intervals at the fractional seconds level.
9. What Are the Differences between BLOB and CLOB?
The main differences between BLOB and CLOB are:
◉ BLOB stores values as LOB (Large OBject) in bitstreams.
◉ CLOB stores values as LOB (Large OBject) in character steams.
10. What Are the ANSI Data Types Supported in Oracle?
The following ANSI data types are supported in Oracle:
◉ CHARACTER(n) / CHAR(n)
◉ CHARACTER VARYING(n) / CHAR VARYING(n)
◉ NATIONAL CHARACTER(n) / NATIONAL CHAR(n) / NCHAR(n)
◉ NATIONAL CHARACTER VARYING(n) / NATIONAL CHAR VARYING(n) / NCHAR VARYING(n)
◉ NUMERIC(p,s)
◉ DECIMAL(p,s)
◉ INTEGER / INT
◉ SMALLINT
◉ FLOAT
◉ DOUBLE PRECISION
◉ REAL
11. How To Write Text Literals?
There are several ways to write text literals as shown in the following samples:
SELECT 'FYICenter.com' FROM DUAL -- The most common format
FYICenter.com
SELECT 'It''s Sunday!' FROM DUAL -- Single quote escaped
It's Sunday!
SELECT N'Allo, C''est moi.' FROM DUAL -- National chars
Allo, C'est moi.
SELECT Q'/It's Sunday!/' FROM DUAL -- Your own delimiter
It's Sunday!
12. How To Write Numeric Literals?
Numeric literals can coded as shown in the following samples:
SELECT 255 FROM DUAL -- An integer
255
SELECT -6.34 FROM DUAL -- A regular number
-6.34
SELECT 2.14F FROM DUAL -- A single-precision floating point
2.14
SELECT -0.5D FROM DUAL -- A double-precision floating point
-0.5
13. How To Write Date and Time Literals?
Date and time literals can coded as shown in the following samples:
SELECT DATE '2002-10-03' FROM DUAL -- ANSI date format
03-OCT-02
SELECT TIMESTAMP '1997-01-31 09:26:50.124' FROM DUAL
31-JAN-97 09.26.50.124000000 AM
-- This is ANSI format
14. How To Write Date and Time Interval Literals?
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
SELECT TIMESTAMP '1997-01-31 09:26:50.124'
+ INTERVAL '30.12345' SECOND(2,4) FROM DUAL
31-JAN-97 09.27.20.247500000 AM
15. How To Convert Numbers to Character Strings?
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
16. How To Convert Characters to Numbers?
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
17. How To Convert Dates to Characters?
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
18. How To Convert Character Strings to Dates?
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
19. How To Convert Times to Character Strings?
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
20. How To Convert Character Strings to Times?
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
21. What Is NULL Value?
NULL is a special value representing "no value" in all data types. NULL can be used on in operations like other values. But most opertations 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
22. How To Use NULL as Conditions?
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
23. How To Concatenate Two Text Values?
There are two ways to concatenate two text values together:
◉ CONCAT() function.
◉ '||' operation.
Here is some examples on how to use them:
SELECT 'FYI' || 'Center' || '.com' FROM DUAL;
FYICenter.com
SELECT CONCAT('FYICenter','.com') FROM DUAL;
FYICenter.com
24. How To Increment Dates by 1?
If you have a date, and you want to increment it by 1. You can do this by adding the date with a date interval. You can also do this by adding the number 1 directly on the date. The tutorial example below shows you how to adding numbers to dates, and take date differences:
SELECT TO_DATE('30-APR-06') + 1 FROM DUAL;
-- Adding 1 day to a date
01-MAY-06
SELECT TO_DATE('01-MAY-06') - TO_DATE('30-APR-06')
FROM DUAL;
-- Taking date differences
1
SELECT SYSTIMESTAMP + 1 FROM DUAL;
-- The number you add is always in days.
08-MAY-06
SELECT TO_CHAR(SYSTIMESTAMP+1,'DD-MON-YYYY HH24:MI:SS.FF3')
FROM DUAL;
-- Error: Adding 1 to a timestamp makes it a date.
25. How To Calculate Date and Time Differences?
If you want to know how many years, months, days and seconds are there between two dates or times, you can use the date and time interval expressions: YEAR ... TO MONTH and DAY ... TO SECOND. The tutorial exercise below gives you some good examples:
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_DATE('01-MAY-2006 16:52:57','DD-MON-YYYY HH24:MI:SS')
-
TO_DATE('31-JAN-1897 09:26:50','DD-MON-YYYY HH24:MI:SS'))
DAY(9) TO SECOND FROM DUAL;
-- 39901 days and some seconds
39901 7:26:7.0
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
YEAR(4) TO MONTH FROM DUAL;
-- 109 years and 3 months
109-3
SELECT
(TO_TIMESTAMP('01-MAY-2006 16:52:57.847',
'DD-MON-YYYY HH24:MI:SS.FF3') -
TO_TIMESTAMP('31-JAN-1897 09:26:50.124',
'DD-MON-YYYY HH24:MI:SS.FF3'))
DAY(9) TO SECOND
FROM DUAL;
-- 39901 days and some fractional seconds
39901 7:26:7.723000000
26. How To Use IN Conditions?
An IN condition is single value again a list of values. It returns TRUE, if the specified value is in the list. Otherwise, it returns FALSE.
Some examples are given in the script below:
SELECT CASE WHEN 3 IN (1,2,3,5) THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN 3 NOT IN (1,2,3,5) THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
FALSE
SELECT CASE WHEN 'Y' IN ('F','Y','I') THEN
'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
27. How To Use LIKE Conditions?
LIKE condition is also called pattern patch. There 3 main rules on using LIKE condition:
◉ '_' is used in the pattern to match any one character.
◉ '%' is used in the pattern to match any zero or more characters.
◉ ESCAPE clause is used to provide the escape character in the pattern.
The following script provides you some good pattern matching examples:
SELECT CASE WHEN 'FYICenter.com' LIKE '%Center%'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN 'FYICenter.com' LIKE '%CENTER%'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
-- Case sensitive by default
FALSE
SELECT CASE WHEN 'FYICenter.com' LIKE '%Center_com'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN '100% correct' LIKE '100\% %' ESCAPE '\'
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
28. How To Use Regular Expression in Pattern Match Conditions?
If you have a pattern that is too complex for LIKE to handle, you can use the regular expression pattern patch function: REGEXP_LIKE().
The following script provides you some good examples:
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*fyi.*',
'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '.*com$',
'i') THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
SELECT CASE WHEN REGEXP_LIKE ('FYICenter.com', '^F.*','i')
THEN 'TRUE' ELSE 'FALSE' END FROM DUAL;
TRUE
Posts
0 comments:
Post a Comment