SQL: DDL, DML, DCL

Structured Query Language (SQL) is the standard language for dealing with Relational Databases. To understand how SQL works, it is best to categorize its commands into three distinct sub-languages based on their function.

1. Data Definition Language (DDL)

DDL commands are used to define the database schema. They deal with descriptions of the database schema and are used to create and modify the structure of database objects in the database.

Note: DDL commands are auto-committed. This means that they permanently save all the changes in the database immediately, and cannot be rolled back in most databases (like Oracle and MySQL).

Core DDL Commands:

• CREATE: Used to create the database or its objects (like tables, indexes, functions, views, store procedures, and triggers).
• ALTER: Used to alter the structure of the database.
• DROP: Used to delete objects from the database entirely.
• TRUNCATE: Used to remove all records from a table, including all spaces allocated for the records are removed.

-- Creating a table (DDL)
CREATE TABLE Students (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

-- Modifying the structure (DDL)
ALTER TABLE Students ADD COLUMN Email VARCHAR(100);

-- Deleting the table permanently (DDL)
DROP TABLE Students;

2. Data Manipulation Language (DML)

DML commands deal with the manipulation of data present in the database. They are responsible for reading, inserting, modifying, and deleting the actual rows of data inside the tables.

Note: DML commands are not auto-committed. This means changes made by DML commands are not permanent until you issue a COMMIT command, and they can be undone using a ROLLBACK command.

Core DML Commands:

• SELECT: Used to retrieve data from the database. (Some strictly categorize this as DQL - Data Query Language, but it is often grouped under DML).
• INSERT: Used to insert data into a table.
• UPDATE: Used to update existing data within a table.
• DELETE: Used to delete specific records from a database table.

-- Inserting a row (DML)
INSERT INTO Students (ID, Name, Age) 
VALUES (1, 'Alice', 20);

-- Modifying existing data (DML)
UPDATE Students 
SET Age = 21 
WHERE ID = 1;

-- Deleting a specific row (DML)
DELETE FROM Students 
WHERE ID = 1;

3. Data Control Language (DCL)

DCL commands deal with the rights, permissions, and other controls of the database system. They are used to grant or revoke access privileges to specific users or roles.

Core DCL Commands:

• GRANT: Gives user access privileges to database.
• REVOKE: Withdraws user access privileges given by using the GRANT command.

-- Granting a user permission to read the table
GRANT SELECT ON Students TO 'dinesh'@'localhost';

-- Revoking permission to modify the table
REVOKE UPDATE ON Students FROM 'dinesh'@'localhost';

Transaction Control Language (TCL)

Often grouped alongside DCL, TCL commands manage the transactions in the database. They are used to manage the changes made by DML statements.

• COMMIT: Commits a transaction.
• ROLLBACK: Rolls back a transaction in case of any error occurs.
• SAVEPOINT: Sets a savepoint within a transaction.