DDL, DML, DQL, DCL and TCL commands in SQL with syntax

SQL Commands are categorized into 5 categories as below 

1. DDL - Data Definition Language
2. DML - Data Manipulation Language
3. DQL - Data Query Language
4. DCL - Data Control Language
5. TCL - Transaction Control Language

Within these SQL commands there are many sub-commands as below.

1. DDL - Data Definition Language

• CREATE
• DROP
• ALTER
• TRUNCATE
• COMMENT
• RENAME

CREATE - this command is used to create database, table or stored procedure

Example:

CREATE DATABASE Test-RPA;

Example:

CREATE Table EMP (
  ID INT PRIMARY KEY AUTO_INCREMENT, EmpId INT,
  F_Name VARCHAR(255) NOT NULL, L_Name VARCHAR(255), Dept VARCHAR(255)
  );

DROP - This command used to remove the database, table and stored procedure

Example:

DROP DATABASE Test-RPA;

Example:    

DROP TABLE EMP;

ALTER - This command is used to modify the structure of the database or its objects.

Add new column to the table

Example:

ALTER TABLE EMP ADD EMAIL_ID VARCHAR(250);

Modify the datatype of existing column

Example:

ALTER TABLE EMP ALTER L_Name VARCHAR(512);

Remove the existing column from the table

Example:

ALTER TABLE EMP DROP COLUMN  EMAIL_ID;

TRUNCATE - This command is used to remove all the records from a table

Example:

TRUNCATE TABLE EMP;

COMMENT - This command is used to add comments to the data table 

"--"

Example:

--Select the Emp data

SELECT * FROM EMP;

RENAME - This command is used to Rename an existing object in the database.

Example:

ALTER DATABASE "Test-RPA" RENAME TO "TestNew-RPA"

2. DML - Data Manipulation Language

• INSERT
• UPDATE
• DELETE
• LOCK
• CALL
• EXPLAIN PLAN

INSERT - This command is used to insert data into a table.

Syntax:

INSERT INTO table-name (column1, column2,.... column n) VALUES (value1, value2,... value n);

Example:

INSERT INTO EMP (EmpId, F_Name, L_Name, Dept) VALUES (1234,'Ashutosh', 'Kumar', 'RPA');

UPDATE - This command is used to update the data into a table.

Syntax:

UPDATE table-name SET column1 = value1, column2 = value2 WHERE condition;

Example:

UPDATE EMP SET L_Name = 'Maurya' WHERE EmpId = 1234;

DELETE - This command is used to delete the data from a table.

Syntax:

DELETE FROM table-name WHERE condition;

Example:

DELETE FROM EMP WHERE EmpId = 1234;

3. DQL - Data Query Language

• SELECT 

SELECT - This command is used to query on the table to see the output from the database.

Syntax:

Select * from Table_name;

Example:

Select * from EMP;

Filter data from Table

Select * From EMP where EmpId = 1234;

Select * From EMP where EmpId = 1234 AND Age < 30;

Fetch Selected Column from a Table 

Select F_Name, L_Name where EmpId >1223 AND Age < 27;

Fetch Maximum of 10 row from a table

Select * From EMP where EmpId = 1234 AND Age < 30 LIMIT 10;

Fetch Count of record from a table 

Select Count(*) from EMP;

Fetch Maximum Salary from a table 

Select Max(Salary) from EMP;

Fetch Minimum Salary from a table

Select Min(Salary) from EMP;

Fetch Sum of Salary from a table

Select Sum(Salary) from EMP;

Fetch Average of Salary from a table 

Select Avg(Salary) from EMP;

4. DCL - Data Control Language

• GRANT
• REVOKE

GRANT - This command is used to provide privileges to the database.

Syntax:

GRANT privileges_Name ON object TO  user

Example:

GRANT SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name' @ 'localhost';

REVOKE - This command is used to withdraw the user's access privileges given to the user.

Syntax:

REVOKE privileges_Name ON object TO  user

Example:

REVOKE SELECT, INSERT, DELETE, UPDATE ON Users TO 'Name' @ 'localhost';

5. TCL - Transaction Control Language

• COMMIT
• ROLLBACK
• SAVEPOINT
• SET TRANSACTION

COMMIT - This command is used to commit the transaction after insert, update and delete in the database.

Syntax:

commit;

Example:

DELETE from EMP where EmpId = 1234;

COMMIT;

ROLLBACK - This command is used to rollback the transaction in case of any error occurs.

Syntax:

rollback;

Example:

DELETE from EMP where EmpId = 1234;

ROLLBACK;

SAVEPOINT - This command is used to set a savepoint within a transaction

Syntax:

SAVEPOINT savepoint_name;

SET TRANSACTION - This command is used to mention the characteristics of the transaction.

Syntax:

SET TRANSACTION Access Name transaction_name;

I hope this article helps you to learn the various SQL commands & queries.

Happy Learning!!