Independence Day Offer - FLAT 30% OFF | JTINFLAT30

- SQL Server Blogs -

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: Top 50 Datastage Interview Questions and Answers

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?

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

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: How to Create Database in Microsoft SQL Server?

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:

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 Interview Questions & Answers For Experienced

  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 are Data Types and Their Usage in SQL Server Tables?

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: What are Data Types and Their Usage in SQL Server Tables?

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: SSIS Tutorial for Beginners

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: Top 50 MongoDB Interview Questions and Answers

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: Top 100 SQL Interview Questions and Answers

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.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

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: How To Differentiate SQL Server JOIN, IN And EXISTS Clause?

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: How to Use Alter, Drop, Rename, Aggregate Function in SQL Server?

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

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

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: Online SQL Queries for Practice Questions with Answers


    Janbask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


Comments

Trending Courses

AWS

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

Upcoming Class

1 day 13 Aug 2020

DevOps

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

Upcoming Class

8 days 20 Aug 2020

Data Science

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

Upcoming Class

5 days 17 Aug 2020

Hadoop

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

Upcoming Class

3 days 15 Aug 2020

Salesforce

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

Upcoming Class

2 days 14 Aug 2020

QA

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

Upcoming Class

5 days 17 Aug 2020

Business Analyst

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

Upcoming Class

2 days 14 Aug 2020

MS SQL Server

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

Upcoming Class

2 days 14 Aug 2020

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation

Upcoming Class

-1 day 11 Aug 2020

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks

Upcoming Class

1 day 13 Aug 2020

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning

Upcoming Class

4 days 16 Aug 2020

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop

Upcoming Class

6 days 18 Aug 2020

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews