Learn SQL Server - Parts of SQL – DDL, DML, DCL, TCL

SQL originally developed by IBM in 1970’s for their DB2 RDBMS. It became the standard since then. All of the RDBMS today are confirm with ANSI SQL92 standard. SQL Server has added more proprietary functionality to this to come up with new dialect of SQL called as Transact-SQL. Here we will be discussing how T-SQL is divided it’s functionality into modules as per their areas of concern.

T-SQL has main four parts as shown below-


1. Data Manipulation Language (DML):

This part of the language helps us to manipulate the data from the database system.
These operations include –

Insert Operation - inserting new data into a database table.
Select Operation - retrieving data from one or more database table/views
Update Operation - updating the existing data in a database table depending upon some specified criteria.
Delete Operation – deleting data from a database table on specified conditions.

2. Data Definition Language (DDL):

For fast data retrieval, the data itself should be stored in a well structured manner so that it easy to find it out from large storage. For this, SQL Server (and all of the RDBMS) provides data structures to store the data. To create these data structure, SQL provides following commands-

CREATE – to create a data structure like table, procedure, function etc.
ALTER – to change the already existing data structure
ADD – to add sub-elements to data structures like adding column to a table.
DROP - to delete/remove a data structure from the database system.

3. Data Control Language (DCL):

Data security is an important aspect of any database management system. SQL provides means for protecting data from unauthorized access. Data security can be achieved by creating database users, roles and giving/revoking them permissions for data structures (here after this referred as database objects). DCL has following commands –

GRANT – gives access to a user/role on specified database object(s)
REVOKE – revokes the access from the user/role for specified database object(s).

4. Transaction Control Language (TCL):

The set of SQL statements that should execute to success or fail as a whole is called as Transaction. In set of SQL statements if atleast one statement fails, the database should be restore in a state in which it was before executing the first statement in a set. SQL provides following commands for managing a transaction-

COMMIT - to save the database state after completing the transaction.
ROLLBACK – to restore the database state, in a state before the start of the transaction.

Learn SQL Server - What is SQL?

SQL is an acronym for Structured Query Language which is used in almost all of the Relational Database Management Systems (RDBMS) to arrange, manipulate and secure the data stored in a database system.

It is a Non-procedural language. By this we mean, we tell RDBMS - what output do we want? - And - how to do it? - is a job of RDBMS, in contrast with other procedural languages like C, C++, C#, Java where we need to code for how a particular problem should be solved.

Some databases like Oracle have a procedural version of SQL called as PL/SQL.

USES OF SQL:
It is used to communicate with DBMS for-

1. Data Structure Operations:
For creating/altering data structures for data storage and data manipulations. Creating and altering tables, procedures, function and triggers etc.

2. Data Manipulation Operations:
a. Putting new data into the database (Create/Insert Operation).
b. Retrieving data (Retrieve/Select Operation).
c. Updating the existing data (Update Operation).
d. Deleting the existing data (Delete Operation).

These basic data operations are collectively called as CRUD operations.

3. Data Access Control Operations:
To secure data stored in a database system, one needs to have facility to protect the data from unauthorized users. For this, SQL provides commands for creating users, roles and allowing/disallowing them from accessing the data from a database system.

4. Transaction Control Operations:
Most of the real life situation is a collection of related actions. If any one of these action fails, we should be able to restore situation at the start of the scenario. The collection of related actions together is called as Transaction. And each action will be represented as SQL command. SQL provides us the facility to handle these kinds of SQL Transactions with the help of in-built commands.