Advanced SQL Interview Questions

An index is a separate sorted structure, usually a B-tree, that maps column values to rows, so the database finds matches in O(log n) steps instead of reading every row.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT name FROM users WHERE email = 'asha@example.com';

The plan says SEARCH users USING INDEX idx_users_email (email=?) instead of SCAN users. The cost: every write updates each index, and an index on a column with few distinct values rarely helps. More in indexes.

A clustered index is the table itself, stored in key order, so there is only one. A non-clustered index is a separate structure of keys and row pointers; reading other columns costs an extra lookup unless the index covers the query.

SQL Server clusters on the primary key by default. MySQL InnoDB always does, and its secondary indexes store the primary key as the pointer, so a wide primary key bloats every index. PostgreSQL tables are heaps with no clustered index.

An index on (last_name, first_name) is sorted by last_name, then by first_name within each last name, like a phone book. It serves filters on the leading column but usually not on first_name alone: the leftmost prefix rule.

CREATE TABLE people (id INTEGER PRIMARY KEY, last_name TEXT, first_name TEXT, city TEXT);
CREATE INDEX idx_name ON people(last_name, first_name);
EXPLAIN QUERY PLAN SELECT * FROM people WHERE first_name = 'Ravi';

The plan is SCAN people. Put equality columns first, then the range or sort column: WHERE customer_id = ? ORDER BY created_at wants (customer_id, created_at).

Usually because the condition, as written, cannot use it:

1. A function on the column: lower(email) = ..., YEAR(created_at) = 2026.
2. A leading wildcard: LIKE '%kumar'.
3. An implicit type conversion, such as a text column compared with a number.
4. A filter that skips the leftmost column of a composite index.
5. Low selectivity or stale statistics, where a scan really is cheaper.

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, name TEXT);
CREATE INDEX idx_users_email ON users(email);
EXPLAIN QUERY PLAN SELECT name FROM users WHERE lower(email) = 'asha@example.com';

The plan is SCAN users. Rewrite the condition on the bare column, or add an expression index on lower(email). Confirm with EXPLAIN QUERY PLAN.

Atomicity (all statements succeed or none do), Consistency (constraints hold before and after), Isolation (concurrent transactions do not see each other's unfinished work) and Durability (a committed change survives a crash).

CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER);
INSERT INTO accounts VALUES (1, 'Asha', 5000), (2, 'Ravi', 1000);
BEGIN;
UPDATE accounts SET balance = balance - 3000 WHERE id = 1;
UPDATE accounts SET balance = balance + 3000 WHERE id = 2;
ROLLBACK;
SELECT name, balance FROM accounts;

The rollback undoes both updates, so the balances stay 5000 and 1000: atomicity. Durability comes from a log written to disk before the commit returns, such as PostgreSQL's WAL. See transactions.

The SQL standard defines four levels, each preventing more anomalies at the cost of more locking or more retries.

The default is Read committed in PostgreSQL, SQL Server and Oracle, and Repeatable read in MySQL InnoDB. PostgreSQL's Repeatable read is snapshot isolation and shows no phantoms; see the PostgreSQL isolation docs. Serializable transactions can fail with an error the application must retry.

Two transactions each hold a lock the other needs. The database detects the cycle, rolls one back with an error, and lets the other finish.

-- Session 1                           -- Session 2
BEGIN;                                 BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
                                       UPDATE accounts SET balance = balance - 50 WHERE id = 2;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- waits for session 2
                                       UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- deadlock

Prevent most by locking rows in the same order everywhere, keeping transactions short, and indexing UPDATE and DELETE conditions so fewer rows get locked. Some cannot be designed away, so retry on the deadlock error.

Read the plan first, then fix the biggest cost:

1. EXPLAIN ANALYZE: look for full scans of big tables and estimates far from actual rows.
2. Index the WHERE, JOIN and ORDER BY columns; a covering index skips table lookups.
3. Keep conditions index-friendly: no functions on indexed columns, no leading %.
4. Read less: no SELECT *, aggregate before joining.
5. Replace correlated subqueries with window functions and big OFFSET with keyset pagination.
6. Refresh statistics with ANALYZE.

Then measure again on the same data.

OFFSET 100000 still reads and discards 100000 rows, so later pages get slower. Keyset pagination starts after the previous page's last key with an indexed WHERE, so every page costs the same.

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 100)
INSERT INTO posts SELECT i, 'Post ' || i FROM n;
-- the previous page ended at id 40
SELECT id, title FROM posts WHERE id > 40 ORDER BY id LIMIT 3;

The next page starts at 41. For a non-unique sort column, add the id as a tiebreaker: WHERE (created_at, id) < (?, ?). The trade-off is that you cannot jump straight to page 50.

Normalization splits data into tables so each fact is stored once, preventing update, insert and delete anomalies.

Follow-up: when do you denormalize? In read-heavy and analytics systems, a stored copy like posts.comment_count saves joins, at the cost of keeping it in sync.

Use the upsert syntax, which detects the conflict through a primary key or unique constraint: INSERT ... ON CONFLICT ... DO UPDATE in SQLite and PostgreSQL, ON DUPLICATE KEY UPDATE in MySQL.

CREATE TABLE page_views (page TEXT PRIMARY KEY, views INTEGER);
INSERT INTO page_views VALUES ('/home', 1);
INSERT INTO page_views VALUES ('/home', 1)
  ON CONFLICT (page) DO UPDATE SET views = page_views.views + excluded.views;
INSERT INTO page_views VALUES ('/about', 1)
  ON CONFLICT (page) DO UPDATE SET views = page_views.views + excluded.views;
SELECT page, views FROM page_views ORDER BY page;

/home becomes 2 and /about is inserted; excluded is the row you tried to insert. Select-then-insert is wrong under concurrency: two sessions both see no row and both insert. See upsert.

SQL injection is user input pasted into the SQL text that changes what the query does. Prevent it with parameterized queries: the SQL and the values travel separately, so a value can never become code.

# Unsafe: the input becomes part of the SQL
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
# email = "x' OR '1'='1" returns every user

# Safe: the driver sends the value as data
cur.execute("SELECT * FROM users WHERE email = ?", (email,))

Parameters cannot replace table or column names; check those against an allow list. More in preventing SQL injection.

A trigger is code the database runs automatically before or after an INSERT, UPDATE or DELETE. A good use is an audit log no application can forget to write.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
CREATE TABLE salary_log (emp_id INTEGER, old_salary INTEGER, new_salary INTEGER);
CREATE TRIGGER log_salary_change
AFTER UPDATE OF salary ON employees
BEGIN
  INSERT INTO salary_log VALUES (OLD.id, OLD.salary, NEW.salary);
END;
INSERT INTO employees VALUES (1, 'Asha', 90000);
UPDATE employees SET salary = 99000 WHERE id = 1;
SELECT * FROM salary_log;

The log row is 1 | 90000 | 99000. Avoid triggers for business logic: they are invisible in application code and slow every write. SQL Server triggers fire once per statement, not per row.

A function returns a value and can be used inside a query (SELECT tax(amount) FROM orders). A stored procedure is called on its own with CALL or EXEC, can return result sets, and is meant for multi-step work that changes data.

SQL Server functions cannot change data, while PostgreSQL functions can. Heavy procedure logic is harder to test and deploy than application code and ties you to one database.

A cursor walks a query result one row at a time inside a stored procedure. People avoid it because a row-by-row loop is far slower than one set-based statement.

-- SQL Server (T-SQL)
DECLARE @name VARCHAR(50);
DECLARE emp_cursor CURSOR FOR SELECT name FROM employees;
OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @name;
  FETCH NEXT FROM emp_cursor INTO @name;
END;
CLOSE emp_cursor;
DEALLOCATE emp_cursor;

The lifecycle is declare, open, fetch in a loop, close, deallocate. Most cursor loops can become one UPDATE with a join or a CASE.