Short intro / TL;DR:

SQL is organized into command categories that each serve a different purpose: DDL (define schema), DML (manipulate data), DCL (control permissions), and TCL (manage transactions). This article explains each command, shows practical examples, points out vendor differences you should know, and gives sample scripts and best practices you can paste into a blog post.

What each SQL category means (quick summary)

DDL — Data Definition Language: Commands to create/modify/drop database objects (tables, indexes, schemas). Examples: CREATE, ALTER, DROP, TRUNCATE, RENAME.
DML — Data Manipulation Language: Commands to read and change the data inside objects. Examples: SELECT, INSERT, UPDATE, DELETE, MERGE (upsert).
DCL — Data Control Language: Manage access and privileges. Examples: GRANT, REVOKE (and DENY in SQL Server).
TCL — Transaction Control Language: Manage atomic units of work. Examples: BEGIN TRAN / START TRANSACTION, COMMIT, ROLLBACK, SAVEPOINT.

DDL (Data Definition Language)

DDL changes the shape of the database — tables, columns, constraints, indexes.

CREATE

Purpose: create tables, indexes, views, schemas.
Syntax (example):

CREATE TABLE Departments (
  DeptID   INT PRIMARY KEY,
  Name     VARCHAR(100) NOT NULL,
  Location VARCHAR(100)
);

When to use: new tables, indexes, views. Use migrations/DDL scripts under source control.

ALTER

Purpose: modify existing objects (add/modify/drop columns, change constraints).
Syntax (examples):

-- add column
ALTER TABLE Employees ADD HireDate DATE;

-- modify column (SQL Server example)
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);

-- drop column
ALTER TABLE Employees DROP COLUMN MiddleName;

Tip: Some engines lock the table for ALTER. For large tables prefer online/zero-downtime migration strategies.

DROP

Purpose: remove objects permanently.

DROP TABLE IF EXISTS TempTable;

Caution: DROP deletes schema + data. Always ensure backups or run in migration scripts.

TRUNCATE

Purpose: remove all rows quickly. Typically faster than DELETE because it deallocates pages.

TRUNCATE TABLE AuditLog;

Gotchas: Behavior varies by DBMS:

Often resets identity/autoincrement counters.
May be minimally logged, so it’s faster.
Some RDBMS treat TRUNCATE as DDL with implicit commit (e.g., older MySQL engines) — in other engines (PostgreSQL, SQL Server) it can be transactional. Check your DB’s docs before relying on rollback behavior.

RENAME

Purpose: rename table or object. Syntax varies:

PostgreSQL: ALTER TABLE oldname RENAME TO newname;
SQL Server: sp_rename 'oldname', 'newname';
Because syntax differs between DBMSs, prefer ALTER TABLE ... RENAME where supported.

DML (Data Manipulation Language)

DML is what you use day-to-day to read & change rows.

SELECT

Purpose: read data. Key clauses: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT/TOP.
Example:

SELECT DeptID, COUNT(*) AS EmployeeCount
FROM Employees
WHERE Active = 1
GROUP BY DeptID
HAVING COUNT(*) > 5
ORDER BY EmployeeCount DESC;

Tip: avoid SELECT * in production; list only needed columns.

INSERT

Insert single row:

INSERT INTO Employees (EmpID, Name, DeptID, Salary)
VALUES (101, 'Asha', 10, 50000);

Insert multiple rows / insert from select:

INSERT INTO ArchiveEmployees (EmpID, Name, DeptID)
SELECT EmpID, Name, DeptID FROM Employees WHERE Active = 0;

UPDATE

Purpose: change rows that meet a condition.

UPDATE Employees
SET Salary = Salary * 1.05
WHERE Performance = 'A';

Caution: UPDATE without WHERE changes every row.

DELETE

Purpose: remove rows.

DELETE FROM Employees WHERE EmpID = 999;

Note: DELETE logs each row (slower than TRUNCATE), but it can usually be rolled back inside a transaction.

MERGE (UPSERT)

Purpose: insert new rows or update existing rows in a single statement (supported in SQL Server, Oracle; alternatives exist in other DBs).
SQL Server example:

MERGE INTO TargetTable AS T
USING (SELECT EmpID, Salary FROM Staging) AS S
  ON T.EmpID = S.EmpID
WHEN MATCHED THEN
  UPDATE SET Salary = S.Salary
WHEN NOT MATCHED THEN
  INSERT (EmpID, Salary) VALUES (S.EmpID, S.Salary);

Alternatives: PostgreSQL INSERT ... ON CONFLICT, MySQL INSERT ... ON DUPLICATE KEY UPDATE.

Caveat: MERGE in some engines had edge-case bugs historically — test carefully or use engine-native upsert idioms.

DCL (Data Control Language)

Controls who can do what.

GRANT

Grant privileges to a user/role.

GRANT SELECT, INSERT ON Employees TO app_user;

REVOKE

Remove privileges:

REVOKE INSERT ON Employees FROM app_user;

DENY

SQL Server specific: explicitly deny a permission (takes precedence over grant).

DENY DELETE ON Employees TO some_user;

Best practice: use roles/groups (e.g., app_readonly, app_writer) and grant roles, not individual users. Keep least privilege principle.

TCL (Transaction Control Language)

TCL ensures consistency by grouping operations into atomic units.

BEGIN TRAN / START TRANSACTION

Start a transaction.

-- SQL Server
BEGIN TRAN;

-- MySQL / PostgreSQL
START TRANSACTION;

COMMIT

Persist all changes made in the transaction.

COMMIT;

ROLLBACK

Undo changes made in the transaction.

ROLLBACK;

SAVEPOINT

Create a named point to roll back part of a transaction.

SAVEPOINT before_bulk_update;
-- do updates
ROLLBACK TO SAVEPOINT before_bulk_update; -- undo updates only
COMMIT;

Important: keep transactions short to reduce lock contention. Use proper isolation levels to balance consistency and performance.

Other important keywords & clauses (how and when to use them)

WHERE

Filter rows.

SELECT * FROM Orders WHERE OrderDate >= '2025-01-01';

GROUP BY / HAVING

Aggregate rows; HAVING filters groups.

SELECT CustomerID, SUM(Amount) AS Total
FROM Orders
GROUP BY CustomerID
HAVING SUM(Amount) > 1000;

ORDER BY

Sort results.

SELECT * FROM Products ORDER BY CreatedAt DESC;

DISTINCT

Remove duplicates.

SELECT DISTINCT Country FROM Customers;

TOP / LIMIT

Return only N rows:

SQL Server: SELECT TOP 10 * FROM ...;
MySQL/Postgres: SELECT * FROM ... LIMIT 10;

JOINs (INNER, LEFT, RIGHT, FULL)

Combine rows from two tables.

Inner join (intersection):

SELECT e.Name, d.Name AS DeptName
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

Left join (all left rows + matching right):

SELECT e.Name, d.Name AS DeptName
FROM Employees e
LEFT JOIN Departments d ON e.DeptID = d.DeptID;

Right / Full join: symmetrical: RIGHT JOIN returns all right rows; FULL JOIN returns rows present in either table (some DBs may not support FULL JOIN).

UNION / EXCEPT / INTERSECT

Combine result sets (remove duplicates, unless UNION ALL).

SELECT Name FROM Customers
UNION
SELECT Name FROM Employees;

INTERSECT: rows common to both queries.
EXCEPT (or MINUS in Oracle): rows in first query not in second.

WITH (CTE — Common Table Expression)

Make query logic modular and readable:

WITH TopSellers AS (
  SELECT ProductID, SUM(Quantity) AS Qty
  FROM OrderItems
  GROUP BY ProductID
)
SELECT p.Name, t.Qty
FROM TopSellers t
JOIN Products p ON p.ProductID = t.ProductID
ORDER BY t.Qty DESC;

OVER, PARTITION BY (Window functions)

Compute aggregates over partitions without collapsing rows:

SELECT
  EmpID,
  DeptID,
  Salary,
  ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS RankInDept,
  SUM(Salary) OVER (PARTITION BY DeptID) AS DeptTotalSalary
FROM Employees;

Window functions are powerful for "top N per group", running totals, moving averages, etc.

Practical examples — small end-to-end script

-- DDL: create tables
CREATE TABLE Departments (
  DeptID INT PRIMARY KEY,
  Name VARCHAR(100)
);

CREATE TABLE Employees (
  EmpID INT PRIMARY KEY,
  Name VARCHAR(100),
  DeptID INT REFERENCES Departments(DeptID),
  Salary DECIMAL(12,2),
  Active BIT DEFAULT 1
);

-- DML: insert sample data
INSERT INTO Departments (DeptID, Name) VALUES (10, 'Engineering'), (20, 'HR');
INSERT INTO Employees (EmpID, Name, DeptID, Salary) VALUES
(1, 'Ravi', 10, 80000), (2, 'Leela', 10, 75000), (3, 'Sonal', 20, 60000);

-- TCL: transaction + savepoint
BEGIN TRAN;
UPDATE Employees SET Salary = Salary * 1.05 WHERE DeptID = 10;
SAVEPOINT sp1;
DELETE FROM Employees WHERE EmpID = 3; -- accidental?
ROLLBACK TO SAVEPOINT sp1; -- undo delete, keep salary update
COMMIT;

-- DML: select using window function
SELECT EmpID, Name, DeptID, Salary,
  ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS RankInDept
FROM Employees;

Best practices & real-world tips

Use migrations (scripts + version control) for DDL — don’t manually change production schema. Tools: Flyway, Liquibase, EF Migrations, etc.
Back up before destructive DDL (DROP, big ALTER).
Keep transactions short. Long-running transactions block other work and can cause locks.
Use parameterized queries to avoid SQL injection.
Index columns used in JOIN, WHERE, and ORDER BY; but don’t over-index (write penalty). Use EXPLAIN to inspect query plans.
Avoid SELECT *. Explicit columns are clearer and cheaper.
Be mindful of DBMS differences. TRUNCATE, MERGE, RENAME, privilege syntax and transaction semantics can differ. Test on your engine.
Use roles for permissions and avoid granting GRANT ALL to application users.

Common gotchas & vendor differences (short list)

TRUNCATE behavior and rollback semantics vary across engines — check documentation.
MERGE is supported differently and had issues historically in some engines — consider engine-native upsert patterns (e.g., INSERT ... ON CONFLICT in PostgreSQL).
DENY exists in SQL Server, not in MySQL/Postgres.
TOP vs LIMIT vs FETCH FIRST — syntax differs across DBMS.