Top SQL Interview Questions for 6+ Years Experience (2026)

SQL interviews for senior developers are a completely different beast from what you faced early in your career. Nobody is asking you to write a basic SELECT or explain what a JOIN is. At the 6+ year level, interviewers expect you to talk about query optimization, execution plans, index strategies, window functions, transaction isolation levels, deadlocks, and how your database decisions affect application performance at scale.

This guide covers the SQL questions most commonly asked to senior developers and database engineers - with detailed answers, real query examples, and the thinking behind each answer that separates a strong senior candidate from an average one.

1. What Is the Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()?

This is one of the most common senior SQL questions. All three are window functions that assign a number to each row, but they handle ties differently:

-- Sample data
-- Name    | Score
-- Smith | 95
-- Priya   | 90
-- Amit    | 90
-- Neha    | 85

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

-- Result:
-- name    | score | row_num | rank | dense_rank
-- Smith |  95   |    1    |  1   |     1
-- Priya   |  90   |    2    |  2   |     2
-- Amit    |  90   |    3    |  2   |     2
-- Neha    |  85   |    4    |  4   |     3

• ROW_NUMBER() - always unique, no ties. Each row gets a different number regardless of duplicate values.
• RANK() - ties get the same rank, but the next rank skips numbers. Two rows tied at rank 2 means the next is rank 4.
• DENSE_RANK() - ties get the same rank, and the next rank does not skip. Two rows tied at rank 2 means the next is rank 3.

When to use which: ROW_NUMBER for pagination. RANK for leaderboards where gaps matter. DENSE_RANK for leaderboards where you want consecutive ranking.

2. How Do You Find the Nth Highest Salary?

A classic question with multiple valid approaches. Knowing all of them shows depth:

-- Method 1: Using DENSE_RANK() - cleanest approach
SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3; -- change 3 to find Nth highest

-- Method 2: Using LIMIT with OFFSET (MySQL)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2; -- OFFSET N-1 for Nth highest

-- Method 3: Using correlated subquery
SELECT DISTINCT salary
FROM employees e1
WHERE 2 = (  -- replace 2 with N-1
  SELECT COUNT(DISTINCT salary)
  FROM employees e2
  WHERE e2.salary > e1.salary
);

3. Explain Query Execution Order in SQL

Most developers write SQL in SELECT - FROM - WHERE order but the database engine executes it in a completely different order. Knowing this explains many common errors and helps write better queries:

-- Written order (how you type it):
SELECT   name, COUNT(orders) AS order_count
FROM     customers
JOIN     orders ON customers.id = orders.customer_id
WHERE    customers.country = 'India'
GROUP BY customers.id, name
HAVING   COUNT(orders) > 5
ORDER BY order_count DESC
LIMIT    10;

-- Actual execution order:
-- 1. FROM      - identify the source tables
-- 2. JOIN      - combine tables
-- 3. WHERE     - filter rows (before grouping)
-- 4. GROUP BY  - group the filtered rows
-- 5. HAVING    - filter groups (after grouping)
-- 6. SELECT    - select columns and expressions
-- 7. DISTINCT  - remove duplicates if needed
-- 8. ORDER BY  - sort the results
-- 9. LIMIT     - return the specified number of rows

This explains why you cannot use a SELECT alias in a WHERE clause - WHERE runs before SELECT, so the alias does not exist yet. It also explains why HAVING filters aggregated results but WHERE cannot.

4. What Is an Index and How Does It Affect Performance?

An index is a data structure - typically a B-tree - that allows the database to find rows without scanning the entire table. Without an index, every query does a full table scan O(n). With an index, the database can find rows in O(log n).

-- Without index - full table scan on 10 million rows
SELECT * FROM orders WHERE customer_id = 12345;
-- Scans all 10 million rows

-- With index on customer_id
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Now finds rows in milliseconds via B-tree lookup

-- Composite index - covers multiple columns
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, order_date);

-- This index helps queries that filter by:
-- customer_id alone
-- customer_id AND order_date
-- But NOT order_date alone (left-most prefix rule)

When indexes hurt performance:

• Heavy write tables - every INSERT, UPDATE, DELETE must also update all indexes
• Low cardinality columns - indexing a boolean column with only 2 values is rarely useful
• Too many indexes - the optimizer has to choose between them, and wrong choices happen

The left-most prefix rule: A composite index on (A, B, C) can be used for queries filtering on A, A+B, or A+B+C - but not B alone or C alone.

5. What Is the Difference Between TRUNCATE, DELETE, and DROP?

6. What Are Transaction Isolation Levels?

This is a question that genuinely separates senior developers from junior ones. Isolation levels control how concurrent transactions see each other's changes - the trade-off between consistency and performance.

-- Four isolation levels (from least to most strict):
-- 1. READ UNCOMMITTED
-- 2. READ COMMITTED
-- 3. REPEATABLE READ  ← MySQL InnoDB default
-- 4. SERIALIZABLE

Problems each level prevents:

• Dirty Read - reading data that another transaction has modified but not yet committed. If that transaction rolls back, you read data that never existed.
• Non-Repeatable Read - reading the same row twice in a transaction gets different values because another transaction updated it between your reads.
• Phantom Read - running the same query twice returns different rows because another transaction inserted or deleted rows.

-- READ UNCOMMITTED - no protection, fastest
-- Can see: dirty reads, non-repeatable reads, phantom reads

-- READ COMMITTED - protects against dirty reads
-- Can see: non-repeatable reads, phantom reads

-- REPEATABLE READ - protects against dirty + non-repeatable reads
-- Can see: phantom reads (though InnoDB prevents most with MVCC)

-- SERIALIZABLE - full protection, slowest
-- No dirty reads, no non-repeatable reads, no phantom reads

-- Set isolation level in MySQL:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- your queries
COMMIT;

7. What Is a Deadlock and How Do You Prevent It?

A deadlock occurs when two or more transactions are waiting for each other to release locks - creating a circular dependency that never resolves. The database detects this and kills one of the transactions (the deadlock victim).

-- Classic deadlock scenario:
-- Transaction A locks Row 1, then tries to lock Row 2
-- Transaction B locks Row 2, then tries to lock Row 1
-- Both wait forever - deadlock

-- Transaction A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- locks row 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- waits for row 2

-- Transaction B (running concurrently)
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- locks row 2
UPDATE accounts SET balance = balance + 50 WHERE id = 1;  -- waits for row 1
-- DEADLOCK - database kills one transaction

Prevention strategies:

• Always access tables and rows in the same order across all transactions. If every transaction locks row 1 before row 2, the deadlock above cannot occur.
• Keep transactions short - the longer a transaction holds locks, the higher the chance of deadlock.
• Use SELECT FOR UPDATE wisely - lock only the rows you actually need to modify.
• Use lower isolation levels where appropriate - READ COMMITTED acquires fewer locks than SERIALIZABLE.

8. Explain CTEs vs Subqueries vs Temp Tables

-- Subquery - inline, executed once per reference
SELECT * FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE country = 'India'
);

-- CTE (Common Table Expression) - named, reusable within query
WITH indian_customers AS (
  SELECT id FROM customers WHERE country = 'India'
)
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM indian_customers);

-- Multiple CTEs - very readable for complex queries
WITH
  active_users AS (
    SELECT id, name FROM users WHERE is_active = 1
  ),
  their_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
  )
SELECT u.name, o.order_count
FROM active_users u
JOIN their_orders o ON o.user_id = u.id
ORDER BY o.order_count DESC;

-- Temp Table - persists for the session, can be indexed
CREATE TEMPORARY TABLE temp_indian_customers AS
SELECT id FROM customers WHERE country = 'India';

CREATE INDEX idx_temp ON temp_indian_customers(id);

SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM temp_indian_customers);

When to use each:

• Subquery - simple, one-off filter conditions
• CTE - complex logic that needs to be broken into readable steps, or when you need to reference the same derived data multiple times
• Temp table - very large intermediate result sets that benefit from indexing, or when you need to reuse the result across multiple separate queries

9. What Is Query Optimization and How Do You Approach It?

Senior developers are expected to diagnose and fix slow queries systematically. Here is the real-world approach:

-- Step 1: Use EXPLAIN to see the execution plan
EXPLAIN SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC;

-- Look for:
-- type = ALL - full table scan - bad
-- type = ref or eq_ref - index used - good
-- key = NULL - no index used
-- rows = 1000000 - scanning too many rows

-- Step 2: Add the right index
CREATE INDEX idx_orders_customer_date
ON orders(customer_id, created_at);

-- Step 3: EXPLAIN again - verify the index is being used
EXPLAIN SELECT * FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC;
-- Now: type=ref, key=idx_orders_customer_date, rows=15

-- Common optimization mistakes to avoid:
-- Using functions on indexed columns (defeats the index)
-- ❌ WHERE YEAR(created_at) = 2026
-- ✅ WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

-- Using wildcards at the start of LIKE (defeats the index)
-- ❌ WHERE name LIKE '%Smith%'
-- ✅ WHERE name LIKE 'Smith%'

-- Selecting unnecessary columns
-- ❌ SELECT * FROM orders WHERE customer_id = 123
-- ✅ SELECT id, total, status FROM orders WHERE customer_id = 123

10. What Is MVCC (Multi-Version Concurrency Control)?

MVCC is the mechanism MySQL InnoDB and PostgreSQL use to allow multiple transactions to read and write simultaneously without locking each other out. Instead of locking a row when someone reads it, the database keeps multiple versions of the row - each transaction sees the version that was current when its transaction started.

This is why in InnoDB, SELECT queries do not block UPDATE queries and vice versa - they are looking at different versions of the same data. MVCC is what makes REPEATABLE READ work without locking the entire table.

The downside of MVCC is undo log growth - long-running transactions accumulate large undo logs as they need to maintain old row versions for consistency. This is why long transactions can cause performance degradation even if they are only reading data.

11. Difference Between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN

-- Tables:
-- customers: id=1 (Alice), id=2 (Bob), id=3 (Charlie)
-- orders:    customer_id=1 (2 orders), customer_id=2 (1 order)
-- Charlie has no orders

-- INNER JOIN - only matching rows from both tables
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- Result: Alice (x2), Bob (x1)
-- Charlie excluded - no matching orders

-- LEFT JOIN - all rows from left table, nulls for non-matches
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Result: Alice (x2), Bob (x1), Charlie (null)
-- Charlie included with null order

-- RIGHT JOIN - all rows from right table, nulls for non-matches
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;
-- Returns all orders, nulls for customers without orders

-- FULL OUTER JOIN - all rows from both tables (MySQL does not support directly)
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.total
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id;

12. Write a Query to Find Duplicate Records

-- Find duplicate emails in users table
SELECT email, COUNT(*) AS count
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY count DESC;

-- Find the actual duplicate rows with their IDs
SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT(*) > 1
)
ORDER BY email;

-- Delete duplicates keeping the one with the lowest ID
DELETE FROM users
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id
    FROM users
    GROUP BY email
  ) AS keep
);

13. What Are Stored Procedures vs Functions vs Triggers?

-- Stored Procedure - executes logic, can modify data, no return value required
DELIMITER $$
CREATE PROCEDURE update_user_status(IN user_id INT, IN new_status VARCHAR(20))
BEGIN
  UPDATE users SET status = new_status WHERE id = user_id;
  INSERT INTO audit_log (user_id, action) VALUES (user_id, 'status_changed');
END$$
DELIMITER ;

-- Call it
CALL update_user_status(123, 'active');

-- Function - must return a value, used inside SELECT
DELIMITER $$
CREATE FUNCTION get_full_name(first_name VARCHAR(50), last_name VARCHAR(50))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
  RETURN CONCAT(first_name, ' ', last_name);
END$$
DELIMITER ;

-- Use it
SELECT get_full_name(first_name, last_name) FROM users;

-- Trigger - automatically fires on INSERT, UPDATE, DELETE
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  UPDATE customers
  SET total_orders = total_orders + 1
  WHERE id = NEW.customer_id;
END$$
DELIMITER ;

14. How Do You Handle Large Table Migrations Without Downtime?

This is a real-world senior question that tests your production experience. Running ALTER TABLE on a table with millions of rows can lock the table for minutes or hours - unacceptable in production.

Strategies used by senior engineers:

• pt-online-schema-change - Percona tool that creates a shadow table, copies data in batches, and swaps tables atomically. Used by most large-scale MySQL deployments.
• Expand/Contract pattern - add the new column as nullable first, deploy code that writes to both old and new columns, backfill old data, then make the column required.
• Blue/Green database deployment - maintain two database instances, migrate one while the other serves traffic, then switch.
• Online DDL in MySQL 8+ - many ALTER TABLE operations can now run online without blocking reads or writes depending on the operation type.

15. SQL Query Optimization Interview Questions You Should Be Ready For

• A query is slow - walk me through how you would diagnose it
• When would you NOT add an index even though a column is frequently queried?
• What is the difference between a clustered and non-clustered index?
• How does the query optimizer decide which index to use?
• What is query plan caching and when does it help vs hurt?
• How would you optimize a query that uses DISTINCT?
• What is an index covering query and why is it faster?

Final Thought

Senior SQL interviews test your understanding of why things work - not just how to write the syntax. Being able to explain execution order, index internals, isolation levels, and deadlock prevention shows that you have worked with databases under real pressure at real scale. These are the answers that make interviewers confident you can make good database design decisions independently.

Review your own projects - what slow queries have you fixed? What index strategy did you use? What was the biggest database problem you solved? Real stories from your own experience are worth more than any memorized answer in a senior interview.

Also check out our related interview guides:

• - Array Coding Interview Problems
• - String Coding Interview Problems
• - Common Developer Interview Questions and Answers
• - Normalization in DBMS Explained - 1NF 2NF 3NF and BCNF