Classification of SQL Statements

SQL, technically speaking is a data sublanguage that is it is a language used to interact with database. In other words all SQL statements are instructions to the database only. And that is where it differs from general purpose programming languages like C or CPP.

SQL provides many different types of commands used for different purposes. SQL commands can be divided into following categories :

1. Data definition language commands
2. Data manipulation language commands
3. Transaction control language commands
4. Session control commands
5. System control commands

The first three categories are discussed below:

DDL Commands: The data definition language commands as the name suggests allow you to perform task related to data definition. That is through these commands you can perform task like :

1. Create, alter and drop schema objects: This section of DDL commands is used to create or define or change or delete object such as a table or view and index. Create commands are used to create schema objects, Alter commands are used to modify or change the definition of already existing schema objects and Drop commands are used to delete or remove schema objects. Some examples of such DDL statements are create table, alter table, drop table, create index, alter index, drop index, rename table, truncate etc.
2. Grant and revoke privileges and roles: This section of DDL commands is used to grant or revoke permissions or privileges to work on schema objects. For example a user who creates a table becomes the owner of the table the owner of the table can allow others to work on his or her table this can be achieved by granting privilege for the same to others. To revoke already granted privileges revoke command is used. This section of the ideal command is also known as DCL data control language commands.
3. Maintenance commands: This session of DDL commands is used to analyze information on a table with an aim of maintaining it. Examples of table maintenance commands are analyze table, check table, repair table, restore table etc.

Data manipulation or DML commands: A data manipulation language is a language that enables users to access and manipulate data as organized by the appropriate data model. By data manipulation we mean:

• The retrieval of information stored in the database
• The insertion of new information into the database
• The deletion of information from the database
• The modification of data stored in the database

The DML is basically of two types:

• Procedural DML require user to specify What data is needed and how to get it
• Non procedural DML requires a user to specify What data is needed without specifying how to get it

Data manipulation language commands as the name suggests a used to manipulate the data that is DML commands query and manipulate data in existing schema objects. For example to insert a tuple in a table a DML statement insert into is used, to modify a tuple in a table another DML statement update is used, to delete a table in a table the DML statement delete is used. Other examples of DML commands are select, lock table etc.

TCL commands: A transaction is one complete unit of work example preparing report card for a student is a transaction but it involves many steps like open students table read data from it open marks table read data from its calculate percentage and grid write the computed result to result file and finally close the three tables. Transaction is successfully completed if and only if all its constituent steps are successfully completed. To manage and control the transactions the transaction control commands are use these commands to manage changes made by DML commands. Some examples of TCL commands are:

• Commit: It makes all the changes made by statements issued permanent.
• Rollback: It induce all changes since the beginning of a transaction or since a savepoint.
• Savepoint: It marks a point up to which all earlier statements have been successfully completed and is required in case of failure one May undo the changes that is rollback after this very point.
• Set Transactions: It establishes properties of the current transaction.