Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

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

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.

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 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.

What is SQL? A Quick look at Features and Characteristics

SQL is widely being used by the database programmers from 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

There are several query-based processes that help in handling database 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. SQL Server Curriculum

Read: MSBI Tutorial Guide for Beginner

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. Even 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:

  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  

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  

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. SQL Server quiz

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:

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()  

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 database 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:


 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: How to Prevent SQL Injection Attacks?

Depending on the data that will be stored in these columns’ user 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 user can create the new table and store data in that.

How Commands are Categorized in the SQL?

As we have discussed earlier that 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.

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. free SQL Server demo 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 if 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: What is Update Query in SQL? How to Update (Column Name, Table, Statement, Values)
  • 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 is 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 sub-divided into various categories. They are used to handle and store the data of that particular type.


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

2 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

3 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

3 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

4 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

12 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

32 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

11 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

3 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

0 day 22 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews