Database Languages-DDL,DML,DCL,TCL

Database Languages

A DBMS need to provide a language for each category of user by which user can express databse queries and updates.Database language is used to create and maintain database on computer.

There are number of database languages used by different user .User can be said as follows: -

Oracle
Mysql
MS Access
dBase

SQL statements can be categorized as data definition language (DDL), data control language (DCL) and data manipulation language (DML).

DDL-

Data definition language is used to create the structure or schema for the required database.

In other words ,we can say it defines the logical schema which includes relation ,views,etc and the storage schema which includes attributes,integrity constraints,security ,authorization etc in data dictionary.

Some commands used under DDL are as follows:-

• CREATE - It is used to create objects or structure in the database.
• ALTER - It is used to alters the structure of the database.
• DROP - It is used to delete objects from the database.
• TRUNCATE - It is used to remove all records from a table, including all spaces allocated for the records are removed.
• COMMENT - It is used to add comments in the data dictionary.
• RENAME - It is used to rename an object/structure.

DML-

Data manipulation language is used to insert ,retrieve and manipulate the data in the database.

In other words ,we can say that it is used to modify the stored data in the database but cannot modify the struture/schema of database or database objects.

In other words,we can also say that it is used for managing data within the schema object.

DML commands are not auto-committed. It means changes made by DML command are temporary, it can be rollback.

Some commands used under DML are as follows:-

• SELECT - It is used to retreive the data from database.
• INSERT - It is used to insert the data in the tables of database.
• UPDATE - It is used to update the existing values in the table of database.
• DELETE - It is used to delete all records from the table,the space of record remain same.
• CALL - It is used to call a pl/sql or java program.

DCL-

Data control language is used to control access to data stored in the database.

In other words ,we can say that it is used to control the privilege in database.Privilege can be defined as the permission to perform operations on the database.

It comprises of two commands:-

• GRANT - It is used to grant the permission to user for accessing the data.
• REVOKE - It is used to take permission back from user.

TCL-

Transaction control language is used to manage the transaction in database.

In other words ,we can say that it is used to control the transaction processing in a database.

Some commands used under TCL are as follows :-

• COMMIT - It is used to save transaction permanently in the database.
• ROLLBACK - It is used to rollback all the transaction to last committed state.It also uses with a savepoint command to jump to a savepoint in a transaction.
• SAVEPOINT - It is used to save transaction temporarily so that one can rollback it to that save point if it is necessary.