SQL Interview Questions & Answers

SQL JOINs combine rows from two or more tables based on a related column. INNER JOIN returns only matching rows from both tables — the intersection. LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right rows show NULL. RIGHT JOIN is the mirror: all right rows, matching left. FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists. CROSS JOIN produces the Cartesian product — every row from table A paired with every row from table B. SELF JOIN is joining a table to itself, useful for hierarchical data like employees and their managers.

In practice, INNER JOIN is most common for combining related data. LEFT JOIN is essential when you need to preserve all records from the primary table (e.g., all customers including those with no orders). FULL OUTER reveals gaps — rows that exist in one table but not the other. CROSS JOIN is rarely used in production but appears in interview puzzles. SELF JOIN solves "find employees earning more than their manager" by aliasing the same table twice.

WHERE filters rows before aggregation; HAVING filters groups after aggregation. This order matters: WHERE is applied first, reducing the dataset. Then GROUP BY creates groups. Finally, HAVING filters those groups based on aggregate values like COUNT, SUM, or AVG.

You cannot use aggregate functions in WHERE because aggregation has not happened yet. For example, "find departments with more than 5 employees" requires HAVING COUNT(*) > 5. Conversely, "find average salary of employees in departments with more than 5 employees" needs both: WHERE could filter individual rows first (e.g., only active employees), then HAVING filters the grouped result.

A common pattern: WHERE dept_id = 10 filters to one department, then GROUP BY and HAVING apply. If you put HAVING dept_id = 10, it would work only if dept_id is in the GROUP BY or is an aggregate. Best practice: use WHERE for row-level filters, HAVING for group-level conditions.

ACID ensures reliable transaction processing. Atomicity means all-or-nothing: either every operation in a transaction commits, or none do. If a transfer deducts $100 from A but fails before crediting B, the entire transaction rolls back. Consistency means the database moves from one valid state to another, respecting constraints (foreign keys, unique, check). A transfer cannot leave total money changed.

Isolation means concurrent transactions do not see each other's uncommitted changes. Without it, one transaction might read another's dirty data. Levels range from READ UNCOMMITTED (dirty reads) to SERIALIZABLE (strictest). Durability means once committed, data survives crashes. Writes are persisted to disk; power loss does not lose committed data.

In practice, databases implement these via write-ahead logging, locks, and MVCC. Interviewers expect you to explain each property with a concrete scenario: "If the server crashes mid-transfer, Atomicity and Durability ensure we don't lose or corrupt money."

Normalization reduces redundancy and update anomalies. 1NF requires atomic values — no repeating groups or arrays in a single cell. A column like "phones" storing "555-1234, 555-5678" violates 1NF; split into separate rows or a related table.

2NF requires 1NF plus no partial dependencies: every non-key attribute must depend on the entire primary key. If the key is (order_id, product_id), storing customer_name with only order_id creates partial dependency — customer_name depends only on order_id. Move customer_name to an orders table.

3NF requires 2NF plus no transitive dependencies: non-key attributes must not depend on other non-keys. If we have (emp_id, dept_id, dept_name), dept_name depends on dept_id, not emp_id. Move dept_name to a departments table.

BCNF (Boyce-Codd) strengthens 3NF: every determinant must be a candidate key. If a non-key determines another attribute, split the table. In practice, 3NF is often sufficient; BCNF handles edge cases with overlapping candidate keys.

Indexes are data structures that speed up lookups by avoiding full table scans. A B-tree index (most common) stores column values in a sorted tree structure, allowing O(log n) lookups instead of scanning every row. Hash indexes provide O(1) lookups for equality checks but do not support range queries or ordering.

Clustered indexes (e.g., primary key in many DBs) store table data in index order — the table is physically sorted. There is typically one per table. Non-clustered indexes store pointers to the actual rows; you can have many. A query filtering on an indexed column uses the index to jump directly to matching rows.

Indexes are not free: they consume storage and slow down INSERTs, UPDATEs, and DELETEs because the index must be maintained. Avoid indexing low-cardinality columns (e.g., boolean), columns rarely used in WHERE/JOIN, or very small tables where a full scan is cheaper. Composite indexes help multi-column filters; column order matters for left-prefix matching.

Use a window function to rank salaries within each department, then filter for rank 2. ROW_NUMBER() assigns unique ranks (1, 2, 3...) and handles ties arbitrarily. DENSE_RANK() gives the same rank to ties but no gaps (1, 2, 2, 3). For "second highest," DENSE_RANK is often preferred: if two people have the highest salary, the next distinct salary is rank 2.

Wrap the ranking in a CTE or subquery, then SELECT WHERE rn = 2 (or dr = 2). Alternatively, use a correlated subquery: for each department, find the MAX salary, then find the MAX salary that is less than that. The window approach is cleaner and more extensible (e.g., top N per department).

Duplicates are rows that share the same values in key columns. Use GROUP BY on those columns and HAVING COUNT(*) > 1 to find groups with more than one row. To list the actual duplicate rows (not just the key), join back to the original table or use a subquery with IN.

For "show all duplicate rows," either group and join, or use a window function: COUNT(*) OVER (PARTITION BY col1, col2) and filter where count > 1. The GROUP BY approach is simpler for just identifying which keys are duplicated. To deduplicate and keep one row, use DISTINCT ON (PostgreSQL), ROW_NUMBER with a subquery, or DELETE with a self-join targeting duplicates.

This is a classic self-join problem. The employees table has manager_id pointing to another employee. Join the table to itself: one alias for the employee, one for the manager. The join condition is employee.manager_id = manager.id. Then filter WHERE employee.salary > manager.salary.

Ensure you use LEFT JOIN if some employees have no manager (CEO), otherwise they would be excluded. For "employees and their managers" with the salary comparison, the self-join is the standard approach. Alternative: use a correlated subquery to fetch the manager's salary and compare, but the self-join is cleaner and more efficient.

A running total (cumulative sum) adds each row's value to the sum of all previous rows. Window functions make this straightforward: SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). The default frame for SUM with ORDER BY is exactly that — from the first row to the current row — so SUM(amount) OVER (ORDER BY date) suffices.

Order matters: ORDER BY date gives a chronological running total. Use PARTITION BY to reset the total per group (e.g., running total per user). For running totals, ROWS or RANGE between preceding and current row defines the window. RANGE handles ties differently (peers get same sum). ROWS is typically faster.

Pivoting transforms rows into columns — e.g., months as rows become columns (Jan, Feb, Mar). The standard approach uses conditional aggregation: MAX(CASE WHEN month = 'Jan' THEN amount END) AS jan, and similarly for other months. Each CASE extracts the value for that category; MAX (or MIN) collapses the single non-null value. GROUP BY the row identifier (e.g., product_id).

Some databases have PIVOT syntax (SQL Server, Oracle). PostgreSQL does not, so CASE + GROUP BY is the norm. For dynamic pivoting (unknown number of columns), you need dynamic SQL. The static CASE approach is common in interviews. Ensure you handle NULLs: COALESCE or default values if needed.

Consecutive days form groups when you subtract a row number from the date — dates in the same consecutive streak yield the same difference. Use ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) to number each login. Then date - rn::int (or similar) gives a constant for consecutive days: 2024-01-01 with rn=1 gives 2023-12-31; 2024-01-02 with rn=2 gives 2023-12-31; 2024-01-05 with rn=3 gives 2024-01-02. Group by user_id and this difference to get streaks, then COUNT and filter for streaks of desired length.

Deduplicate login dates first (one row per user per day). The key insight: date - row_number is invariant within a consecutive run.

Without LIMIT/OFFSET, use a subquery that counts how many distinct salaries are greater than the current row's salary. If exactly N-1 salaries are higher, the current row has the Nth highest. For each employee, the correlated subquery counts SELECT COUNT(DISTINCT salary) FROM employees WHERE salary > e.salary. When that count equals N-1, we have the Nth highest.

Alternatively, use DENSE_RANK() or ROW_NUMBER() in a subquery/CTE and filter WHERE rn = N. The correlated subquery approach works in any SQL dialect and demonstrates understanding of correlated subqueries. For N=2, we want exactly 1 distinct salary greater — the second highest. Handle ties: DENSE_RANK treats ties as same rank; the COUNT approach naturally handles ties (multiple people can have 2nd highest).

Window functions compute a value for each row based on a set of related rows (the window) without collapsing rows like GROUP BY. They use OVER (PARTITION BY ... ORDER BY ...) to define the window.

ROW_NUMBER assigns unique sequential integers (1, 2, 3...) per partition. Ties get arbitrary order. RANK assigns same rank to ties but leaves gaps: 1, 2, 2, 4. DENSE_RANK also ties but no gaps: 1, 2, 2, 3. Use ROW_NUMBER for unique ordering, RANK for "Olympic" ranking (two gold, no silver), DENSE_RANK when you want no gaps.

LEAD(column, n) returns the value n rows ahead; LAG returns n rows behind. Useful for comparing to previous/next row: LAG(salary) OVER (ORDER BY date) gives previous period's salary. Default n=1. Both accept a default for out-of-window (e.g., NULL).

A CTE is a named temporary result set defined with WITH ... AS (...) that exists only for the duration of the query. It improves readability by breaking complex queries into named steps. You can reference the CTE in the main query and in subsequent CTEs (chained CTEs).

Use CTEs when: the same subquery is used multiple times (defined once, referenced twice); the logic is complex and benefits from named steps; you need recursive queries. Recursive CTEs have a base case and a recursive part that references itself — essential for hierarchical data (org charts, tree traversal).

CTEs are not always optimized as separate materialized steps; the optimizer may inline them. In PostgreSQL, a CTE can be a "optimization fence" (materialized) with MATERIALIZED or NOT MATERIALIZED hints. Prefer CTEs over nested subqueries for readability; use them for recursive patterns.

An execution plan shows how the database will run a query: which indexes it uses, join algorithms, and cost estimates. Use EXPLAIN (ANALYZE) to see the plan. ANALYZE actually runs the query and reports real timings. Look for: Seq Scan (full table scan — often bad on large tables), Index Scan/Index Only Scan (good), Nested Loop vs Hash Join vs Merge Join, and high cost or actual time.

Optimization techniques: add indexes on columns in WHERE, JOIN, and ORDER BY; avoid functions on indexed columns (WHERE LOWER(name) = 'x' cannot use index on name); use covering indexes to avoid heap lookups; rewrite OR as UNION if it helps index usage; avoid SELECT * when few columns needed; use EXISTS instead of IN for subqueries when appropriate; batch inserts; analyze statistics with ANALYZE. Watch for implicit type casts and unnecessary sorts.