Databases & SQL interview questions

An index is a separate data structure that lets the DB find rows without a full table scan. B-tree (most common): balanced tree where leaves contain actual row pointers. Finding a value = O(log n) instead of O(n). Supports range queries (BETWEEN, >, <) because data is sorted. Trade-offs of over-indexing: - Every INSERT/UPDATE/DELETE must also update all relevant indexes — slower writes - Indexes consume disk space - Query planner may choose the wrong index - Covering indexes can help (index includes all columns the query needs — avoids table lookup) Index when: WHERE clause columns, JOIN columns, ORDER BY columns. Don't index low-cardinality columns (gender, boolean).

Atomicity: all operations in a transaction succeed or all are rolled back. A bank transfer deducting from A and crediting B must both happen or neither. Consistency: transaction brings DB from one valid state to another. Constraints (foreign keys, NOT NULL) are always maintained. Isolation: concurrent transactions appear to execute serially. Two transfers happening simultaneously shouldn't interfere. Durability: committed data persists even if the system crashes. Write-ahead logging (WAL) ensures this. Bank transfer example: if the system crashes after debiting A but before crediting B — Atomicity rolls back the debit. Without it, money disappears.

-- Method 1: subquery SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- Method 2: LIMIT OFFSET SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; -- Method 3: DENSE_RANK (handles ties properly) SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank FROM employees ) ranked WHERE rank = 2; DENSE_RANK is the most robust — handles duplicate salaries correctly. LIMIT/OFFSET is simplest for interviews.

SQL (relational): structured schema, ACID transactions, rich JOIN queries, vertical scaling. Best for: financial systems, e-commerce orders, anything needing strong consistency and complex queries. NoSQL: flexible schema, horizontal scaling, various data models (document, key-value, graph, column-family). Best for: user profiles, product catalogs, real-time feeds, time-series data. Choose SQL when: data is relational, transactions matter, schema is stable, complex queries needed. Choose NoSQL when: schema evolves rapidly, need to scale horizontally, data is hierarchical (documents), very high write throughput (Cassandra, DynamoDB). In practice: most systems use both (polyglot persistence).

INNER JOIN: returns only rows where there is a match in BOTH tables. SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; LEFT JOIN (LEFT OUTER): all rows from the left table, NULLs for non-matching right rows. Use when: you want all employees even if they have no department. RIGHT JOIN: all rows from the right table, NULLs for non-matching left rows. (Rarely used — just swap table order and use LEFT JOIN for clarity.) FULL OUTER JOIN: all rows from both tables. NULLs where there's no match on either side. Use when: you want all employees AND all departments, including unlinked ones. CROSS JOIN: Cartesian product — every row from A paired with every row from B. Use when: generating combinations (e.g., all size × color pairs). SELF JOIN: a table joined to itself. Common for hierarchical data (manager-employee). SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;

Normalization eliminates redundancy and ensures data integrity. 1NF (First Normal Form): each column holds atomic (indivisible) values; no repeating groups. Violation: a "phones" column with "9876543210, 9123456789". Fix: separate Phones table. 2NF (Second Normal Form): 1NF + every non-key column is fully dependent on the entire primary key (no partial dependency). Violation: in OrderItems(orderId, productId, productName) — productName depends only on productId, not the full composite key. Fix: move productName to Products table. 3NF (Third Normal Form): 2NF + no transitive dependencies (non-key column depending on another non-key column). Violation: Employees(empId, deptId, deptName) — deptName depends on deptId, not empId. Fix: move to Departments table. BCNF (Boyce-Codd): stricter than 3NF — every determinant must be a candidate key. In practice: design to 3NF; denormalize selectively for read performance in analytics/OLAP.

Window functions perform calculations across a set of table rows related to the current row, without collapsing rows. -- ROW_NUMBER: unique sequential number per partition SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num FROM employees; -- RANK: same rank for ties, gaps after (1,1,3) -- DENSE_RANK: same rank for ties, no gaps (1,1,2) SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk FROM employees; -- LAG/LEAD: access previous/next row's value SELECT name, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary, salary - LAG(salary) OVER (ORDER BY hire_date) AS salary_change FROM employees; Key: window functions are applied AFTER WHERE/GROUP BY/HAVING, so they can't be used in WHERE clauses directly — wrap in a subquery/CTE.

A CTE (WITH clause) is a named temporary result set defined within a query. -- Basic CTE WITH dept_avg AS ( SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id ) SELECT e.name, e.salary, d.avg_sal FROM employees e JOIN dept_avg d ON e.dept_id = d.dept_id WHERE e.salary > d.avg_sal; -- Recursive CTE (org hierarchy) WITH RECURSIVE org AS ( SELECT id, name, manager_id, 0 AS level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, org.level + 1 FROM employees e JOIN org ON e.manager_id = org.id ) SELECT * FROM org; CTE vs Subquery: - CTEs are named and reusable within the same query — write once, reference multiple times - CTEs are more readable for complex multi-step transformations - Subqueries inline are fine for simple one-off filters - Most DBs optimize CTEs and subqueries similarly; CTEs don't guarantee materialization

Problems: - Dirty read: reading uncommitted data from another transaction - Non-repeatable read: same query returns different values within a transaction (another tx committed between reads) - Phantom read: same range query returns different rows (another tx inserted/deleted between reads) Isolation levels (lowest → highest isolation): 1. Read Uncommitted: reads dirty data — no protection. Rarely used. 2. Read Committed (PostgreSQL default): prevents dirty reads. Non-repeatable reads still possible. 3. Repeatable Read (MySQL InnoDB default): prevents dirty + non-repeatable reads. Phantom reads still possible. 4. Serializable: full isolation — prevents all three. Locks everything. Slowest. Higher isolation = fewer anomalies, more locking, lower concurrency. Most apps use Read Committed. Financial systems may use Serializable for critical operations. PostgreSQL implements Repeatable Read using MVCC (no read locks, uses snapshots).

Deadlock: Transaction A holds lock on row X and waits for row Y. Transaction B holds lock on row Y and waits for row X. Both wait forever. Example: T1: UPDATE accounts SET bal = bal - 100 WHERE id = 1; -- locks row 1 T2: UPDATE accounts SET bal = bal - 100 WHERE id = 2; -- locks row 2 T1: UPDATE accounts SET bal = bal + 100 WHERE id = 2; -- waits for T2 T2: UPDATE accounts SET bal = bal + 100 WHERE id = 1; -- waits for T1 → DEADLOCK Database resolution: DBs detect deadlocks automatically and roll back one transaction (the victim). Your app must handle this with retry logic. Prevention strategies: 1. Consistent lock ordering: always acquire locks in the same order (always lock lower ID first) 2. Keep transactions short — minimize lock hold time 3. Use SELECT ... FOR UPDATE to acquire all needed locks upfront 4. Reduce lock granularity where possible (row-level vs table-level) 5. Optimistic locking for read-heavy workloads (version column, no locks)

SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS headcount FROM employees GROUP BY dept_id HAVING AVG(salary) > 60000 ORDER BY avg_salary DESC; Key distinctions: - WHERE filters rows BEFORE grouping - HAVING filters groups AFTER aggregation Common mistake: using WHERE AVG(salary) > 60000 — this fails because AVG() is not available at the WHERE stage. -- Get department name too (with JOIN) SELECT d.name, AVG(e.salary) AS avg_salary FROM employees e JOIN departments d ON e.dept_id = d.id GROUP BY d.id, d.name HAVING AVG(e.salary) > 60000;

N+1 problem: fetching N parent records with 1 query, then issuing 1 additional query per record to fetch related data. Example (ORM): const orders = await Order.findAll(); // 1 query — fetches 100 orders for (const order of orders) { const customer = await order.getCustomer(); // 100 more queries! } Total: 101 queries instead of 1. Detection: - Log SQL queries in development (Django Debug Toolbar, Rails Bullet gem, Sequelize logging) - Query count in integration tests Fix: 1. Eager loading: JOIN related data in the original query SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id; 2. ORM include/eager syntax: Order.findAll({ include: Customer }) // Sequelize Order.objects.select_related('customer') // Django 3. DataLoader pattern (GraphQL): batches N individual lookups into 1 query Rule: never fetch related records inside a loop.