Master SQL: A Comprehensive Guide to DDL, DML, DCL, and TCL

Posted on June 10, 2024

Structured Query Language (SQL) is the backbone of managing and manipulating relational databases. Whether you’re working with MySQL, PostgreSQL, SQLite, or any other SQL-based database, knowing the different facets of SQL is crucial. Let’s delve into the main sub-languages of SQL: DDL, DML, DCL, and TCL.

Data Definition Language (DDL)

DDL commands help define and manage database structures such as tables, schemas, and indexes. Here are some essential DDL commands:

  • CREATE: Use this command to create a new table.
SQL
CREATE TABLE Employees (
    EmployeeID int,
    Name varchar(100),
    Position varchar(50),
    Salary decimal(10, 2)
);
  • ALTER: Modify an existing table.
SQL
ALTER TABLE Employees
ADD COLUMN Department varchar(50);
  • DROP: Delete a table.
SQL
DROP TABLE Employees;
  • TRUNCATE: Remove all records from a table without deleting the table.
SQL
TRUNCATE TABLE Employees;

Data Manipulation Language (DML)

DML commands handle data within the database. Here are the primary DML commands:

  • SELECT: Retrieve data from a table.
SQL
SELECT * FROM Employees;
  • INSERT: Add new records to a table.
SQL
INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES (1, 'John Doe', 'Software Engineer', 70000.00);
  • UPDATE: Modify existing records.
SQL
UPDATE Employees
SET Salary = 75000.00
WHERE EmployeeID = 1;
  • DELETE: Remove records from a table.
SQL
DELETE FROM Employees
WHERE EmployeeID = 1;

Data Control Language (DCL)

DCL commands manage access permissions. Here are the key DCL commands:

  • GRANT: Give users specific permissions.
SQL
GRANT SELECT, INSERT ON Employees TO User1;
  • REVOKE: Remove permissions from users.
SQL
REVOKE INSERT ON Employees FROM User1;

Transaction Control Language (TCL)

TCL commands manage transactions within the database. Here are the essential TCL commands:

  • COMMIT: Save all changes made during the current transaction.
SQL
COMMIT;
  • ROLLBACK: Undo changes made during the current transaction.
SQL
ROLLBACK;
  • SAVEPOINT: Set a savepoint within a transaction.
SQL
SAVEPOINT Savepoint1;
  • RELEASE SAVEPOINT: Remove a previously defined savepoint.
SQL
RELEASE SAVEPOINT Savepoint1;

Popular SQL Databases

  • MySQL: One of the most popular open-source databases used widely in web applications.
  • PostgreSQL: Known for its advanced features and standards compliance, suitable for large-scale applications.
  • SQLite: A lightweight, self-contained database engine commonly used in mobile applications and small-scale projects.
  • Microsoft SQL Server: A comprehensive database management system developed by Microsoft for enterprise environments.
  • Oracle Database: A robust database solution designed for complex applications requiring high performance and security.

Understanding and utilising these commands effectively will enhance your ability to manage and manipulate your databases, ensuring your applications run smoothly and efficiently. Happy querying!

If you are looking for SQL resources:

Youtube: https://www.youtube.com/watch?v=HXV3zeQKqGY
Amazon (India): https://amzn.to/3RhVa1q
Amazon: https://amzn.to/3VBKu0t
Online: https://www.tutorialspoint.com/sql/index.htm, https://www.javatpoint.com/sql-tutorial