types of SQL

The various categories of SQL commands are:

DDL (Data Definition Language)
DML (Data Manipulation Language)
DQL (Data Query Language)
DCL (Data Control Language)
DAL (Data Administration Commands)
TCL (Transactional Control Commands)

DDL (Data Definition Language):

These commands are used to create database objects like databases, tables and indexes . Most commonly used commands that fall under this category are:
SQL DDL Commands:

  • CREATE TABLE
  • CREATE DATABASE
  • ALTER TABLE
  • DROP TABLE
  • CREATE INDEX
  • ALTER INDEX
  • DROP INDEX
  • CREATE VIEW
  • DROP VIEW
  • CREATE ALIAS
  • DELETE ALIAS
  • REVOKE PROCEDURE
  • REVOKE PACKAGE
  • RENAME
  • TRUNCATE

DML (Data Manipulation Language):

The commands in this category are used for manipulating the data in the existing schema objects which were created using the DDL (Data Definition Language) commands. Most commonly used DML commands include:

  • INSERT
  • UPDATE
  • DELETE
  • MERGE
  • LOCK TABLE
  • CALL
  • EXPLAIN PLAN

It is important to note that the above list contains commands from PL/SQL too.

DQL (Data Query Language):

Not all books and tutorials create a special category for the querying of data. But since the SELECT statement is by far the most used statement in SQL and has so many options to be added to it, it deserves a separate category.

  • SELECT

The SELECT statement should never be added in Data Manipulation category because SELECT statement never modifies the data present in database tables.

DCL (Data Control Language):

These commands control the user access level and privileges in the database for various DB objects. Also concurrency and transaction processing commands fall into this category.

  • ALTER PASSWORD
  • GRANT
  • REVOKE
  • CREATE SYNONYM

DAL (Data Administration Commands):

The data administration SQL commands are used to perform audits and analysis of the database system. These commands are a part of database administration role which further comprises of full database setup and maintenance and uses commands from all categories.

  • START AUDIT
  • STOP AUDIT

TCL (Transactional Control Commands):

Transaction management is an important part of any application. The databases need to support the same. SQL has a set of commands to initiate, close and rollback transactions as listed below:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • SET TRANSACTION
Types of commands in sql admin Database
The various categories of SQL commands are: DDL (Data Definition Language) DML (Data Manipulation Language) DQL (Data Query Language) DCL (Data Control Language) DAL (Data Administration Commands) TCL (Transactional Control Commands) DDL (Data Definition Language): These commands are used to create database objects like databases, tables and indexes . Most commonly used commands that fall...
<a href="http://www.javaexperience.com/wp-content/uploads/2013/10/types-of-SQL.png"><img src="http://www.javaexperience.com/wp-content/uploads/2013/10/types-of-SQL-300x151.png" alt="types of SQL" width="300" height="151" class="alignnone size-medium wp-image-4486" /></a> The various categories of SQL commands are: <a href="#ddl">DDL (Data Definition Language)</a> <a href="#dml">DML (Data Manipulation Language)</a> <a href="#dql">DQL (Data Query Language)</a> <a href="#dcl">DCL (Data Control Language)</a> <a href="#dal">DAL (Data Administration Commands)</a> <a href="#tcl">TCL (Transactional Control Commands)</a> <a name="ddl"></a> <h2>DDL (Data Definition Language):</h2> These commands are used to create database objects like databases, tables and indexes . Most commonly used commands that fall under this category are: SQL DDL Commands: <ul> <li>CREATE TABLE</li> <li>CREATE DATABASE</li> <li>ALTER TABLE </li> <li>DROP TABLE </li> <li>CREATE INDEX </li> <li>ALTER INDEX </li> <li>DROP INDEX </li> <li>CREATE VIEW </li> <li>DROP VIEW</li> <li>CREATE ALIAS</li> <li>DELETE ALIAS</li> <li>REVOKE PROCEDURE</li> <li>REVOKE PACKAGE</li> <li>RENAME</li> <li>TRUNCATE</li> </ul> <a name="dml"></a> <h2>DML (Data Manipulation Language):</h2> The commands in this category are used for manipulating the data in the existing schema objects which were created using the DDL (Data Definition Language) commands. Most commonly used DML commands include: <ul> <li>INSERT</li> <li>UPDATE</li> <li>DELETE</li> <li>MERGE</li> <li>LOCK TABLE</li> <li>CALL</li> <li>EXPLAIN PLAN</li> </ul> It is important to note that the above list contains commands from PL/SQL too. <a name="dql"></a> <h2>DQL (Data Query Language):</h2> Not all books and tutorials create a special category for the querying of data. But since the SELECT statement is by far the most used statement in SQL and has so many options to be added to it, it deserves a separate category. <ul> <li>SELECT</li> </ul> The SELECT statement should never be added in Data Manipulation category because SELECT statement never modifies the data present in database tables. <a name="dcl"></a> <h2>DCL (Data Control Language):</h2> These commands control the user access level and privileges in the database for various DB objects. Also concurrency and transaction processing commands fall into this category. <ul> <li>ALTER PASSWORD </li> <li>GRANT </li> <li>REVOKE </li> <li>CREATE SYNONYM </li> </ul> <a name="dal"></a> <h2>DAL (Data Administration Commands):</h2> The data administration SQL commands are used to perform audits and analysis of the database system. These commands are a part of database administration role which further comprises of full database setup and maintenance and uses commands from all categories. <ul> <li>START AUDIT </li> <li>STOP AUDIT </li> </ul> <a name="tcl"></a> <h2>TCL (Transactional Control Commands):</h2> Transaction management is an important part of any application. The databases need to support the same. SQL has a set of commands to initiate, close and rollback transactions as listed below: <ul> <li>COMMIT </li> <li>ROLLBACK </li> <li>SAVEPOINT </li> <li>SET TRANSACTION</li> </ul>