SQL Data Types
Data type is the way to define the range and type of values that can be stored in a field. There are various data types provided by SQL.
1. NUMBER
This data type is used to store the numeric data . It may or may not include decimal point.
The field of number data type can store a maximum value of 9.99´10124. This data type can be used in three forms.
a. NUMBER
In this form we don’t need to specify the number of digits to be stored in a particular field.
Example:
create table student(fees number);
b. NUMBER(P)
In this form we can specify the maximum number of digits which can be stored in a field of NUMBER type. Here P is called precision.
Example:
create table student (fees number (5));
In above example the field fees can contain a numeric value of maximum five digits i.e. 99999.
c. NUMBER(P,S)
In this form we can specify the maximum number of digits as well as the number of digits after decimal point which can be stored in a field of NUMBER type.
Here P is called precision and S is known as scale.
Example:
create table student (fees number (7,2));
In above example, the field fees can contain a numeric value of maximum seven digits and which can contain two digits after decimal point i.e. 99999.99 is the maximum value which can be stored in field named fees.
2. INTEGER
It is equivalent to NUMBER(38) in Oracle.
This means if we declare an INTEGER type field That means we have a NUMBER field with 38 digits and 0 decimal places.
Example:
create table student (fees INTEGER);
In above example, the field fees can contain a numeric value of Integer type.
3. BINARY_FLOAT
This data type is used to store real values in a field.NUMBER uses decimal precision but BINARY_FLOAT uses binary precision.
BINARY_FLOAT can perform arithmetic calculations faster and takes lesser memory.
This data type stores approximate representations of decimal values rather than the exact value.
It is a 32-bit single precision value.
Example:
create table student (distance BINARY_FLOAT);
In above example, the field distance can contain a numeric value of real type.
4. BINARY_DOUBLE
BINARY_DOUBLE also stores approximate values and performs faster calculations than NUMBER.
The difference between BINARY_FLOAT and BINARY_DOUBLE is that BINARY_DOUBLE is that the BINARY_DOUBLE is a 64-bit double precision value.
Example:
create table student (distance BINARY_DOUBLE);
In above example, the field distance can contain a numeric value of real type.
5. BOOLEAN
BOOLEAN values are used to store true or false.
It is only available in PL/SQL. To store boolean values in SQL, we can use a NUMBER(1) field.
6. CHAR
This data type is used to store alphabets, digits, special characters as well as spaces. This data type is used for fixed length string data . The field of this data type can store maximum of 255 characters.
We need to specify the size when we declare a field of this data type which is used to specify the maximum number of characters which can be stored in that field.
If the value stored in this type of field has lesser number of characters than the maximum size then blank spaces will be filled in rest of the size.
Example:
create table student( name char(20));
In this example name field can contain maximum of 20 characters.
7. NCHAR
The NCHAR data type is similar to the CHAR data type, except that it stores Unicode data.
It can store up to 2000 bytes and must be shorter than 2000 characters.
We need to specify the size when we declare a field of this data type which is used to specify the maximum number of characters which can be stored in that field.
Example:
create table student( name nchar(20));
In this example name field can contain maximum of 20 characters.
8. VARCHAR
This data type is also used to store alphabets, digits, special characters as well as spaces.
This data type is used for variable length string data . The field of this data type can store maximum of 2000 characters.
We need to specify the size when we declare a field of this data type which is used to specify the maximum number of characters which can be stored in the field.
If the value stored in this type of field has lesser number of characters than the maximum size then empty space will not be wasted but used in next field.
Example
create table student(name varchar(20));
In this example name field can contain maximum of 20 characters.
9. VARCHAR2
This data type is also used to store alphabets, digits, special characters as well as spaces. This data type is used for variable length string data . The field of this data type can store maximum of 4000 characters.
We need to specify the size when we declare a field of this data type which is used to specify the maximum number of characters which can be stored in the field.
If the value stored in this type of field has lesser number of characters than the maximum size then empty space will not be wasted but used in next field.
Example
create table student(name varchar2(30));
In this example name field can contain maximum of 30 characters.
10. NVARCHAR2
NVARCHAR2 is similar to VARCHAR2 data type.
NVARCHAR2 stores Unicode data. This data type is also used for variable length data.
We need to specify the size when we declare a field of this data type which is used to specify the maximum number of characters which can be stored in the field.
Example
create table student(name varchar2(30));
In this example name field can contain maximum of 30 characters.
11. DATE
This data type is also used to store date type data. Which can contain date as well as time.
The format for storing data in this field is DD-MON-YY where DD specified the day which can have values from 1 to 31.
MON specified the month which can take values from January to December but only first three characters in month name will be used like Jan,Feb,Mar………..Nov,Dec. and YY specifies the Year.
For Example we can store a date like 12-Nov-2005. We don’t need to specify the size with this data type as size is taken automatically by field.
Example
create table student(dateofbirth date);
12. LONG
This data type is also used to store variable length character string containing upto 2GB of data. Long data type values can’t be indexed and string operations can’t be applied on field of this data type.
Example
create table student(resume LONG);
13. LONG RAW/RAW
This data type is also used to store binary data such as digital picture or image The field of raw data type can contain maximum of 255 characters and filed of Long Raw data type can contain upto 2GB of data.
Example
create table student(resume RAW);
14. BLOB Data Type
This data type is used to store unstructured binary large objects.
It can store a lot of data in a single file, but it cannot store character data.
It has a maximum size of 4 GB.
We can specify the size if we want.
BLOB [ ( length [{K|M|G|}]
Here K specifies kilobytes, M specifies megabytes and G specifies gigabytes. If no size is specified, the default value is 4 GB.
It’s useful for storing images or audio files.
Example
create table student(photo BLOB(10k));
In above example, field photo can contain upto 10 Kilo Bytes of data.
15. CLOB Data Type
This data type is used to store large amounts of character data. It cannot store binary data.
It also has a maximum size of 4 GB.
We can also specify the size if we want as:
CLOB[( length [{K|M|G|}]
Here K specifies kilobytes, M specifies megabytes and G specifies gigabytes. If no size is specified, the default value is 4 GB.
We need to use it to store large amounts of text that cannot fit into VARCHAR2 field.
Example
create table student(publication CLOB);
In above example, field publication can contain upto 4GB Kilo Bytes of data.
16. BFILE
This data type is used to refer to other files on the server, which are outside the database system. It stores a pointer to a file.
They are read-only to Oracle. The maximum size is 4 GB.