What are the different data types used in SQL queries?

Data type specifies the kind of data that a field stores. There are different types of data types used in the structured query language (SQL) queries which are as follows −

• Character data type
• Number data type
• Date and time data types

Let’s see each data type with an example.

Character data type

Character data types are used to store characters. We can use CHAR, VARCHAR, VARCHAR2, NVARCHAR2, NCAHR and LONG as character data types.

VARCHAR2

It stores variable-length character data up to a maximum of 4000 characters. Values in different records can have a different number of characters.

The syntax is as follows −

fieldname VARCHAR2(maximum size)

Example

Given below is an example of VARCHAR2 character data type −

create table employee(ename varchar2(30),department varchar2(20));

The output is given herewith: Table created

VARCHAR

It is the same as VARCHAR2 but it is in ANSI standard.

The syntax is as follows −

fieldname VARCHAR(maximum size)

Example

Given below is an example of VARCHAR character data type −

create table student(stdname varchar(20), branch varchar(30));

The output is given herewith: Table created

CHAR

It has fixed length character data up to a maximum size of 2000 characters. Data values for different records all have the same number of characters.

DBMS adds trailing blank spaces to the end of the entry to make the entry fill with maximum size value. Data longer than maximum size causes an error.

The syntax is as follows −

fieldname CHAR(maximum size)

Example

Given below is an example of CHAR character data type −

create table student(stdname char(20), branch char(30));
Output: Table created;

In the above example student name and branch both are of character data types, so a table is created with column names stdname and branch.

NVARCHAR and NCHAR

It is analogous to VARCHAR2 and CHAR but uses Unicode rather than ASCII. It is used to hold character data in languages other than English.

Example

Given below is an example of NVARCHAR and NCHAR character data type −

create table employee(ename NVARCHAR2(30),department NCHAR2(20));
insert into employee values('bhanu','CSE');
insert into employee values('priya','ECE');
insert into employee values('hari','EEE');
select * from employee;

Output

The output is as follows −

bhanu|CSE
priya|ECE
hari |EEE

Long

Variable length character data up to 2 GB, max per one table.

Number data type

This data type stores negative, positive, fixed and floating-point numbers with precision up to 38 decimal places.

The syntax is as follows −

filedname NUMBER[(precision, scale)]

Integer: fieldname NUMBER(precision)

Fixed Point: fieldname NUMBER[(precision, scale)]

Example

Given below is an example of the number data type −

create table employee(ename NVARCHAR2(30),department NCHAR2(20), average NUMBER(5,2));
insert into employee values('bhanu','CSE',-0.5);
insert into employee values('priya','ECE',15.6);
insert into employee values('hari','EEE',27.8);
select * from employee;

Output

The output is given below −

bhanu|CSE|-0.5
priya|ECE|15.6
hari|EEE|27.8

Date and time data type

Dates are from BC to AD. Default date format is DD-MM-YY.

Default time format is: HH:MI:SS AM

The syntax is as follows −

fieldname DATE

Timestamp

It is similar to DATE but stores fractional seconds.

The syntax is as follows −

fieldname TIMESTAMP(fractional-seconds-pre)