DDL, DML, DCL and TCL Statements in SQL with Examples

DDL, DML, DCL and TCL Statements in SQL

SQL language is divided into four types of primary language statements.
They are DML, DDL, DCL and TCL.

1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

Using these statements, we can define the structure of a database by creating and altering database objects.
These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records.
We also can control which user can read/write data or manage transactions to create a single unit of work.

DML statements include the following:

SELECT – select records from a table
INSERT – insert new records
UPDATE – update/Modify existing records
DELETE – delete existing records

DDL (Data Definition Language)

DDL statements are used to alter/modify a database or table structure and schema.

statements are used to define the database structure or schema.

CREATE – create a new Table, database, schema
ALTER – alter existing table, column description
DROP – delete existing objects from database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

DCL (Data Control Language)

DCL statements control the level of access that users have on database objects.

GRANT – allows users to read/write on certain database objects
REVOKE – keeps users from read/write permission on database objects
TCL (Transaction Control Language)

TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements.

BEGIN Transaction – opens a transaction
COMMIT Transaction – commits a transaction
ROLLBACK Transaction – ROLLBACK a transaction in case of any error