Alex Spinov

Posted on Mar 25

The Only SQL Cheatsheet You'll Ever Need (With Real Examples)

#beginners #database #sql #tutorial

Stop Googling SQL Syntax

I've been writing SQL for 10 years and I still forget syntax. So I made this cheatsheet with real examples — not just abstract SELECT column FROM table.

Bookmark this. You'll use it weekly.

Basic Queries

-- Select specific columns
SELECT name, email, created_at FROM users;

-- Filter rows
SELECT * FROM users WHERE age > 25 AND country = 'US';

-- Sort results
SELECT * FROM products ORDER BY price DESC;

-- Limit results
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- Remove duplicates
SELECT DISTINCT country FROM users;

Aggregations (GROUP BY)

-- Count users per country
SELECT country, COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

-- Average order value per customer
SELECT customer_id, AVG(total) as avg_order, COUNT(*) as orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 5  -- Only customers with 5+ orders
ORDER BY avg_order DESC;

-- Multiple aggregations
SELECT
    category,
    COUNT(*) as products,
    MIN(price) as cheapest,
    MAX(price) as most_expensive,
    AVG(price) as avg_price,
    SUM(stock) as total_stock
FROM products
GROUP BY category;

JOINs

-- INNER JOIN (only matching rows)
SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN (all users, even without orders)
SELECT u.name, COALESCE(COUNT(o.id), 0) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;

-- Multiple JOINs
SELECT u.name, p.name as product, o.quantity
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2026-01-01';

Subqueries

-- Find users who spent above average
SELECT name, total_spent
FROM (
    SELECT u.name, SUM(o.total) as total_spent
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
) subquery
WHERE total_spent > (SELECT AVG(total) FROM orders);

-- EXISTS (check if related rows exist)
SELECT name FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

Window Functions (The Power Tool)

-- Rank users by total spending
SELECT
    name,
    total_spent,
    RANK() OVER (ORDER BY total_spent DESC) as rank
FROM (
    SELECT u.name, SUM(o.total) as total_spent
    FROM users u JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
) t;

-- Running total
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as running_total
FROM daily_revenue;

-- Compare to previous row
SELECT
    date,
    revenue,
    LAG(revenue) OVER (ORDER BY date) as prev_day,
    revenue - LAG(revenue) OVER (ORDER BY date) as change
FROM daily_revenue;

-- Percentage of total
SELECT
    category,
    revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 1) as pct_of_total
FROM category_revenue;

Date Functions

-- Filter by date range
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';

-- Group by month
SELECT
    DATE_TRUNC('month', created_at) as month,
    COUNT(*) as orders,
    SUM(total) as revenue
FROM orders
GROUP BY 1
ORDER BY 1;

-- Days since last order
SELECT
    name,
    MAX(o.created_at) as last_order,
    CURRENT_DATE - MAX(o.created_at)::date as days_since
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY name;

String Functions

-- Search (case-insensitive)
SELECT * FROM users WHERE LOWER(name) LIKE '%john%';

-- Extract domain from email
SELECT SPLIT_PART(email, '@', 2) as domain, COUNT(*)
FROM users
GROUP BY 1
ORDER BY 2 DESC;

-- Concatenate
SELECT CONCAT(first_name, ' ', last_name) as full_name FROM users;

-- Replace
SELECT REPLACE(phone, '-', '') as clean_phone FROM users;

CTEs (Common Table Expressions)

-- Much cleaner than nested subqueries
WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) as month,
        SUM(total) as revenue
    FROM orders
    GROUP BY 1
),
revenue_growth AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) as prev_month,
        ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
              / LAG(revenue) OVER (ORDER BY month), 1) as growth_pct
    FROM monthly_revenue
)
SELECT * FROM revenue_growth ORDER BY month;

Quick Tricks

-- COALESCE: default value for NULLs
SELECT name, COALESCE(phone, 'No phone') FROM users;

-- CASE: conditional logic
SELECT name,
    CASE
        WHEN total_spent > 10000 THEN 'VIP'
        WHEN total_spent > 1000 THEN 'Regular'
        ELSE 'New'
    END as tier
FROM customers;

-- INSERT with conflict handling (PostgreSQL)
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- Generate series (for filling date gaps)
SELECT generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day'::interval) as date;

Practice These

Want to practice SQL on real data without setting up a database? Use DuckDB — run SQL directly on CSV files:

pip install duckdb
python3 -c "import duckdb; print(duckdb.sql(\"SELECT * FROM 'your_data.csv' LIMIT 5\"))"

Full DuckDB tutorial →

What SQL pattern do you use most often? Any tricks I missed? Drop them in the comments.

I write practical developer cheatsheets and tutorials. Follow for more.

DEV Community