Types of SQL Commands UNIT- 3

SQL commands are essential for managing databases effectively. These

commands are divided into categories such as Data Definition Language (DDL),
Data Manipulation Language (DML), Data Control Language (DCL), Data Query
Language (DQL), and Transaction Control Language (TCL). Each category serves
specific purposes, from defining database structures to managing transactions and
permissions.
SQL commands are extensively used to interact with databases, enabling
users to perform a wide range of actions on database systems. Understanding
these commands is crucial for effectively managing and manipulating data. SQL is
the database language by which we can perform certain operations on the existing
database, and we can also use this language to create a database.
SQL commands can be broadly categorized into five types:
1) Data Query Language (DQL): Primarily includes the SELECT statement.
2) Data Definition Language (DDL): Commands like CREATE, ALTER, and DROP used to
define database structure.
3) Data Manipulation Language (DML): Commands such as INSERT, UPDATE,
and DELETE to modify data.
4) Data Control Language (DCL): Includes GRANT and REVOKE to control access permissions.
5) Transaction Control Language (TCL): Commands like COMMIT, ROLLBACK,
and SAVEPOINT used to manage transactions.
1.1 DDL (DATA DEFINITION LANGUAGE)
Data Definition Language actually consists of the SQL commands that can
be used to define the database schema. It simply deals with descriptions of the
database schema and is used to create and modify the structure of database
objects in the database.
DDL is a set of SQL commands used to create, modify, and delete database
structures but not data. These commands are normally not used by a general user,
who should be accessing the database via an application.

1.1.1 LIST OF DDL COMMANDS:

Here are all the main DDL (Data Definition Language) commands along with their
syntax:
Command Description Syntax
CREATE Create database or its objects CREATE TABLE table_name (column1
data_type, column2 data_type,
(table, index, function, views, ...);
store procedure, and triggers)
DROP Delete objects from the database DROP TABLE table_name;
ALTER Alter the structure of the database ALTER TABLE table_name ADD COLUMN
column_name data_type;

TRUNCATE Remove all records from a table, TRUNCATE TABLE table_name;

including all spaces allocated for
the records are removed

COMMENT Add comments to the data COMMENT 'comment_text' ON TABLE

Prepared by Dr. Ajay D. Nagne Page 1 of 4

Types of SQL Commands UNIT- 3
dictionary table_name;
RENAME Rename an object existing in the RENAME TABLE old_table_name TO
new_table_name;
database

1.2 DQL (DATA QUERY LANGUAGE)

DQL statements are used for performing queries on the data within schema
objects. The purpose of the DQL Command is to get some schema relation based
on the query passed to it. We can define DQL as follows it is a component of SQL
statement that allows getting data from the database and imposing order upon it. It
includes the SELECT statement.
This command allows getting the data out of the database to perform
operations with it. When a SELECT is fired against a table or tables the result is
compiled into a further temporary table, which is displayed or perhaps received by
the program i.e. a front-end.
DQL COMMAND
There is only one DQL command in SQL i.e.
Command Description Syntax
SELECT It is used to retrieve data from SELECT column1, column2, ...FROM
table_name WHERE condition;
the database
1.3 DML (DATA MANIPULATION LANGUAGE)
The SQL commands that deal with the manipulation of data present in the
database belong to DML or Data Manipulation Language and this includes most of
the SQL statements.
It is the component of the SQL statement that controls access to data and to
the database. Basically, DCL statements are grouped with DML statements.

1.3.1 LIST OF DML COMMANDS

Here are all the main DML (Data Manipulation Language) commands along with
their syntax:
Command Description Syntax
INSERT Insert data into a INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
table
UPDATE Update existing data UPDATE table_name SET column1 = value1,
column2 = value2 WHERE condition;
within a table
DELETE Delete records from a DELETE FROM table_name WHERE condition;
database table
LOCK Table control LOCK TABLE table_name IN lock_mode;
concurrency
CALL Call a PL/SQL or CALL procedure_name(arguments);
JAVA subprogram
EXPLAIN Describe the access EXPLAIN PLAN FOR SELECT * FROM table_name;
PLAN path to data
1.4 DCL (DATA CONTROL LANGUAGE)
DCL includes commands such as GRANT and REVOKE which mainly deal with
the rights, permissions, and other controls of the database system.
Prepared by Dr. Ajay D. Nagne Page 2 of 4
Types of SQL Commands UNIT- 3
1.4.1 LIST OF DCL COMMANDS:
Two important DCL commands and their syntax are:
Command Description Syntax
GRANT Assigns new privileges to a user GRANT privilege_type
[(column_list)] ON [object_type]
account, allowing access to object_name TO user [WITH GRANT
specific database objects, OPTION];
actions, or functions.
REVOKE Removes previously granted REVOKE [GRANT OPTION FOR]
privilege_type [(column_list)] ON
privileges from a user account, [object_type] object_name FROM user
taking away their access to [CASCADE];
certain database objects or
actions.
1.5 TCL (TRANSACTION CONTROL LANGUAGE)
Transactions group a set of tasks into a single execution unit. Each transaction
begins with a specific task and ends when all the tasks in the group are
successfully completed. If any of the tasks fail, the transaction fails.
Therefore, a transaction has only two results: success or failure.

1.5.1 LIST OF TCL COMMANDS

Some TCL commands and their syntax are:
Command Description Syntax
BEGIN Starts a new transaction BEGIN TRANSACTION
[transaction_name];
TRANSACTION
COMMIT Saves all changes made during COMMIT;
the transaction
ROLLBACK Undoes all changes made during ROLLBACK;
the transaction
SAVEPOINT Creates a savepoint within the SAVEPOINT savepoint_name;
current transaction

2 DDL vs DML Commands

Explore the difference between DDL and DML commands in the below table. Understand
how DDL commands shape database structures, while DML commands manipulate data
within the database
SR.
DDL DML
NO.
1 Used to define database objects like tables, indexes, Used to manipulate data within the
views, etc. database.
2 Examples of DML statements include
Examples of DDL statements include CREATE,
SELECT, INSERT, UPDATE, and
ALTER, and DROP.
DELETE.
3 Changes made using DDL affect the structure of the Changes made using DML affect the data
database. stored in the database.
4 DDL statements are not transactional, meaning they DML statements are transactional, meaning

Prepared by Dr. Ajay D. Nagne Page 3 of 4

Types of SQL Commands UNIT- 3
SR.
DDL DML
NO.
cannot be rolled back. they can be rolled back if necessary.
5 DDL statements are usually executed by a database DML statements are executed by
administrator. application developers or end-users.
6 DDL statements are typically used during the design DML statements are used during normal
and setup phase of a database. operation of a database.
7 Examples of DDL statements: CREATE TABLE,
Examples of DML statements: SELECT,
DROP TABLE, ALTER TABLE, CREATE INDEX,
INSERT, UPDATE, DELETE, etc.
etc.

3 References
1) Elmasri, R., & Navathe, S.B. (2015). Fundamentals of Database Systems. 7th edition. Pearson
Education.
2) https://www.tutorialspoint.com/what-is-a-query-language-in-dbms
3) Date, C. J. (2004). An Introduction to database systems. 8th edition. Pearson Education.
4) https://www.techtarget.com/searchdatamanagement/definition/SQL
5) https://www.geeksforgeeks.org/sql-ddl-dql-dml-dcl-tcl-commands/

Prepared by Dr. Ajay D. Nagne Page 4 of 4