SQL & MySQL Interview Questions & Answers

A simple portable approach: `SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)`. A more flexible version for the Nth-highest value uses a window function: `SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2`. DENSE_RANK (rather than RANK or ROW_NUMBER) correctly handles duplicate salary values without skipping ranks.

Using LIMIT 1 OFFSET 1 without an ORDER BY, which returns an arbitrary row rather than the actual second-highest value.

If you know the two result sets can't contain duplicates, or duplicates are acceptable/expected, UNION ALL avoids the (potentially expensive) sort/hash step UNION needs to identify and remove duplicate rows, making it meaningfully faster on large result sets.

Default to UNION ALL unless you specifically need deduplication -- using UNION out of habit adds unnecessary overhead.

`SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1` returns every email value that appears more than once. To get the actual duplicate rows (not just the count), join this result back against the original table, or use a window function like `COUNT(*) OVER (PARTITION BY email)` in a subquery and filter where that count is greater than 1.

An INNER JOIN excludes any row that doesn't have a corresponding match in the other table. A LEFT JOIN keeps every row from the left table regardless of whether a match exists on the right, filling unmatched columns with NULL -- useful for 'find all X, with optional related Y' queries, like all customers and their orders (including customers with zero orders).

Using INNER JOIN when the intent was actually 'show all rows from table A even without a match,' silently dropping rows that have no corresponding match.

WHERE is applied first, row by row, before any grouping or aggregation happens, and it can't reference an aggregate function like COUNT() or SUM() directly. HAVING is applied after GROUP BY, so it can filter based on aggregate results, e.g. `HAVING COUNT(*) > 5` to find groups with more than five rows.

Trying to filter on an aggregate value (like COUNT(*)) using WHERE instead of HAVING, which either errors or doesn't do what's intended.

A primary key must be unique and non-null for every row, and a table can have only one. A foreign key doesn't have to be unique in the child table (many rows can reference the same parent row) but the database enforces that any value inserted must exist in the referenced table's primary (or unique) key -- preventing orphaned references.

An unnormalized table that repeats the same customer name and address on every one of their orders wastes space and risks inconsistency if one copy gets updated and another doesn't. Normalizing splits this into a Customers table and an Orders table linked by a foreign key, so each customer's details are stored exactly once. Third normal form (3NF) is the common practical target for transactional systems -- every non-key column depends on the whole primary key and nothing but the key.

Over-normalizing to the point that simple, common queries require many joins across many tiny tables, hurting both readability and performance.

Atomicity means a multi-statement transaction is all-or-nothing -- if any part fails, everything rolls back. Consistency means the database moves from one valid state to another, respecting constraints. Isolation controls how concurrent transactions see each other's uncommitted changes (governed by isolation levels like READ COMMITTED or SERIALIZABLE, which trade consistency guarantees for concurrency/performance). Durability means once a transaction commits, the change survives even a subsequent crash, typically via a write-ahead log.

READ UNCOMMITTED allows dirty reads (seeing another transaction's uncommitted changes). READ COMMITTED (MySQL's InnoDB default alongside REPEATABLE READ variations) prevents dirty reads but still allows a row's value to change between two reads in the same transaction (non-repeatable read). REPEATABLE READ prevents that but can still allow phantom rows to appear in a repeated range query. SERIALIZABLE prevents all of these by effectively serializing transactions, at the cost of the most locking and lowest concurrency.

Default to your database's standard isolation level (often READ COMMITTED or REPEATABLE READ) and only raise it to SERIALIZABLE for the specific transactions that truly need that guarantee, since it costs concurrency.

Because a clustered index dictates row storage order, lookups by that key are extremely fast since the data itself is organized that way -- in InnoDB (MySQL's default engine), the primary key is always the clustered index. A non-clustered index is a separate lookup structure mapping key values to row locations (or to the primary key, in InnoDB's case), requiring an extra lookup step to fetch the full row unless the query is covered entirely by the index's own columns.

Design a covering index (including all columns a frequent query needs) when that query is performance-critical, avoiding the extra lookup back to the full row.

For example, transaction A locks row 1 then waits for row 2, while transaction B locks row 2 then waits for row 1 -- neither can complete. MySQL's InnoDB detects this cycle and aborts one transaction with a deadlock error, which the application should catch and retry. Prevention strategies include always acquiring locks (or updating rows) in a consistent order across all transactions, and keeping transactions short so locks are held as briefly as possible.

Access tables/rows in a consistent order across all transactions in the codebase, and keep transactions as short as possible to minimize the window for lock contention.

Not handling deadlock errors with a retry in application code, causing an otherwise-recoverable transient error to surface as a hard failure to the user.

As a single database instance's write throughput or storage becomes the bottleneck, sharding distributes rows across several instances based on a chosen key (e.g. user_id), so each shard only holds a fraction of the data. The trade-off is that queries needing data across multiple shards (like a global aggregate or a join across differently-sharded tables) become significantly harder and often require an application-level fan-out and merge, plus rebalancing shards as data grows unevenly becomes an ongoing operational concern.

Choose a shard key that matches your dominant query pattern (so most queries stay within a single shard) before sharding, not after.

Sharding on a key that doesn't align with common query patterns, forcing most queries to fan out across every shard anyway and losing most of the benefit.

Pushing logic into the database can reduce network round-trips for multi-step operations and centralizes rules that every consumer of the database must follow. The trade-offs are real, though: stored procedure logic is harder to version-control and test compared to application code, harder to unit test in isolation, and ties business logic to a specific database vendor's procedural language, complicating a future migration.

Reserve stored procedures for genuinely data-proximate, performance-critical, or cross-application-enforced logic -- keep general business logic in the application layer where it's easier to test and evolve.

Pushing significant business logic into stored procedures by default, making it hard to unit test, code-review, and version alongside the rest of the application.

Without an index, a query filtering on a column requires a full table scan -- checking every row. An index on that column lets the database navigate directly to matching rows in roughly O(log n) time instead of O(n), which is the difference between milliseconds and seconds (or worse) on large tables. Indexes aren't free, though -- they add overhead to every INSERT/UPDATE/DELETE since the index structure must be maintained alongside the table.

Index columns that are frequently used in WHERE clauses, JOIN conditions, and ORDER BY -- but avoid indexing every column reflexively, since each index adds write overhead.

Adding an index to every column 'just in case,' which slows down writes without meaningfully helping reads that never filter on those columns.

EXPLAIN reveals whether the database is using an index or falling back to a full table scan, how many rows it estimates it'll examine, and the join order/strategy chosen. Common fixes include adding a missing index on a filtered/joined column, rewriting a non-sargable predicate (like wrapping a column in a function, e.g. `WHERE YEAR(created_at) = 2024`, which prevents index use) into a sargable form (`WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'`), and avoiding SELECT * when only a few columns are actually needed.

Always check the execution plan before guessing at a fix -- adding an index blindly doesn't help if the planner isn't even using indexes for a different reason (like a function wrapped around the column).

Wrapping an indexed column in a function in the WHERE clause (e.g. LOWER(email) = ...), which silently disables index usage for that predicate unless a functional index exists.

Interviewers want a real, specific story: how the problem was noticed (monitoring, user complaints, slow query logs), how it was diagnosed (EXPLAIN plans, query logs, APM traces), what the actual fix was (added an index, batched N+1 queries into one, denormalized a hot-path table), and how the improvement was confirmed (before/after query times, reduced database load).

Orders holds order-level data (customer_id, status, created_at). OrderItems holds one row per product in that order (order_id, product_id, quantity, unit_price_at_purchase) -- capturing the price at the time of purchase rather than joining live to Products.price is important, since product prices change over time and historical orders must reflect what was actually charged. Indexes on customer_id (for order history lookups) and order_id (for fetching an order's items) support the most common access patterns.

Store a snapshot of price-sensitive fields (like unit price) at transaction time rather than relying on a live join to a mutable Products table, so historical records stay accurate as prices change.

Computing an order's total by joining to the current Products.price at read time, which silently changes historical order totals whenever a product's price is updated later.