DBMS: SQL Commands DDL, DML, DCL, TCL, and DQL

SQL commands are instructions to the database to perform a specific operation. For example, you can use SELECT command to read the data from database, you can use UPDATE command to update data in database. There are several commands that are available in SQL for various type of tasks and these commands are divided in following categories:

1. DDL (Data Definition Language) – Commands such as CREATE, DROP, ALTER.
2. DML (Data Manipulation Language) – Commands such as INSERT, UPDATE, DELETE.
3. DCL (Data Control Language) – Commands such as GRANT, REVOKE.
4. TCL (Transaction Control Language) – Commands such as COMMIT, ROLLBACK.
5. DQL (Data Query Language) – Commands such as SELECT.

1. DDL (Data Definition Language)

• DDL commands are used to create, drop and alter the databases, aliases, indexes, tables etc.
• Once a DDL statement is executed, it takes effect immediately in the database. Which means any changes done to the database using DDL command is permanent.
• Popular DDL commands are: CREATE, DROP, ALTER and TRUNCATE.

a) CREATE: This command is used to create database and tables inside a database.

Syntax for creating database:

CREATE DATABASE database_name;

Syntax for creating table:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

b) DROP: This command is used to drop database and tables.

Syntax for dropping database:

DROP DATABASE database_name;

Syntax for dropping table:

DROP TABLE table_name;

c) ALTER: This command is used to add, delete, or modify columns in an existing table.

Syntax: To add a new column in the existing table

ALTER TABLE table_name
ADD column_name datatype;

Example: The following ALTER TABLE statement will add a new column Address to the existing table Employees.

ALTER TABLE EMPLOYEES
ADD Address VARCHAR2(200);

d) TRUNCATE: This statement deletes all the rows from the table. This is different from the DROP command, the DROP command deletes the entire table along with the table schema, however TRUNCATE just deletes all the rows and leaves an empty table.

Syntax:

TRUNCATE TABLE table_name;

Example: Deletes all the rows from the table Employees.

TRUNCATE TABLE EMPLOYEES;

2. DML (Data Manipulation Language)

SQL commands that deals with the manipulation of data comes under DML category. The popular commands that come under DML are:

• INSERT − Insert data into a table.
• UPDATE − Update existing data in the specified table.
• DELETE − Delete records from the specified table in database.

a) INSERT: This command is used to insert records in a table

Syntax: When you are not inserting the data for all the columns and leaving some columns empty. In that case specify the column name and corresponding value.

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Syntax: When inserting the data for all the columns. No need to specify column name.

INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example: Adding an employee record into the table Employee. However adding the values for only two columns name & age into the table.

INSERT INTO EMPLOYEE (name, age) VALUES ("Chaitanya", "35");  

a) UPDATE: This is used to modify the existing records in a table.

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Updating the name and city of the employee with employee id “1001”. This will search all the records in the table where Employee_id is equal to 1001 then it will update the name field to “Chaitanya” and city field to “Noida” for all the filtered records.

UPDATE EMPLOYEE
SET name = 'Chaitanya', city = 'Noida'
WHERE Employee_id = 1001;

a) DELETE: It is used to delete the existing records from the table.

Syntax:

DELETE FROM table_name WHERE condition;

Example: The following SQL query will delete all the rows from the table Employees where name is equal to "Chaitanya".

DELETE FROM EMPLOYEES  
WHERE name="Chaitanya";  

3. DCL (Data Control Language)

DCL commands are used to set permissions on the table. This is the command that is responsible for the security of database. By setting up the permission, user can prevent unauthorised access to the database.

DCL commands are:

• GRANT
• REVOKE

a) Grant: It is used to give user access privileges to a database.

Example: The following command will grant users USER1 & USER2, the select, update and delete access to the table TABLE1 .

GRANT SELECT, UPDATE, DELETE ON TABLE1 TO USER1, USER2;

b) Revoke: It revokes the given access to the user.

Example: Let’s revoke the access from USER1 & USER2 given above using GRANT command.

REVOKE SELECT, UPDATE, DELETE ON TABLE1 FROM USER1, USER2;

4. TCL (Transaction Control Language)

The purpose of TCL commands is to maintain the consistency of the database. These commands are generally used along with the DML commands such as INSERT, UPDATE and DELETE. The changes made by DML commands are either committed or rolled back by TCL commands. There is another TCL command that can place a save point in the transactions which makes it possible to rollback all the transaction till the last save point.

TCL commands are:

• COMMIT
• ROLLBACK
• SAVEPOINT

a) Commit: Commit command make the changes made to the database permanent.

Syntax:

COMMIT;

Example: The following update command changes the name in the STUDENT table from "CPS" to "Chaitanya Pratap Singh". By using COMMIT just after the update statement save these changes permanently in the table.

UPDATE STUDENT 
SET NAME = ‘Chaitanya Pratap Singh’ 
WHERE NAME = ‘CPS’;
COMMIT;

b) Rollback: Rollback command is used to undo the changes that have been made to the database temporarily. The important point to note here is that the changes saved using COMMIT command cannot be undone using ROLLBACK command.

Syntax:

ROLLBACK;

Example: The following commands would have deleted all the students from the STUDENT table who have age greater than 15. Since we have used the ROLLBACK command after the DELETE statement, these changes were undone and no record deleted from the table.

The important point to note here is that DELETE and ROLLBACK are two different commands, they are not written together. To understand this, let’s say the user mistakenly written the following DELETE command but as soon as he realises his mistake, he use ROLLBACK command to undo the changes that were made unintentionally.

DELETE FROM STUDENT  
WHERE AGE > 15;  
ROLLBACK;

c) SAVEPOINT: This command helps in roll backing the transactions till a certain point. For example, a transaction consists of several DML commands and we want to undo the changes till a certain point and not completely, this can be achieved by using SAVEPOINT.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

INSERT into EMPLOYEE (name, age)
VALUES ('Chaitanya', 35);

Commit;

UPDATE EMPLOYEE 
SET name = ‘Ajeet’ 
WHERE age = 49;

SAVEPOINT A;

UPDATE EMPLOYEE 
SET name = ‘Hari’ 
WHERE age = 51;

Savepoint B;

UPDATE EMPLOYEE 
SET name = ‘Steve’ 
WHERE age = 38;

Savepoint C;

Now if we want to roll back the certain DML commands, we can do so by using Rollback like this:

This will rollback the transaction till save point A:

Rollback to A; 

This will rollback the transaction till save point B:

Rollback to B; 

5. DQL (Data Query Language)

DQL command is used to fetch data from the database. There is only one DQL command: SELECT.
a) SELECT: SELECT command fetches the records from the specified table that matches the given condition, if no condition is provided, it fetches all the records from the table.

Syntax:

SELECT expressions
FROM TABLE_NAME
WHERE conditions;

Example: The following query will fetch all the student names from the STUDENT table where the student age is greater than 15.

SELECT name
FROM STUDENT
WHERE age > 15;