Data Types in SQL

• Data Type is used to specify which type of data a column can hold.
• It tells how much memory should be allocated.

SQL provides following Data Types:

Number Related Data Types:

Number(p):
• It is used to hold integers.
• “p” stands for “Precision”. Precision means, Total Number of Digits”.
• Valid range of p is: 1 to 38.
• Default size is: 38.
• If we don’t specify the precision default size will be taken.
• Default Valid Range of values: -99999……99 38 digits to
99999……….99 38 digits.

Example:
Marks Number(3) => Range: -999 to 999
Empno Number(4) => Range : -9999 to 9999
Customer_ID Number(6) => Range: -999999 to 999999

Number(p,s):
• It is used to hold floating point type values.
• “p” stands for Precision. “s” stands for Scale.
• Precision means, Total Number of Decimal Places
• Scale means, Number oF Decimal Plces.
• Valid Range of Precision is: 1 t0 38.
• Valid range of Scale is -84 to 127.
Example:
If maximum average is 100.00 then declare as:
Avrg_Marks Number(5,2) => Range: -999.99 to 999.99
If maximum salary is 100000 .00[1 Lakh] then declare as:
Salary Number(8,2) => -999999.99 to 999999.99

Character Related Data Types:

Char(n):
• It is used to hold a set of characters [strings].
• “n” means maximum number of characters.
• It is a Fixed Length Character Data Type.
• It is a single byte Character Data Type.
• It is ASCII Code Character Data type.
• Maximum size is: 2000 Bytes
• Default size is: 1
• To hold fixed length characters like state code, country code, PAN Card
Number, and Gender …etc. we can use this data type.
Example:
State_Code Char(2)
STATE_CODE
AP
TS
Country_Code Char(3)
COUNTRY_CODE
IND
AUS
Gender Char(1)
GENDER
M
F

Varchar(n):
• It is used to hold a set of characters.
• “n” means maximum number of characters.
• It is a Variable Length Character Data Type.
• It is a single byte Character Data Type.
• It is ASCII Code Character Data type.
• Maximum size is: 4000 Bytes
• There is no default size for it. We must specify the size.
• To hold variable length characters like Emp_Name, Student_Name,
Product_Name …etc. we can use this data type.
Example:
Emp_Name Varchar2(20) => it can hold maximum of 20 characters
Job Varchar2(10) => it can hold maximum of 10 characters

Long:
• It is used to hold a set of characters.
• It is a Variable Length Character Data Type.
• It is a single byte Character Data Type.
• It is ASCII Code Character Data type.
• Maximum size is: 2 GB
Example:
Experience_summary LONG
Customer_Feedback LONG

CLOB:
• It is used to hold a set of characters.
• CLOB stands for Character Large Object.
• It is a Variable Length Character Data Type.
• It is a single byte Character Data Type.
• It is ASCII Code Character Data type.
• Maximum size is: 4 GB
Example:
Experience_Summary CLOB
Product_Features CLOB
Remarks CLOB
Customer_Feedback CLOB

nChar(n):
• It is used to hold a set of characters.
• In “nChar“, n means National.
• It is a Fixed Length Character Data Type.
• It is a multi-byte Character Data Type.
• It is UNI Code Character Data type.
• Maximum size is: 2000 Bytes
• Default size is: 1
• To hold English and other language characters, we use it.

nVarchar(n):
• It is used to hold a set of characters.
• In “nVarchar“, n means National.
• It is a Variable Length Character Data Type.
• It is a multi-byte Character Data Type.
• It is UNI Code Character Data type.
• Maximum size is: 4000 Bytes
• No default size for it. We must specify the size.
• To hold English and other language characters, we use it.

nCLOB:
• It is used to hold a set of characters.
• In “nCLOB“, n means National.
• It is a Variable Length Character Data Type.
• It is a multi-byte Character Data Type.
• It is UNI Code Character Data type.
• Maximum size is: 4 GB.
• To hold English and other language characters, we use it.

Date & Time Related Data Types:

Date:
• It is used to hold date values.
• It can hold time values also.
• It can hold day, month, year, hours, minutes and seconds
• It cannot hold fractional seconds [Milli Seconds].
• To insert date value, we use to_Date() Function. Even if we don’t use
this function implicitly given value will be converted to Date type.
• Default time value is: 12:00:00 AM
• To insert time value, we use to_Date() Function.
• Default Date Format is: DD-MON-RR.
• RR means year last 2 digits. If RR value is between 0 to 49, year value
will be prefixed with 20. If RR value is between 50 to 99, year value will
be prefixed with 19.
• 7 Bytes Fixed Memory will be allocated for it.
• Valid Range is : 1st January, 4712 BC to 31st December, 9999 AD
Example:
Date_Of_Birth Date
Date_OfJoining Date

Timestamp:
• It is used to hold time values.
• It can hold date values also.
• It can hold day, month, year, hours, minutes, seconds and fractional
seconds [Milli Seconds].
• 11 Bytes Fixed Memory will be allocated for it.
Binary Related Data Types:
BFILE:
• In BFILE, B stands for “Binary”.
• It is used to hold multimedia objects like images, audios, videos and
documents.
• It can hold path of multimedia object.
• It acts like a pointer to multimedia object.
• It maintains path of the object & object is stored out of the database.
That’s why it can be also called as “External Large Object”.
• It is not secured one. Because object is stored out of the database.
• Maximum size is: 4 GB.
Example:
Student_Photo BFILE

BLOB:
• BLOB stands for “Binary Large Object”.
• It is used to hold multimedia objects like images, audios, videos and
documents.
• It can hold binary data of the multimedia object and displays to us in
hexadecimal format.
• It maintains path of the object & object is stored out of the database.
That’s why it can be also called as “External Large Object”.
• It is secured one. Because object stored with in the database.
• Maximum size is: 4 GB.
Example:
Student_Photo BLOB

List of SQL Data Types & Their Maximum Size: