DDL, DML, DCL, and TCL - what are they used for?

DDL - Data Definition Language

SQL statements belonging this category are used to define the structure of the database, which is normally known as database schema. Few of the SQL commands belonging to this category are:-

• CREATE - used for creating database objects, such as tables, views, seq, indexes, etc.
• DROP - used for deleting database objects.
• ALTER - used for modifying the structure of database objects - structure not values.
• RENAME - used for renaming database objects.
• TRUNCATE - used for deleting all records from a table.
• COMMENT - used for adding comments to data dictionary

DML - Data Manipulation Language

As the name suggests, SQL commands belonging to this category are used for manipulating the data contained by the database objects, euch as tables, views, etc. Few of the SQL commands of this category are:-

• SELECT - probably the most commonly used SQL command. It's used for fetching the data from a database.
• INSERT - used for inserting new records/data in a table.
• UPDATE - used for updating the data within a table. The difference between ALTER and UPDATE is that the former is used for modifying the structure of database objects whereas the latter is used for modifying the data contained by a table (a database object).
• DELETE - used for deleting records from a table. The point to remember here is that this command doesn't remove the spaces of those deleted records. They still remain there. This is the difference between TRUNCATE and DELETE. Read more in this article - Difference between DELETE, TRUNCATE, & DROP >>
• MERGE - used for conditional INSERT/UPDATE. I'll post a separate post on MERGE. Please refer the article MERGE statement/Upsert Functionality >> for more details.
• CALL - as the name might suggest, this command is used for calling either a PL/SQL subprogram or a Java subprogram.
• EXPLAIN PLAN - used for describing the access path to the data. Read the details in this article EXPLAIN PLAN - what's it & how do we use it?
• LOCK TABLE - as the name suggest, this is used for locking a table and hence controlling the simultaneous access to the table.

DCL - Data COntrol Language

These commands are used for controlling the access to the data in the database by either granting the permission to access or by revoking the already granted permission. The SQL commands belonging to this category are:-

• GRANT - used for granting the permission to access the data.
• REVOKE - used for revoking the already granted pemission to access the data.

TCL - Transaction Control Language

These commands are used for controlling the state of the transaction they are fired from. Controlling a transaction includes capabilities of committing it, rolling back the changes, identifying the SAVEPOINTs to control the ROLLBACK, etc. The command belonging to this category are:-

• COMMIT - used for making the changes permanent.
• ROLLBACK - used for restoring the database to the previously COMMITted state.
• SAVEPOINT - used for identifying a point to which the transaction can be rolled back if required of course before the transaction gets COMMITted.
• SET TRANSACTION - used for modifying the properties of the current transaction like modifying the Isolation Level of the transaction.