The most common types of SQL (Structured Query Language) are used for managing and manipulating relational databases. SQL commands are broadly categorized based on their functionality:
1. How the Families Fit Together
The classification is about intent. DDL (Data Definition Language) changes the schema: tables, views, indexes. DML (Data Manipulation Language) changes rows. DQL (Data Query Language) reads them; many texts fold SELECT into DML, which is why it appears in both lists below. DCL (Data Control Language) manages privileges. TCL (Transaction Control Language) groups DML statements into atomic units. Whether DDL is transactional varies: PostgreSQL can roll back most DDL, while MySQL and Oracle commit implicitly before and after DDL statements.
1. Data Definition Language (DDL)
Purpose:
DDL commands are used to define and manage database structures such as tables, indexes, and schemas.
Common Commands:
CREATE
- Creates a new table, database, index, or other objects.
ALTER
- Modifies the structure of an existing database object (e.g., adding or modifying columns in a table).
DROP
- Deletes an existing database object such as a table or index.
TRUNCATE
- Removes all records from a table, but the table structure remains.
2. Data Manipulation Language (DML)
Purpose:
DML commands are used for inserting, updating, deleting, and retrieving data within the database.
Common Commands:
SELECT
- Retrieves data from one or more tables. It is the most commonly used SQL command.
INSERT
- Adds new rows of data to a table.
UPDATE
- Modifies existing data in a table.
DELETE
- Removes existing data from a table.
3. Data Control Language (DCL)
Purpose:
DCL commands are used to control access to data in the database.
Common Commands:
GRANT
- Provides specific privileges to users or roles (e.g., the ability to query or modify tables).
REVOKE
- Removes previously granted privileges from users or roles.
4. Transaction Control Language (TCL)
Purpose:
TCL commands are used to manage transactions in the database, ensuring the integrity of data.
Common Commands:
COMMIT
- Saves the current transaction's changes permanently in the database.
ROLLBACK
- Undoes the changes made in the current transaction.
SAVEPOINT
- Sets a point within a transaction to which you can later roll back.
SET TRANSACTION
- Specifies characteristics for the transaction, such as isolation level.
5. Data Query Language (DQL)
Purpose:
DQL is primarily concerned with the retrieval of data from the database.
Common Commands:
SELECT
- While technically part of DML, SELECT is often considered its own category (DQL) due to its significance in querying data.
7. Additional SQL Variants and Extensions
Procedural SQL (PL/SQL, T-SQL, etc.):
SQL extensions that introduce procedural elements (e.g., loops, conditionals) to SQL. Common in Oracle (PL/SQL) and SQL Server (T-SQL).
Analytical SQL:
Includes commands for complex data analysis, like WINDOW FUNCTIONS (e.g., ROW_NUMBER, RANK), GROUP BY CUBE, and ROLLUP.
8. How a SELECT Is Evaluated
SQL is declarative: you describe the result, and the optimizer chooses join orders, indexes and algorithms. The logical order in the diagram defines what a query means, even though the physical execution plan may differ. Understanding it explains several rules: aggregates cannot appear in WHERE (groups do not exist yet), HAVING filters after aggregation, and window functions are computed after grouping, so to filter on a window function result you wrap the query in a subquery or common table expression. Use EXPLAIN to see the physical plan.
9. Worked Example: All Five Families
-- DDL
CREATE TABLE accounts (
id INT PRIMARY KEY,
owner VARCHAR(100) NOT NULL,
balance DECIMAL(12,2) NOT NULL CHECK (balance >= 0)
);
-- DML
INSERT INTO accounts VALUES (1, 'Alice', 500.00), (2, 'Bob', 100.00);
-- TCL: transfer money atomically
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT; -- or ROLLBACK; if either update failed
-- DQL
SELECT owner, balance,
RANK() OVER (ORDER BY balance DESC) AS rnk
FROM accounts
WHERE balance > 0
ORDER BY rnk;
-- DCL
CREATE ROLE reporting;
GRANT SELECT ON accounts TO reporting;
REVOKE SELECT ON accounts FROM reporting;
The transfer shows why TCL matters: without the transaction, a failure between the two updates would lose 50 from Alice without crediting Bob. The CHECK constraint is enforced by the engine, so an overdraft makes the first UPDATE fail and the whole transaction can be rolled back.
10. Choosing the Right Command
Goal
Command
Notes
Remove all rows quickly
TRUNCATE
Minimal logging, resets storage; usually no WHERE and no row triggers
Remove some rows
DELETE ... WHERE
Logged per row, fires triggers, can be rolled back
Remove the table itself
DROP TABLE
Removes data and definition
Insert or update in one step
MERGE / INSERT ... ON CONFLICT
Syntax varies by engine
Partial undo inside a transaction
SAVEPOINT / ROLLBACK TO
Keeps earlier work in the transaction
11. Pitfalls
UPDATE or DELETE without a WHERE clause affects every row; run the equivalent SELECT first.
Comparing with NULL using = is never true; use IS NULL.
Granting privileges to individual users instead of roles makes access hard to audit.
Long open transactions hold locks and block other sessions.
Relying on implicit ordering: without ORDER BY, row order is not guaranteed.