Collection OF 28 FAQ Oracle SQL Language Basics

A collection of 28 FAQs on Oracle SQL language basics. Clear answers are provided with tutorial exercises on data types, data literals, date and time values, data and time intervals, converting to dates and times, NULL values, pattern matches. Topics included in this FAQ are:

1. What Is SQL?
2. How Many Categories of Data Types?
3. What Are the Oracle Built-in Data Types?
4. What Are the Differences between CHAR and NCHAR?
5. What Are the Differences between CHAR and VARCHAR2?
6. What Are the Differences between NUMBER and BINARY_FLOAT?
7. What Are the Differences between DATE and TIMESTAMP?
8. What Are the Differences between INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND?
9. What Are the Differences between BLOB and CLOB?
10. What Are the ANSI Data Types Supported in Oracle?
11. How To Write Text Literals?
12. How To Write Numeric Literals?
13. How To Write Date and Time Literals?
14. How To Write Date and Time Interval Literals?
15. How To Convert Numbers to Characters?
16. How To Convert Characters to Numbers?
17. How To Convert Dates to Characters?
18. How To Convert Characters to Dates?
19. How To Convert Times to Characters?
20. How To Convert Characters to Times?
21. What Is NULL?
22. How To Use NULL as Conditions?
23. How To Concatenate Two Text Values?
24. How To Increment Dates by 1?
25. How To Calculate Date and Time Differences?
26. How To Use IN Conditions?
27. How To Use LIKE Conditions?
28. How To Use Regular Expression in Pattern Match Conditions?

Sample scripts used in this FAQ can be executed with SQL*Plus to any Oracle database server.

What Is SQL?

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

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.

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

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.

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.

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.

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.

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.

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.

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

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!

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 

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

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  

How To Convert Numbers to Characters?

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

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

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 

How To Convert Characters 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 

How To Convert Times to Characters?

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

How To Convert Characters 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

What Is NULL?

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

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

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 

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. 

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 

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

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   

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;