- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- Explain different comparison operators used in SQL queries
- What are different Perl Data Types?
- What are the different data types in Apache Pig?
- What are the different data types of arrays in C#?
- What are different types of data in C language?
- What are the different types of stoves used in India to cook food?
- What are different speed types of flow used in identifying Mach number?
- What are the most used SQL clauses in DBMS?
- What are the default values used by DB2 for various data types?
- What are the different types of psychotherapy?
- What are the Different Types of Marketing?
- What are the different types of fabrics?
- What are the different types of Respiration?
- What are the different types of solutions?
- What are the different types of motion?
