SQL - Intro,DDL, DML

Introduction

If you've worked with data you've probably come across SQL.

SQL (Structured Query Language) is the structured language used to interact with relational databases. Relational databases include PostgreSQL, MySQL, SQL Server, and Oracle.

SQL is used to:

• retrieve data
• insert new data
• update existing data
• delete data
• define database structures
• control access to data

SQL works by allowing users to ask structured questions from data and receive results.

Two of the most fundamental categories are:

• DDL (Data Definition Language) - commands that define and modify the structure of database objects (tables, schemas, indexes).
• DML (Data Manipulation Language) - commands that work with the data stored inside those structures (inserting, updating, deleting, querying rows).

Understanding the difference between "shaping the container" (DDL) and "working with what's inside it" (DML) is one of the first things that makes SQL click.

Part 1: DDL - Data Definition Language

DDL commands define, modify, and remove the structure of database objects. These changes affect the schema itself, not the data inside it. Most DDL statements in PostgreSQL are auto-committed, meaning they take effect immediately.

CREATE

Defines a new database object - a table, schema, index, or view.

CREATE TABLE employees (
    employee_id     SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    department      VARCHAR(50),
    hire_date       DATE DEFAULT CURRENT_DATE,
    salary          NUMERIC(10, 2) CHECK (salary >= 0)
);

This creates a table with a primary key, required fields, a default value, and a constraint that prevents negative salaries.

ALTER

Modifies the structure of an existing object: adding, dropping, or changing columns and constraints.

-- Add a new column
ALTER TABLE employees ADD COLUMN email VARCHAR(100);

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

-- Add a constraint after the table already exists
ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE (email);

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

DROP

Permanently removes an object and all its data. There's no undo — use with care, especially in production.

DROP TABLE employees;

-- Safer version: won't error if the table doesn't exist
DROP TABLE IF EXISTS employees;

TRUNCATE

Removes all rows from a table instantly, but keeps the table structure intact. Faster than DELETE for clearing a whole table because it doesn't scan row by row.

TRUNCATE TABLE employees;

RENAME

Renames an existing table or column.

ALTER TABLE employees RENAME TO staff;
ALTER TABLE staff RENAME COLUMN first_name TO given_name;

---

Part 2: DML - Data Manipulation Language

DML commands work with the actual rows of data inside tables — reading, adding, changing, and removing them. Unlike most DDL, DML changes typically need to be committed (COMMIT) or can be rolled back (ROLLBACK) within a transaction.

INSERT

Adds new rows to a table.

INSERT INTO employees (first_name, last_name, department, salary)
VALUES ('Amina', 'Otieno', 'Finance', 85000.00);

-- Insert multiple rows in one statement
INSERT INTO employees (first_name, last_name, department, salary)
VALUES
    ('Brian', 'Kamau', 'Sales', 62000.00),
    ('Cynthia', 'Wanjiru', 'Marketing', 58000.00);

SELECT

Retrieves data from one or more tables. Technically part of DQL (Data Query Language) in strict definitions, but it's used constantly alongside DML and is essential to understand here.

-- Basic retrieval
SELECT first_name, last_name, salary
FROM employees
WHERE department = 'Sales';

-- With sorting and filtering
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000
ORDER BY avg_salary DESC;

UPDATE

Modifies existing rows that match a condition. Always use a WHERE clause - an UPDATE without one changes every row in the table.

UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Sales';

-- Update multiple columns at once
UPDATE employees
SET department = 'Business Development', salary = 70000.00
WHERE employee_id = 3;

DELETE

Removes rows that match a condition. Like UPDATE, omitting WHERE deletes every row in the table.

DELETE FROM employees
WHERE department = 'Marketing';

-- Delete a single record by primary key
DELETE FROM employees
WHERE employee_id = 5;

---

DDL vs. DML: Quick Reference

Aspect	DDL	DML
Affects	Structure (schema, tables, columns)	Data (rows, values)
Common commands	CREATE, ALTER, DROP, TRUNCATE	INSERT, SELECT, UPDATE, DELETE
Auto-committed?	Usually yes	No — can be rolled back in a transaction
Reversible?	Rarely (DROP is permanent)	Yes, if wrapped in a transaction before commit

---

Practical Example: Putting It Together

Here's a small end-to-end sequence showing how DDL and DML work together when standing up a new table:

-- 1. DDL: Define the structure
CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    order_total   NUMERIC(10, 2),
    order_date    DATE DEFAULT CURRENT_DATE
);

-- 2. DML: Populate it with data
INSERT INTO orders (customer_name, order_total)
VALUES
    ('Wafula Auto Parts', 1250.00),
    ('Green Valley Traders', 430.50);

-- 3. DML: Query it
SELECT * FROM orders WHERE order_total > 500;

-- 4. DDL: Evolve the structure as requirements change
ALTER TABLE orders ADD COLUMN status VARCHAR(20) DEFAULT 'pending';

-- 5. DML: Update existing rows to reflect the new column
UPDATE orders SET status = 'shipped' WHERE order_id = 1;

This pattern; define, populate, query, evolve - is the everyday rhythm of working with a relational database.

---

Key Takeaways

• DDL shapes the database: CREATE, ALTER, DROP, TRUNCATE.
• DML works with the data inside: INSERT, SELECT, UPDATE, DELETE.
• DDL changes are structural and usually immediate; DML changes affect rows and can typically be rolled back within a transaction.
• Always pair UPDATE and DELETE with a WHERE clause unless you genuinely intend to affect every row.

Mastering these two command families is the foundation for everything else in SQL - from writing analytical queries to designing full database schemas.