Difference Between DDL, DML and DCL Commands

Whether you are a Developer or DBA, you need to understand DDL, DML and DCL commands. Once you understand the difference, you can create & alter database objects, you can manipulate the data in database and many more database transactions.

Lets see what are the DDL, DML & DCL commands and their use in Oracle Database.

1) Data Definition Language (DDL)

Data Definition Language commands are used to define the structure or schema of a database.

CREATE: This command is used to create database objects like (TABLE, VIEW and INDEX etc.)

ALTER: This command alters the structure of database.

DROP: Removes objects from database.

TRUNCATE: To remove all rows from a Table. (Can’t be rolled back)

COMMENT: To add comments to the data dictionary.

RENAME: To rename an Object.

2) Data Manipulation Language (DML)

Data Manipulation Language commands are used for managing or manipulating data within schema objects.

SELECT: To retrieves data from the database and objects.

INSERT: To insert records into a Table.

UPDATE: To update existing data within a Table.

DELETE: To remove records from a Table.

MERGE: To create UPSERT operation (Insert or Update)

CALL: To call a PL/SQL or Java Program

EXPLAIN PLAN: It explains access path to data.

LOCK TABLE: To control concurrency

3) Data Control Language (DCL)

Data Control Language commands are used to control the database objects.

GRANT: To give users access privileges to database.

REVOKE: Withdrawal access privileges given by the GRANT command.

4) Transaction Control (TC)

 These commands manage the changes made by DML commands.

COMMIT: To make changes permanent to database.

ROLLBACK: To restore database to its original state since the last COMMIT occurred.

SAVEPOINT: You can identify a point in a transaction to which you can later on Rollback.

SET TRANSACTION: This option is used to change transaction options like isolation level etc.

If I have missed some commands, write below in comment section. We will incorporate the same in this tutorial.

Subscribe to get free tech advice, tips and in-depth articles.

SiteGround Web Hosting Promo Discount Banner

Leave a Reply

Your email address will not be published. Required fields are marked *