SQL Core Reference

Standard SQL patterns that work across SQLite, PostgreSQL, and MySQL.

Data Retrieval

SELECT with filtering and sorting

SELECT name, email, created_at
FROM users
WHERE active = true
ORDER BY created_at DESC
LIMIT 10;

DISTINCT — deduplicate results

SELECT DISTINCT department FROM employees;

OFFSET pagination — skip rows, then take N

SELECT * FROM logs
ORDER BY timestamp DESC
LIMIT 20 OFFSET 40;  -- page 3, 20 per page

Aggregation

GROUP BY with aggregate functions

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY headcount DESC;

COUNT, SUM, AVG, MIN, MAX operate on groups. Without GROUP BY they aggregate the entire result set.

HAVING — filter after aggregation (WHERE filters before)

SELECT status, COUNT(*) AS cnt
FROM orders
GROUP BY status
HAVING COUNT(*) > 100;

Data Modification

INSERT single row

INSERT INTO devices (hostname, ip_address, device_type)
VALUES ('sw-core-01', '10.50.1.10', 'switch');

INSERT from query results

INSERT INTO audit_log (user_id, action, timestamp)
SELECT id, 'deactivated', NOW()
FROM users
WHERE last_login < '2025-01-01';

UPDATE with WHERE guard — always include WHERE

UPDATE devices
SET vlan_id = 20, updated_at = NOW()
WHERE hostname = 'sw-core-01';

UPDATE or DELETE without WHERE affects every row in the table. Always verify with a SELECT using the same WHERE clause first.

DELETE with safety check

-- Verify first
SELECT COUNT(*) FROM sessions WHERE expires_at < NOW();

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

UPSERT (Insert or Update)

PostgreSQL ON CONFLICT

INSERT INTO configs (key, value)
VALUES ('max_retries', '5')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;

SQLite ON CONFLICT

INSERT OR REPLACE INTO configs (key, value)
VALUES ('max_retries', '5');

MySQL ON DUPLICATE KEY

INSERT INTO configs (config_key, value)
VALUES ('max_retries', '5')
ON DUPLICATE KEY UPDATE value = VALUES(value);

Subqueries and CTEs

Subquery in WHERE

SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE region = 'west'
);

Correlated subquery — references outer query per row

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary) FROM employees WHERE department = e.department
);

CTE (Common Table Expression) — named temporary result set

WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE active = true AND last_login > '2026-01-01'
)
SELECT a.name, COUNT(o.id) AS order_count
FROM active_users a
JOIN orders o ON o.user_id = a.id
GROUP BY a.name;

CTEs improve readability over nested subqueries. They execute once and are referenced by name.

Recursive CTE — traverse hierarchical data

WITH RECURSIVE org_chart AS (
    -- anchor: top-level managers
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- recursive: each level of reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

Window Functions

ROW_NUMBER, RANK, DENSE_RANK

SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM employees;

ROW_NUMBER gives unique sequential numbers. RANK leaves gaps on ties. DENSE_RANK does not.

Running total with SUM OVER

SELECT date, amount,
    SUM(amount) OVER (ORDER BY date) AS running_total
FROM transactions;

LAG / LEAD — access previous/next row

SELECT date, revenue,
    LAG(revenue) OVER (ORDER BY date) AS prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change
FROM daily_sales;

Conditional Logic

CASE expression

SELECT hostname,
    CASE
        WHEN last_seen > NOW() - INTERVAL '5 minutes' THEN 'online'
        WHEN last_seen > NOW() - INTERVAL '1 hour' THEN 'stale'
        ELSE 'offline'
    END AS status
FROM devices;

COALESCE — first non-NULL value (null-safe default)

SELECT name, COALESCE(phone, email, 'no contact') AS contact
FROM users;

NULLIF — return NULL if two values match (avoid division by zero)

SELECT total / NULLIF(count, 0) AS average
FROM stats;

Views

Create a view — saved query accessible like a table

CREATE VIEW active_devices AS
SELECT hostname, ip_address, device_type
FROM devices
WHERE status = 'active';

-- Use it
SELECT * FROM active_devices WHERE device_type = 'switch';

Views do not store data. They re-execute the underlying query each time. Materialized views (PostgreSQL) cache results and must be refreshed explicitly.

Schema Operations

CREATE TABLE with constraints

CREATE TABLE devices (
    id          SERIAL PRIMARY KEY,          -- PostgreSQL auto-increment
    hostname    VARCHAR(255) NOT NULL UNIQUE,
    ip_address  INET,                        -- PostgreSQL IP type
    device_type VARCHAR(50) DEFAULT 'unknown',
    created_at  TIMESTAMP DEFAULT NOW()
);

ALTER TABLE — add column, rename, change type

ALTER TABLE devices ADD COLUMN vlan_id INTEGER;
ALTER TABLE devices RENAME COLUMN ip_address TO mgmt_ip;
ALTER TABLE devices ALTER COLUMN device_type SET NOT NULL;

DROP with safety

DROP TABLE IF EXISTS temp_imports;
DROP VIEW IF EXISTS stale_devices;

Date and Time

Date truncation for time-series aggregation (PostgreSQL)

SELECT DATE_TRUNC('month', created_at) AS month,
       COUNT(*) AS signups
FROM users
GROUP BY 1
ORDER BY 1;

Date arithmetic

-- PostgreSQL
SELECT * FROM events WHERE start_time > NOW() - INTERVAL '7 days';

-- SQLite
SELECT * FROM events WHERE start_time > datetime('now', '-7 days');

-- MySQL
SELECT * FROM events WHERE start_time > NOW() - INTERVAL 7 DAY;

EXPLAIN — Query Analysis

Show execution plan

-- PostgreSQL: EXPLAIN ANALYZE runs the query and shows actual timings
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'pending';

-- SQLite: EXPLAIN QUERY PLAN
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE status = 'pending';

Read the output bottom-up. Look for Seq Scan (full table scan — may need an index) vs Index Scan (using an index). The cost and actual time values indicate relative expense.