SQL Cheat Sheet — Complete Reference

e.g. SELECT first_name AS name, salary * 12 AS annual_salary FROM employees;

e.g. SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

e.g. SELECT * FROM users WHERE status = 'active';

e.g. SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

e.g. SELECT * FROM users WHERE country IN ('US', 'UK', 'DE');

e.g. SELECT * FROM users WHERE email LIKE '%@gmail.com';

e.g. SELECT * FROM products WHERE name ILIKE '%phone%';

e.g. SELECT * FROM users WHERE email ~ '^[a-z]+@';

e.g. SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

e.g. SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;

e.g. SELECT * FROM orders JOIN products USING (product_id);

e.g. SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL;

e.g. SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

e.g. SELECT country, COUNT(*) FROM users GROUP BY country;

e.g. SELECT country, COUNT(*) AS cnt FROM users GROUP BY country HAVING COUNT(*) > 10;

e.g. SELECT department, STRING_AGG(name, ', ') FROM employees GROUP BY department;

e.g. SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

e.g. SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

e.g. SELECT * FROM (SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id) AS order_counts WHERE cnt > 5;

e.g. WITH active_users AS ( SELECT * FROM users WHERE status = 'active' ) SELECT * FROM active_users WHERE created_at > '2024-01-01';

e.g. WITH RECURSIVE tree AS ( SELECT id, name, parent_id, 0 AS depth FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, c.parent_id, t.depth + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT * FROM tree;

e.g. SELECT u.name, latest.total FROM users u, LATERAL (SELECT total FROM orders WHERE user_id = u.id ORDER BY created_at DESC LIMIT 1) AS latest;

e.g. INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');

e.g. INSERT INTO users (name) VALUES ('Bob') RETURNING id, name;

e.g. INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

e.g. UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';

e.g. CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, email VARCHAR(255) UNIQUE, created_at TIMESTAMP DEFAULT NOW() );

e.g. CHECK (price > 0)

e.g. user_id INTEGER REFERENCES users (id) ON DELETE CASCADE

e.g. SUBSTRING('Hello World' FROM 1 FOR 5) -- 'Hello'

e.g. LPAD('42', 5, '0') -- '00042'

e.g. REGEXP_REPLACE('abc123', '[0-9]+', 'NUM')

e.g. SPLIT_PART('a.b.c', '.', 2) -- 'b'

e.g. EXTRACT(YEAR FROM created_at) EXTRACT(DOW FROM NOW()) -- day of week

e.g. DATE_TRUNC('month', created_at) -- start of month

e.g. NOW() - INTERVAL '30 days' NOW() + INTERVAL '2 hours'

e.g. AGE(NOW(), created_at)

e.g. TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS')

e.g. SELECT generate_series('2024-01-01'::date, '2024-12-31'::date, '1 month'::interval);

e.g. SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;

e.g. NTILE(4) OVER (ORDER BY salary) AS quartile

e.g. SELECT department, name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank FROM employees;

e.g. SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total

e.g. LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary

e.g. SELECT name, CASE WHEN salary > 100000 THEN 'high' WHEN salary > 50000 THEN 'mid' ELSE 'low' END AS bracket FROM employees;

e.g. SELECT COALESCE(nickname, first_name, 'Anonymous') AS display_name FROM users;

e.g. SELECT total / NULLIF(count, 0) AS average FROM stats;

e.g. SELECT '42'::INTEGER; SELECT CAST('2024-01-01' AS DATE);

e.g. CREATE VIEW active_users AS SELECT * FROM users WHERE status = 'active';

e.g. BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;