SQL

Select Basics

SELECT id, name, email FROM users;
SELECT * FROM users;                     -- avoid in production code; name columns explicitly
SELECT DISTINCT country FROM users;
SELECT name AS full_name FROM users;      -- alias
SELECT * FROM users ORDER BY created_at DESC LIMIT 10 OFFSET 20;

Clause order in a query: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT. Logical evaluation order differs from the written order (FROM/WHERE evaluate before SELECT).

Filtering With Where

SELECT * FROM orders WHERE status = 'shipped';
SELECT * FROM orders WHERE total > 100 AND status != 'cancelled';
SELECT * FROM orders WHERE status IN ('shipped', 'delivered');
SELECT * FROM users WHERE email LIKE '%@gmail.com';
SELECT * FROM users WHERE last_login IS NULL;
SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';

NULL never equals anything, even another NULL — always use IS NULL / IS NOT NULL, never = NULL.

Joins

-- INNER JOIN: only matching rows on both sides
SELECT o.id, u.name FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- LEFT JOIN: all rows from the left table, NULLs where no match on the right
SELECT u.name, o.id FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- RIGHT JOIN: mirror of LEFT (all rows from the right table)
-- FULL OUTER JOIN: all rows from both sides, NULLs where unmatched
-- CROSS JOIN: cartesian product (every row × every row) — rare, be deliberate
SELECT * FROM colors CROSS JOIN sizes;

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

Aggregation And Group By

SELECT status, COUNT(*) FROM orders GROUP BY status;
SELECT user_id, SUM(total), AVG(total), MAX(total), MIN(total)
FROM orders
GROUP BY user_id
HAVING SUM(total) > 500;   -- filters groups (WHERE filters rows before grouping)

WHERE filters rows before aggregation; HAVING filters the aggregated groups after. Every non-aggregated column in SELECT must appear in GROUP BY.

Subqueries

-- scalar subquery
SELECT name FROM users WHERE id = (SELECT user_id FROM orders ORDER BY total DESC LIMIT 1);

-- IN subquery
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'shipped');

-- correlated subquery — re-evaluated per outer row
SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000);

-- CTE (common table expression) — named, readable subquery
WITH big_spenders AS (
  SELECT user_id, SUM(total) AS spent FROM orders GROUP BY user_id HAVING SUM(total) > 1000
)
SELECT u.name, b.spent FROM users u JOIN big_spenders b ON b.user_id = u.id;

Window Functions

SELECT
  user_id, total,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS row_num,
  RANK()       OVER (ORDER BY total DESC) AS rank,
  SUM(total)   OVER (PARTITION BY user_id) AS user_total,
  LAG(total)   OVER (PARTITION BY user_id ORDER BY created_at) AS prev_total
FROM orders;

Unlike GROUP BY, window functions keep every row and add a computed column alongside it — useful for running totals, rankings, and comparing a row to its neighbors.

Inserts Updates And Deletes

INSERT INTO users (name, email) VALUES ('Ana', 'ana@example.com');
INSERT INTO users (name, email) VALUES ('Ana', 'a@x.com'), ('Bo', 'b@x.com');  -- bulk

UPDATE users SET email = 'new@example.com' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE status = 'pending' AND created_at < NOW() - INTERVAL '3 days';

DELETE FROM users WHERE id = 1;
DELETE FROM sessions WHERE expires_at < NOW();   -- always scope DELETE/UPDATE with WHERE

Always test UPDATE/DELETE predicates with a SELECT first — an omitted WHERE clause touches every row.

Indexes

CREATE INDEX idx_orders_user_id ON orders (user_id);
CREATE UNIQUE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_status_created ON orders (status, created_at);  -- composite

EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;  -- inspect the query plan

Index columns used in WHERE, JOIN ON, and ORDER BY. Composite index column order matters — it can serve queries filtering on a left-prefix of the columns, not any subset. Indexes speed reads but cost writes (every insert/update maintains them).

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;      -- or ROLLBACK; on error, undoes both statements

ACID: Atomicity (all-or-nothing), Consistency (valid state to valid state), Isolation (concurrent transactions don’t corrupt each other), Durability (committed data survives a crash). Isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trade consistency guarantees for concurrency.

Schema Design Basics

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

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  total NUMERIC(10, 2) NOT NULL CHECK (total >= 0)
);

Normalization (1NF–3NF) removes redundancy: each table represents one entity, each column is atomic, non-key columns depend only on the key. Denormalize deliberately (duplicate data) only when read performance requires it, and document the tradeoff.

Common Query Patterns

-- pagination
SELECT * FROM posts ORDER BY id LIMIT 20 OFFSET 40;

-- upsert (Postgres)
INSERT INTO settings (user_id, key, value) VALUES (1, 'theme', 'dark')
ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value;

-- find duplicates
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;

-- top-N per group (window function)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
) t WHERE rn <= 3;