SQL SERVER - What is DDL, DML, DCL and TCL Commands

SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL. Using these statements, we can define the structure of a database by creating and altering database objects, and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or manage transactions to create a single unit of work.

The four main categories of SQL statements are as follows:

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

DML (Data Manipulation Language)

DML statements affect records in a table. 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.

DML statements include the following:

SELECT – select records from a table

INSERT – insert new records

             INSERT INTO Employee(EID, Department, Name, Salary) VALUES( 11,’HR’, ‘MICHAEL’,35000);

UPDATE – update/Modify existing records

             UPDATE Employee SET Salary= 35000 WHERE Name = ‘Mike’;

DELETE – delete existing records

             DELETE from Employee WHERE EID=10;

DDL (Data Definition Language)

DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects.

CREATE – create a new Table, database, schema

              CREATE TABLE (
                      < column_name1 > < datatype1 > < constraint1 >
                      < column_name2 > < datatype2 > < constraint2 >
                      < constraint-list >
                ) ;

ALTER – alter existing table, column description

              Alter table < table name > add < column name >< data type >;

DROP – delete existing objects from database

               Drop < table name >;

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

                GRANT [ privilege ] ON [ object ]TO [ user ][ WITH GRANT OPTION ]

REVOKE – keeps users from read/write permission on database objects

                REVOKE [ GRANT OPTION FOR ] [ permission ]ON [ object ] FROM [ user ]

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

             COMMIT;

ROLLBACK Transaction – ROLLBACK a transaction in case of any error

           ROLLBACK SavePoint-Name;

SAVEPOINT:
          SAVEPOINT SavePoint-Name;