DBMS Interview Questions

This is the revision lesson for the DBMS track. It collects the questions most commonly asked in campus placements and fresher interviews at product and service companies, grouped by topic, each with a crisp answer you can say in under a minute. After the theory come 10 SQL queries that appear again and again in interviews and online tests, with solutions tested in SQLite, and 6 numerical problems (attribute closure, candidate keys, normal forms, serializability, B+ tree order, join cost) solved step by step. A revision checklist closes the lesson. Each section links back to the full lesson, so when an answer feels shaky, go back and read the details.

How to use it: cover the answer, say yours out loud, then compare. Interviewers care less about textbook wording and more about whether you can explain with an example and handle the follow-up question.

Basics and architecture

Full lesson: DBMS introduction.

Q1. What is a DBMS, and why not just use files?

A database management system is software that stores, retrieves and manages data while enforcing integrity, security, concurrency and recovery. With plain files, every application must handle its own formats, duplicate data drifts out of sync, concurrent writers corrupt data, there is no crash recovery, and every new question needs a new program. A DBMS solves these centrally and adds a query language.

Q2. What is the difference between a DBMS and an RDBMS?

An RDBMS is a DBMS based on the relational model: data lives in tables (relations) of rows and columns, relationships are expressed through keys, and it is queried with SQL. A generic DBMS may use other models (hierarchical, network, document, key-value). PostgreSQL, MySQL and Oracle are RDBMSs; MongoDB is a document DBMS.

Q3. Explain the three-schema architecture.

The internal (physical) level describes how data is stored: files, pages, indexes. The conceptual (logical) level describes what data exists and how it relates: tables, columns, constraints. The external (view) level describes what each user or application sees. The mappings between levels give data independence.

Q4. What is data independence?

Physical data independence means you can change storage details, such as adding an index or moving files, without changing the logical schema or applications. Logical data independence means you can change the logical schema, such as adding a column, without breaking external views and applications. Physical independence is easier to achieve.

Q5. What are DDL, DML, DCL and TCL?

DDL (data definition language) defines structure: CREATE, ALTER, DROP, TRUNCATE. DML (data manipulation language) works with data: SELECT, INSERT, UPDATE, DELETE. DCL (data control language) manages permissions: GRANT, REVOKE. TCL (transaction control language) manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

Q6. What is metadata, and what is the data dictionary?

Metadata is data about data: table names, column types, constraints, indexes, users and permissions. The DBMS stores it in the data dictionary or system catalog, which the parser and optimiser consult for every query. In PostgreSQL it lives in pg_catalog; the SQL-standard view of it is information_schema.

Q7. What is the difference between OLTP and OLAP?

OLTP (online transaction processing) systems handle many short reads and writes of a few rows, such as placing orders, and are optimised for concurrency and low latency with normalised schemas. OLAP (online analytical processing) systems run fewer, heavy queries scanning large amounts of data for reports, and are optimised for scans and aggregation, often with columnar storage and denormalised star schemas.

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

DELETE removes selected rows (with a WHERE clause), is logged row by row, fires triggers and can be rolled back. TRUNCATE removes all rows quickly by deallocating pages, usually does not fire row triggers, and resets identity counters in many databases; it is transactional in PostgreSQL but causes an implicit commit in MySQL. DROP removes the table itself, including its structure and indexes.

ER model, relational model and keys

Full lessons: ER model and relational model and keys.

Q9. What are entities, attributes and relationships?

An entity is a real-world object or concept with independent existence, such as a student. Attributes describe it: simple or composite, single or multi-valued, stored or derived. A relationship associates entities, such as a student enrolls in a course, and has a cardinality (one-to-one, one-to-many, many-to-many).

Q10. What is a weak entity?

An entity that cannot be uniquely identified by its own attributes and depends on an owner (identifying) entity, such as an order line that only makes sense within an order. Its key is the owner's key plus a partial key (discriminator), such as (order_id, line_no). In ER diagrams it is drawn with a double rectangle.

Q11. Explain participation constraints and cardinality ratios.

Cardinality ratio is the maximum number of relationship instances an entity can take part in: 1:1, 1:N or M:N. Participation is the minimum: total participation means every entity must take part (each order must belong to a customer), partial means it may not. Together they are often written as (min, max) pairs.

Q12. How do you convert an M:N relationship to tables?

Create a separate junction table containing the primary keys of both entities as foreign keys, plus any attributes of the relationship. Its primary key is usually the combination of the two foreign keys. For example, enrollments (student_id, course_id, grade).

Q13. Define super key, candidate key and primary key.

A super key is any set of attributes that uniquely identifies a row. A candidate key is a minimal super key: remove any attribute and it stops being unique. The primary key is the candidate key chosen to identify rows; it cannot be NULL. The remaining candidate keys are alternate keys, usually enforced with UNIQUE.

Q14. What is a foreign key, and what does ON DELETE CASCADE do?

A foreign key is a column (or set) that must match a primary or unique key value in another table, or be NULL, which enforces referential integrity. ON DELETE CASCADE means deleting the parent row automatically deletes the child rows. Other options are RESTRICT / NO ACTION (block the delete), SET NULL and SET DEFAULT.

Q15. What is the difference between a natural key and a surrogate key?

A natural key comes from the data itself, such as an email or PAN number. A surrogate key is an artificial identifier with no business meaning, such as an auto-increment integer or a UUID. Surrogate keys never change and keep foreign keys small; natural keys should still be enforced with unique constraints.

Q16. What are entity integrity and referential integrity?

Entity integrity says a primary key value must be unique and not NULL, so every row is identifiable. Referential integrity says every foreign key value must match an existing referenced key or be NULL. The DBMS enforces both with primary key and foreign key constraints.

SQL

Full lessons: SQL fundamentals and advanced SQL.

Q17. What is the logical order of execution of a SELECT statement?

FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, and finally LIMIT/OFFSET. This explains why you cannot use a SELECT alias in WHERE in standard SQL, and why aggregates are filtered in HAVING, not WHERE.

Q18. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates, such as HAVING COUNT(*) > 5. Put non-aggregate conditions in WHERE so fewer rows are grouped.

Q19. Explain the types of joins.

An inner join returns rows with matches in both tables. A left (outer) join returns all rows from the left table, with NULLs where the right has no match; a right join is the mirror; a full outer join returns all rows from both. A cross join returns the Cartesian product, and a self join joins a table to itself, such as employees to their managers.

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

UNION combines result sets and removes duplicates, which requires a sort or hash step. UNION ALL keeps all rows, including duplicates, and is faster. Use UNION ALL when duplicates are impossible or wanted.

Q21. How do NULLs behave in SQL?

NULL means unknown, so any comparison with NULL yields unknown, not true: x = NULL never matches, and you must use IS NULL. Aggregates such as SUM and AVG ignore NULLs, while COUNT(*) counts rows and COUNT(col) counts non-NULL values. NOT IN with a subquery that returns a NULL yields no rows, so prefer NOT EXISTS.

Q22. What is the difference between a correlated and a non-correlated subquery?

A non-correlated subquery runs independently of the outer query and can be evaluated once. A correlated subquery references columns of the outer query, so logically it is evaluated once per outer row, such as finding employees who earn more than their department's average. Optimisers often rewrite correlated subqueries into joins.

Q23. What are window functions?

Functions that compute a value for each row over a related set of rows (a window) without collapsing them like GROUP BY does. Examples are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running SUM(...) OVER (PARTITION BY ... ORDER BY ...). They are the standard tool for top-N per group and running totals.

Q24. Explain RANK, DENSE_RANK and ROW_NUMBER.

For salaries 300, 200, 200, 100: ROW_NUMBER gives 1, 2, 3, 4 (unique, ties broken arbitrarily unless you add a tie-breaker); RANK gives 1, 2, 2, 4 (gaps after ties); DENSE_RANK gives 1, 2, 2, 3 (no gaps). Use DENSE_RANK for "Nth highest distinct salary".

Q25. What is a view? What is a materialized view?

A view is a stored query that behaves like a virtual table; it stores no data and runs its query when used. It simplifies queries and restricts access. A materialized view stores the query's result physically and must be refreshed; it speeds up expensive reports at the cost of staleness. PostgreSQL supports CREATE MATERIALIZED VIEW; MySQL does not.

Q26. What are stored procedures and triggers?

A stored procedure is named code stored in the database and called explicitly, used to bundle logic close to the data. A trigger runs automatically before or after an INSERT, UPDATE or DELETE on a table, used for auditing or derived values. Triggers are invisible to callers, so overusing them makes behaviour hard to follow.

Normalization

Full lesson: normalization.

Q27. What is normalization and why is it needed?

Normalization organises tables so each fact is stored once, by decomposing them based on functional dependencies. It removes redundancy and the update, insertion and deletion anomalies that come with it. The usual goal for OLTP schemas is 3NF or BCNF.

Q28. What are insertion, update and deletion anomalies?

In a table (student_id, course_id, instructor, instructor_phone): an update anomaly is having to change an instructor's phone in many rows (and missing one); an insertion anomaly is being unable to record a new instructor until a student enrolls; a deletion anomaly is losing the instructor's phone when the last student drops the course.

Q29. What is a functional dependency?

X → Y means that any two rows with the same values for X must have the same values for Y; X determines Y. For example, student_id → name. Functional dependencies come from the meaning of the data, not from the rows currently in the table.

Q30. Define 1NF, 2NF and 3NF.

1NF: every attribute holds atomic values; no repeating groups or lists in a cell. 2NF: 1NF and no non-prime attribute depends on only part of a candidate key (no partial dependency). 3NF: 2NF and no non-prime attribute depends transitively on a key; formally, for every X → A, either X is a super key or A is a prime attribute.

Q31. What is BCNF, and how does it differ from 3NF?

BCNF requires that for every non-trivial dependency X → Y, X is a super key. 3NF additionally allows X → A when A is prime (part of some candidate key). So BCNF is stricter; a relation like R(A, B, C) with AB → C and C → B is in 3NF but not BCNF.

Q32. What is a lossless-join decomposition?

A decomposition of R into R1 and R2 is lossless if joining them back gives exactly R, with no spurious rows. The test: the common attributes must be a super key of R1 or of R2, that is, (R1 ∩ R2) → R1 or (R1 ∩ R2) → R2.

Q33. What is dependency preservation, and can you always get it with BCNF?

A decomposition preserves dependencies if every original functional dependency can be checked within a single decomposed table, without joins. A lossless and dependency-preserving decomposition into 3NF always exists; into BCNF it does not always exist. That is the main reason designers sometimes stop at 3NF.

Q34. What is 4NF, and when is denormalization appropriate?

4NF removes non-trivial multivalued dependencies, such as storing a person's independent skills and languages in one table, which forces every combination to be stored. Denormalization deliberately adds redundancy for read performance, such as a stored order total or a like counter, and is appropriate when a read path is hot and you have a reliable way (same transaction, trigger) to keep the copy correct.

Indexing and B+ trees

Full lesson: indexing and B-trees.

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

An index is a separate data structure, usually a B+ tree, that maps column values to row locations so lookups avoid scanning the whole table. It speeds up reads for selective conditions, joins and sorting, but uses space and slows every insert, update and delete, since each index must be maintained.

Q36. Clustered versus non-clustered index?

A clustered index determines the physical order of rows; the table data itself is stored in index order, so there can be only one. A non-clustered (secondary) index is a separate structure pointing to the rows. In InnoDB, the primary key is the clustered index and secondary indexes store the primary key; PostgreSQL tables are heaps and all indexes are secondary.

Q37. Why do databases use B+ trees instead of binary search trees or B trees?

A B+ tree node is a whole disk page with hundreds of keys, so the tree is very shallow (three or four levels for millions of rows), which minimises disk reads. Compared with B trees, all data pointers are in the leaves and leaves are linked, so range scans are a sequential walk, and internal nodes hold only keys, increasing fan-out further.

Q38. Why are hash indexes not used for range queries?

A hash function scatters adjacent keys to unrelated buckets, so there is no order to scan. Hash indexes support only equality lookups. B+ trees support equality, ranges, prefix matches and ordered output.

Q39. What is a composite index, and what is the leftmost-prefix rule?

A composite index covers several columns in order, such as (last_name, first_name). It can be used for queries that filter on a leftmost prefix of its columns, last_name alone or both, but generally not on first_name alone. Put equality-filtered columns first and the range or sort column last.

Q40. What is a covering index?

An index that contains every column a query needs, so the query can be answered from the index alone without visiting the table (an index-only scan). It avoids random reads into the table. PostgreSQL and SQL Server support INCLUDE columns for this purpose.

Q41. When will the database not use an index?

When the condition matches a large fraction of rows, so a sequential scan is cheaper; when a function or type cast wraps the column; when a LIKE pattern starts with a wildcard; when the query does not use the composite index's leading column; or when statistics are stale and the optimiser misjudges selectivity.

Q42. What is the difference between dense and sparse indexes?

A dense index has an entry for every search-key value (or every record). A sparse index has entries for only some values, typically one per data block, and works only when the data file is sorted on the key. Sparse indexes are smaller; dense indexes can answer "does this value exist" without reading the data.

Transactions

Full lesson: transactions and ACID.

Q43. What is a transaction? Explain ACID.

A transaction is a group of operations executed as one unit. Atomicity: all or nothing, via undo logs. Consistency: integrity rules hold before and after, via constraints plus correct logic. Isolation: concurrent transactions behave as if run one at a time, via locking or MVCC. Durability: committed changes survive crashes, via write-ahead logging flushed at commit.

Q44. What are the states of a transaction?

Active, partially committed (last statement done, commit not yet durable), committed, failed and aborted, ending in terminated. After an abort the system can restart the transaction (for transient errors such as deadlocks) or kill it (for logical errors).

Q45. What is a schedule, and what is a serializable schedule?

A schedule is the interleaved order of operations from concurrent transactions. It is serializable if its effect equals that of some serial schedule, where transactions run one after another. Serializability is the correctness criterion for concurrent execution.

Q46. What is conflict serializability, and how do you test it?

Two operations conflict if they are from different transactions, on the same item, and at least one is a write. A schedule is conflict serializable if it can be turned into a serial one by swapping non-conflicting adjacent operations. Test it by drawing a precedence graph with an edge Ti → Tj for each conflict where Ti's operation comes first; it is conflict serializable if and only if the graph has no cycle.

Q47. What is view serializability?

Two schedules are view equivalent if every transaction reads the same values (the same initial reads and reads-from) and the same transaction writes each item last. View serializability admits more schedules than conflict serializability, specifically some with blind writes, but testing it is NP-complete, so databases do not use it.

Q48. What are recoverable, cascadeless and strict schedules?

Recoverable: if Tj reads data written by Ti, Ti commits before Tj commits. Cascadeless: transactions read only committed data, so one abort never forces others to abort. Strict: transactions neither read nor overwrite uncommitted data, which makes undo simple. Strict is a subset of cascadeless, which is a subset of recoverable.

Q49. What is a savepoint?

A named marker inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work after it and keeps the transaction open; RELEASE SAVEPOINT removes the marker. It is used for partial error handling and nested transactions in ORMs.

Concurrency control

Full lesson: concurrency control.

Q50. What problems arise from concurrent transactions?

Lost updates (one write overwrites another based on stale data), dirty reads (reading uncommitted data), non-repeatable reads (a row changes between two reads), phantom reads (a repeated query returns new rows), and write skew (two transactions update different rows based on the same stale check and jointly break a rule).

Q51. What are the SQL isolation levels and what do they prevent?

Read Uncommitted allows dirty reads, non-repeatable reads and phantoms. Read Committed prevents dirty reads. Repeatable Read also prevents non-repeatable reads. Serializable prevents all three and behaves like some serial order. Real databases often prevent more than the standard minimum.

Q52. What are the default isolation levels in PostgreSQL and MySQL?

PostgreSQL defaults to Read Committed, and its Repeatable Read is snapshot isolation. MySQL InnoDB defaults to Repeatable Read, using a transaction snapshot for plain reads and next-key locks for locking reads and writes. PostgreSQL never allows dirty reads, even at Read Uncommitted.

Q53. What are shared and exclusive locks?

A shared (read) lock allows other shared locks but blocks exclusive ones, so many readers can proceed. An exclusive (write) lock blocks all other locks on the item. Intention locks (IS, IX, SIX) on tables let the database combine table-level and row-level locking efficiently.

Q54. What is two-phase locking? Does it prevent deadlocks?

Each transaction acquires locks in a growing phase and releases them in a shrinking phase, never acquiring after releasing; this guarantees conflict serializability. Strict 2PL also holds exclusive locks until commit to avoid cascading aborts. It does not prevent deadlocks; only conservative 2PL, which takes all locks up front, does.

Q55. How are deadlocks handled in databases?

By detection (find cycles in the wait-for graph and abort a victim), prevention with timestamps (wait-die: an older requester waits, a younger one aborts; wound-wait: an older requester aborts the younger holder, a younger requester waits), or timeouts. InnoDB detects deadlocks immediately; PostgreSQL checks after deadlock_timeout. Applications should retry the aborted transaction.

Q56. What is MVCC?

Multi-version concurrency control keeps several versions of each row so that readers see a consistent snapshot of committed data while writers create new versions; readers and writers do not block each other. PostgreSQL stores old versions in the table and cleans them with VACUUM; InnoDB keeps them in the undo log and purges them in the background.

Q57. Optimistic versus pessimistic concurrency control?

Pessimistic control locks data before using it, assuming conflicts are likely; for example SELECT ... FOR UPDATE. Optimistic control lets transactions proceed without locks and checks for conflicts at commit, retrying on conflict; in applications this is usually a version column checked in the UPDATE's WHERE clause. Use optimistic when conflicts are rare.

Recovery

Full lesson: recovery.

Q58. What is write-ahead logging?

Log records describing a change must reach stable storage before the changed page is written to disk, and all of a transaction's log records must be flushed before its commit is acknowledged. This makes undo of uncommitted changes and redo of committed changes possible after a crash, while commit costs only one sequential log flush.

Q59. What do steal and no-force mean, and why do they need undo and redo?

Steal allows dirty pages of uncommitted transactions to be written to disk, so recovery must undo them. No-force allows committing without writing the changed pages, so recovery must redo them. Most databases use steal and no-force for performance, so they need both.

Q60. What is a checkpoint?

A point recorded in the log from which recovery can start, so the whole log need not be scanned. A fuzzy checkpoint records the active transactions and dirty pages without stopping work. Transactions that committed before the checkpoint need no recovery action.

Q61. Describe ARIES briefly.

ARIES recovers in three phases. Analysis scans forward from the last checkpoint to find loser transactions and dirty pages. Redo repeats history from the oldest dirty page's recLSN, reapplying all changes not already on disk. Undo rolls back the losers newest-first, writing compensation log records so undo is never repeated after another crash.

Q62. Is replication a backup?

No. Replication copies every change, including accidental deletes and corrupting bugs, to replicas within milliseconds; it provides availability. Backups with point-in-time recovery (a base backup plus archived logs) let you restore to just before a mistake. You need both.

Query processing and optimization

Full lesson: query processing and optimization.

Q63. What happens when you run a SQL query?

The parser checks syntax and resolves names against the catalog, the rewriter expands views and simplifies, the optimiser picks the cheapest physical plan using statistics and a cost model, and the executor runs the plan as a pipeline of operators. Plans may be cached for prepared statements.

Q64. Name the join algorithms and when each is best.

Nested loop (with an index on the inner side) is best when the outer side is small, typical for OLTP lookups. Hash join is best for large equality joins without useful order. Sort-merge join is best when inputs are already sorted on the key or the output must be sorted. Plain nested loop handles any join condition.

Q65. What is the difference between heuristic and cost-based optimisation?

Heuristic optimisation applies rules that almost always help, such as pushing selections and projections down and turning Cartesian products into joins. Cost-based optimisation estimates the cost of alternative plans, including join orders and algorithms, using statistics such as row counts, distinct values and histograms, and picks the cheapest.

Q66. How do you find out why a query is slow?

Run EXPLAIN ANALYZE (PostgreSQL, MySQL 8.0.18+) to see the plan with actual times and row counts. Look for sequential scans with many rows removed by a filter, big differences between estimated and actual rows, sorts or hashes spilling to disk, and nested loops with huge loop counts. Then add the right index, rewrite the query, or refresh statistics, and measure again.

Q67. Why is OFFSET pagination slow, and what is the alternative?

OFFSET n makes the database produce and discard n rows, so deep pages get linearly slower and results shift when rows are inserted. Keyset (cursor) pagination filters by the last seen sort key, such as WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, which is an index range scan with constant cost.

NoSQL and distributed databases

Full lesson: NoSQL and distributed databases.

Q68. What are the main types of NoSQL databases?

Key-value stores (Redis) for lookups by key such as caches and sessions; document stores (MongoDB) for nested, flexible records; wide-column stores (Cassandra) for partitioned, write-heavy data such as time series; and graph databases (Neo4j) for relationship traversals such as social graphs and fraud detection.

Q69. Explain the CAP theorem.

In a distributed data store, when a network partition occurs you must choose between consistency (every read sees the latest write) and availability (every non-failed node responds). Partitions cannot be ruled out, so the real choice is CP or AP during a partition. PACELC adds that without a partition you trade latency against consistency.

Q70. What is the difference between ACID and BASE?

ACID systems guarantee atomic, isolated, durable transactions with strong consistency. BASE systems (basically available, soft state, eventually consistent) prioritise availability and scale, letting replicas temporarily disagree and converge later. Many modern systems offer tunable points between the two.

Q71. What is the difference between replication and sharding?

Replication stores copies of the same data on several nodes for availability and read scaling. Sharding (partitioning) splits different data across nodes so each holds a subset, for write scaling and storage capacity. Production systems usually shard and then replicate each shard.

Q72. How do you choose between SQL and NoSQL?

Choose relational by default when data is relational, needs transactions and constraints, or will be queried in varied ways, and it fits on one powerful server with replicas. Choose a NoSQL store for a specific pattern: key-value for caching, wide-column for massive partitioned writes, documents for flexible aggregates, graphs for deep traversals. Many systems use both.

10 SQL queries asked in interviews

All queries below were run in SQLite 3.51 (which supports window functions from 3.25) on this sample data. They also run in PostgreSQL and MySQL 8.0 unless noted.

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE employees (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  dept_id     INTEGER REFERENCES departments(id),
  manager_id  INTEGER REFERENCES employees(id),
  salary      INTEGER NOT NULL,
  email       TEXT    NOT NULL
);
INSERT INTO departments VALUES (1,'Engineering'),(2,'Sales'),(3,'HR'),(4,'Legal');
INSERT INTO employees VALUES
 (1,'Asha',  1,NULL,250000,'asha@x.com'),
 (2,'Ravi',  1,1,   180000,'ravi@x.com'),
 (3,'Meera', 1,1,   260000,'meera@x.com'),
 (4,'Kiran', 2,1,   120000,'kiran@x.com'),
 (5,'Sana',  2,4,   130000,'sana@x.com'),
 (6,'Arjun', 2,4,   120000,'arjun@x.com'),
 (7,'Divya', 3,1,    90000,'divya@x.com'),
 (8,'Ravi K',1,1,   180000,'ravi@x.com');

CREATE TABLE orders (
  id INTEGER PRIMARY KEY, customer TEXT NOT NULL,
  order_date TEXT NOT NULL, amount INTEGER NOT NULL
);
INSERT INTO orders VALUES
 (1,'asha','2026-10-01',500),(2,'ravi','2026-10-01',300),
 (3,'asha','2026-10-02',200),(4,'asha','2026-10-03',100),
 (5,'ravi','2026-10-05',700),(6,'meera','2026-10-05',50);

SQL 1. Second highest salary

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

The inner query finds the highest (260000); the outer finds the largest value below it. If only one distinct salary exists it returns NULL, which is usually the desired behaviour. Follow-up: generalise to N (next query).

SQL 2. Nth highest distinct salary (N = 3)

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 3;
-- 180000   (260000 is 1st, 250000 is 2nd, 180000 is 3rd)

DENSE_RANK gives tied salaries the same rank without gaps, which matches "Nth highest distinct salary". An alternative without window functions is ORDER BY salary DESC LIMIT 1 OFFSET N-1 over SELECT DISTINCT salary.

SQL 3. Highest-paid employee in each department

SELECT d.name AS department, e.name, e.salary
FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees e
) e
JOIN departments d ON d.id = e.dept_id
WHERE e.rnk = 1
ORDER BY d.name;
-- Engineering | Meera | 260000
-- HR          | Divya |  90000
-- Sales       | Sana  | 130000

RANK keeps all employees tied for the top. The classic pre-window version is WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id) (row-value IN works in PostgreSQL, MySQL and SQLite 3.15+).

SQL 4. Employees earning more than their manager

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;
-- Meera | 260000 | Asha  | 250000
-- Sana  | 130000 | Kiran | 120000

A self join: the same table plays the employee role (e) and the manager role (m). Asha has no manager, so the inner join drops her, which is correct here.

SQL 5. Find duplicate emails

SELECT email, COUNT(*) AS occurrences
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;
-- ravi@x.com | 2

HAVING filters groups after aggregation; WHERE COUNT(*) > 1 would be an error.

SQL 6. Departments with no employees

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
-- Legal

The left join keeps every department; departments without a match have NULL in every employee column. NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id) is equivalent and often clearer. Avoid NOT IN (SELECT dept_id FROM employees): if any dept_id were NULL, it would return nothing.

SQL 7. Top 2 earners per department

SELECT dept_id, name, salary
FROM (
  SELECT e.*,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC, id) AS rn
  FROM employees e
) ranked
WHERE rn <= 2
ORDER BY dept_id, rn;
-- 1 | Meera | 260000
-- 1 | Asha  | 250000
-- 2 | Sana  | 130000
-- 2 | Kiran | 120000
-- 3 | Divya |  90000

Kiran and Arjun tie at 120000; the id tie-breaker makes the result deterministic (Kiran has the lower id). Use DENSE_RANK() ... <= 2 instead if ties should all be included.

SQL 8. Running total of order amounts per customer

SELECT customer, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer
                         ORDER BY order_date, id) AS running_total
FROM orders
ORDER BY customer, order_date;
-- asha  | 2026-10-01 | 500 |  500
-- asha  | 2026-10-02 | 200 |  700
-- asha  | 2026-10-03 | 100 |  800
-- meera | 2026-10-05 |  50 |   50
-- ravi  | 2026-10-01 | 300 |  300
-- ravi  | 2026-10-05 | 700 | 1000

With ORDER BY inside OVER, the default frame runs from the start of the partition to the current row, which gives a cumulative sum.

SQL 9. Departments whose average salary exceeds the company average

SELECT d.name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.dept_id
GROUP BY d.id, d.name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);
-- Engineering | 217500.0

Company average: (250000 + 180000 + 260000 + 120000 + 130000 + 120000 + 90000 + 180000) / 8 = 1,330,000 / 8 = 166,250. Engineering averages 870,000 / 4 = 217,500; Sales averages 370,000 / 3 ≈ 123,333; HR 90,000. Only Engineering qualifies.

SQL 10. Customers who ordered on 3 or more consecutive days

SELECT customer,
       MIN(order_date) AS streak_start,
       MAX(order_date) AS streak_end,
       COUNT(*)        AS days
FROM (
  SELECT customer, order_date,
         julianday(order_date)
           - ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date) AS grp
  FROM (SELECT DISTINCT customer, order_date FROM orders) d
) t
GROUP BY customer, grp
HAVING COUNT(*) >= 3;
-- asha | 2026-10-01 | 2026-10-03 | 3

The "gaps and islands" trick: for consecutive dates, the date minus its row number is constant, so it identifies each streak. julianday is SQLite-specific; in PostgreSQL use order_date - ROW_NUMBER() OVER (...)::int on a date column, and in MySQL DATE_SUB(order_date, INTERVAL ROW_NUMBER() OVER (...) DAY).

Bonus: delete duplicate rows, keeping the lowest id

DELETE FROM employees
WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY email);
-- removes 'Ravi K' (id 8); 7 rows remain

This works in SQLite and PostgreSQL. MySQL refuses to delete from a table referenced in a subquery (error 1093); wrap the subquery in a derived table, ... NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM employees GROUP BY email) keep), or use a self-join DELETE.

6 numerical and theory problems

Problem 1. Attribute closure and candidate keys

Given R(A, B, C, D, E) with functional dependencies F = { A → B, B → C, CD → E, E → A }. Find all candidate keys.

Step 1: attributes that must be in every key. D appears on no right-hand side, so nothing determines it; every key must contain D. No attribute appears only on right-hand sides that we could exclude outright (B, C, E and A each appear on both sides).

Step 2: is D alone a key? D⁺ = {D}: no dependency has a left side contained in {D}. Not a key.

Step 3: try D with one more attribute.

• (AD)⁺: start {A, D}. A → B adds B. B → C adds C. CD → E adds E. Result {A, B, C, D, E}: AD is a key.
• (BD)⁺: {B, D}, B → C gives C, CD → E gives E, E → A gives A. All five: BD is a key.
• (CD)⁺: {C, D}, CD → E gives E, E → A gives A, A → B gives B. All five: CD is a key.
• (DE)⁺: {D, E}, E → A gives A, A → B gives B, B → C gives C. All five: DE is a key.

Step 4: minimality. Each is two attributes and neither D nor the other attribute alone is a key (A⁺ = {A, B, C} lacks D), so all four are minimal. Any larger set containing D plus one of A, B, C, E is a super key but not candidate.

Answer: candidate keys are AD, BD, CD and DE. Prime attributes: A, B, C, D, E (all of them).

Problem 2. Highest normal form

Given the same R and F as Problem 1. What is the highest normal form?

• BCNF? Check A → B: is A a super key? A⁺ = {A, B, C}, which lacks D and E, so no. BCNF is violated.
• 3NF? For every dependency X → Y, either X is a super key or every attribute of Y is prime. All attributes are prime (Problem 1), so every dependency satisfies the second condition. 3NF holds.

Answer: 3NF (and therefore also 2NF and 1NF), but not BCNF.

A second quick case for practice: R(A, B, C, D) with AB → C and C → D. The only key is AB. There is no partial dependency (no attribute depends on A alone or B alone), so 2NF holds; but C → D has a non-key determinant and D is non-prime, a transitive dependency, so 3NF fails. Highest: 2NF.

Problem 3. BCNF decomposition and dependency preservation

Given R(A, B, C) with AB → C and C → B. Think of A = student, B = subject, C = teacher: each teacher teaches one subject, and a student has one teacher per subject.

Keys. (AB)⁺ = {A, B, C}, so AB is a key. (AC)⁺ = {A, C, B} using C → B, so AC is a key too. Prime attributes: A, B, C.

Normal form. C → B: C is not a super key, so BCNF is violated; B is prime, so 3NF holds.

Decompose on C → B: R1(C, B) and R2(A, C).

• Lossless? R1 ∩ R2 = {C}, and C → B means C is a key of R1. Lossless.
• BCNF? R1(C, B) has key C and the only dependency C → B: BCNF. R2(A, C) has only the trivial dependencies: BCNF.
• Dependency preserving? AB → C involves A, B and C, which are no longer in one table. It can only be checked by joining. Not preserved.

Answer: the BCNF decomposition (CB, AC) is lossless but loses AB → C. This is the textbook example of why BCNF and dependency preservation cannot always both be achieved; staying in 3NF keeps the dependency checkable.

Problem 4. Conflict serializability

(a) Is this schedule conflict serializable? If yes, give the equivalent serial order.

S: R1(A) R2(A) R3(B) W1(A) R2(C) R2(B) W2(B) W1(C)

Process each item.

• A: R1(A) R2(A) W1(A). R1/R2 is read-read, no conflict. R2(A) before W1(A): T2 → T1.
• B: R3(B) R2(B) W2(B). R3(B) before W2(B): T3 → T2.
• C: R2(C) W1(C). R2(C) before W1(C): T2 → T1.

  T3 ---> T2 ---> T1

No cycle. Conflict serializable, equivalent to T3, T2, T1.

(b) And this one?

S: R2(X) W3(X) W1(Y) R2(Y) W1(X)

• X: R2(X) W3(X) W1(X). R2(X) before W3(X): T2 → T3. R2(X) before W1(X): T2 → T1. W3(X) before W1(X): T3 → T1.
• Y: W1(Y) before R2(Y): T1 → T2.

Edges T2 → T1 and T1 → T2 form a cycle. Not conflict serializable.

Problem 5. B+ tree order and height

Given block size 4,096 bytes, search key 12 bytes, block (child) pointer 8 bytes, record pointer 8 bytes. Find the order of internal and leaf nodes, and the height needed to index 10 million records if every node is full.

Internal node. An internal node with n child pointers holds n − 1 keys:

8n + 12(n − 1) ≤ 4096, so 20n − 12 ≤ 4096, so 20n ≤ 4108, so n ≤ 205.4. Internal order n = 205 (205 pointers, 204 keys).

Leaf node. A leaf holds m (key, record pointer) pairs plus one pointer to the next leaf:

m(12 + 8) + 8 ≤ 4096, so 20m ≤ 4088, so m ≤ 204.4. Leaf capacity m = 204 entries.

Height for 10,000,000 records, nodes full.

• Leaves: ⌈10,000,000 / 204⌉ = 49,020.
• Level above: ⌈49,020 / 205⌉ = 240 nodes.
• Next: ⌈240 / 205⌉ = 2 nodes.
• Root: 1 node.

That is 4 levels (root, 2 nodes, 240 nodes, 49,020 leaves). A point lookup reads 4 index blocks plus 1 data block = 5 block reads, fewer in practice because the root and upper levels stay cached in memory. (If nodes are only about two-thirds full, as is typical after random inserts, the height is still 4 here.)

Problem 6. Join cost

Given relation r with 1,000 blocks and s with 200 blocks, M = 52 buffer blocks, equality join on a non-indexed column. Compare block nested loop and hash join.

Block nested loop, smaller relation (s) as outer: use M − 2 = 50 blocks for the outer.

• Outer chunks: ⌈200 / 50⌉ = 4.
• Cost = 4 × 1,000 + 200 = 4,200 block transfers.
• With r as outer instead: ⌈1,000 / 50⌉ × 200 + 1,000 = 20 × 200 + 1,000 = 5,000. The smaller relation should be the outer.

Hash join: s (200 blocks) is the build input. It does not fit in 52 blocks, so partition. With M − 1 = 51 partitions, each s partition is about 200 / 51 ≈ 3.9 blocks, which fits, so one partitioning pass suffices.

• Cost = 3 × (1,000 + 200) = 3,600 block transfers.

Answer: the hash join (3,600) beats the best block nested loop (4,200). With M ≥ 202 (whole of s in memory, plus input and output buffers), block nested loop would cost just 1,000 + 200 = 1,200, as would an in-memory hash join.

Revision checklist

Tick these off the night before. Each line links to its lesson.

• Explain DBMS versus file system, three-schema architecture and data independence (introduction).
• Draw an ER diagram with weak entities and convert M:N relationships to tables (ER model).
• Define super, candidate, primary, alternate and foreign keys with examples (keys).
• Write joins, GROUP BY with HAVING, subqueries and handle NULLs correctly (SQL fundamentals).
• Use window functions for Nth highest, top-N per group and running totals (advanced SQL).
• Compute attribute closures, find all candidate keys and the highest normal form (normalization).
• Explain B+ trees, clustered versus secondary indexes, composite indexes and compute B+ tree order (indexing).
• Explain ACID with mechanisms, draw a precedence graph, classify recoverable/cascadeless/strict (transactions).
• Give timelines for each anomaly, explain 2PL, wait-die/wound-wait, MVCC and isolation defaults (concurrency control).
• Explain WAL, steal/no-force, checkpoints and the three ARIES phases (recovery).
• Compute join costs and read an EXPLAIN ANALYZE plan (query optimization).
• State CAP correctly, compare NoSQL models, replication styles and sharding (NoSQL).
• Design e-commerce, social feed and booking schemas with constraints and indexes (design practice).
• Take a timed quiz to find weak spots (quizzes).

Key takeaways

• Group your revision by topic: basics, modelling and keys, SQL, normalization, indexing, transactions, concurrency, recovery, optimisation, NoSQL and design.
• Answer theory with definition, example and trade-off; name the database when behaviour differs.
• Window functions (ROW_NUMBER, RANK, DENSE_RANK, SUM OVER) solve most "tricky" SQL questions; self joins and HAVING solve most of the rest.
• For closure problems, start from attributes that never appear on a right-hand side; they belong to every key.
• A relation where every attribute is prime is automatically in 3NF, but may still fail BCNF.
• Precedence graph: one item at a time, conflicts only involve at least one write, acyclic means serializable.
• B+ tree order comes from fitting pointers and keys into one block; height grows with the logarithm of the record count, which is why lookups take only a handful of reads.

Next lesson

That completes the DBMS track. Start again from the introduction for a full revision pass, or test yourself with the practice quizzes.