Concepts of DDL, DML and DCL

DATA-DEFINITION LANGUAGE (DDL)

DDL stands for Data-Definition language. It is a specification notation for defining the database schema. It is used to create and modify the structures of database objects in the database. Following are some of the examples:

• CREATE- To create objects in the database
• ALTER- To alter objects of the database
• DROP- To delete objects of the database
• TRUNCATE - Remove all records from a table, including all spaces allocated for the records are removed
• COMMENT- Adds comment to the data dictionary
• RENAME- Renames an object

DDL compiler generates a set of tables stored in a data dictionary. A special set of tables is called data dictionary. The data dictionary contains metadata that is data about data that describes the object in the database. DDL is a database schema. The integrity constraints of DDL are:

• Primary Key is an ID that uniquely identifies instructors.
• Referential integrity

DATA-MANIPULATION LANGUAGE (DML)

DML stands for Data-Manipulation Language. DML is used to retrieve, store, modify, delete, insert and update in the database. Following are some of the examples:

• SELECT - Retrieves data from a table
• INSERT - Inserts data into a table
• UPDATE - Updates existing data within a table
• DELETE - Deletes all records from a table

DML is also known as the query language. There are two classes of DML. They are:

1. Procedural Language: In this language, the user specifies what data is required and how to get those data.
2. Declarative: In this language, the user specifies what data is required without specifying how to get those data.

DATA-CONTROL LANGUAGE (DCL)

DCL stands for Data-Control Language. DCL is used to create roles, permissions, and referential integrity as well. It is used to control access to the database by securing it. Following are some of the examples:

• GRANT - Gives user's access privileges to database
• REVOKE - Withdraw users' access privileges given with the GRANT command

TRANSACTIONAL CONTROL LANGUAGE (TCL)

TCL stands for Transactional Control Language. TCL is used to manage different transactions that occur within a database. Following are some of the examples:

• COMMIT - Saves work is done in transactions
• ROLLBACK- Restores database to original state since the last COMMIT command in transactions
• SAVE TRANSACTION- Sets a savepoint within a transaction

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhattacharya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.
4. Oracle FAQs. Retreived from orafaq.com/faq/dcl-dml-ddl