What are the different types of JOINs in SQL and when should you use each?

Start by naming the main JOIN types and describing their behavior briefly: INNER JOIN returns rows matching in both tables, LEFT JOIN returns all rows from the left table with matching right rows or NULLs, RIGHT JOIN is the opposite of LEFT, FULL OUTER returns all rows from both tables with NULLs where there is no match, and CROSS JOIN produces the Cartesian product. Describe the common use cases, for example use INNER JOIN for filtering to related rows, LEFT JOIN when you need missing matches preserved, and FULL OUTER when you need a union-like view of both sides. Give a short example: suppose you have orders and customers tables, use INNER JOIN to show only orders with valid customers, use LEFT JOIN to list all customers and their orders regardless of whether they ordered, and use CROSS JOIN only when generating combinations intentionally. Write a small query to illustrate, for example: SELECT c.id, o.id FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE c.active = 1. Tip: watch NULL behavior when joining and filtering, because putting conditions on the joined table in the WHERE clause can turn a LEFT JOIN into an effective INNER JOIN. Avoid CROSS JOIN unless you need all combinations, and prefer explicit ON clauses so your intention is clear.

How do indexes work and when should you add them?

Explain that an index is a data structure that speeds up lookups by allowing the database to find rows without scanning the whole table, most commonly using B-tree or hash structures. Describe that indexes help read performance for queries that filter or sort on indexed columns, but they add write overhead because inserts, updates, and deletes must update the index as well. Give a practical example: if you query WHERE email = ? on a users table often, adding a unique index on email makes lookups fast and enforces uniqueness. For composite queries that filter on multiple columns, create a composite index with column order matching the most common filter or ordering pattern. Common pitfalls include over-indexing small tables or adding many indexes that slow writes, indexing low-cardinality columns like boolean flags without a covering need, and forgetting to measure with EXPLAIN to confirm the index is used.

Explain normalization and denormalization, and when you might choose one over the other.

Define normalization as organizing data into related tables to reduce redundancy and maintain integrity, often following normal forms like 1NF, 2NF, and 3NF. Define denormalization as intentionally duplicating data to reduce joins and improve read performance in high-read scenarios. Provide an example: store user contact info in a single users table for normalized design, but denormalize by adding a cached recent_order_count column if you frequently display that value and want to avoid a join or expensive aggregation. Explain that denormalization can speed reads but requires you to keep duplicated values in sync, typically via triggers, application logic, or background jobs. Tip: choose normalization during initial design to ensure data correctness, then denormalize only after measuring performance bottlenecks and planning how to maintain consistency.

How would you find duplicate rows in a table and remove them safely?

Describe an approach using GROUP BY with HAVING to identify duplicates by columns that should be unique, for example GROUP BY email HAVING COUNT(*) > 1. Then explain you can use window functions like ROW_NUMBER() PARTITION BY to mark duplicates and delete rows with row_number > 1 while preserving one copy. Give a specific technique: create a SELECT to preview duplicates, then wrap deletion in a transaction or copy the data to a temp table first. For example: WITH ranked AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1). Tip: never run destructive deletes without a backup or a transaction, and test your deduplication on a copy of the data so you confirm which rows will be removed.

What are window functions and when do you use them?

Start by defining window is functions that perform calculations across a set of table rows related to the current row, without collapsing rows like GROUP BY does. Explain common window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), and aggregate windows like SUM() OVER, and that they are useful for running totals, pagination, and comparing rows within partitions. Give an example: to get the latest order per customer, use ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) and filter for row_number = 1. For running totals, use SUM(amount) OVER (PARTITION BY account_id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Common gotchas include performance on very large partitions, so consider appropriate indexes and limiting partitions, and remember window functions cannot be used in WHERE clauses directly, so wrap them in a subquery or CTE for filtering.

How do you approach query performance tuning?

Outline a reproducible process: reproduce the slow query, capture the execution plan with EXPLAIN or the database's profiler, identify bottlenecks such as full table scans or large sorts, and iterate with targeted changes like adding indexes, rewriting joins, or limiting data scanned. Emphasize measuring before and after each change so you can confirm impact rather than guessing. Give a concrete example: if an EXPLAIN shows a sequential scan due to a WHERE on a column without an index, add an index and re-run the plan, or rewrite correlated subqueries as joins if the optimizer performs poorly. Also consider statistics, vacuuming, or updating table stats because stale stats can produce bad plans. Tip: avoid premature optimization, and focus first on queries that matter by looking at slow query logs or application metrics, then optimize the highest-impact statements.

Explain ACID properties and why they matter in databases.

Define ACID as Atomicity, Consistency, Isolation, and Durability, and give one-sentence summary for each: Atomicity means a transaction is all or nothing, Consistency means transactions move the database between valid states, Isolation controls how concurrent transactions interact, and Durability ensures committed changes survive crashes. Explain that ACID matters because it provides predictable behavior for concurrent access and error recovery, which is critical for financial or data-integrity sensitive systems. Provide a practical scenario: when transferring funds between accounts you need atomicity so both debit and credit happen together, and proper isolation to avoid lost updates when two transfers run concurrently. Mention that different databases provide isolation levels like READ COMMITTED or SERIALIZABLE, which trade off performance and strictness. Tip: pick the isolation level that matches your correctness needs, and test concurrency with realistic loads because behavior can differ under contention.

How do you design a many-to-many relationship in a relational database?

Explain that you model many-to-many by introducing a join table, sometimes called an associative or junction table, which stores foreign keys referencing the two related tables. Describe typical columns for the join table such as the two foreign keys, created_at, and any attributes specific to the relationship like role or quantity. Give an example: for users and projects, create user_projects(user_id, project_id, role, joined_at) with a composite primary key of (user_id, project_id) or a surrogate id depending on whether you need a stable row id. Explain how to query with JOINs to get projects for a user or users for a project, and how to enforce referential integrity with foreign key constraints. Tip: consider indexing each foreign key for join performance, and add unique constraints if duplicate relationships are not allowed. Use soft deletes carefully because they complicate uniqueness and joins.

Write a query to find the nth highest salary in an employees table.

Describe two common approaches: use distinct salaries with ORDER BY and LIMIT/OFFSET, or use window functions such as DENSE_RANK() or ROW_NUMBER() to rank salaries and then filter. Explain trade-offs: LIMIT/OFFSET is simple but can be awkward with ties, while DENSE_RANK handles ties explicitly. Provide a concrete example with ties handled: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rnk FROM employees) t WHERE rnk = 3 to get the third highest distinct salary. For an approach without window functions, you can use a correlated subquery filtering how many salaries are greater than the current one, but this is usually less efficient. Tip: clarify whether you want distinct salaries or the nth row including ties before choosing a method, and test performance on large datasets because window functions may be heavy without proper indexing.

What is an execution plan and how do you read it?

Start by saying an execution plan shows how the database intends to execute a query, including join algorithms, index usage, and estimated row counts. Explain the key pieces to read: cost estimates, actual vs estimated rows when available, join order, and whether scans are sequential or index-based. Give a practical step list: run EXPLAIN or EXPLAIN ANALYZE to get details, look for big differences between estimated and actual rows, and check for expensive operations like large sorts or nested loops over many rows. Use the plan to drive targeted fixes like adding missing predicates, indexes, or rewriting joins. Tip: always test changes with realistic data and use the database-specific tools because plan output differs between systems such as Postgres, MySQL, and SQL Server.

Tell me about a time you resolved a production SQL performance issue.

Situation: In a recent role our reporting API started timing out during peak hours, and users reported slow dashboards. Task: I was responsible for identifying the bottleneck and restoring acceptable response times quickly while keeping reports accurate. Action: I reproduced the slow queries in a staging copy, captured EXPLAIN ANALYZE output, and found a frequent full table scan on a large transactions table. I added an index on the filtered columns, rewrote a correlated subquery into a join, and deployed the change behind a feature flag to monitor impact. Result: Response times for the reporting endpoints improved from a median of 12 seconds to under 800 milliseconds, and CPU usage during peak windows dropped by about 40 percent. We rolled the fix to production with monitoring and rolled back quickly if any issue appeared.

Describe a time you disagreed with a database design decision and how you handled it.

Situation: On a project the team wanted to store frequently queried summary counts in application memory rather than in the database, which risked inconsistency. Task: My job was to evaluate the trade-offs and propose a reliable alternative that kept performance without risking data drift. Action: I gathered metrics showing the frequency of updates and read patterns, demonstrated a denormalized column with database triggers and background reconciliation, and presented a rollback plan. I also prototyped both approaches and ran load tests to compare latency, complexity, and failure modes. Result: The team agreed to use database-side denormalization with a reconciliation job, which preserved performance while keeping a single source of truth. The change reduced cache complexity and cut incident churn related to stale application memory by half over the following quarter.

Tell me about a time you had to learn a new SQL feature or tool quickly and apply it.

Situation: A client needed partitioned tables to manage a rapidly growing time-series dataset and I had not used table partitioning in production before. Task: I needed to learn partitioning options for our database and implement a safe migration plan under a tight deadline. Action: I read the database docs, built a small test harness with sample data, and evaluated partition pruning, maintenance operations, and backup strategies. I then created a staged migration that copied historical data into partitions and switched live writes with minimal downtime. Result: The partitioned design reduced query times for daily reports by 70 percent and improved table maintenance tasks like vacuuming and index rebuilds. The migration completed within the planned maintenance window and the team adopted the partitioning pattern for other large tables.

How do you use EXPLAIN ANALYZE to debug a slow query? Provide an example.

Explain ANALYZE actually runs the query and returns timing plus row counts so you can compare estimated versus actual costs and rows, which helps find where the optimizer was misled. Start by running EXPLAIN ANALYZE in a staging environment or with read-only settings to avoid side effects, then inspect nodes with high actual time or large row mismatches. Example: If EXPLAIN ANALYZE shows a Nested Loop joining a small table to a very large one with high actual rows, consider adding an index on the join key or rewriting the query to use a hash join by changing join order or enabling optimizer settings. A sample command is EXPLAIN ANALYZE SELECT o.id, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at > '2025-01-01'; Pitfalls: never assume estimated costs are perfect, and avoid interpreting EXPLAIN output without comparing to actual execution in production-like data volumes. Also be careful running ANALYZE on long-running statements in production without safeguards.

Show how to write a query using window functions to compute a 7-day moving average of sales.

Describe you use window frames to compute moving averages over a range of rows ordered by date, applying PARTITION BY if you need per-group calculations. Use SUM() OVER with an appropriate frame and CAST to get decimals when needed. Example query: SELECT date, SUM(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7.0 AS moving_avg FROM daily_sales ORDER BY date; For per-store averages add PARTITION BY store_id: SUM(sales) OVER (PARTITION BY store_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7.0. Pitfalls include handling gaps in dates which can skew averages, so consider generating a complete date series or using RANGE frames carefully. Also watch performance for large windows and add indexes on partition and order columns.

How do transactions and isolation levels affect concurrent updates? Give an example of a race condition and how to prevent it.

Explain that transactions group operations so they commit atomically, while isolation levels control visibility and concurrency effects like dirty reads, nonrepeatable reads, and phantom reads. Lower isolation increases concurrency but can introduce anomalies, and higher isolation like SERIALIZABLE prevents anomalies but may reduce throughput. Example race: two transactions read an account balance and both subtract funds simultaneously, causing an overdraft if both commit. Prevent this by using SELECT ... FOR UPDATE to lock the row inside a transaction, or by using an UPDATE that checks a condition and returns the affected row count to ensure only one transaction succeeds. Pitfalls include locking too broadly which hurts concurrency, and relying on application-level retries without idempotency. Measure conflict rates and pick an isolation strategy that balances correctness and performance.

Write a query to pivot rows into columns for a small fixed set of categories.

Explain that for a small, known set of categories you can use conditional aggregation with CASE expressions and GROUP BY to pivot rows to columns. This technique is portable across SQL dialects and avoids relying on vendor-specific PIVOT features. Example query: SELECT product_id, SUM(CASE WHEN region = 'north' THEN sales ELSE 0 END) AS north_sales, SUM(CASE WHEN region = 'south' THEN sales ELSE 0 END) AS south_sales FROM sales GROUP BY product_id; This yields one row per product with columns per region. Tip: for many dynamic categories consider returning rows and pivoting in the application layer or using a reporting tool that supports dynamic pivots, because large numbers of CASE branches can become unwieldy.

How do you handle schema migrations safely in production?

Outline a safe migration pattern: add new columns or tables in a backward-compatible way, deploy application changes that write to both old and new schema if needed, backfill data in background jobs, and finally remove legacy structures after verifying. Use small, reversible steps and deploy during low traffic when possible with feature flags or migration scripts that are idempotent. Give a concrete strategy: to rename a column, add the new column, update writes to populate both columns, backfill existing rows in batches, switch reads to the new column, and then drop the old column once you confirm stability. Always test migrations on a staging clone of production data and monitor metrics during rollout. Pitfalls include long transactions that lock large tables, migrations that assume exclusive downtime in a 24/7 system, and removing columns before all consumers have migrated. Plan rollbacks and ensure backups are available.

sql Interview Questions: Complete Guide

This guide on sql interview questions prepares you for common formats like phone screens, technical take-home tasks, and whiteboard or pair-programming rounds. Expect a mix of conceptual questions, live query writing, and performance troubleshooting, and remember you can show your thinking as you go to help the interviewer follow your approach. Stay calm, talk through trade-offs, and practice a few query patterns ahead of time.

Common Interview Questions

Behavioral Questions (STAR Method)

STAR Method: Structure your answers using Situation, Task, Action, and Result to tell compelling stories about your experience.

Technical Questions

Questions to Ask the Interviewer

Show your interest by asking thoughtful questions

•What does success look like in this role after the first 6 months, specifically for data or query performance goals?
•Can you describe the team structure and who I would work with when solving database performance issues?
•What are the largest data scale or growth challenges the team expects in the next year?
•How does the team handle schema migrations and production rollouts for high-traffic tables?
•What tools do you use for monitoring query performance, alerts, and post-incident analysis?

Interview Preparation Tips

1
Practice writing queries by hand and explain your steps aloud, because interviewers value clear, logical thinking over memorized syntax.
2
When solving a problem, state assumptions, outline edge cases, and ask clarifying questions before writing code or queries.
3
Use EXPLAIN or a profiler during practice so you can interpret execution plans quickly in interviews and propose concrete optimizations.
4
Prepare one or two concise stories about performance fixes or schema changes that include metrics, because concrete outcomes make behavioral answers credible.

sql Interview Questions: Complete Guide

Michael Rodriguez

Common Interview Questions

Behavioral Questions (STAR Method)

Technical Questions

Questions to Ask the Interviewer

Interview Preparation Tips

Related guides

agile Interview Questions: Complete Guide

algorithms Interview Questions: Complete Guide

angular Interview Questions: Complete Guide

aws Interview Questions: Complete Guide

sql Interview Questions: Complete Guide

Michael Rodriguez

Common Interview Questions

Q1What are the different types of JOINs in SQL and when should you use each?

Q2How do indexes work and when should you add them?

Q3Explain normalization and denormalization, and when you might choose one over the other.

Q4How would you find duplicate rows in a table and remove them safely?

Q5What are window functions and when do you use them?

Q6How do you approach query performance tuning?

Q7Explain ACID properties and why they matter in databases.

Q8How do you design a many-to-many relationship in a relational database?

Q9Write a query to find the nth highest salary in an employees table.

Q10What is an execution plan and how do you read it?

Behavioral Questions (STAR Method)

B1Tell me about a time you resolved a production SQL performance issue.

B2Describe a time you disagreed with a database design decision and how you handled it.

B3Tell me about a time you had to learn a new SQL feature or tool quickly and apply it.

Technical Questions

T1How do you use EXPLAIN ANALYZE to debug a slow query? Provide an example.

T2Show how to write a query using window functions to compute a 7-day moving average of sales.

T3How do transactions and isolation levels affect concurrent updates? Give an example of a race condition and how to prevent it.

T4Write a query to pivot rows into columns for a small fixed set of categories.

T5How do you handle schema migrations safely in production?

Questions to Ask the Interviewer

Interview Preparation Tips

Related guides

agile Interview Questions: Complete Guide

algorithms Interview Questions: Complete Guide

angular Interview Questions: Complete Guide

aws Interview Questions: Complete Guide