SQL Data types

In relational model the data are stored in the form of tables. A table is composed of rows and columns. When we create a table we must specify a data type for each row of its columns. These data types define the domain of values that each column can take. Oracle provides a number of built in data types as well as several categories for user defined types that can be used as data types.

1. String Data type: In string we have CHAR and VARCHAR data types. Character data type store data which are words and free formatted text in the database character set.

a) Character Data Type: The CHAR data type specifies a fixed length character string. The syntax of CHAR data type declaration is

CHAR(n) - Fixed length character data 'n' characters 

b) VARCHAR2 data type: VARCHAR2 data type specifies a variable length character string. The syntax of VARCHAR2 data type declaration is 

VARCHAR2(n) - Variable length character of 'n' length.

2. Number Data type: The number data type stores zero, positive and negative fixed and floating point numbers. The syntax to store fixed point number is 

NUMBER(p,q) - Where 'P' is the total number of digits and 'q' is the number of digits to the right of decimal point.

3. DATE data type: The DATE data type is used to store the date and time information. For each date value, Oracle stores the century, year, month, date, hour, minute, and second information. The ANSI date literal contains no time portion, and must be specified in YYYY-MM-DD format.

4. TIME STAMP Data Type: The time stamp data type is used to store both date and time. It stores the year, month ad date of the date data types and also hour, minute and second values.

5. LOB data type: multi media data like sound, pictures and video need more storage space. The LOB data types such as BLOB, CLOB and BFILE allows us to store large block of data.