Core SQL: DDL, DML, Data Types & Declarative Relational Constraints

⚡ The Language of Data: SQL (Structured Query Language) is the universal lingua franca of data engineering. Standardized by ANSI and ISO, SQL allows engineers to declaratively define relational schemas (DDL), enforce ironclad business invariants at the database level, and atomically manipulate records (DML).

1. The Structure of SQL: DDL vs DML

SQL statements are divided into two fundamental operational domains:

SQL SUBSETS:
┌────────────────────────────────────────┬────────────────────────────────────────┐
│ DDL (Data Definition Language)         │ DML (Data Manipulation Language)       │
├────────────────────────────────────────┼────────────────────────────────────────┤
│ • Defines the schema and structure.    │ • Operates on the data records.        │
│ • CREATE, ALTER, DROP, TRUNCATE        │ • INSERT, UPDATE, DELETE, SELECT       │
│ • Schema-level metadata modifications. │ • Row-level tuple operations.          │
└────────────────────────────────────────┴────────────────────────────────────────┘

2. Relational Data Types (Choosing the Right Physical Representation)

Choosing the correct data type is critical for memory footprint, index efficiency, and precision:

-- Integer Types
SMALLINT        -- 2 Bytes (-32,768 to 32,767)
INTEGER / INT   -- 4 Bytes (-2.1B to +2.1B)
BIGINT          -- 8 Bytes (-9.22 × 10¹⁸ to +9.22 × 10¹⁸) - Ideal for Primary Keys

-- Text Types
VARCHAR(255)    -- Variable length with maximum character cap
TEXT            -- Variable length unbounded text (ideal for descriptions, JSON)

-- Financial & Precise Numeric Types
NUMERIC(12, 2)  -- Exact fixed-point decimal (10 digits before comma, 2 after)
                -- NEVER use FLOAT or DOUBLE for financial currencies!

-- Temporal & Boolean Types
BOOLEAN         -- 1 Byte (TRUE, FALSE, or NULL)
TIMESTAMPTZ     -- Timestamp with Time Zone (UTC-normalized epoch)
UUID            -- 16-byte universally unique identifier

3. Data Definition Language (DDL) & Declarative Constraints

Declarative constraints allow the database engine to enforce business rules with zero application latency and absolute consistency:

-- Creating an Enterprise E-Commerce Schema

-- 1. Parent Table: USERS
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. Child Table: ORDERS with Foreign Key Integrity & Check Constraints
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Foreign Key referencing parent table
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) 
        REFERENCES users(id) 
        ON DELETE CASCADE,

    -- Declarative Check Constraints for business rules
    CONSTRAINT chk_positive_amount 
        CHECK (total_amount >= 0.00),
        
    CONSTRAINT chk_valid_status 
        CHECK (status IN ('PENDING', 'PROCESSING', 'PAID', 'SHIPPED', 'CANCELLED'))
);

Schema Alterations with ALTER TABLE:

-- Adding a column
ALTER TABLE users ADD COLUMN phone_number VARCHAR(20);

-- Adding a constraint after table creation
ALTER TABLE users ADD CONSTRAINT chk_email_format CHECK (email LIKE '%@%');

-- Dropping a column safely
ALTER TABLE users DROP COLUMN phone_number;

4. Data Manipulation Language (DML)

1. INSERT: Adding Records & Multi-Row Batches

-- Single row insert
INSERT INTO users (username, email) 
VALUES ('linus_torvalds', 'linus@kernel.org');

-- Multi-row batch insert (High throughput)
INSERT INTO users (username, email) VALUES 
    ('ada_lovelace', 'ada@analytical.org'),
    ('ken_thompson', 'ken@bell-labs.com'),
    ('dennis_ritchie', 'dmr@bell-labs.com');

-- Modern PostgreSQL / SQLite RETURNING clause
INSERT INTO orders (user_id, total_amount, status)
VALUES (1, 149.99, 'PAID')
RETURNING id, created_at, status;

2. Upsert Mechanics (ON CONFLICT DO UPDATE)

When inserting data that might already exist, modern SQL provides atomic upsert capability:

INSERT INTO users (username, email)
VALUES ('linus_torvalds', 'linus_new@kernel.org')
ON CONFLICT (username) 
DO UPDATE SET 
    email = EXCLUDED.email,
    created_at = CURRENT_TIMESTAMP;

3. UPDATE: Mutating Records

-- Always specify a precise WHERE clause!
UPDATE orders 
SET status = 'SHIPPED' 
WHERE id = 1042 AND status = 'PAID';

⚠️ Critical Danger: Omitting the WHERE clause in an UPDATE or DELETE statement mutates or wipes every single record in the entire table!

4. DELETE vs TRUNCATE: The Architectural Difference

┌─────────────────────────┬────────────────────────────────────────────────────────┐
│ Feature                 │ DELETE FROM table;         │ TRUNCATE TABLE table;     │
├─────────────────────────┼────────────────────────────┼───────────────────────────┤
│ SQL Subset              │ DML (Data Manipulation)    │ DDL (Data Definition)     │
│ Execution Mechanism     │ Scans and deletes row-by-row│ Deallocates all disk pages│
│ Transaction Log (WAL)   │ High (Logs every tuple)    │ Minimal (Logs allocation) │
│ Speed on Large Tables   │ Slow for 10M rows          │ Near Instantaneous        │
│ Filter with WHERE?      │ Yes                        │ No (All rows removed)     │
│ Triggers Fired?         │ Yes (ON DELETE triggers)   │ No                        │
└─────────────────────────┴────────────────────────────┴───────────────────────────┘

5. Declarative Integrity in Action (Handling Constraint Failures)

When a query violates a schema invariant, the database halts the transaction and returns a strict error code:

-- Attempting to insert a negative amount (Violates chk_positive_amount):
INSERT INTO orders (user_id, total_amount) VALUES (1, -50.00);
-- ERROR: new row for relation "orders" violates check constraint "chk_positive_amount"

-- Attempting to insert an order for a non-existent user (Violates fk_orders_user):
INSERT INTO orders (user_id, total_amount) VALUES (999999, 100.00);
-- ERROR: insert on table "orders" violates foreign key constraint "fk_orders_user"

6. Key Takeaways & Summary