SQL Data Types

Read this article to understand the SQL Data types along with the examples. We have shared few examples and we have shared basic as well as advance level common interview questions also.

In this post, I will provide you a short overview of SQL Data Types. In addition, we have covered some questions which we receive from the audience. 

First, let’s talk about the theory of SQL Data Types and why we should use it.

Definition

As we know, we use RDBMS systems to store the data. SQL (structured query language) is used to manipulate the structure of the database objects as well as manipulate the data of the table. We use data types to store the data into the table. As you know, we have a different types of data like numbers, strings, date, time and json data etc. For storing the data of each types we have a different data type.

Reasons to Use

Data types are used to store the data in to each column. Each column have a different data types to store the data.

1. Store the user names in to database.
2. Store the address and birth date of customers.
3. Store the employee salary details and skills into employee table.
4. Store the gender and married status of the employee.

SQL provides some data types for storing the data based on it’s type. Let’s learn about the all data types.

Integer

This data type is used store the integer number into the table. Integer number means whole numbers like 1234, 44, 10 etc. For storing the integer numbers we have multiple keywords like INT, INTEGER, SMALLINT, TINYINT, BIGINT etc. Each type has a specified range. For storing the large number range we will use INT/INTEGER while for storing small number we will use TINYINT(8 bit).

Syntax

CREATE TABLE table_name( column_name integer_data_type);

Example

Example 1 :

create table Customers( customer_id int);

Above example create Customer table with customer_id column and has a integer data type which will store whole numbers ranging upto 4 bytes. Check the below table for int data types.

Data type	Size	Range
TINYINT	1 byte	-128 to 127
SMALLINT	2 byte	-32,768 to 32,767
INT	4 byte	-2,147,483,648 to 2,147,483,647
INTEGER	4 byte	-2,147,483,648 to 2,147,483,647
BIGINT	8 byte	-2^32 to +2^32

Float

This data type is used store the floating number into the table. Floating number means real numbers like 123.4, 4.4, 10 etc. For storing the floating numbers we have multiple keywords like FLOAT, DOUBLE, DECIMAL, etc. Each type has a specified range. For storing the large number range we will use DOUBLE while for storing small real number we will use FLOAT(4 byte).

Syntax

CREATE TABLE table_name( column_name float_data_type);

Example

Example 2 :

create table Customers( customer_id int,
salary float);

Above example create Customer table with customer_id column with integer data type while salary will store real numbers upto 4 byte. Check the below table for float data types.

Data type	Size	Range
FLOAT	4 byte	24 bits of precision
DOUBLE	8 byte	53 bits of precision
DECIMAL	12 byte	65 bits of precision

Char

Char data type is used to store the characters or string in to the table. Char data type store the 1 byte of data while for storing the string we need to provide the size of characters or max string size. For storing the characters or strings we have CHAR, VARCHAR, TEXT etc data types.

Syntax

CREATE TABLE table_name( column_name char_data_type);

Example

Example 3 :

create table Customers( customer_id int,
first_name char(20),
address varchar(255),
salary float);

Above example create Customer table with customer_id column with integer data type, salary will store real numbers upto 4 byte, first name is stored in char data type and max allowed characters are 20 while address having 255 characters. Below are the same differences of the Char, Varchar and Text.

Char	Varchar	Text
Char data type is a fixed size data type.	Varchar data type is a variable type data storage data type.	Text data type store the textual large data.
Char data type uses when we have fixed data size column like Gender.	Varchar data type uses when we don’t have fixed data in a column like address.	Text data type is used to store the large number of textual information like summary of the product.
Char is beneficial when we have fixed type data.	Varchar is beneficial when we have variable data type.	Text is beneficial when we have large number of textual information upto 2GB.
We can index this column.	We can index this column.	We can not index this column.

Boolean

This data type is used to store the true or false value. We can use BOOLEAN keyword to store the true or false value.

Syntax

CREATE TABLE table_name( column_name boolean);

Example

Example 4 :

create table Customers( customer_id int,
first_name char(20),
address varchar(255),
salary float,
is_married boolean);

Above example create table Customers with is_married column for storing true or false value. Boolean data type needs single bit for storing the true or false. 0 means false while 1 means true.

Date and Time

Date and Time data type is used to store the date or time or both date-time in to the table. For storing the date or time we have DATE, TIME, DATETIME, TIMESTAMP etc data types.

Syntax

CREATE TABLE table_name( column_name date_time_data_type);

Example

Example 5 :

create table Customers( customer_id int,
first_name char(20),
address varchar(255),
salary float,
is_married boolean,
inserted_time_stamp datetime);

Above example create Customer table with customer_id, first_name, address, salary, is_married status along with the inserted_time_stamp column with datetime data type. This data type store the date as well as time into the column. Lets see some formats of the date data types.

Date	Time	DateTime	TimeStamp	Year
YYYY-MM-DD	HH:MM:SS	‘YYYY-MM-DD HH:MM:SS	‘YYYY-MM-DD HH:MM:SS	YYYY / YY
Only date can be stored.	Only time can be stored.	Date and time is stored.	Date and time is stored.	Only year is stored.

Binary

This data type is used to store the audio, video and images in to the table. For storing the images in to table BINARY, VARBINARY, BLOB etc data types are used.BLOB column can hold binary data up to 65,535 bytes long.

Syntax

CREATE TABLE table_name( column_name binary_data_type);

Example

Example 5 :

create table Customers( customer_id int,
first_name char(20),
address varchar(255),
salary float,
is_married boolean,
photo binary(1000),
inserted_time_stamp datetime);

Above example create Customer table with customer_id, first_name, address, salary, is_married status along with the inserted_time_stamp column with datetime data type. We have added photo columns for storing the image data into table. BINARY data type is a fixed data type while VARBINARY is variable type binary data. While inserting the data into BINARY column we need to use hexadecimal values. Lets see the below example where we are inserting customers details with image.

insert into customers
values (1,'James','MH, INDIA',50000,0,0xFFD8FFE000104, now())

Above example insert the customer details along with current datetime and customer image into the table.

I hope all above example clears the concept of data types and it’s use.

Linking

Data types are used in DDL commands like Create table or Alter table. You can refer all the interview questions. Please refer all the links for understanding the SQL data types in depth.

SQL Data Types with Latest SQL Interview Questions 2023

Basic SQL Data Types Interview questions

1. What are some common data types in SQL?
2. Can you change the data type of a column in SQL?
3. What is the difference between CHAR and VARCHAR data types?
4. What is the difference between DATE , TIME , and DATETIME data types?

Intermediate SQL Data Types Interview questions

1. How do you specify a default value for a column in SQL?
2. What is the maximum and minimum value of a BIGINT data type?
3. What is the difference between a TIMESTAMP and DATETIME data type?

Advanced SQL Data Types Interview questions

1. How do you specify the precision and scale of a DECIMAL data type in SQL?

Questions covered from comment section

In this section we will cover the new questions as well as answers. Please comment the questions into comment section so we will include it here. Below are few questions which we thought should be covered.

Que : How do you specify the precision and scale of a DECIMAL data type in SQL?

Ans : As we learn about the syntax of float data types. Decimal is the example of floating data type. The syntax of declaring the size of decimal or any floating data type is (precision,scale). Precision means total number of digits in the number while scale is the number of digits after decimal point.

Syntax :

column_name DECIMAL(precision,scale)

Example :

salary decimal(9,3)

Above column will store maximum 999999.999 value means 6 digits before period and 3 digits after period.

Quiz

Click on this link to solve few MCQ questions on Data Types in SQL.

I hope you have enjoyed this session. Happy offline learning… Lets meet in next session.