DEV Community

Ahmed Kadiwala
Ahmed Kadiwala

Posted on

SQL Commands Explained: DDL vs DML vs DQL vs DCL vs TCL

SQL (Structured Query Language) is the backbone of working with databases. But SQL is not just about writing SELECT * FROM table;.

It has multiple categories of commands, each with a specific purpose. If you’ve ever been confused between DDL, DML, DQL, DCL, and TCL, this blog will clear it up for you with simple explanations and examples.


πŸ—οΈ 1. DDL (Data Definition Language)

Definition:

DDL commands are used to define and manage the structure of database objects like tables, schemas, and indexes.

Common Commands:

  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
  • RENAME

Examples:

-- Create a table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

-- Alter table to add a new column
ALTER TABLE Students ADD Email VARCHAR(100);

-- Drop a table
DROP TABLE Students;

-- Truncate a table (removes all data but keeps structure)
TRUNCATE TABLE Students;

-- Rename a table
ALTER TABLE Students RENAME TO Learners;
Enter fullscreen mode Exit fullscreen mode

✏️ 2. DML (Data Manipulation Language)

Definition:

DML commands are used to manipulate the actual data stored inside tables.

Common Commands:

  • INSERT
  • UPDATE
  • DELETE
  • MERGE

Examples:

-- Insert a new record
INSERT INTO Students (StudentID, Name, Age) VALUES (1, 'Alice', 20);

-- Update existing data
UPDATE Students SET Age = 21 WHERE StudentID = 1;

-- Delete a record
DELETE FROM Students WHERE StudentID = 1;

-- Merge data (commonly used in SQL Server/Oracle)
MERGE INTO Students S
USING NewStudents N
ON (S.StudentID = N.StudentID)
WHEN MATCHED THEN UPDATE SET S.Age = N.Age
WHEN NOT MATCHED THEN INSERT (StudentID, Name, Age) VALUES (N.StudentID, N.Name, N.Age);
Enter fullscreen mode Exit fullscreen mode

πŸ” 3. DQL (Data Query Language)

Definition:

DQL is focused on querying and retrieving data. The main command is SELECT.

But querying becomes powerful with the use of clauses and operators.

Main Command:

  • SELECT

Clauses:

  • WHERE – filter rows
  • GROUP BY – group rows by column values
  • HAVING – filter groups
  • ORDER BY – sort results
  • LIMIT / TOP – restrict the number of rows

Operators:

  • LIKE – pattern matching
  • IN – match any value in a list
  • BETWEEN – range check
  • Comparison (=, >, <, >=, <=)
  • Logical (AND, OR, NOT)

Examples:

-- Simple SELECT
SELECT Name, Age FROM Students;

-- WHERE clause
SELECT * FROM Students WHERE Age > 18;

-- LIKE operator
SELECT * FROM Students WHERE Name LIKE 'A%';

-- IN operator
SELECT * FROM Students WHERE Age IN (18, 20, 22);

-- BETWEEN operator
SELECT * FROM Students WHERE Age BETWEEN 18 AND 25;

-- GROUP BY + HAVING
SELECT Age, COUNT(*) AS Count
FROM Students
GROUP BY Age
HAVING COUNT(*) > 2;

-- ORDER BY
SELECT * FROM Students ORDER BY Age DESC;

-- LIMIT (MySQL/PostgreSQL)
SELECT * FROM Students LIMIT 5;

-- TOP (SQL Server)
SELECT TOP 5 * FROM Students;
Enter fullscreen mode Exit fullscreen mode

πŸ”‘ 4. DCL (Data Control Language)

Definition:

DCL commands are used to control access to data inside the database.

Common Commands:

  • GRANT
  • REVOKE

Examples:

-- Grant SELECT permission
GRANT SELECT ON Students TO user1;

-- Revoke permission
REVOKE SELECT ON Students FROM user1;
Enter fullscreen mode Exit fullscreen mode

πŸ”„ 5. TCL (Transaction Control Language)

Definition:

TCL commands handle transactions in databases. They help maintain data consistency by grouping multiple DML operations.

Common Commands:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT
  • SET TRANSACTION

Examples:

-- Start a transaction
BEGIN;

-- Insert data
INSERT INTO Students (StudentID, Name, Age) VALUES (2, 'Bob', 22);

-- Commit changes
COMMIT;

-- Rollback example
BEGIN;
UPDATE Students SET Age = 25 WHERE StudentID = 2;
ROLLBACK; -- Undo the update

-- Savepoint example
BEGIN;
INSERT INTO Students VALUES (3, 'Charlie', 23);
SAVEPOINT sp1;
UPDATE Students SET Age = 30 WHERE StudentID = 3;
ROLLBACK TO sp1; -- Undo update but keep insert
COMMIT;
Enter fullscreen mode Exit fullscreen mode

πŸ“Œ Summary: DQL at the Core

  • DQL = SELECT + Clauses + Operators
  • It’s what we use the most when working with databases daily.
  • DDL defines structure, DML manipulates data, DCL controls access, and TCL ensures safe transactions.

πŸ“Š Quick Reference Comparison Table

Category Commands Clauses (DQL only) Operators (DQL only)
DDL CREATE, ALTER, DROP, TRUNCATE, RENAME - -
DML INSERT, UPDATE, DELETE, MERGE - -
DQL SELECT WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, TOP LIKE, IN, BETWEEN, =, >, <, AND, OR, NOT
DCL GRANT, REVOKE - -
TCL COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION - -

πŸš€ Takeaway

Think of SQL like this:

  • DDL builds the house 🏠
  • DML arranges the furniture πŸͺ‘
  • DQL lets you explore inside πŸ”
  • DCL decides who gets the keys πŸ”‘
  • TCL ensures everything stays safe and consistent πŸ”„

Master these categories, and you’ll have full control over any database you work with!

Top comments (0)