DDL, DML & DCL

SQL commands aren’t all the same kind. They fall into four categories based on what they do. Knowing these categories helps us understand what a SQL statement is actually doing at a higher level.

The Four Categories

DDL — Data Definition Language

DDL commands define or modify the structure of our database — tables, columns, indexes, schemas. They don’t touch the actual data. Think of DDL as building the shelves, not putting books on them.

DDL commands are auto-committed. Once we run them, there’s no rolling back (in most databases).

CREATE

-- Create a new table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    department_id INT,
    salary DECIMAL(10,2) DEFAULT 0,
    hired_at TIMESTAMP DEFAULT NOW()
);

-- Create an index
CREATE INDEX idx_employees_department ON employees(department_id);

-- Create a schema (namespace)
CREATE SCHEMA hr;

ALTER

-- Add a column
ALTER TABLE employees ADD COLUMN phone VARCHAR(20);

-- Change a column's data type
ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);

-- Rename a column
ALTER TABLE employees RENAME COLUMN phone TO mobile;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT fk_department
    FOREIGN KEY (department_id) REFERENCES departments(id);

-- Drop a column
ALTER TABLE employees DROP COLUMN mobile;

DROP

-- Drop a table entirely (structure + data, gone forever)
DROP TABLE employees;

-- Drop only if it exists (avoids error)
DROP TABLE IF EXISTS employees;

-- Drop a table and everything that depends on it
DROP TABLE departments CASCADE;

TRUNCATE

-- Remove ALL rows but keep the table structure
TRUNCATE TABLE employees;

-- Faster than DELETE because it doesn't log individual row deletions
-- Resets auto-increment counters
-- Cannot be rolled back in most databases (it's DDL, not DML)

The key difference: DROP removes the table itself. TRUNCATE empties it but keeps the structure. DELETE removes specific rows (and is DML, not DDL).

DML — Data Manipulation Language

DML commands work with the actual data — the rows inside our tables. These are the commands we use 95% of the time.

DML commands are transactional. We can wrap them in BEGIN/COMMIT and roll them back if something goes wrong.

SELECT

-- Basic query
SELECT name, email FROM employees WHERE department_id = 5;

-- With sorting and limiting
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

-- With joins (more on this in the Joins note)
SELECT e.name, d.name AS department
FROM employees e
JOIN departments d ON e.department_id = d.id;

INSERT

-- Insert a single row
INSERT INTO employees (name, email, department_id, salary)
VALUES ('Manish', 'manish@example.com', 3, 75000);

-- Insert multiple rows
INSERT INTO employees (name, email, department_id, salary)
VALUES
    ('Alice', 'alice@example.com', 1, 80000),
    ('Bob', 'bob@example.com', 2, 70000),
    ('Charlie', 'charlie@example.com', 1, 90000);

-- Insert from a query
INSERT INTO archived_employees
SELECT * FROM employees WHERE hired_at < '2020-01-01';

UPDATE

-- Update specific rows
UPDATE employees SET salary = 85000 WHERE id = 1;

-- Update with a calculation
UPDATE employees SET salary = salary * 1.10 WHERE department_id = 3;
-- 10% raise for everyone in department 3

-- Update with a JOIN (PostgreSQL syntax)
UPDATE employees e
SET department_id = d.id
FROM departments d
WHERE d.name = 'Engineering' AND e.name = 'Manish';

DELETE

-- Delete specific rows
DELETE FROM employees WHERE id = 5;

-- Delete with a condition
DELETE FROM employees WHERE hired_at < '2015-01-01';

-- Delete all rows (but table structure remains — unlike TRUNCATE, this is logged)
DELETE FROM employees;

DCL — Data Control Language

DCL manages who can do what in the database. It’s about permissions and access control.

-- Grant SELECT permission on a table
GRANT SELECT ON employees TO readonly_user;

-- Grant all permissions
GRANT ALL PRIVILEGES ON employees TO admin_user;

-- Grant permission on a schema
GRANT USAGE ON SCHEMA hr TO analyst_role;

-- Revoke a permission
REVOKE DELETE ON employees FROM intern_user;

-- Revoke all
REVOKE ALL PRIVILEGES ON employees FROM ex_employee;

In real projects, we usually manage permissions through roles, not individual users:

-- Create a role and assign permissions
CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Assign the role to a user
GRANT analyst TO manish;

TCL — Transaction Control Language

TCL commands manage transactions — grouping multiple DML operations into an all-or-nothing unit.

-- Start a transaction
BEGIN;

-- Do some work
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE id = 2;

-- Save a checkpoint (we can rollback to here instead of the beginning)
SAVEPOINT after_transfer;

-- Do more work that might fail
UPDATE audit_log SET last_transfer = NOW() WHERE account_id = 1;

-- Oops, something went wrong? Rollback to the savepoint
ROLLBACK TO SAVEPOINT after_transfer;

-- Or commit everything
COMMIT;

DROP vs TRUNCATE vs DELETE

This comparison comes up a lot in interviews:

Interview Tip

Interviewers love asking “What’s the difference between DELETE and TRUNCATE?” or “Is TRUNCATE DDL or DML?” The answer is that TRUNCATE is DDL (it’s a structural operation that deallocates pages), DELETE is DML (it logs each row deletion and fires triggers). Knowing this distinction shows we understand what’s happening under the hood.