SQL Interview Questions 2026

Tip: INNER JOIN returns only matching rows in both tables. LEFT JOIN returns all left rows plus matches (NULLs where none). FULL OUTER JOIN returns all rows from both, matched where possible. Draw it mentally as set intersection vs union.

Tip: WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and can use aggregates like COUNT(*) > 5. You cannot use an aggregate in WHERE. Logical order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.

Tip: Use DENSE_RANK(): SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employees) t WHERE rnk = N. DENSE_RANK handles ties correctly. Alternatively LIMIT 1 OFFSET N-1 on distinct salaries when there are no ties.

Tip: An index (usually a B+ tree) speeds up lookups and range scans by avoiding full table scans, at the cost of extra storage and slower writes (each INSERT/UPDATE maintains it). Index columns used in WHERE/JOIN/ORDER BY; composite-index column order follows the leftmost-prefix rule.

Tip: Atomicity (all-or-nothing), Consistency (constraints preserved), Isolation (concurrent transactions do not corrupt each other), Durability (committed data survives crashes). Isolation levels (Read Committed, Repeatable Read, Serializable) trade consistency against concurrency.

Tip: Normalisation (1NF→3NF) removes redundancy and update anomalies by splitting data into related tables. Denormalise (duplicate data, add summary columns) deliberately for read-heavy/reporting workloads to avoid expensive joins — accepting redundancy for query speed.

Tip: Window functions (ROW_NUMBER, RANK, SUM() OVER, LAG/LEAD) compute across a set of rows related to the current row without collapsing them — GROUP BY collapses rows into one per group. Use OVER(PARTITION BY ... ORDER BY ...) for running totals, rankings, and period-over-period comparisons.

Tip: Run EXPLAIN / EXPLAIN ANALYZE to see the plan: look for full table scans, missing indexes, and bad row estimates. Add or fix indexes, avoid functions on indexed columns in WHERE (non-sargable), select only needed columns, and consider rewriting correlated subqueries as joins.