SQL Data Types for Oracle PL/SQL, MySQL, SQL Server, and MS Access

Introduction

SQL is a database language that is developed to handle database creation, deletion, row fetching, row modification, and other data related activities etc. SQL is basically a structured query language that helps in handling database tables in a robust way and this is known as the computer language too that is suitable for storing, manipulating and retrieving data from a relational database too.

Read: SSRS Sub Reports and deployment process-How to do it

All relational database management systems like SQL Server, MySQL, Oracle, MS Access, Sybase, Postgres, and the Informix can be managed and handled with the help of SQL. Many other SQL dialects are:

• Oracle by using PL/SQL
• MS SQL Server using T-SQL
• MS Access version

Today, we have brought this post to discuss SQL and its data types. As there are many data types used in SQL like MySQL data types, SQL Server data types, MS SQL Data types etc that helps in handling various types of data. So, in the first section, we will introduce What is SQL, its various features and characteristics, then in the next section of this blog, we will discuss SQL data types in detail. Let’s start, shall we?

What is SQL? A Quick look at its Features and Characteristics

SQL has been widely used by database programmers for  several years. It is a commonly used query language and is equally powerful as well. There are several reasons due to which it is mainly used by the database programmers. Here, we have listed a few of them:

• It allows users to access the data from relational database systems.
• It helps in describing the data
• It can help in handling and manipulating data.
• It can be embedded in the languages.
• This can be used to create and drop databases and tables
• The user can set permissions on tables, views, and procedures with the help of SQL

Read: Introducing SSIS Architecture & DW Concepts Overview

There are several query-based processes that help in handling databases and they are considered an important part of the SQL. The components that are majorly used in query processing include:

• Query Dispatcher
• SQL Query Engine
• Classic Query Engine
• Optimization Engine

All SQL queries can be managed by a classic query engine, but logical files cannot be handled by the SQL query engine.

A Detailed Discussion on SQL Data Types

In SQL each record of the table may have a certain data type, even each column, parameter, expression, and the local variable has a related data type. Here, the data type of the table can handle integer data, monetary data, date or time or binary values. While creating the table structure you will have to specify the type of data or data type. You can choose any data type of your wish and the type of data that you are going to store in that row or column. Below mentioned six types of data can be stored in SQL server and for that data type categories are specified there:

Read: SSRS Pie Chart - Having a Slice of the Pie

1. Exact Numeric Data Types
2. Approximate Numeric Data Types
3. Character String Data Types
4. Date and Time Data Types
5. Binary Data Types
6. Unicode Character String Data Type
7. Miscellaneous Data Types

Let us describe one by one and the sub data types of these categories to be more specific about them.

1). Exact Numeric Data Types

bigint	numeric
Bit	smallest
Decimal	small money
int	tinyint
money

Read: What is the Substring Function in the SQL? Example of SQL Server Substring

2). Approximate Numeric Data Types

float

real

3). Character String Data Types

char	varchar
text

4). Date and Time Data Types

Date	datetimeoffset
datetime2	smalldatetime
Datetime	time

5). Binary Data Types

Binary	varbinary
Image

Read: How to Become a Database Administrator? Just Know the 5 Steps

6). Unicode Character String Data Type

Nchar	nvarchar
Ntext

7). Miscellaneous Data Types

rowversion	xml
Uniqueidentifier	rowversion
Sql_variant	hierarchyid
Spatial geometry types	cursor
Table

These data types that are used in SQL server have different ranges and you can refer the menu of SQL Server to know more about them.

MySQL Data Types

Unlike SQL Server, in MySQL there are three main data types that are used in the tables. They are text, date, and number. In these categories below listed data types are being used by the database developers and are quite popular as well. Mainly data can be divided into text, date and numeric formats so they are quite sufficient for these categories:

Read: Know Everything About a SQL Developer Salary for Freshers & Experienced Professionals in 2021!

A). Text Data Types

char	Mediumblob
Varchar	Longtext
Tinytext	Longblob
Text	Enum
Blob	set
mediumtext

B). Number or Numeric Data Types

Tinyint	Bigint
Smallint	Float
Mediumint	Double
Int	Decimal

C). Date Data Types

Date()	Time()
Datetime()	Year()
Timestamp()

Read: 70+ Most Asked SSIS Interview Questions for Freshers & Experienced

D). Microsoft Access Data Types

Text	Currency
Memo	Autonumber
Byte	Date/time
Integer	Yes/no
Long	Ole object
Single	Hyperlink
Double	Lookup wizard

In this way, every type of database has a separate data type handling category and the user can use any of it as per his or her choice. SQL databases  can handle various types of data and for this purpose, you will have to use any particular data type. In the case of MS Access, SQL server or SQL database, you can have many types of data type handling. Like in the following table the data types are specified in front of the field or record name like shown in the below example:

Read: What is Primary Key in SQL? How to Add, Remove, Or Set Primary Key

Create Table table_name { Column1 datatype, Column2 datatype, Column3 datatype, Column4 datatype, } ;

Here, in this query of table creation Create Table keyword is used to tell the database engine that a database table is going to be created. In each row of this table, there is specified a separate column and these columns have certain specific data types. These data types also have pre-specified ranges for the values that can be stored in them.

Read: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

Depending on the data that will be stored in these columns’ users can use the appropriate data type. Even though any existing database table a new table can also be created and for that the two queries will be used. Here through Select and Create queries in nested way users can create the new table and store data in that.

How Commands are Categorized in the SQL?

As we have discussed earlier,  SQL is a relational database query language. RDBMS is the basic database management system for all latest database systems including IBM DB2, Oracle, MS SQL Server, Microsoft Access and others. The RDBMS (Relational Database System) is based on relational models. Mainly following concepts are used in RDBMS:

The data of RDBMS systems are stored in tables that are database objects. A number of relational data entries are stored in tables that consist of several rows and columns. The table is considered one of the simplest forms to store data.

Read: Windows SQL Function-All you Need to Know

Tables are in-turn broken into fields or records. These fields form the table columns that are maintained to store any specific information of every record or any particular customer. Table records are also known as rows, that are maintained as an individual entry of the table. Table rows or records can also have NULL values that are different than Zero. Here the NULL value means that the record is left blank at the time of creation. SQL language also has constraints that can be table or row level. The constraints are used to limit the type of data that is stored in the table. The table data accuracy and reliability are checked with the help of these constraints. Table constraints can be table or column level. Various commands are used to interact with the database that is Create, Select, Insert, Update, Delete and Drop. SQL commands are divided into the following groups that include:

1). Data Definition Language or DDL

The commands that are used to interact with the database like Create, Alter or Drop are known as data definition language commands. The commands are described below:

• Create: The command is used to create a new table or view the table or other objects
• Alter: It is used to modify database object like tables
• Drop: It is used to delete or drop the entire table or the table objects

2). Data Manipulation Language

The commands that are used to manipulate data are known as data manipulation commands. Main commands for data manipulation are:

Read: All You Need to Know About SQL: SQL Tutorial for Beginners & Experienced

• Select: It is used to retrieve certain specified and desired records from the database table
• Insert: It is used to create a new record
• Update: It is used to modify records
• Delete: The command is used to delete records

3). Data Control Language

The commands that are used to provide control or grant any privilege to the user are known as data control language. These commands can be used by the database administrator and help to handle data. The commands are:

• Grant: This command is used to provide a certain privilege to the user.
• Revoke: This command is used to take back the user privilege that was granted earlier.

Final Words

SQL is one of the most used database engines these days. Database developers use this to store data in a relational manner. The relational database is a powerful database that is used to store data in a convenient manner so that it can be retrieved or accessed smoothly. Even to store data in the database table you may need certain data types that can differentiate data. SQL has mainly numeric, date and text data types that are further subdivided  into various categories. These SQL data types are used to handle and store the data of that particular type.

Read: Normalization in SQL | 1NF, 2NF, 3NF and BCNF with Examples

FaceBook Twitter LinkedIn Pinterest Email