DDL, DML, DCL, and TCL

The 4 Categories at a Glance

DDL Data Definition Language

DDL commands define the structure of your database — tables, columns, constraints, indexes. They answer: "What shape does the container have?"

CREATE — Build Something New

-- Create a table
CREATE TABLE employees (
  id       INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  email    VARCHAR(255) UNIQUE,
  dept_id  INT REFERENCES departments(id),
  salary   DECIMAL(10, 2) DEFAULT 0,
  hired_at DATE NOT NULL
);

-- Create an index
CREATE INDEX idx_emp_dept ON employees(dept_id);

-- Create a view
CREATE VIEW high_earners AS
  SELECT name, salary FROM employees WHERE salary > 100000;

ALTER — Change Existing Structure

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

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

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 0);

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

DROP — Destroy Permanently

-- Drop a table (all data gone!)
DROP TABLE employees;

-- Drop only if it exists (prevents errors)
DROP TABLE IF EXISTS temp_data;

TRUNCATE — Delete All Rows, Keep Structure

-- Remove all rows but keep the table
TRUNCATE TABLE logs;

DML Data Manipulation Language

DML commands work with the data inside tables. This is what you'll use 90% of the time.

SELECT — Read Data

SELECT name, salary FROM employees WHERE dept_id = 10 ORDER BY salary DESC;

INSERT — Add New Rows

-- Single row
INSERT INTO employees (id, name, email, dept_id, salary, hired_at)
VALUES (1, 'Alice', '[email protected]', 10, 95000, '2024-01-15');

-- Multiple rows
INSERT INTO employees (id, name, email, dept_id, salary, hired_at) VALUES
  (2, 'Bob', '[email protected]', 20, 90000, '2024-02-01'),
  (3, 'Charlie', '[email protected]', 10, 90000, '2024-03-10');

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

UPDATE — Modify Existing Rows

-- Update specific rows
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 10;

-- Update with a JOIN (PostgreSQL syntax)
UPDATE employees e
SET salary = salary * 1.05
FROM departments d
WHERE e.dept_id = d.id AND d.dept_name = 'Engineering';

DELETE — Remove Rows

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

-- Delete with a subquery
DELETE FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE archived = true);

MERGE / UPSERT

-- PostgreSQL: INSERT or UPDATE if exists
INSERT INTO employees (id, name, salary)
VALUES (1, 'Alice', 100000)
ON CONFLICT (id) DO UPDATE SET salary = EXCLUDED.salary;

DCL Data Control Language

DCL controls who can do what. It's the security layer of SQL.

GRANT — Give Permissions

-- Grant SELECT on a table
GRANT SELECT ON employees TO analyst_role;

-- Grant multiple permissions
GRANT SELECT, INSERT, UPDATE ON employees TO app_role;

-- Grant all on a schema
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin_role;

REVOKE — Remove Permissions

-- Revoke write access
REVOKE INSERT, UPDATE, DELETE ON employees FROM analyst_role;

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

TCL Transaction Control Language

TCL manages transactions — groups of operations that must succeed or fail together.

COMMIT — Save Changes

BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;  -- Both updates are now permanent

ROLLBACK — Undo Changes

BEGIN;
  DELETE FROM employees WHERE dept_id = 30;
  -- Oops, wrong department!
ROLLBACK;  -- Nothing was deleted

SAVEPOINT — Partial Rollback

BEGIN;
  INSERT INTO orders VALUES (1, 'Widget', 100);
  SAVEPOINT after_order;

  INSERT INTO payments VALUES (1, 100, 'credit_card');
  -- Payment failed!
  ROLLBACK TO after_order;
  -- Order still exists, payment rolled back

  INSERT INTO payments VALUES (1, 100, 'bank_transfer');
COMMIT;

When to Use What