Sql

SQL

Overview

SQL patterns for querying, data manipulation, and database design.

---

Query Fundamentals

Basic Queries

-- SELECT with filtering
SELECT
    id,
    email,
    name,
    created_at
FROM users
WHERE active = true
  AND created_at >= '2024-01-01'
ORDER BY created_at DESC
LIMIT 10 OFFSET 0;

-- Multiple conditions
SELECT *
FROM orders
WHERE status IN ('pending', 'processing')
  AND total_amount > 100
  AND (priority = 'high' OR customer_type = 'premium');

-- LIKE and pattern matching
SELECT *
FROM products
WHERE name LIKE '%widget%'          -- Contains 'widget'
   OR name LIKE 'Premium%'          -- Starts with 'Premium'
   OR sku SIMILAR TO '[A-Z]{3}-[0-9]{4}';  -- Regex pattern (PostgreSQL)

-- NULL handling
SELECT
    id,
    COALESCE(nickname, name, 'Anonymous') AS display_name,
    NULLIF(discount, 0) AS discount_or_null
FROM users
WHERE deleted_at IS NULL;

-- CASE expressions
SELECT
    id,
    name,
    CASE
        WHEN total >= 1000 THEN 'Gold'
        WHEN total >= 500 THEN 'Silver'
        WHEN total >= 100 THEN 'Bronze'
        ELSE 'Standard'
    END AS tier,
    CASE status
        WHEN 'active' THEN 1
        WHEN 'pending' THEN 2
        ELSE 3
    END AS sort_order
FROM customers
ORDER BY sort_order;

-- Distinct and counting
SELECT DISTINCT category
FROM products;

SELECT
    category,
    COUNT(*) AS product_count,
    COUNT(DISTINCT brand) AS brand_count
FROM products
GROUP BY category;

Joins

-- INNER JOIN (only matching rows)
SELECT
    o.id AS order_id,
    o.total_amount,
    u.name AS customer_name,
    u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed';

-- LEFT JOIN (all from left, matching from right)
SELECT
    u.id,
    u.name,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Multiple joins
SELECT
    o.id AS order_id,
    u.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days';

-- Self join
SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Cross join (cartesian product)
SELECT
    p.name AS product,
    c.name AS color
FROM products p
CROSS JOIN colors c;

Aggregations

-- Basic aggregation
SELECT
    category,
    COUNT(*) AS product_count,
    SUM(price) AS total_value,
    AVG(price) AS avg_price,
    MIN(price) AS min_price,
    MAX(price) AS max_price
FROM products
GROUP BY category
HAVING COUNT(*) >= 5
ORDER BY product_count DESC;

-- Group by multiple columns
SELECT
    EXTRACT(YEAR FROM created_at) AS year,
    EXTRACT(MONTH FROM created_at) AS month,
    status,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY
    EXTRACT(YEAR FROM created_at),
    EXTRACT(MONTH FROM created_at),
    status
ORDER BY year, month;

-- ROLLUP (subtotals and grand total)
SELECT
    COALESCE(category, 'TOTAL') AS category,
    COALESCE(brand, 'All Brands') AS brand,
    COUNT(*) AS count,
    SUM(price) AS total
FROM products
GROUP BY ROLLUP (category, brand);

-- CUBE (all combinations)
SELECT
    category,
    brand,
    SUM(sales) AS total_sales
FROM product_sales
GROUP BY CUBE (category, brand);

-- GROUPING SETS
SELECT
    category,
    brand,
    SUM(sales) AS total_sales
FROM product_sales
GROUP BY GROUPING SETS (
    (category, brand),
    (category),
    (brand),
    ()
);

---

Advanced Queries

Window Functions

-- Row numbering
SELECT
    id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- Running totals
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) AS running_total,
    SUM(amount) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7_day
FROM daily_sales;

-- Rank functions
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

-- LAG and LEAD
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change,
    100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY date))
        / LAG(revenue, 1) OVER (ORDER BY date) AS pct_change
FROM daily_revenue;

-- First/Last values
SELECT
    department,
    employee_name,
    salary,
    FIRST_VALUE(employee_name) OVER (
        PARTITION BY department ORDER BY salary DESC
    ) AS highest_paid,
    LAST_VALUE(employee_name) OVER (
        PARTITION BY department ORDER BY salary DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS lowest_paid
FROM employees;

Common Table Expressions (CTEs)

-- Basic CTE
WITH active_users AS (
    SELECT *
    FROM users
    WHERE active = true
)
SELECT
    au.name,
    COUNT(o.id) AS order_count
FROM active_users au
LEFT JOIN orders o ON au.id = o.user_id
GROUP BY au.id, au.name;

-- Multiple CTEs
WITH
monthly_sales AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_revenue
    FROM monthly_sales
)
SELECT
    month,
    revenue,
    prev_revenue,
    100.0 * (revenue - prev_revenue) / prev_revenue AS growth_pct
FROM growth
WHERE prev_revenue IS NOT NULL;

-- Recursive CTE (hierarchical data)
WITH RECURSIVE org_hierarchy AS (
    -- Base case: top-level employees
    SELECT
        id,
        name,
        manager_id,
        1 AS level,
        ARRAY[name] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: employees with managers
    SELECT
        e.id,
        e.name,
        e.manager_id,
        oh.level + 1,
        oh.path || e.name
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.id
)
SELECT
    id,
    name,
    level,
    array_to_string(path, ' -> ') AS hierarchy_path
FROM org_hierarchy
ORDER BY path;

Subqueries

-- Scalar subquery
SELECT
    name,
    salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- IN subquery
SELECT *
FROM products
WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE parent_id IS NULL
);

-- EXISTS subquery
SELECT *
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
);

-- Correlated subquery
SELECT
    e.name,
    e.salary,
    e.department,
    (
        SELECT AVG(salary)
        FROM employees e2
        WHERE e2.department = e.department
    ) AS dept_avg
FROM employees e;

-- Lateral join (row-by-row subquery)
SELECT
    u.name,
    recent_orders.order_count,
    recent_orders.total_spent
FROM users u
CROSS JOIN LATERAL (
    SELECT
        COUNT(*) AS order_count,
        COALESCE(SUM(total_amount), 0) AS total_spent
    FROM orders o
    WHERE o.user_id = u.id
      AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
) recent_orders;

---

Data Modification

-- INSERT
INSERT INTO users (email, name, created_at)
VALUES ('user@example.com', 'New User', NOW());

-- INSERT multiple rows
INSERT INTO products (name, price, category)
VALUES
    ('Product A', 19.99, 'Electronics'),
    ('Product B', 29.99, 'Electronics'),
    ('Product C', 9.99, 'Accessories');

-- INSERT from SELECT
INSERT INTO order_archive (id, user_id, total_amount, created_at)
SELECT id, user_id, total_amount, created_at
FROM orders
WHERE created_at < '2023-01-01';

-- INSERT with conflict handling (PostgreSQL)
INSERT INTO users (email, name)
VALUES ('user@example.com', 'New User')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;

-- INSERT with conflict (MySQL)
INSERT INTO users (email, name)
VALUES ('user@example.com', 'New User')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- UPDATE
UPDATE products
SET price = price * 1.1,
    updated_at = NOW()
WHERE category = 'Electronics';

-- UPDATE with JOIN
UPDATE orders o
SET status = 'archived'
FROM users u
WHERE o.user_id = u.id
  AND u.deleted_at IS NOT NULL;

-- DELETE
DELETE FROM sessions
WHERE expires_at < NOW();

-- DELETE with subquery
DELETE FROM order_items
WHERE order_id IN (
    SELECT id FROM orders WHERE status = 'cancelled'
);

-- UPSERT (PostgreSQL)
INSERT INTO page_views (page_id, view_count)
VALUES (1, 1)
ON CONFLICT (page_id)
DO UPDATE SET view_count = page_views.view_count + 1;

---

Schema Design

-- Create table with constraints
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user' CHECK (role IN ('admin', 'user', 'guest')),
    active BOOLEAN DEFAULT true,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Foreign keys
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(50) NOT NULL DEFAULT 'pending',
    total_amount DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),

    CONSTRAINT valid_status CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'))
);

-- Junction table (many-to-many)
CREATE TABLE product_categories (
    product_id UUID REFERENCES products(id) ON DELETE CASCADE,
    category_id UUID REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (product_id, category_id)
);

-- Indexes
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status) WHERE status != 'completed';
CREATE INDEX idx_products_search ON products USING gin (to_tsvector('english', name || ' ' || description));

-- Partial index
CREATE UNIQUE INDEX idx_active_users_email
ON users (email)
WHERE active = true;

-- Generated columns (PostgreSQL 12+)
ALTER TABLE products
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (to_tsvector('english', name || ' ' || COALESCE(description, ''))) STORED;

-- Triggers
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at();

---

Performance

-- EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT *
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending';

-- Query hints (PostgreSQL)
SET enable_seqscan = off;  -- Force index usage for testing

-- Batch processing
WITH batch AS (
    SELECT id
    FROM large_table
    WHERE processed = false
    ORDER BY id
    LIMIT 1000
    FOR UPDATE SKIP LOCKED
)
UPDATE large_table
SET processed = true
WHERE id IN (SELECT id FROM batch);

-- Pagination with keyset
SELECT *
FROM products
WHERE (created_at, id) < ('2024-01-01', 'abc123')
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Materialized view
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS revenue
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', created_at);

-- Refresh materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

---

Related Skills

• [[database]] - Database design patterns
• [[backend]] - Database integration
• [[performance-optimization]] - Query optimization

SQL Templates

Migration and schema templates for SQL databases.

Files

Usage

Migration Naming

{number}_{description}.sql

Examples:
001_create_users_table.sql
002_add_user_settings.sql
003_create_sessions_table.sql

Apply Migration

# Using psql
psql -d mydb -f migrations/001_create_users_table.sql

# Using migrate tool
migrate -path ./migrations -database "postgres://..." up

# Using Prisma
npx prisma migrate deploy

Common Patterns

Primary Keys

-- UUID (recommended for distributed)
id UUID PRIMARY KEY DEFAULT uuid_generate_v4()

-- Serial (auto-increment)
id SERIAL PRIMARY KEY

-- ULID/CUID (sortable)
id VARCHAR(26) PRIMARY KEY

Timestamps

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at TIMESTAMPTZ  -- For soft delete

Auto-update Trigger

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER {table}_updated_at
    BEFORE UPDATE ON {table}
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

Soft Delete

-- Add column
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ;

-- Soft delete
UPDATE users SET deleted_at = NOW() WHERE id = 'uuid';

-- Query active only
SELECT * FROM users WHERE deleted_at IS NULL;

JSONB Columns

-- Create with JSONB
settings JSONB DEFAULT '{}',

-- Create GIN index
CREATE INDEX idx_settings ON users USING GIN (settings);

-- Query JSONB
SELECT * FROM users WHERE settings->>'theme' = 'dark';
SELECT * FROM users WHERE settings @> '{"notifications": true}';

Enum Types

-- Create enum
CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended');

-- Use enum
status user_status NOT NULL DEFAULT 'active'

-- Add value (PostgreSQL 9.1+)
ALTER TYPE user_status ADD VALUE 'pending';

Foreign Keys

-- Basic FK
user_id UUID REFERENCES users(id)

-- With cascade
user_id UUID REFERENCES users(id) ON DELETE CASCADE

-- With set null
user_id UUID REFERENCES users(id) ON DELETE SET NULL

Index Guidelines

-- Single column
CREATE INDEX idx_users_email ON users (email);

-- Composite (order matters)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Partial index
CREATE INDEX idx_users_active ON users (email) WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- JSONB index
CREATE INDEX idx_settings ON users USING GIN (settings);

Migration Best Practices

DO

• Use transactions for safety
• Add indexes concurrently in production
• Include rollback statements
• Test on staging first

DON'T

• Drop columns without backup
• Run long migrations during peak hours
• Forget to update ORM schema

Safe Column Add

-- Safe: adds nullable column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Then update data
UPDATE users SET phone = 'default' WHERE phone IS NULL;

-- Then add constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Safe Index Creation

-- Concurrent (no table lock)
CREATE INDEX CONCURRENTLY idx_name ON table (column);