DDL and DML Operations

SQL commands are divided into categories based on what they do. The two most fundamental categories are DDL (Data Definition Language) and DML (Data Manipulation Language).

DDL — Data Definition Language

DDL commands define and modify the structure of database objects like tables, indexes, and schemas.

CREATE

Creates a new table or database object:

CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    salary DECIMAL(10,2),
    hire_date DATE DEFAULT CURRENT_DATE
);

ALTER

Modifies an existing table structure:

-- Add a column
ALTER TABLE Employees ADD COLUMN email VARCHAR(255);

-- Modify a column type
ALTER TABLE Employees ALTER COLUMN salary TYPE DECIMAL(12,2);

-- Rename a column
ALTER TABLE Employees RENAME COLUMN email TO contact_email;

-- Drop a column
ALTER TABLE Employees DROP COLUMN contact_email;

DROP

Completely removes a table and its data:

DROP TABLE Employees;

Use DROP TABLE IF EXISTS to avoid errors:

DROP TABLE IF EXISTS Employees;

TRUNCATE

Removes all rows but keeps the table structure. Faster than DELETE because it doesn’t generate individual row delete logs:

TRUNCATE TABLE Employees;

DML — Data Manipulation Language

DML commands manage data within the tables.

INSERT

Adds new rows:

INSERT INTO Employees (id, name, salary)
VALUES (1, 'Alice', 60000);

-- Insert multiple rows at once
INSERT INTO Employees (id, name, salary) VALUES
(2, 'Bob', 55000),
(3, 'Charlie', 70000);

-- Insert from another table
INSERT INTO EmployeesArchive
SELECT * FROM Employees WHERE hire_date < '2020-01-01';

UPDATE

Modifies existing rows:

UPDATE Employees
SET salary = 65000
WHERE id = 1;

-- Update multiple columns
UPDATE Employees
SET salary = salary * 1.1, last_raised = CURRENT_DATE
WHERE department_id = 5;

DELETE

Removes rows:

DELETE FROM Employees WHERE id = 1;

-- Delete all rows (slower than TRUNCATE)
DELETE FROM Employees;

DDL vs DML Summary

Transaction Safety

DML commands are transactional — you can roll them back:

BEGIN;
DELETE FROM Employees;
-- Oops, I didn't mean to delete everything
ROLLBACK;
-- All rows are restored!

DDL commands in most databases are auto-committed and cannot be rolled back.

CREATE TABLE Products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category VARCHAR(50) DEFAULT 'General'
);

ALTER TABLE Products ADD COLUMN description TEXT;

INSERT INTO ArchivedOrders
SELECT * FROM Orders WHERE order_date < '2023-01-01';

UPDATE Employees
SET salary = salary * 1.15
WHERE department_id = 3 AND performance_rating = 'A';

DELETE FROM Orders
WHERE order_date < CURRENT_DATE - INTERVAL '1 year';

TRUNCATE TABLE StagingData;