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 DCL DDL TCL SQL

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;

No comments: