What are the difference between DDL, DML and DCL commands?

DDL

Data Definition Language (DDL) statements are used to define the DB structure or schema. i.e.:-

• CREATE – providing a way to create objects in the db.

• ALTER – capable to alter the structure of the db objects.

• COMMENT – for addition of comments to the data dictionary.

• TRUNCATE - remove all records from a table including all spaces allocated for the objects.

• DROP – responsible for deleting objects from db.

• RENAME – for renaming an object.

DML

Data Manipulation Language (DML) statements are used for managing data within schema objects. i.e.:-

• INSERT – for the insertion of data into a table.

• UPDATE – for updating existing data within a table.

• DELETE – It deletes all records from a table but the space for the objects remain.

• SELECT – for retrieving the data from the db.

• LOCK TABLE - for controling concurrency.

• CALL - calling a PL/SQL or Java subprogram.

• EXPLAIN PLAN - explain access path to data.

• MERGE - for performing insert or update operation.

DCL

  

Data Control Language (DCL) statements are used to manage user access into db i.e.-:

• GRANT - gives user's access privileges to database.

• REVOKE - withdraw access privileges given with the GRANT command.

• COMMIT – It will allows saving the completed work.

• ROLLBACK - restore db to original since the last COMMIT.

• SAVEPOINT - identify a point in a transaction to which we can later roll back.

• SET TRANSACTION – It helps to change transaction isolation level and rollback segment (which rollback segment needs to use).

Note: - COMMIT, SAVEPOINT and ROLLBACK are also known as TCL (Transaction Control Statements). These statements are used to manage the changes made by DML statements. It also allows statements to be grouped or combined together as a unit for performing logical transactions.