Advanced DBMS Interview Questions

It prints 3 | 4. Without AUTOINCREMENT, SQLite assigns max(rowid) + 1, so id 3 is reused; with it, SQLite records the largest id ever used in sqlite_sequence (SQLite AUTOINCREMENT docs). In general, ids are unique but not gapless: sequences and AUTO_INCREMENT leave gaps on rollback, and before MySQL 8.0 InnoDB could reuse a deleted top id after a restart.

It prints 1 | 500. The retry hits the unique idempotency_key, and ON CONFLICT DO NOTHING makes it a no-op, so the payment is stored once however often the client retries. The constraint is what guarantees it; a check-then-insert in the app races. See upsert.

OFFSET 100000 reads and discards 100,000 rows first, so deep pages get slower. Keyset pagination continues after the previous page's last row with an indexed WHERE, so every page costs the same:

CREATE TABLE posts (id INTEGER PRIMARY KEY, created_at TEXT, title TEXT);
CREATE INDEX idx_posts_created ON posts(created_at, id);
INSERT INTO posts (created_at, title) VALUES
 ('2026-10-01', 'a'), ('2026-10-02', 'b'), ('2026-10-02', 'c'),
 ('2026-10-03', 'd'), ('2026-10-04', 'e');

-- the previous page ended at ('2026-10-02', 3); fetch the next 2
SELECT id, created_at, title FROM posts
WHERE (created_at, id) > ('2026-10-02', 3)
ORDER BY created_at, id
LIMIT 2;

Order by a unique combination (created_at, id), or rows with equal timestamps get skipped or repeated. You give up jumping straight to page 50.

Nested loop, hash join and sort-merge, chosen from row estimates, indexes and memory. A nested loop looks up inner matches for each outer row, ideal for a small outer side and an indexed join column. A hash join builds a hash table on the smaller input and probes it, O(n + m), for large equality joins without an index (MySQL only since 8.0.18, see the MySQL hash join docs). A sort-merge join walks two inputs sorted on the key, best when they arrive sorted. A wrong pick usually comes from a bad row estimate.

Measure, read the plan, fix the expensive step. Find the query with the slow query log or pg_stat_statements, ranked by total time. In EXPLAIN ANALYZE, look for full scans of big tables, sorts spilling to disk, and estimates far from actual rows. Usual causes: a missing index, a function on an indexed column, stale statistics, N+1 queries or lock waits.

CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
CREATE INDEX idx_orders_customer ON orders(customer_id);
EXPLAIN QUERY PLAN
SELECT c.city, sum(o.total)
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE c.city = 'Pune'
GROUP BY c.city;

SCAN c reads every customer to filter by city, then SEARCH o uses the index; an index on customers(city) removes the scan. See EXPLAIN QUERY PLAN.

N+1 is one query for a list plus one per item for related data: 1 query for 50 orders, then 50 for their customers. ORM lazy loading in a loop usually causes it. Fetch the related rows in one query with a join or IN:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO customers VALUES (1, 'Asha'), (2, 'Ravi');
INSERT INTO orders VALUES (10, 1, 500), (11, 2, 300), (12, 1, 200);

-- one query instead of 1 + N
SELECT o.id, o.total, c.name
FROM orders o JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

ORMs have it built in: Django select_related, JPA JOIN FETCH, Rails includes. A query count that grows with page size gives it away.

SQL injection is user input pasted into SQL text, where it can change the statement. Parameterized queries fix it: the SQL and the values travel separately, so a value never becomes code.

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE users (name TEXT, password TEXT)")
db.executemany("INSERT INTO users VALUES (?, ?)", [("asha", "s3cret"), ("ravi", "hunter2")])

name = "nobody"
password = "' OR '1'='1"

unsafe = f"SELECT name FROM users WHERE name = '{name}' AND password = '{password}'"
print("string building:", db.execute(unsafe).fetchall())

safe = "SELECT name FROM users WHERE name = ? AND password = ?"
print("parameters:     ", db.execute(safe, (name, password)).fetchall())

The built string ends password = '' OR '1'='1', and since AND binds tighter than OR, every user matches; the parameterized query matches nothing. Table and column names cannot be parameters, so check them against an allow list. See preventing SQL injection.

A stored procedure is named code you call (CALL, EXEC) that can change data and manage transactions; a function returns a value inside queries; a trigger runs automatically on INSERT, UPDATE or DELETE. SQLite has only triggers:

CREATE TABLE salaries (emp TEXT PRIMARY KEY, amount INTEGER);
CREATE TABLE salary_audit (emp TEXT, old_amount INTEGER, new_amount INTEGER);
CREATE TRIGGER log_salary_change AFTER UPDATE OF amount ON salaries
BEGIN
    INSERT INTO salary_audit VALUES (OLD.emp, OLD.amount, NEW.amount);
END;
INSERT INTO salaries VALUES ('Asha', 50000);
UPDATE salaries SET amount = 55000 WHERE emp = 'Asha';
SELECT emp, old_amount, new_amount FROM salary_audit;

Keep triggers small, like audit logs, since logic outside the application code is easy to miss when debugging. See triggers.

Replication lag is the delay before a primary's commit shows on a replica. With asynchronous replication, the MySQL and PostgreSQL default, a user who saves a profile and reads from a replica may see the old one. Fixes: send a user's reads to the primary briefly after they write, pin a user to one replica so data never goes backwards, or read only from a replica past the write's log position (LSN, GTID).

Expand and contract: each deploy is backward compatible, so old and new code both work. To rename fullname to display_name: add the new column, write both, backfill in small batches, switch reads, stop writing the old column, then drop it in a later deploy. Traps: some ALTER TABLE forms rewrite or lock the table, so check your engine version or use gh-ost; in PostgreSQL, build indexes with CREATE INDEX CONCURRENTLY and set a short lock_timeout so a blocked ALTER fails fast instead of queuing every query behind it.

Auto-increment integers are 4 or 8 bytes and insert at the end of the index, the fastest choice on one database; UUIDs can be generated anywhere and do not leak row counts. Random UUIDv4 keys hurt clustered indexes (InnoDB, SQL Server): inserts land on random pages, causing splits and cache misses. UUIDv7 (RFC 9562) puts a timestamp first, so inserts are nearly sequential, though it reveals creation time. A common compromise is a BIGINT primary key plus a random public id.