MySQL Interview Questions and Practical SQL Problems

Strong MySQL interview answers connect SQL syntax to data integrity, query plans, and concurrency. Explain assumptions, edge cases, and how you would verify behavior—not just the keyword or definition. Before coding, clarify the MySQL version, table relationships, whether duplicates or NULL values are possible, the expected result shape, and approximate data volume. These questions demonstrate practical judgment and prevent solving a subtly different problem.

Core concepts to explain

• Primary key vs. unique key: both enforce uniqueness, while a primary key identifies each row and cannot contain NULL. A table has one primary key, which may be composite, and can have multiple unique constraints.
• WHERE vs. HAVING: WHERE filters input rows before grouping; HAVING filters grouped results, often using aggregates. Prefer WHERE for row-level predicates when that expresses the intended logic.
• Index trade-offs: indexes can speed lookups, joins, and ordering when they match the workload, but consume storage and add write, maintenance, and cache costs. An index is not automatically useful simply because a column appears in a query.
• Transactions: InnoDB transactions group related changes and coordinate concurrent work. Discuss commit, rollback, isolation, locking, and how long-running transactions affect other sessions.
• NULL: NULL represents an unknown or missing value, not zero or an empty string. Use IS NULL and reason about three-valued logic in comparisons and filters.
• Normalization: explain how reducing duplicate facts improves consistency, then discuss intentional denormalization only when a measured access pattern justifies its maintenance cost.

Practice: find customers with multiple orders

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY order_count DESC, customer_id;

The query groups orders by customer, filters groups with more than one row, and sorts deterministically. Explain how NULL customer IDs behave if the schema permits them: all NULL values form one group. If the relationship is mandatory, a NOT NULL foreign key can make that case impossible. If the question asks for customer names, join the customer table and consider whether customers with no orders should be included; that changes the join and aggregation logic.

Practice: return the latest order for each customer

WITH ranked_orders AS (
  SELECT order_id, customer_id, total,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, order_id DESC
         ) AS row_num
  FROM orders
)
SELECT order_id, customer_id, total
FROM ranked_orders
WHERE row_num = 1;

This requires MySQL 8.0 or later for common table expressions and window functions. The unique tie-breaker makes the selected row deterministic when timestamps match. Ask whether ties should return one row or all equally latest rows; the latter calls for a different ranking strategy. If orders can have a NULL timestamp, state whether those should be eligible and define an explicit ordering policy.

Practice: find items above their category average

SELECT product_id, category_id, price
FROM (
  SELECT product_id, category_id, price,
         AVG(price) OVER (PARTITION BY category_id) AS category_avg
  FROM products
) AS priced
WHERE price > category_avg;

Explain what rows participate in each average and how NULL prices are treated by AVG. If the interviewer specifies an older server without window functions, discuss an aggregate subquery joined by category instead. Mention that the best form depends on version and data distribution, and verify its plan on representative data.

Reason about performance and correctness

For a query, identify likely access paths, join keys, and result size before proposing indexes. A composite index's column order matters; an index designed for one filter/order combination may not help a different predicate. Use EXPLAIN to inspect the chosen plan and investigate unexpected scans, estimates, or temporary work. Do not promise that adding an index always fixes latency: lock waits, network transfer, excessive result size, and application processing can dominate.

For pagination questions, use deterministic ORDER BY with a unique tie-breaker and explain the trade-off between offset and keyset pagination. For duplicate detection, clarify whether duplicate means identical across all columns or repeated business keys. For joins, state the expected cardinality and handle unmatched rows according to the requirement. Small details often distinguish a correct answer from one that merely runs.

Discuss safe writes and production behavior

Never concatenate untrusted input into SQL. Use parameterized statements for values, allowlist identifiers such as selectable sort columns, and perform authorization separately from injection prevention. For a bulk update, first preview the predicate with SELECT and a count, confirm the intended environment, use a transaction where it is appropriate, and verify affected rows. Large updates may need bounded batches to control locks, undo, and replication impact.

When discussing errors, distinguish validation problems from deadlocks, timeouts, lost connections, and constraint violations. Retries should be bounded and safe for idempotent operations; blindly repeating a write after an uncertain connection failure can create duplicate effects. Explain how you would observe query latency, slow queries, pool waits, and error rates. Favor precise, honest trade-offs over claiming one technique is universally best.

A structured interview approach

First restate the result needed and assumptions. Sketch the relevant schema and identify keys. Write the simplest correct query, then check duplicates, NULLs, ties, empty inputs, and version compatibility. Next discuss indexes and use EXPLAIN rather than guessing. Finally cover transaction boundaries, parameterization, privileges, and how the query behaves as data grows. If requirements are unclear, ask a focused question and explain how alternate answers would change the SQL.

Practice joins, window functions, execution plans, transactions, and SQL injection prevention. A strong interview answer is correct, explainable, version-aware, and safe to operate.