SQL Cheat Sheet

Learn SQL from the basics →

Querying

SELECT name, price FROM products;: Choose columns from a table
SELECT DISTINCT city FROM customers;: Unique values only
SELECT price * 1.2 AS with_tax FROM products;: Calculated column with an alias
ORDER BY price DESC, name: Sort (descending, then ascending)
LIMIT 10 OFFSET 20: Page through results (FETCH FIRST in standard SQL)

Filtering

WHERE city = 'London' AND price > 50: Combine conditions
WHERE price BETWEEN 40 AND 100: Inclusive range
WHERE category IN ('Audio', 'Video'): Match any value in a list
WHERE name LIKE 'Mech%': Pattern match (% = any text, _ = one character)
WHERE city IS NULL: Missing values (never = NULL)

Aggregation

COUNT(*) / COUNT(col) / SUM / AVG / MIN / MAX: Aggregate functions
GROUP BY category: One result row per group
HAVING COUNT(*) > 2: Filter groups (after aggregation)
COUNT(DISTINCT customer_id): Count unique values

Joins

FROM orders o JOIN customers c ON c.id = o.customer_id: Inner join: matching rows only
LEFT JOIN orders o ON o.customer_id = c.id: Keep all left rows
WHERE o.id IS NULL (after LEFT JOIN): Rows with no match
FROM employees e JOIN employees m ON m.id = e.manager_id: Self-join

Subqueries & CTEs

WHERE price > (SELECT AVG(price) FROM products): Scalar subquery
WHERE id IN (SELECT customer_id FROM orders): Subquery in a filter
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id): Correlated EXISTS
WITH totals AS (SELECT ...) SELECT * FROM totals: Common table expression

Changing Data

INSERT INTO products (name, category, price) VALUES ('Mouse', 'Accessories', 25);: Add a row
UPDATE products SET price = price * 0.9 WHERE category = 'Audio';: Change rows (always use WHERE)
DELETE FROM orders WHERE status = 'cancelled';: Remove rows (always use WHERE)
BEGIN; ... COMMIT; / ROLLBACK;: Transaction: all or nothing

Tables & Keys

CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT NOT NULL);: Create a table
customer_id INTEGER REFERENCES customers(id): Foreign key
email TEXT UNIQUE / CHECK (price > 0): Constraints
CREATE INDEX idx_orders_customer ON orders (customer_id);: Index for faster lookups
ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;: Change a table

Window Functions

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC): Rank within groups
SUM(total) OVER (ORDER BY day): Running total
LAG(total) OVER (ORDER BY day): Previous row's value
AVG(salary) OVER (PARTITION BY dept): Group average on every row