SQL Interview Questions and Answers: 60 Questions from Easy to Hard

SQL interview questions: Easy questions (freshers)

1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, so it cannot use aggregate functions. HAVING filters groups after GROUP BY has computed the aggregates, so conditions such as COUNT(*) > 10 or SUM(amount) > 100000 belong there. Put every condition that does not need an aggregate in WHERE: it removes rows early, can use indexes and makes the grouping cheaper. For example, to find departments with more than 10 active employees, filter the status in WHERE and the count in HAVING.

SELECT dept_id, COUNT(*) AS headcount
FROM employees
WHERE status = 'active'
GROUP BY dept_id
HAVING COUNT(*) > 10;

2. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows one at a time, accepts a WHERE clause, fires row triggers and can be rolled back inside a transaction. TRUNCATE empties the whole table at once by deallocating its storage, so it is much faster, cannot filter rows and does not fire DELETE triggers; in MySQL it also resets the AUTO_INCREMENT counter. DROP removes the table itself, including its structure, indexes and permissions. A dialect detail interviewers like: in MySQL, TRUNCATE causes an implicit commit and cannot be rolled back, while in PostgreSQL it is transactional and can be.

3. What is the difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN returns only rows that have a match in both tables. A LEFT JOIN returns every row from the left table and fills the right table's columns with NULL where no match exists. When listing employees with department names, an INNER JOIN drops employees whose dept_id is NULL or invalid, while a LEFT JOIN keeps them with a NULL department. RIGHT JOIN is the mirror image and is usually rewritten as a LEFT JOIN for readability. FULL OUTER JOIN keeps unmatched rows from both sides; MySQL lacks it, so emulate it with a UNION of a LEFT and a RIGHT JOIN.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

4. What is the difference between a primary key and a unique key?

Both enforce uniqueness and are backed by an index. A table has only one primary key, its columns can never be NULL, and it is the row's main identity and the usual target of foreign keys. A table can have many unique constraints, and in both MySQL and PostgreSQL a unique column accepts multiple NULLs, because one NULL is not considered equal to another. In MySQL's InnoDB engine, the primary key also decides the physical order of rows, so a short, stable, increasing key such as BIGINT AUTO_INCREMENT works best.

5. What is a foreign key, and what do ON DELETE CASCADE and ON DELETE RESTRICT do?

A foreign key makes a column's values refer to an existing row in another table, usually its primary key, which guarantees referential integrity: you cannot insert an order for a customer who does not exist. The ON DELETE action decides what happens when the parent row is deleted. RESTRICT, or the default NO ACTION, blocks the delete while child rows exist; CASCADE deletes the children too; SET NULL clears the reference. Use CASCADE carefully, because one delete can silently remove thousands of rows. In MySQL, foreign keys are enforced by InnoDB, not MyISAM.

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE RESTRICT
);

6. Why does WHERE manager_id = NULL return no rows?

NULL means unknown, so any comparison with NULL, including NULL = NULL, evaluates to UNKNOWN rather than TRUE, and WHERE keeps only rows where the condition is TRUE. Use IS NULL or IS NOT NULL instead. The same three-valued logic affects other expressions: NULL + 100 is NULL, and a condition such as salary <> 50000 silently skips rows where salary is NULL. COALESCE(col, fallback) replaces NULL with a value for display or arithmetic, for example COALESCE(bonus, 0).

SELECT name FROM employees WHERE manager_id IS NULL;   -- top of the hierarchy
SELECT name, salary + COALESCE(bonus, 0) AS total_pay FROM employees;

7. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows, including rows whose columns are all NULL. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values. So with 100 employees of whom 20 have a NULL email, COUNT(*) is 100 and COUNT(email) is 80. COUNT(1) behaves exactly like COUNT(*) in MySQL and PostgreSQL, and neither is faster. Other aggregates such as SUM and AVG also ignore NULLs, so AVG(bonus) averages only the employees who have a bonus.

SELECT COUNT(*) AS all_rows,
       COUNT(email) AS with_email,
       COUNT(DISTINCT dept_id) AS departments
FROM employees;

8. What is the difference between UNION and UNION ALL?

Both stack the results of two SELECT statements that have the same number of columns with compatible types. UNION removes duplicate rows, which needs an extra sort or hash step; UNION ALL keeps every row and is therefore faster. Use UNION ALL whenever duplicates are impossible or acceptable, such as combining this year's table with an archive table, and use UNION only when you truly need distinct rows. Column names come from the first SELECT, and an ORDER BY at the end sorts the combined result.

SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;

9. In what order does SQL logically process a SELECT query?

Logically: FROM and JOINs, then WHERE, GROUP BY, HAVING, SELECT including window functions, DISTINCT, ORDER BY and finally LIMIT or OFFSET. This explains common errors. You cannot use a column alias defined in SELECT inside WHERE, because WHERE is evaluated earlier, but you can use it in ORDER BY. It also explains why window functions cannot appear in WHERE and need a subquery or CTE. The optimiser may physically execute steps in a different order, but the result must match this logical order.

10. Write a query to find the second highest salary.

Take the maximum of all salaries below the overall maximum. This handles ties, since everyone sharing the top salary is excluded, and it returns NULL rather than an empty result when no second salary exists, which is usually what the question expects. Another common answer is SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET 1, which needs DISTINCT to handle ties and returns no row at all when there is no second value. For the general Nth highest salary, use DENSE_RANK.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

11. How do you find duplicate email addresses in a users table?

Group by the column that should be unique and keep only groups with more than one row. This returns each duplicated value once, with the number of times it appears. To see the full duplicate rows, join this result back to the table or use COUNT(*) OVER (PARTITION BY email) in a subquery. In real data, normalise case and spaces first with LOWER(TRIM(email)), because 'Asha@x.com' and 'asha@x.com ' are the same address, then add a unique constraint so duplicates cannot come back.

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

12. What is an index, and what does it cost?

An index is a separate sorted structure, usually a B-tree, that maps column values to rows, so the database can find matching rows without scanning the whole table, much like the index at the back of a book. It speeds up WHERE filters, joins, ORDER BY and uniqueness checks. The costs are extra storage and slower INSERT, UPDATE and DELETE statements, because every index must be maintained on each write. So index columns that are frequently filtered or joined on and that are selective, and avoid indexing everything on write-heavy tables.

CREATE INDEX idx_employees_email ON employees (email);

13. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed length: shorter values are padded with spaces to n characters. VARCHAR(n) stores only the actual characters plus a small length prefix, up to n. Use CHAR for values that always have the same length, such as a two-letter state code like MH or KA, and VARCHAR for names, emails and addresses. In MySQL, trailing spaces of a CHAR value are removed when you read it. In PostgreSQL, CHAR has no performance advantage, and VARCHAR or TEXT is the usual choice. Store money as DECIMAL, never as text or FLOAT.

14. What is normalisation, and what are 1NF, 2NF and 3NF?

Normalisation organises tables so each fact is stored once, which prevents update, insert and delete anomalies. 1NF: every column holds a single atomic value and there are no repeating groups, so no comma-separated skills column. 2NF: the table is in 1NF and every non-key column depends on the whole primary key, not just part of a composite key. 3NF: the table is in 2NF and non-key columns depend only on the key, not on other non-key columns; storing dept_name in employees, when it depends on dept_id, breaks 3NF. Most transactional schemas aim for 3NF.

15. What are the ACID properties of a transaction?

Atomicity: all statements in a transaction succeed together or none do, so a fund transfer never debits one account without crediting the other. Consistency: a transaction moves the database from one valid state to another and respects every constraint. Isolation: concurrent transactions do not see each other's partial work, to the degree set by the isolation level. Durability: once a commit returns, the change survives a crash, because it was first written to a log, InnoDB's redo log or PostgreSQL's write-ahead log.

START TRANSACTION;
UPDATE accounts SET balance = balance - 5000 WHERE id = 1;
UPDATE accounts SET balance = balance + 5000 WHERE id = 2;
COMMIT;

16. Write a query that lists each employee with their manager's name.

Join the employees table to itself, with one alias for the employee and one for the manager, matching the employee's manager_id to the manager's id. Use a LEFT JOIN so that employees without a manager, such as the CEO, still appear with a NULL manager name. A self join is an ordinary join where both sides are the same table, so aliases are required to tell the two roles apart. The same pattern answers follow-ups such as finding employees who earn more than their managers.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

17. Write a query showing each department's headcount, total salary and average salary.

Join departments to employees, group by the department and apply aggregate functions per group. Group by the department id as well as the name, because two departments can share a name. Starting from departments with a LEFT JOIN keeps departments that have no employees, and COUNT(e.id) correctly shows 0 for them, whereas COUNT(*) would count the NULL-filled row as 1. COALESCE turns the NULL sum of an empty department into 0. As a rule, every non-aggregated column in SELECT should appear in GROUP BY.

SELECT d.id, d.name,
       COUNT(e.id) AS headcount,
       COALESCE(SUM(e.salary), 0) AS total_salary,
       ROUND(AVG(e.salary), 2) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.name;

18. How do LIKE wildcards work, and is LIKE case-sensitive?

LIKE matches patterns with two wildcards: % matches any sequence of characters, including none, and _ matches exactly one character. So 'A%' finds names starting with A, and '_a%' finds names whose second letter is a. Case sensitivity depends on the database: MySQL's default case-insensitive collations ignore case, while PostgreSQL's LIKE is case-sensitive and offers ILIKE for case-insensitive matching. A pattern that starts with %, such as '%sharma', cannot use a normal B-tree index for seeking, so it scans.

SELECT name FROM employees WHERE name LIKE 'A%';          -- starts with A
SELECT name FROM employees WHERE email LIKE '%@gmail.com';

19. Write a query to find employees who earn more than the company average.

Compute the average in a subquery and compare each row against it. The inner query runs once and returns a single value, which the outer WHERE uses like a constant. You cannot write WHERE salary > AVG(salary) directly, because aggregate functions are not allowed in WHERE. The usual follow-up is 'more than their own department's average', which needs a correlated subquery or a window function, because the comparison value then changes from department to department.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

20. How do you use a CASE expression in SQL?

CASE returns a value based on conditions, like if-else inside a query. The searched form tests its WHEN conditions in order and returns the first match, so put the most specific condition first. If no branch matches and there is no ELSE, the result is NULL, so add ELSE when every row needs a value. CASE works in SELECT, ORDER BY, GROUP BY and inside aggregates, which makes it the basis of conditional counts and pivots, for example SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END).

SELECT name, salary,
       CASE
         WHEN salary >= 100000 THEN 'High'
         WHEN salary >= 50000 THEN 'Medium'
         ELSE 'Entry'
       END AS band
FROM employees;

SQL interview questions: Medium questions (1-3 years)

21. What is the difference between ROW_NUMBER, RANK and DENSE_RANK, and how do you use one to find the Nth highest salary?

All three number rows within a window ordered by some column; they differ on ties. ROW_NUMBER gives unique numbers 1, 2, 3, 4 even for equal values, breaking ties arbitrarily unless you add a tiebreaker column. RANK gives tied rows the same number and then skips: 1, 2, 2, 4. DENSE_RANK gives ties the same number without gaps: 1, 2, 2, 3. For the Nth highest salary, DENSE_RANK is correct, because the third highest salary should be the third distinct value. Window functions need MySQL 8.0 or later, or any supported PostgreSQL version.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
  WHERE salary IS NOT NULL
) ranked
WHERE rnk = 3;

22. Write a query to get the top two earners in each department.

Number the rows within each department using a window function partitioned by dept_id and ordered by salary descending, then filter on that number in an outer query, because a window function's result cannot be used in the WHERE clause of the same SELECT. ROW_NUMBER returns exactly two people per department; use DENSE_RANK if everyone tied on a top salary should be included. Top N per group is one of the most frequent SQL interview tasks, and this pattern replaces older correlated-subquery solutions that are harder to read.

WITH ranked AS (
  SELECT name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT dept_id, name, salary
FROM ranked
WHERE rn <= 2
ORDER BY dept_id, salary DESC;

23. How do you calculate a running total, and what is the difference between ROWS and RANGE in a window frame?

Use SUM(amount) OVER (ORDER BY ...). With an ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with the same sort value as peers and includes all of them, so two orders on the same day show the same running total. ROWS counts physical rows instead, giving a strict row-by-row total. Write the frame explicitly and add a unique tiebreaker, such as id, to avoid surprises. A three-row moving average uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, id
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY order_date, id
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

24. How would you calculate month-over-month revenue growth?

First aggregate revenue per month, then use LAG to bring the previous month's value onto the current row and compute the percentage change. LAG(x) reads one row back in the window order and LEAD reads one row forward; both return NULL, or an optional default, when no such row exists, so the first month shows NULL growth. NULLIF in the denominator avoids division-by-zero errors. Months without any orders are missing entirely, so for strict comparisons generate a calendar of months and LEFT JOIN the revenue onto it.

-- PostgreSQL (in MySQL, use DATE_FORMAT(order_date, '%Y-%m-01') for the month)
WITH monthly AS (
  SELECT date_trunc('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) AS growth_pct
FROM monthly
ORDER BY month;

25. What is a CTE, and how would you use a recursive CTE to fetch everyone who reports to a manager, directly or indirectly?

A common table expression, written WITH name AS (...), is a named subquery that exists for one statement. It makes complex queries readable by splitting them into steps, and it can be referenced more than once. A recursive CTE has an anchor query plus a recursive part that joins back to the CTE itself, repeating until no new rows appear. That handles hierarchies such as org charts or category trees without knowing their depth. MySQL 8.0+ and PostgreSQL both need the RECURSIVE keyword. Add a depth limit to guard against cycles in bad data.

WITH RECURSIVE reports AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE id = 1                      -- the manager we start from
  UNION ALL
  SELECT e.id, e.name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
  WHERE r.depth < 10
)
SELECT * FROM reports WHERE depth > 0;

26. What is the difference between IN and EXISTS, and why can NOT IN return no rows?

IN compares a value with a list or with a subquery's results; EXISTS is true as soon as a correlated subquery finds one row. Modern optimisers in MySQL 8 and PostgreSQL often turn both into semi-joins, so their speed is usually similar. The real difference appears with NOT. If the subquery behind NOT IN returns even one NULL, x NOT IN (...) can never be TRUE, because x <> NULL is UNKNOWN, so the query silently returns nothing. NOT EXISTS has no such trap, which makes it the safer default for finding rows without a match.

-- Returns no rows at all if any orders.customer_id is NULL
SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe version
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

27. Write a query to find customers who have never placed an order.

This is an anti-join. The LEFT JOIN version joins orders to customers and keeps rows where the order side is NULL, meaning no match was found. Test a column that can never be NULL in a real match, such as the orders primary key. NOT EXISTS expresses the same intent directly and avoids the NOT IN NULL trap, and recent versions of MySQL 8 and PostgreSQL execute both forms as an anti-join. Make sure orders.customer_id is indexed, or each lookup has to scan the orders table.

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

28. How does a composite index work, and what is the leftmost prefix rule?

A composite index on (a, b, c) is sorted by a, then by b within each a, then by c, like a phone book sorted by surname and then first name. The database can seek with it on a, on a and b, or on all three, but not efficiently on b or c alone, because those values are scattered across the index. A range condition on one column stops the columns after it from being used for seeking. So put equality columns first, then the range or sort column: (org_id, status, created_at) serves WHERE org_id = ? AND status = ? ORDER BY created_at.

CREATE INDEX idx_tickets_org_status_created
  ON tickets (org_id, status, created_at);

-- Uses the index for both the filter and the ORDER BY
SELECT id, subject FROM tickets
WHERE org_id = 42 AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

29. What is a clustered index, and how do MySQL InnoDB and PostgreSQL differ here?

A clustered index stores the table rows themselves in index order, so a table can have only one. In InnoDB the primary key is always the clustered index: rows live in the leaves of the primary-key B-tree, and every secondary index stores the primary key value, so a secondary-index lookup performs a second search in the primary index. That is why a long primary key bloats every secondary index. PostgreSQL stores rows in an unordered heap, and every index, including the primary key, points to row locations; its CLUSTER command reorders a table once but does not maintain that order.

30. You added an index, but the query still does a full scan. What could be the reasons?

Common causes: a function or expression on the column, such as DATE(created_at) = '2026-01-15' or LOWER(email) = ...; a leading wildcard such as LIKE '%kumar'; an implicit type conversion, such as comparing a VARCHAR phone column with a number in MySQL; a composite index whose leading column is not filtered; OR across different columns; or low selectivity, where the optimiser correctly decides that reading most of the table is cheaper. Stale statistics can also mislead the planner. Check with EXPLAIN, rewrite the predicate so it is sargable, or add an expression index.

-- Not sargable: the index on created_at cannot be used for seeking
SELECT id FROM orders WHERE DATE(created_at) = '2026-01-15';

-- Sargable rewrite
SELECT id FROM orders
WHERE created_at >= '2026-01-15' AND created_at < '2026-01-16';

31. How do you read an EXPLAIN plan?

EXPLAIN shows how the database plans to run a query; EXPLAIN ANALYZE actually runs it and reports real row counts and timings, in PostgreSQL and in MySQL 8.0.18 and later. In MySQL's tabular output, check type (ALL means a full table scan, while ref, range and const are better), key (the index chosen), rows (estimated rows examined) and Extra, where Using filesort or Using temporary signals extra work. In PostgreSQL, look for Seq Scan versus Index Scan nodes and compare estimated with actual rows; a large gap points to stale statistics, which ANALYZE refreshes.

EXPLAIN ANALYZE
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01'
GROUP BY c.name;

32. What are the transaction isolation levels, and which anomalies does each prevent?

The SQL standard defines four levels. READ UNCOMMITTED allows dirty reads of uncommitted changes. READ COMMITTED prevents dirty reads, but rereading a row can return a newer committed value, a non-repeatable read. REPEATABLE READ keeps rows you have read stable, though the standard still allows phantom rows to appear in a repeated range query. SERIALIZABLE makes transactions behave as if they ran one after another. Defaults differ: InnoDB uses REPEATABLE READ and PostgreSQL uses READ COMMITTED. PostgreSQL treats READ UNCOMMITTED as READ COMMITTED, and its REPEATABLE READ also prevents phantoms.

-- PostgreSQL
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- MySQL: applies to the next transaction in this session
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

33. What is BCNF, and when is it acceptable to denormalise?

Boyce-Codd Normal Form tightens 3NF: for every functional dependency X -> Y, X must be a candidate key. A table can be in 3NF but not BCNF when candidate keys overlap. For example, in (student, course, teacher) where each teacher teaches exactly one course, teacher determines course but is not a key. Denormalise deliberately when reads dominate and joins are measurably too slow, such as storing an order's total or a post's comment count, or building reporting tables. Keep the copy correct with transactions or a scheduled rebuild, and document which column is the source of truth.

34. Why does SELECT dept_id, name, MAX(salary) FROM employees GROUP BY dept_id fail, and how do you fix it?

name is neither grouped nor aggregated, so each department has many candidate names and the query is ambiguous. PostgreSQL rejects it, and so does MySQL 5.7.5 and later with the default ONLY_FULL_GROUP_BY mode; older MySQL returned an arbitrary name, which looked plausible but was not necessarily the top earner's. Both allow a non-grouped column that is functionally dependent on a grouped primary key. To get the highest earner's name per department, join back to the per-department maximum or use ROW_NUMBER. Never switch off ONLY_FULL_GROUP_BY just to silence the error.

SELECT e.dept_id, e.name, e.salary
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS max_sal
      FROM employees
      GROUP BY dept_id) m
  ON m.dept_id = e.dept_id AND m.max_sal = e.salary;

35. How do you delete duplicate rows but keep one copy of each?

Decide which copy survives, usually the lowest id, then delete every other row in each group. The portable version keeps MIN(id) per email. In MySQL the inner query must be wrapped in a derived table, because MySQL does not let a subquery read the table being deleted from (error 1093); the aggregation forces the derived table to be materialised first. In PostgreSQL, a ROW_NUMBER CTE works too. Run it inside a transaction, check the affected row count against a SELECT first, then add a unique constraint so duplicates cannot return.

DELETE FROM users
WHERE id NOT IN (
  SELECT keep_id FROM (
    SELECT MIN(id) AS keep_id
    FROM users
    GROUP BY email
  ) AS keepers
);

36. How do you pivot rows into columns, such as monthly sales per product?

Use conditional aggregation: group by the row label and, for each output column, aggregate only the matching rows with a CASE expression inside SUM. This works in every major database, whereas MySQL and PostgreSQL have no PIVOT keyword. PostgreSQL also accepts the neater SUM(amount) FILTER (WHERE ...) form. The output columns must be known when you write the query; if the categories are dynamic, build the SQL in application code or use PostgreSQL's crosstab function from the tablefunc extension.

SELECT product,
       SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 1 THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 2 THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN EXTRACT(MONTH FROM sale_date) = 3 THEN amount ELSE 0 END) AS mar
FROM sales
WHERE sale_date >= '2026-01-01' AND sale_date < '2026-04-01'
GROUP BY product;

37. Two requests debit the same wallet at the same time. How do you prevent a lost update?

Read and write inside one transaction while holding a row lock. SELECT ... FOR UPDATE locks the wallet row, so a second transaction that tries to lock it waits until the first commits and then reads the new balance. Check the balance in the application, update, and commit quickly. For a single row there is an even simpler option, an atomic conditional update: UPDATE wallets SET balance = balance - 500 WHERE id = 7 AND balance >= 500, then confirm one row was affected. Never read the balance, compute in code and write back an absolute value without a lock.

START TRANSACTION;
SELECT balance FROM wallets WHERE id = 7 FOR UPDATE;
-- the application checks that balance >= 500
UPDATE wallets SET balance = balance - 500 WHERE id = 7;
COMMIT;

38. Why does LIMIT 20 OFFSET 100000 get slow, and what is the alternative?

OFFSET does not jump: the database still reads and discards the first 100,000 rows before returning 20, so every deeper page costs more, and rows inserted between requests shift the pages, causing duplicates or gaps. Keyset, or seek, pagination remembers the last row's sort key and asks for rows after it, so the index starts exactly at that point and every page is equally fast. The sort must be unique, so add a tiebreaker such as id. The trade-off is that users cannot jump straight to an arbitrary page number.

-- First page
SELECT id, title, created_at FROM jobs
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: pass the last row's created_at and id from the previous page
SELECT id, title, created_at FROM jobs
WHERE created_at <= '2026-10-30 09:15:00'
  AND (created_at < '2026-10-30 09:15:00' OR id < 98231)
ORDER BY created_at DESC, id DESC
LIMIT 20;

39. Find employees who earn more than the average salary of their own department.

The comparison value changes per department, so use either a correlated subquery or a window function. A correlated subquery recomputes the average for each outer row's department; an index on dept_id keeps that reasonable. The window version computes each department's average in a single pass, attaches it to every row and filters in the outer query. It is usually faster on large tables and also lets you show the department average next to each employee, which interviewers often ask for next.

SELECT name, dept_id, salary, dept_avg
FROM (
  SELECT name, dept_id, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg;

40. How do CHECK and NOT NULL constraints help, and what changed for CHECK in MySQL?

Constraints push data rules into the database, so every code path, including scripts and manual fixes, has to obey them. NOT NULL makes a value mandatory, DEFAULT supplies one, and CHECK enforces a condition on the row, such as salary > 0 or end_date >= start_date. Before MySQL 8.0.16, CHECK clauses were parsed but silently ignored; since 8.0.16 they are enforced, so an old schema may hold rows that a new check would reject. PostgreSQL has always enforced them and can add one to a large table as NOT VALID, then run VALIDATE CONSTRAINT later.

CREATE TABLE leave_requests (
  id BIGINT PRIMARY KEY,
  emp_id BIGINT NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  CHECK (end_date >= start_date),
  CHECK (status IN ('pending', 'approved', 'rejected'))
);

SQL interview questions: Hard questions (experienced)

41. Find each user's longest streak of consecutive login days.

This is the gaps-and-islands pattern. Number each user's distinct login dates in order with ROW_NUMBER, then subtract that many days from the date. For consecutive dates both values rise by one, so the difference stays constant within a streak and changes after a gap. Group by user and that constant to get each streak's length, then take the maximum per user. Remove duplicate dates first, because two logins on one day would break the arithmetic. The query below is PostgreSQL; in MySQL, compute ROW_NUMBER in the CTE and use DATE_SUB(login_date, INTERVAL rn DAY).

WITH days AS (
  SELECT DISTINCT user_id, login_date FROM logins
), grouped AS (
  SELECT user_id,
         login_date - (ROW_NUMBER() OVER (PARTITION BY user_id
                                          ORDER BY login_date))::int AS grp
  FROM days
)
SELECT user_id, MAX(streak) AS longest_streak
FROM (SELECT user_id, grp, COUNT(*) AS streak
      FROM grouped GROUP BY user_id, grp) s
GROUP BY user_id;

42. How does MVCC work in PostgreSQL and MySQL InnoDB, and what problems can it cause?

Multi-version concurrency control keeps several versions of a row, so readers see a consistent snapshot without blocking writers, and writers do not block readers. PostgreSQL writes a new row version on every UPDATE and marks the old one dead; VACUUM, normally autovacuum, later reclaims the space. InnoDB updates the row in place and keeps older versions in undo logs, which purge threads clean up. The shared risk is long-running transactions: they pin old snapshots, so PostgreSQL tables bloat and InnoDB's history list grows, slowing queries. Keep transactions short and monitor sessions left idle in a transaction.

43. What is a deadlock, and how do you prevent and handle deadlocks in production?

A deadlock occurs when two transactions each hold a lock the other needs: T1 locks account 1 and waits for account 2, while T2 locks account 2 and waits for account 1. The database detects the cycle and aborts one transaction with a deadlock error, MySQL error 1213 or PostgreSQL SQLSTATE 40P01. Prevent most deadlocks by locking rows in a consistent order, such as ascending id, keeping transactions short, and indexing the columns used by UPDATE and DELETE so fewer rows are locked. Deadlocks cannot be eliminated completely, so the application must catch the error and retry the whole transaction.

-- Lock both accounts in ascending id order, whatever the transfer direction
START TRANSACTION;
SELECT id, balance FROM accounts WHERE id IN (17, 42) ORDER BY id FOR UPDATE;
UPDATE accounts SET balance = balance - 1000 WHERE id = 42;
UPDATE accounts SET balance = balance + 1000 WHERE id = 17;
COMMIT;

44. How do MySQL and PostgreSQL deal with phantom reads at REPEATABLE READ, and what anomaly can still happen?

In both, plain SELECTs read from a snapshot, so repeating a query does not show rows committed later by others. For locking reads such as SELECT ... FOR UPDATE, UPDATE and DELETE, InnoDB also takes next-key locks on index records and the gaps between them, blocking inserts into the scanned range; without a suitable index it locks far more. PostgreSQL's REPEATABLE READ is snapshot isolation without gap locks, and updating a row that another transaction changed fails with a serialization error. Snapshot isolation still allows write skew: two doctors each see a colleague on call and both go off duty. PostgreSQL's SERIALIZABLE level detects that.

45. Why can a random UUID primary key hurt performance in MySQL InnoDB, and what are the alternatives?

InnoDB stores rows inside the primary-key B-tree, so inserts with random UUIDv4 keys land on random pages instead of appending at the end. That causes page splits, half-empty pages, poor cache locality and much more disk I/O once the table outgrows the buffer pool. A 36-character string key also enlarges every secondary index, since each entry carries the primary key. Better options: a BIGINT AUTO_INCREMENT key with a separate public UUID column, time-ordered UUIDv7 values stored as BINARY(16), or UUID_TO_BIN(uuid, 1) to reorder the time fields of UUIDv1 values.

46. What is a covering index, and how does it enable an index-only scan?

An index covers a query when every column the query needs, in filters, joins, sorting and the SELECT list, is in the index, so the database answers from the index without visiting the table rows. MySQL's EXPLAIN shows Using index. InnoDB secondary indexes already contain the primary key, so an index on (status, created_at) covers SELECT id, created_at ... WHERE status = 'open'. PostgreSQL shows Index Only Scan and, since version 11, can add non-key payload columns with INCLUDE; it still checks the visibility map, so well-vacuumed tables benefit most. The cost is extra storage and slower writes.

-- PostgreSQL: key columns for filtering and sorting, amount as payload
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date) INCLUDE (amount);

SELECT order_date, amount
FROM orders
WHERE customer_id = 501
ORDER BY order_date DESC;

47. When would you use a partial index or an expression index?

A partial index, available in PostgreSQL, indexes only rows matching a WHERE clause, such as pending jobs in a table where 99% are completed, so the index stays small, and the planner uses it when the query's condition implies the index's condition. Rows outside the condition are not indexed, which also saves write cost. An expression index stores the result of an expression such as LOWER(email), so case-insensitive lookups can use it if the query uses the identical expression. MySQL has no partial indexes, but 8.0.13 and later support functional key parts written in an extra pair of parentheses.

-- PostgreSQL
CREATE INDEX idx_jobs_pending ON jobs (created_at) WHERE status = 'pending';
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
SELECT id FROM users WHERE LOWER(email) = 'asha@example.com';

-- MySQL 8.0.13+
CREATE INDEX idx_users_email_lower ON users ((LOWER(email)));

48. Why is SELECT COUNT(*) slow on a large InnoDB or PostgreSQL table, and what can you do?

Under MVCC, different transactions can see different numbers of rows, so neither engine keeps one exact row counter the way MyISAM did. COUNT(*) has to scan an index or the table and check which rows are visible, which takes seconds on hundreds of millions of rows. Options: show an estimate from statistics, using TABLE_ROWS in information_schema for InnoDB or reltuples in pg_class for PostgreSQL; maintain a counter table updated in the same transaction or by a periodic job; count only with a selective indexed filter; or avoid exact totals in the UI, for example showing '10,000+ results'.

-- PostgreSQL: fast estimate from planner statistics
SELECT reltuples::bigint AS approx_rows FROM pg_class WHERE relname = 'orders';

-- MySQL: estimate from InnoDB statistics
SELECT table_rows FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'orders';

49. How do you add an index to a 50-million-row table in production without downtime?

In PostgreSQL, use CREATE INDEX CONCURRENTLY. It builds the index without blocking inserts, updates or deletes, at the cost of a slower build with two table scans. It cannot run inside a transaction block, and if it fails it leaves an INVALID index that you must drop before retrying. In MySQL 8, InnoDB builds secondary indexes online, and adding ALGORITHM=INPLACE, LOCK=NONE makes the statement fail rather than quietly take a blocking lock. Tools such as gh-ost or pt-online-schema-change handle bigger changes. Either way, watch disk space, replication lag and long transactions that block the brief metadata lock.

-- PostgreSQL: cannot run inside BEGIN ... COMMIT
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);

-- MySQL 8: online build that errors out instead of locking writes
ALTER TABLE orders ADD INDEX idx_orders_customer (customer_id),
  ALGORITHM=INPLACE, LOCK=NONE;

50. Write a query to find the median salary without a built-in median function.

Number the rows in salary order with ROW_NUMBER and get the total with COUNT(*) OVER (). The median sits in the middle positions: for an odd count n it is the single row at (n + 1) / 2, and for an even count it is the average of the rows at n / 2 and n / 2 + 1. Taking FLOOR and CEIL of (n + 1) / 2 selects the right rows in both cases with one condition. Exclude NULL salaries first. PostgreSQL also offers PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary), but MySQL has no median function, so this window version is the portable answer.

SELECT AVG(salary) AS median_salary
FROM (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS cnt
  FROM employees
  WHERE salary IS NOT NULL
) t
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0));

51. Write a query that returns the Nth highest distinct salary, or NULL when fewer than N distinct salaries exist.

Wrap the lookup in a scalar subquery. SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1 on its own returns an empty result when the value does not exist, which breaks callers that expect exactly one row. As a scalar subquery, an empty result becomes NULL, so the outer SELECT always returns one row. DISTINCT makes ties count once, and excluding NULLs matters because PostgreSQL sorts NULLs first in descending order. In a MySQL stored function, LIMIT cannot take an expression such as N-1, so compute it into a variable first.

SELECT (
  SELECT DISTINCT salary
  FROM employees
  WHERE salary IS NOT NULL
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2          -- N - 1, here N = 3
) AS third_highest_salary;

52. When does table partitioning help, and what are its pitfalls?

Partitioning splits one logical table into physical pieces by a key, usually a date range. It helps when queries filter on that key, because the planner prunes partitions and scans only, say, the current month, and when old data must go: dropping a monthly partition is instant compared with deleting millions of rows. Queries that ignore the partition key gain nothing and must touch every partition. Pitfalls: in MySQL every unique key, including the primary key, must contain the partitioning columns, and partitioned InnoDB tables cannot have foreign keys; PostgreSQL unique constraints must also include the partition key.

-- MySQL: the primary key must include the partitioning column
CREATE TABLE attendance_log (
  id BIGINT NOT NULL AUTO_INCREMENT,
  emp_id BIGINT NOT NULL,
  punched_at DATETIME NOT NULL,
  PRIMARY KEY (id, punched_at)
)
PARTITION BY RANGE COLUMNS (punched_at) (
  PARTITION p2026_10 VALUES LESS THAN ('2026-11-01'),
  PARTITION p2026_11 VALUES LESS THAN ('2026-12-01'),
  PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

53. What is the difference between a view and a materialized view, and when would you use each?

A view is a saved query: every select from it runs the underlying query, so it simplifies access and can hide columns, but it does not make anything faster. A materialized view stores the query's result like a table, so reads are fast but the data is only as fresh as the last refresh. PostgreSQL supports them with REFRESH MATERIALIZED VIEW, and the CONCURRENTLY option avoids blocking readers but requires a unique index on the view. MySQL has no materialized views, so teams maintain a summary table refreshed by a scheduled job. Use them for dashboards and reports, not real-time data.

-- PostgreSQL
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT order_date, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
GROUP BY order_date;

CREATE UNIQUE INDEX ON daily_revenue (order_date);
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

54. Explain optimistic and pessimistic locking. When would you choose each?

Pessimistic locking takes a lock before changing data, typically with SELECT ... FOR UPDATE, so other writers wait; it suits short, high-contention operations such as a wallet debit. Optimistic locking takes no lock. Each row carries a version number, and the update succeeds only if the version is unchanged since it was read. If the update affects zero rows, someone else changed the record first, so the application reloads and retries or shows a conflict to the user. It suits low-contention edits that span user think time, such as a form left open for minutes, where holding a database lock would be unacceptable.

-- Earlier read: SELECT id, title, version FROM job_posts WHERE id = 10;  -- version = 4
UPDATE job_posts
SET title = 'Senior Python Developer', version = version + 1
WHERE id = 10 AND version = 4;
-- 0 rows affected: another user saved first, so reload and retry

55. How would you design tables for analytics differently from an OLTP schema?

OLTP schemas are normalised for fast, consistent small writes. Analytics favours a star schema: a central fact table holds measurable events, such as one row per order line with quantity and amount, plus foreign keys to denormalised dimension tables for date, product, customer and city. Queries filter on dimensions and aggregate facts with few, predictable joins. Fix the grain, meaning what one fact row represents, before anything else. Track dimension history with slowly changing dimensions, for example type 2 rows with valid_from and valid_to. Large analytical workloads usually move to a columnar warehouse rather than the OLTP database.

56. With soft deletes, how do you keep email unique among active users only?

A plain UNIQUE(email) stops a user from registering again after their old row was soft-deleted. In PostgreSQL, create a partial unique index on email WHERE deleted_at IS NULL, so only live rows must be unique. MySQL has no partial indexes, but a unique index allows many NULLs, so add a generated column that holds the email for active rows and NULL for deleted ones, and put the unique index on that column. Both keep the rule in the database, which is safer than an application check that two concurrent sign-ups can pass at the same moment.

-- PostgreSQL
CREATE UNIQUE INDEX uq_users_active_email ON users (email) WHERE deleted_at IS NULL;

-- MySQL 8
ALTER TABLE users
  ADD COLUMN active_email VARCHAR(255)
    GENERATED ALWAYS AS (IF(deleted_at IS NULL, email, NULL)) VIRTUAL,
  ADD UNIQUE INDEX uq_users_active_email (active_email);

57. How do NULLs behave in GROUP BY, ORDER BY, aggregates and unique constraints?

GROUP BY and DISTINCT put all NULLs into one group, even though NULL = NULL is not true. Aggregates skip NULLs: AVG(bonus) divides by the number of non-NULL bonuses, not by all rows, and SUM over only NULLs returns NULL rather than 0, so wrap it in COALESCE. Sorting differs: MySQL puts NULLs first in ascending order, while PostgreSQL puts them last and supports NULLS FIRST and NULLS LAST to control it. Unique constraints allow many NULLs in both databases; PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to change that.

SELECT dept_id,
       COUNT(*) AS people,
       COUNT(bonus) AS with_bonus,
       AVG(bonus) AS avg_of_non_null,
       COALESCE(SUM(bonus), 0) AS total_bonus
FROM employees
GROUP BY dept_id;   -- employees with a NULL dept_id form one group

58. How do you write an upsert safely in MySQL and PostgreSQL?

Avoid 'SELECT, and if nothing is found, INSERT' in application code: two concurrent requests can both find nothing and both insert, causing duplicates or a unique-key error. Let a unique constraint decide atomically instead. PostgreSQL uses INSERT ... ON CONFLICT (key) DO UPDATE, where EXCLUDED refers to the row you tried to insert, or DO NOTHING. MySQL uses INSERT ... ON DUPLICATE KEY UPDATE; version 8.0.19 added a row alias such as AS new, and using VALUES() in that clause is deprecated. MySQL's form reacts to a conflict on any unique key, not a chosen one, so tables with several unique keys need care.

-- PostgreSQL
INSERT INTO daily_stats (stat_date, page_views) VALUES ('2026-11-01', 1)
ON CONFLICT (stat_date)
DO UPDATE SET page_views = daily_stats.page_views + EXCLUDED.page_views;

-- MySQL 8.0.19+
INSERT INTO daily_stats (stat_date, page_views) VALUES ('2026-11-01', 1) AS new
ON DUPLICATE KEY UPDATE page_views = daily_stats.page_views + new.page_views;

59. How would you compute a rolling 7-day revenue total when some days have no orders?

A ROWS frame of 6 PRECEDING counts rows, not days, so missing days make it stretch beyond a week. Use a RANGE frame with an interval offset, which looks back by value: RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW covers the current day and the six calendar days before it, whatever rows exist. PostgreSQL 11+ and MySQL 8 support this, with MySQL writing INTERVAL 6 DAY. Aggregate to one row per day first. To display the empty days as well, join to a calendar built with generate_series in PostgreSQL or a recursive CTE in MySQL.

-- PostgreSQL
WITH daily AS (
  SELECT order_date, SUM(amount) AS revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date
                          RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily
ORDER BY order_date;

60. What is the most efficient way to get each customer's first order?

The portable answer is ROW_NUMBER partitioned by customer and ordered by order date, with id as a tiebreaker, keeping row 1. PostgreSQL also offers DISTINCT ON (customer_id) with a matching ORDER BY, which is concise and usually fast. With an index on (customer_id, order_date, id), both can read rows in index order. When there are few customers but many orders each, a LATERAL join with LIMIT 1 per customer can be fastest, because it seeks once per customer instead of reading every order. Confirm the winner with EXPLAIN ANALYZE on realistic data volumes.

-- Portable: MySQL 8+ and PostgreSQL
SELECT customer_id, id AS order_id, order_date
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id
                                 ORDER BY order_date, id) AS rn
  FROM orders o
) t
WHERE rn = 1;

-- PostgreSQL only
SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, order_date
FROM orders
ORDER BY customer_id, order_date, id;