SQL Commands: DQL, DDL, DML, DCL & TCL with Examples

What is SQL?

SQL is a database language designed for the retrieval and management of data in a relational database.

SQL is the standard language for database management. All the RDBMS systems like MySQL, MS Access, Oracle, Sybase, Postgres, and SQL Server use SQL as their standard database language. The SQL programming language uses various commands for different operations. We will learn about the DCL, TCL, DQL, DDL, and DML commands in SQL with examples.

Why Use SQL?

Here are important reasons for using SQL:

• It helps users access data in the RDBMS system.
• It helps you describe the data.
• It allows you to define the data in a database and manipulate that specific data.
• With the help of SQL commands, you can create and drop databases and tables.
• SQL lets you use functions in a database, and create views and stored procedures.
• You can set permissions on tables, procedures, and views.

Brief History of SQL

Here are important landmarks from the history of SQL:

• 1970 – Dr. Edgar F. “Ted” Codd described a relational model for databases.
• 1974 – Structured Query Language appeared, originally named SEQUEL at IBM.
• 1979 – Oracle released the first commercially available SQL-based database, based on IBM’s System R research prototype.
• 1986 – ANSI published the first SQL standard, SQL-86.
• 1989 – SQL-89 arrived as the first revision of the standard.
• 1992 – SQL-92 (SQL2) became the major baseline still referenced by most databases today.
• 1999 – SQL:1999 (SQL3) launched with features like triggers, recursive queries, and object-orientation.
• 2003 – SQL:2003 added window functions, sequence generators, and XML-related features.
• 2006 – SQL:2006 added support for XML Query Language (XQuery).
• 2008 – SQL:2008 introduced the TRUNCATE statement and INSTEAD OF triggers.
• 2011 – SQL:2011 improved support for temporal databases.
• 2016 – SQL:2016 added JSON support and row pattern matching (MATCH_RECOGNIZE).
• 2019 – SQL/MDA (Part 15) added multi-dimensional array support to the standard.
• 2023 – SQL:2023 added a native JSON data type and property graph queries (SQL/PGQ).
• 2024 – GQL, the first new ISO database language since SQL, was published as a graph query companion standard.
• 2026 – SQL:2023 remains the current standard, with the next revision under development at ISO/IEC.

Types of SQL Commands

Here are five types of widely used SQL commands:

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
• Data Query Language (DQL)

The table below summarises the five categories before each is covered in detail.

What is DDL?

Data Definition Language helps you define the database structure or schema. Let us learn about the DDL commands with syntax. The main DDL commands in SQL are CREATE, DROP, ALTER, and TRUNCATE.

CREATE

The CREATE statement is used to define the database structure or schema:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

For example:

Create database university;
Create table students;
Create view for_students;

DROP

The DROP command removes tables and databases from the RDBMS.

Syntax:

DROP TABLE ;

For example:

Drop object_type object_name;
Drop database university;
Drop table student;

ALTER

The ALTER command allows you to alter the structure of the database.

Syntax: to add a new column in the table:

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

For example:

Alter table guru99 add subject varchar;

TRUNCATE

This command is used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table students;

What is Data Manipulation Language?

Data Manipulation Language (DML) allows you to modify the database instance by inserting, modifying, and deleting its data. It is responsible for performing all types of data modification in a database. The important DML commands in SQL are:

• INSERT
• UPDATE
• DELETE

INSERT

This statement is used to insert data into the rows of a table.

Syntax:

INSERT INTO TABLE_NAME (col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Or
INSERT INTO TABLE_NAME
VALUES (value1, value2, value3, .... valueN);

For example:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

UPDATE

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

For example:

UPDATE students
SET FirstName = 'Jhon', LastName= 'Wick'
WHERE StudID = 3;

DELETE

This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM students
WHERE FirstName = 'Jhon';

What is DCL?

DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to give rights and permissions. Other commands control parameters of the database system.

Grant

This command is used to give a user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

For example:

GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke

This command is useful to take back permissions from the user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

What is TCL?

Transaction Control Language, or TCL commands, deal with the transaction within the database. They work directly with the states described under transaction management.

Commit

This command is used to save all the transactions to the database.

Syntax:

Commit;

For example:

DELETE FROM Students
WHERE RollNo =25;
COMMIT;

Rollback

The Rollback command allows you to undo transactions that have not already been saved to the database.

Syntax:

ROLLBACK;

Example:

DELETE FROM Students
WHERE RollNo =25;

SAVEPOINT

This command helps you set a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINT RollNo;

What is DQL?

Data Query Language (DQL) is used to fetch data from the database. It uses only one command, SELECT.

SELECT

This command helps you select the attributes based on the condition described by the WHERE clause.

Syntax:

SELECT expressions
FROM TABLES
WHERE conditions;

For example:

SELECT FirstName
FROM Student
WHERE RollNo > 15;

DELETE vs TRUNCATE vs DROP

These three commands all remove data, but they differ in what they remove and whether the action can be rolled back. Confusing them is a common beginner mistake.

In short, use DELETE to remove specific rows, TRUNCATE to empty a table quickly, and DROP to discard the table entirely.

FAQs