SQL cheat sheet

Reading data

SELECT name, email FROM users WHERE active = 1 ORDER BY name ASC LIMIT 20;
SELECT DISTINCT country FROM customers;
SELECT COUNT(*) AS total FROM orders WHERE status = 'paid';

Joins

SELECT c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- LEFT JOIN keeps every left-hand row even without a match
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

Aggregation

SELECT customer_id, SUM(total) AS revenue, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000
ORDER BY revenue DESC;

Writing data

INSERT INTO users (name, email) VALUES ('Ana', 'ana@example.com');
UPDATE orders SET status = 'shipped' WHERE id = 42;
DELETE FROM carts WHERE created_at < NOW() - INTERVAL 30 DAY;

Indexes and performance

CREATE INDEX idx_orders_customer ON orders (customer_id);
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Subqueries and CTEs

-- subquery in the WHERE clause
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE total > 500);

-- CTE (WITH), more readable for complex queries
WITH big_spenders AS (
    SELECT customer_id, SUM(total) AS revenue
    FROM orders
    GROUP BY customer_id
    HAVING SUM(total) > 1000
)
SELECT c.name, b.revenue
FROM big_spenders b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.revenue DESC;

Window functions

-- rank each customer's orders without collapsing rows (unlike GROUP BY)
SELECT customer_id, total,
       RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank,
       SUM(total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Unlike GROUP BY, which merges rows, a window function keeps every individual row while computing an aggregate value "alongside" it — perfect for a ranking, a running total, or a comparison to the group average without losing the row-level detail.