Introduction to DDL, DML, DCL, DQL and TCL

Understanding the key difference between DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), DQL (Data Query Language) and TCL (Transaction Control Language) is fundamental in SQL (Structured Query Language).

SQL, which stands for Structured Query Language, is a language used in database management. The SQL has five core built in sub-languages, DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), DQL (Data Query Language) and TCL (Transaction Control Language). Each of these sub-languages has their own use for database operations.

In this blog we are going to cover difference between DDL, DML DCL, DQL and TCL sub-languages in SQL:

Data Definition Language (DDL)

Data Definition Language is used to define and manage the structure of the database. As the name, Data Definition Language contains definition word, which you can remember as DDL is used for defining something.

Common DDL Commands:

CREATE

CREATE command is used to create new database objects like tables, views and indexes.

Example of CREATE Command:

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
FirstName VARCHAR(30),
LastName VARCHAR(30)
);

By using ‘CREATE’ in the above query, we are creating a table called ‘Students’ with columns for student ID, First Name and Last Name.

ALTER

ALTER allows us to modify or update the structure of existing database objects, such as modifying, adding or dropping columns in a table in the database.

Using ALTER command:

ALTER TABLE Students
ADD Address VARCHAR(100);

Here we are adding ‘Address’ column in the existing ‘Students’ table. (Note: We created a ‘Students’ table by CREATE command previously)

DROP

DROP command is used to remove or delete database objects like indexes, tables or views. DROP command permanently removes the object from the database. Always use DROP command carefully, you may blow your whole production database 😁😁😁.

Example of DROP Command:

DROP TABLE Students;

Here, we just deleted the ‘Students’ table from the database.

TRUNCATE

The TRUNCATE command is used to remove all the records (rows) from a table without modifying the structure of the table.

Example:

TRUNCATE TABLE Students;

Here we removed all the data from ‘Students’ table, but the table structure is not changed.

Data Manipulation Language (DML)

Data Manipulation Language plays a role for manipulation or modification of the stored data in the database.

Common DML Commands:

INSERT

INSERT is used to add new records (rows) into a table.

Example of INSERT:

INSERT INTO Students (StudentID, FirstName, LastName)
VALUES (1, 'Rahul', 'Rao');

By using ‘INSERT’ we added a new student data with StudentID of 1, first name ‘Rahul’ and last name ‘Rao’.

UPDATE

UPDATE command allows us to modify the existing records (rows) in a table and specify the columns whom data to be updated.

Example:

UPDATE Students
SET LastName = 'Singh'
WHERE StudentID = 1;

Here we just updated the last name of student whom studentID is 1 that is Rahul. Now, the last name of Rahul is setted to Singh.

DELETE

DELETE is used to remove records (rows) from a table based on the specified conditions. DELETE command wouldn’t delete the table itself.

Example:

DELETE FROM Students
WHERE StudentID = 1;

Here we deleted the student with studentID from the ‘Students’ table.

Data Control Language (DCL)

Data Control Language is used for setting up the permissions and access control for the users to a database.

Common DCL Commands:

GRANT

GRANT command allow us to set specific permissions to the users to perform actions in database objects.

Example:

GRANT SELECT, INSERT ON Students TO User1;

Here, ‘User1’ has given the permission to SELECT and INSERT data into the Students table.

REVOKE

REVOKE is used to remove the permissions given to the user for performing specific actions in database object.

Example:

REVOKE INSERT ON Students FROM User1;

Here, the permission to INSERT data into the ‘Students’ table has been revoked for ‘User1’.

Data Query Language (DQL)

Data Query Language plays an important role, which are responsible for querying and retrieving data from the database.

Common DQL Command:

SELECT

SELECT command is used to retrieve data from one or multiple tables. With SELECT, we can choose which columns of data we want to see and apply filters in it.

Example:

SELECT FirstName, LastName
FROM Students
WHERE StudentID = 1;

Here we are retrieving the first name and last name from the ‘Students’ table with a StudentID of 1.

Transaction Control Language (TCL)

Transaction Control Language commands are used to manage and control the transactions in the database. Transactions are a set of actions we perform in the database, like adding or deleting data.

Common TCL Commands:

COMMIT

COMMIT is a command to save all the changes we made in a transaction. It’s like using a ‘Save’ button. Once we COMMIT, all the changes becomes permanent in the database.

Example:

COMMIT;

ROLLBACK

Think ROLLBACK is like a “Cancel” button for transactions. If something goes wrong while you’re making changes, ROLLBACK lets you undo all the changes and take the database back to how it was before you started the transaction.

Example:

ROLLBACK;

SAVEPOINT

Think SAVEPOINT is like a bookmark within a transaction. SAVEPOINT allows us to mark a point in your work, and if something goes wrong later, you can go back to that specific point instead of starting all over again.

Example:

SAVEPOINT MyBackupPoint;

Here we have setted ‘MyBackupPoint’ as a backup point, if something goes wrong in the transaction, we can return to this marked point.

Conclusion

In this blog, we’ve explored each of the sub-languages of SQL (Structured Query Language). We’ve provided detailed explanations of DDL, DML, DCL, DQL, and TCL, along with the commands that make up these sub-languages.

In the next blog post, we will be posting a set of Multiple-Choice Questions (MCQs) based on the topics coved in this blog.