Databases

INNER JOIN keeps only rows matching in both tables. LEFT/RIGHT OUTER JOIN keep all rows from one side, filling NULL for the missing side. FULL OUTER JOIN keeps unmatched rows from both. The condition goes in ON or USING.

A cursor is a pointer to a row within a query result set, letting an app fetch and process rows one at a time instead of all at once. It's useful for huge results, but row-by-row work is slower than set-based SQL, so avoid it when possible.

Left-join orders and keep the unmatched rows: SELECT c.id, c.name FROM Customers c LEFT JOIN Orders o ON c.id = o.customer_id WHERE o.customer_id IS NULL. The LEFT JOIN ... IS NULL anti-join keeps exactly the customers with no matching order.

Group by the column and keep groups of size > 1: SELECT email FROM Person GROUP BY email HAVING COUNT(*) > 1. You must filter the aggregate with HAVING, not WHERE — WHERE is evaluated before rows are grouped, so it cannot see COUNT(*).

A transaction is a sequence of DB operations run as one unit. ACID: Atomicity (all-or-nothing, else rollback), Consistency (valid states only), Isolation (concurrent txns don't interfere), Durability (committed data survives a crash).

COMMIT saves changes permanently, ROLLBACK undoes them, SAVEPOINT marks a point to partially roll back to, and SET TRANSACTION configures properties. They apply to DML (INSERT/UPDATE/DELETE), not to DDL like table creation.

Left-join from departments and count the joined key: SELECT d.name, COUNT(e.id) FROM Departments d LEFT JOIN Employee e ON e.dept_id = d.id GROUP BY d.id, d.name. Use COUNT(e.id) (not COUNT(*)) so an empty department shows 0 — COUNT(*) would count the one NULL-padded row as 1.

Take the max salary strictly below the overall max: SELECT MAX(salary) FROM Employee WHERE salary < (SELECT MAX(salary) FROM Employee). This returns NULL cleanly when there is no second salary. Alternative: ORDER BY salary DESC LIMIT 1 OFFSET 1 over DISTINCT salaries.

Rank within each department and keep rank 1: SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM Employee) t WHERE rn = 1. PARTITION BY dept_id restarts the ranking per department — the canonical top-N-per-group pattern.

Self-join the table on the manager link and compare salaries: SELECT e.name FROM Employee e JOIN Employee m ON e.manager_id = m.id WHERE e.salary > m.salary. The table is joined to itself, aliasing one copy as the employee and one as the manager via manager_id → id.

EXPLAIN shows the planner's chosen plan with estimated costs without running the query. EXPLAIN ANALYZE executes it and reports timings and row counts — including side effects, so wrap an INSERT/UPDATE/DELETE in a rolled-back transaction.

They trade isolation for concurrency, defining which anomalies are allowed: Read Uncommitted (dirty reads), Read Committed (none), Repeatable Read / snapshot (no non-repeatable reads), Serializable (no anomalies). Higher levels cost performance.

Rank salaries with a window function and filter on the rank: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM Employee) t WHERE rnk = :N. DENSE_RANK gives tied salaries the same rank with no gaps in the numbering.

PostgreSQL is object-relational, more standards-compliant and feature-rich (rich types, CTEs, window queries, server-side cursors). MySQL offers pluggable storage engines like InnoDB and was historically tuned for read speed and key lookups.

Use a windowed SUM with an ordered frame: SELECT id, name, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM Employee. The frame accumulates from the first row up to the current one.

In standard SQL/PostgreSQL there are no true nested transactions: a transaction begun inside an active one joins the same outermost transaction. A COMMIT commits that whole outer transaction at once; real nesting is emulated with SAVEPOINTs, which let you partially roll back.

Under MVCC, updates and deletes leave old row versions ("dead tuples") instead of removing them at once. VACUUM reclaims that space and updates visibility and statistics, so it must run periodically, especially on changed tables.

Dirty read (seeing uncommitted data) — blocked at Read Committed+. Non-repeatable read (a re-read sees a changed row) — blocked at Repeatable Read+. Phantom read (a re-run query sees new rows) — blocked at Serializable, via 2PL or SSI.

MVCC keeps multiple row versions; each transaction reads from a consistent snapshot as of its start, so readers never block writers and vice versa, giving repeatable reads without locks. Old versions become dead tuples that VACUUM later reclaims.