Components of SQL

Components of SQL

SQL commands are divided into five categories:

DATA DEFINITION LANGUAGE

The Data Definition Language (DDL) consist of SQL statements used to define the database structure or schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in databases.

The DDL provides a set of definitions to specify the storage structure and access methods used by the database system.

A DDL performs the following functions :

1.     It should identify the type of data division such as data item, segment, record and database file.

2.     It gives a unique name to each data item type, record type, file type and data base.

3.     It should specify the proper data type.

4.     It should define the size of the data item.

5.     It may define the range of values that a data item may use.

6.     It may specify privacy locks for preventing unauthorized data entry.

SQL commands which comes under Data Definition Language are:

Create : To create tables in the database.

Alter : Alters the structure of the database.

Drop : Delete tables from database.

Truncate : Remove all records from a table, also release the space occupied by those records.

DATA MANIPULATION LANGUAGE

A Data Manipulation Language (DML) is a computer programming language used for adding (inserting), removing (deleting), and modifying (updating) data in a database. In SQL, the data manipulation language comprises the SQL-data change statements, which modify stored data but not the schema of the database table.

After the database schema has been specified and the database has been created, the data can be manipulated using a set of procedures which are expressed by DML.

By Data Manipulation we mean,   

·        Insertion of new information into the database

·        Retrieval of information stored in a database.

·        Deletion of information from the database.

·        Modification of data stored in the database.

The DML is basically of two types:

Procedural DML – Requires a 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.

SQL commands which comes under Data Manipulation Language are :

Insert : Inserts data into a table

Update : Updates the existing data within a table.

Delete : Deletes all records from a table, but not the space occupied by them.

DATA CONTROL LANGUAGE

A Data Control Language (DCL) is a programming language used to control the access of data stored in a database. It is used for controlling privileges in the database (Authorization). The privileges are required for performing all the database operations such as creating sequences, views of tables etc.

SQL commands which come under Data Control Language are:

Grant : Grants permission to one or more users to perform specific tasks.

Revoke : Withdraws the access permission given by the GRANT statement.

TRANSACTIONAL CONTROL LANGUAGE

Transactional control language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made to the data in a table by DML statements.

SQL command which come under Transfer Control Language are:

Commit : Saves any transaction into the database permanently.

Roll back : Restores the database to last commit state.

Save point : Temporarily save a transaction so that you can rollback.

DATA QUERY LANGUAGE

The Data Query Language consist of commands used to query or retrieve data from a database. One such SQL command in Data Query Language is

Select : It displays the records from the table.