Databases

SQL

Queries, joins, aggregation, and window functions.

SELECT col1, col2
FROM table
WHERE age > 18
ORDER BY name ASC
LIMIT 10 OFFSET 20;

SELECT DISTINCT city FROM users;

INNER JOINRows matching in both

LEFT JOINAll left + matches

RIGHT JOINAll right + matches

FULL OUTER JOINAll rows, both sides

CROSS JOINCartesian product

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
LEFT JOIN refunds r ON r.order_id = o.id
WHERE o.total > 100;

SELECT dept, COUNT(*), AVG(salary)
FROM employees
GROUP BY dept
HAVING COUNT(*) > 5
ORDER BY AVG(salary) DESC;

INSERT INTO t (a) VALUES (1)Add row

UPDATE t SET a=1 WHERE id=2Modify rows

DELETE FROM t WHERE id=2Remove rows

UPSERT / ON CONFLICTInsert or update

Always include a WHERE clause on UPDATE/DELETE.

SELECT name, salary,
  RANK() OVER (
    PARTITION BY dept
    ORDER BY salary DESC
  ) AS rank,
  SUM(salary) OVER () AS total
FROM employees;

More Databases cheatsheets

PostgreSQL

psql client — connect, meta-commands, and administration.