SQL Quick Reference: From Basics to
Advanced Queries
Introduction
SQL (Structured Query Language) is the universal language for working with
relational databases. This reference covers essential patterns for
PostgreSQL, MySQL, and SQLite — from basic CRUD operations to advanced
analytical queries.
CRUD Operations
CREATE — Inserting Data
-- Single row insert
INSERT INTO users (name, email, created_at)
VALUES ('John Doe', 'john@[Link]', NOW());
-- Multiple rows
INSERT INTO users (name, email) VALUES
('Alice', 'alice@[Link]'),
('Bob', 'bob@[Link]'),
('Carol', 'carol@[Link]');
-- Insert from another table
INSERT INTO archived_users (name, email)
SELECT name, email FROM users WHERE active = false;
READ — Querying Data
-- Basic select with conditions
SELECT name, email, created_at
FROM users
WHERE active = true AND role = 'admin'
ORDER BY created_at DESC
LIMIT 10;
-- Pattern matching
SELECT * FROM products WHERE name LIKE '%wireless%';
-- Aggregate functions
SELECT department, COUNT(*) as headcount, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;
UPDATE — Modifying Data
-- Simple update
UPDATE users SET email = 'newemail@[Link]' WHERE id = 42;
-- Update with calculation
UPDATE products SET price = price * 1.10 WHERE category =
'electronics';
-- Update from join
UPDATE orders o
SET status = 'cancelled'
FROM users u
WHERE o.user_id = [Link] AND [Link] = true;
DELETE — Removing Data
-- Conditional delete
DELETE FROM sessions WHERE expires_at < NOW();
-- Delete with subquery
DELETE FROM users
WHERE id NOT IN (SELECT DISTINCT user_id FROM orders WHERE created_at
> '2025-01-01');
Joins Explained
-- INNER JOIN: Only matching rows from both tables
SELECT [Link], [Link]
FROM users u
INNER JOIN orders o ON [Link] = o.user_id;
-- LEFT JOIN: All users, even those without orders
SELECT [Link], COALESCE(COUNT([Link]), 0) as order_count
FROM users u
LEFT JOIN orders o ON [Link] = o.user_id
GROUP BY [Link];
-- Self join: Employees and their managers
SELECT [Link] as employee, [Link] as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = [Link];
Window Functions
-- Ranking
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as
dept_rank
FROM employees;
-- Running total
SELECT date, amount,
SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as
running_total
FROM transactions;
-- Moving average
SELECT date, value,
AVG(value) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT
ROW) as weekly_avg
FROM metrics;
Common Table Expressions (CTEs)
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', created_at) as month,
SUM(total) as revenue
FROM orders
WHERE status = 'completed'
GROUP BY 1
),
growth AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) as prev_revenue
FROM monthly_revenue
)
SELECT month, revenue,
ROUND((revenue - prev_revenue) / prev_revenue * 100, 1) as
growth_pct
FROM growth;
Useful Patterns
-- Upsert (PostgreSQL)
INSERT INTO settings (key, value) VALUES ('theme', 'dark')
ON CONFLICT (key) DO UPDATE SET value = [Link];
-- Pagination with cursor
SELECT * FROM posts WHERE id > 100 ORDER BY id LIMIT 20;
-- Recursive CTE (org chart)
WITH RECURSIVE org AS (
SELECT id, name, manager_id, 1 as depth FROM employees WHERE
manager_id IS NULL
UNION ALL
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e JOIN org o ON e.manager_id = [Link]
)
SELECT * FROM org ORDER BY depth, name;
-- Check query plan
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@[Link]';
© 2026 — Developer Reference Series