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