DBMS Interview Questions 2026 Top 40 with Answers

The top 40 DBMS interview questions for freshers and experienced in 2026 cluster into eight groups: SQL basics + JOINs (30%), normalization 1NF/2NF/3NF/BCNF (18%), ACID properties + transactions (12%), indexing and query optimization (10%), keys and constraints (8%), ER modeling (7%), subqueries and views (8%), and concurrency control (7%). TCS NQT, Infosys InfyTQ, Cognizant GenC, and Accenture each test 10–12 DBMS MCQs and 1–2 SQL coding questions; product companies go deeper on indexing, transactions, and concurrency. This guide gives all 40 with answers and 12 working SQL examples.

Table of Contents

• DBMS Topic Weightage in 2026 Fresher Interviews
• Company Round Formats (TCS / Infosys / Accenture)
• DBMS Fundamentals (Q1–Q7)
• SQL Queries (Q8–Q14)
• Normalization (Q15–Q22)
• Transactions & ACID (Q23–Q28)
• Indexing & Optimization (Q29–Q33)
• Concurrency Control (Q34–Q36)
• ER Modeling & Keys (Q37–Q40)
• 3-Week DBMS Interview Prep Plan

DBMS Topic Weightage in 2026 Fresher Interviews

Trend in 2026: Indexing and query-optimization questions have grown from ~5% in 2024 to ~10% in 2026 across product-company fresher rounds (Razorpay, Flipkart, PhonePe). Expect one EXPLAIN-based or "how would you speed up this query" question in every product-company interview.

Company Round Formats: DBMS / SQL Coverage

1. Q1. What is a database?
   Answer: An organized collection of structured data, typically stored electronically and managed by a DBMS. Modern databases support concurrent access, query processing, transaction management, and recovery from failures.
2. Q2. What is the difference between DBMS and RDBMS?
   Answer: DBMS (Database Management System) stores data in files (can be hierarchical, network, or relational) with no enforced relationships between tables. RDBMS (Relational DBMS) stores data in related tables with ACID guarantees, supports SQL, enforces referential integrity via foreign keys, and organizes data per the relational model. All modern production databases (MySQL, PostgreSQL, Oracle, SQL Server) are RDBMS.
3. Q3. What are the advantages of an RDBMS over a flat file?
   Answer: Data independence (schema separate from application), reduced redundancy (normalization), data integrity (constraints, foreign keys), concurrent access (transactions, locking), security (per-user permissions), backup/recovery (write-ahead logs), query power (SQL with JOINs, aggregations, subqueries).
4. Q4. What is a schema?
   Answer: The structure of a database — the tables, their columns, data types, constraints, and relationships. In MySQL, "schema" and "database" are synonymous. In PostgreSQL/Oracle, a schema is a namespace within a database. Three-schema architecture (ANSI/SPARC): external (user views), conceptual (logical), internal (physical storage).
5. Q5. What is the difference between a candidate key and a super key?
   Answer: A super key is any combination of columns that uniquely identifies a row (could have extra columns). A candidate key is a minimal super key — no subset of it is a super key. The primary key is the chosen candidate key. Alternate keys are the other candidate keys not chosen as primary.
6. Q6. What is the difference between primary key and unique key?
   Answer: Both enforce uniqueness. Primary key: NOT NULL, only one per table, used as the canonical row identifier, often the clustered index. Unique key: allows NULL (one NULL in most DBs), can have multiple per table, used to enforce business uniqueness (email, phone). Every primary key is a unique key, but not every unique key is a primary key.
7. Q7. What is a foreign key?
   Answer: A column in one table that references the primary key of another table, enforcing referential integrity. Cannot insert a row with a foreign-key value that doesn't exist in the referenced table; cannot delete a referenced row while child rows exist (unless ON DELETE CASCADE is set). Example: orders.customer_id references customers.id.

SQL Queries (Q8–Q14)

1. Q8. Write a query to find the second-highest salary from an Employee table.
   Answer:

   -- Approach 1: Subquery with MAX (works on all DBs)
   SELECT MAX(salary) FROM employees
   WHERE salary < (SELECT MAX(salary) FROM employees);
   
   -- Approach 2: LIMIT + OFFSET (MySQL/PostgreSQL)
   SELECT DISTINCT salary FROM employees
   ORDER BY salary DESC LIMIT 1 OFFSET 1;
   
   -- Approach 3: DENSE_RANK (modern — PostgreSQL/Oracle/SQL Server)
   SELECT salary FROM (
       SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
       FROM employees
   ) ranked
   WHERE rnk = 2;

2. Q9. Write a query to find employees earning more than their department average.
   Answer:

   -- Approach 1: Correlated subquery
   SELECT name, salary, department
   FROM employees e
   WHERE salary > (SELECT AVG(salary) FROM employees WHERE department = e.department);
   
   -- Approach 2: CTE + window function (clearer + faster on large data)
   WITH dept_avg AS (
       SELECT department, AVG(salary) AS avg_sal
       FROM employees
       GROUP BY department
   )
   SELECT e.name, e.salary, e.department
   FROM employees e
   JOIN dept_avg d ON e.department = d.department
   WHERE e.salary > d.avg_sal;

3. Q10. Write a query to find the top 3 salaries in each department.
   Answer:

   SELECT *
   FROM (
       SELECT
           name, department, salary,
           DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
       FROM employees
   ) ranked
   WHERE rnk <= 3;
   
   -- Use DENSE_RANK (not ROW_NUMBER) when ties at the boundary should be included.
   -- Use ROW_NUMBER when you want exactly 3 per department.

4. Q11. Write a query to find duplicate email addresses.
   Answer:

   -- Approach 1: GROUP BY + HAVING
   SELECT email, COUNT(*) AS occurrences
   FROM users
   GROUP BY email
   HAVING COUNT(*) > 1;
   
   -- Approach 2: Self-join (also returns the duplicate rows)
   SELECT DISTINCT a.*
   FROM users a
   JOIN users b ON a.email = b.email AND a.id <> b.id;

5. Q12. Write a query to find customers who never placed an order.
   Answer:

   -- LEFT JOIN + IS NULL (the interview classic)
   SELECT c.*
   FROM customers c
   LEFT JOIN orders o ON c.id = o.customer_id
   WHERE o.id IS NULL;
   
   -- NOT EXISTS (often faster on large data)
   SELECT *
   FROM customers c
   WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

6. Q13. Write a query to calculate running total of sales by date.
   Answer:

   SELECT
       order_date,
       amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
   FROM orders;
   
   -- Partition by category for per-category running totals:
   SELECT
       order_date, category, amount,
       SUM(amount) OVER (PARTITION BY category ORDER BY order_date) AS category_running_total
   FROM orders;

7. Q14. Write a query to find the Nth-highest salary (generalized Q8).
   Answer:

   -- N = 3 means third-highest. Use a parameter:
   WITH ranked AS (
       SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
       FROM employees
   )
   SELECT DISTINCT salary
   FROM ranked
   WHERE rnk = :N;   -- bind N as a parameter

Normalization (Q15–Q22)

1. Q15. What is normalization?
   Answer: The process of organizing tables to reduce data redundancy and improve data integrity. Goal: eliminate update, insertion, and deletion anomalies. Each normal form adds a stricter rule; most production schemas are in 3NF or BCNF.
2. Q16. What is 1NF?
   Answer: Every column holds atomic (indivisible) values; no repeating groups; each row is unique (enforced by a primary key). Violation: phone_numbers = "555-1234, 555-5678" in a single column. Fix: split into separate rows or a related table.
3. Q17. What is 2NF?
   Answer: 1NF + every non-key column depends on the WHOLE primary key (no partial dependency). Violation: in order_items(order_id, product_id, product_name, qty) with composite PK, product_name depends only on product_id. Fix: split into products + order_items tables.
4. Q18. What is 3NF?
   Answer: 2NF + no transitive dependencies (non-key column depending on another non-key column). Violation: employees(employee_id, department_id, department_name) — department_name depends on department_id, which depends on employee_id. Fix: move department_name to the departments table.
5. Q19. What is BCNF?
   Answer: 3NF + every determinant is a candidate key. A determinant is any column on which another column fully depends. Example violation: (student, subject, professor) with rule "each subject has exactly one professor" — professor is determined by subject, but subject alone is not a candidate key. Fix: split into (student, subject) and (subject, professor) tables.
6. Q20. Walk through a normalization example from unnormalized to 3NF.
   Answer:

   -- UNNORMALIZED — one row per order with multiple items in a single column
   CREATE TABLE orders_unnormalized (
       order_id    INT,
       customer    VARCHAR(50),
       items       VARCHAR(200)    -- 'Laptop, Mouse, Charger' — violation of 1NF
   );
   
   -- 1NF — atomic values, separate rows per item
   CREATE TABLE orders_1nf (
       order_id    INT,
       customer    VARCHAR(50),
       item        VARCHAR(50),
       qty         INT
   );
   -- 1NF now holds, but customer repeats on every item row.
   
   -- 2NF — split items into a product table (assuming order_id is the only PK, item is fully dependent)
   CREATE TABLE orders_2nf (
       order_id    INT PRIMARY KEY,
       customer_id INT,
       order_date  DATE
   );
   CREATE TABLE order_items_2nf (
       order_id    INT,
       item        VARCHAR(50),
       qty         INT,
       PRIMARY KEY (order_id, item)
   );
   -- 2NF holds for order_items. Now customer_id is still in orders, but customer details are not.
   
   -- 3NF — split customer details out (transitive dependency: orders.customer_id → customers.name)
   CREATE TABLE customers (
       customer_id INT PRIMARY KEY,
       name        VARCHAR(50),
       email       VARCHAR(100)
   );
   CREATE TABLE orders_3nf (
       order_id    INT PRIMARY KEY,
       customer_id INT REFERENCES customers(customer_id),
       order_date  DATE
   );
   CREATE TABLE order_items_3nf (
       order_id    INT REFERENCES orders_3nf(order_id),
       item        VARCHAR(50),
       qty         INT,
       PRIMARY KEY (order_id, item)
   );
   -- 3NF holds — no transitive dependencies, no partial dependencies.

7. Q21. What are the trade-offs of normalization?
   Answer: Pros: less redundancy, fewer update anomalies, smaller storage, simpler writes. Cons: more JOINs required for reads, slower complex queries, harder to denormalize mentally. Most OLTP systems target 3NF; data warehouses denormalize aggressively for read performance.
8. Q22. What is denormalization and when is it used?
   Answer: Adding controlled redundancy to a normalized schema to speed up reads. Common patterns: precomputed aggregate columns, denormalized 'wide' tables for reporting, materialized views, caching foreign-key columns. Trade-off: faster reads, slower writes (must update multiple places), risk of inconsistency. Used in OLAP/data warehouses, dashboards, and high-traffic read paths.

Transactions & ACID (Q23–Q28)

1. Q23. What is a transaction?
   Answer: A logical unit of work consisting of one or more SQL statements that must all succeed or all fail. Example: transferring money between two bank accounts is one transaction (debit account A, credit account B) — if the credit fails, the debit must be rolled back.
2. Q24. What are ACID properties?
   Answer:

   Property	Meaning
   Atomicity	All operations in a transaction succeed or all fail (no partial state)
   Consistency	Transaction moves DB from one valid state to another (constraints not violated)
   Isolation	Concurrent transactions don't see each other's uncommitted changes
   Durability	Committed changes survive crashes (written to disk, not just memory)

3. Q25. What are the four transaction isolation levels?
   Answer:

   Level	Dirty reads	Non-repeatable reads	Phantom reads
   READ UNCOMMITTED	Yes	Yes	Yes
   READ COMMITTED (PostgreSQL default)	No	Yes	Yes
   REPEATABLE READ (MySQL InnoDB default)	No	No	Yes
   SERIALIZABLE	No	No	No

   Higher isolation = more correctness = less concurrency. Most production systems use READ COMMITTED for performance with explicit row locks for critical sections.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
SAVEPOINT after_debit;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If the credit fails, undo only it (not the whole transaction):
ROLLBACK TO SAVEPOINT after_debit;
-- The debit is preserved. Then either commit or roll back the rest.
COMMIT;

Indexing & Optimization (Q29–Q33)

1. Q29. What is an index?
   Answer: A separate data structure (typically a B-tree) that lets the database find rows matching a column value without scanning the entire table. Trade-off: indexes speed up SELECT/WHERE/JOIN/ORDER BY but slow down INSERT/UPDATE/DELETE (the index must be updated too) and consume disk space.
2. Q30. When should you create an index?
   Answer:
   • Columns frequently used in WHERE, JOIN, ORDER BY, GROUP BY
   • High-cardinality columns (many distinct values)
   • Foreign-key columns
   • Composite indexes for queries that filter on multiple columns together (column order matters)

   Don't index: low-cardinality columns, small tables, columns frequently updated.

EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- Shows how the DB plans to execute the query:
--   - Index scan vs full table scan
--   - Join order and join algorithm (nested loop, hash join, merge join)
--   - Estimated row counts at each step
-- Use EXPLAIN ANALYZE (PostgreSQL) to also see actual timings and row counts.

Concurrency Control (Q34–Q36)

1. Q34. What is a transaction schedule?
   Answer: A sequence of operations from one or more concurrent transactions. A schedule is serializable if its result is equivalent to running the transactions one after another (no concurrency). Schedules can be serial (no overlap), serializable (equivalent to serial), or non-serializable (may produce anomalies).
2. Q35. What are phantom reads?
   Answer: Within a transaction, the same SELECT returns different sets of rows because another transaction inserted/deleted matching rows in between. Example: T1 reads "all employees in Sales = 5", T2 inserts a new Sales employee, T1 reads again and gets 6. SERIALIZABLE isolation prevents phantoms; REPEATABLE READ prevents in MySQL InnoDB (via gap locks) but not in PostgreSQL.
3. Q36. What is two-phase locking (2PL)?
   Answer: A concurrency protocol where transactions acquire all locks in a "growing phase" (no releases) and release them in a "shrinking phase" (no new acquires). Guarantees serializability but can cause deadlocks. Most production DBMSs use a variant (strict 2PL: hold write locks until commit).

ER Modeling & Keys (Q37–Q40)

1. Q37. What is an ER diagram?
   Answer: Entity-Relationship diagram: a visual schema notation. Entities (rectangles) = tables. Attributes (ovals) = columns. Relationships (diamonds) = associations between entities. Cardinality (1:1, 1:N, M:N) is shown with notation on the edges. Example: a Customer entity has a 1:N relationship with Order; an Order has an M:N relationship with Product (resolved by an order_items junction table).
2. Q38. What is the difference between a candidate key and a composite key?
   Answer: A candidate key is any minimal set of columns that uniquely identifies a row. A composite key is a candidate key made of multiple columns (e.g., (order_id, line_item_id)). A natural key uses real-world data (email, SSN). A surrogate key is an artificial identifier (auto-increment INT or UUID) added by the schema designer.
3. Q39. What is a CHECK constraint?
   Answer:

   CREATE TABLE employees (
       id      INT PRIMARY KEY,
       salary  INT CHECK (salary > 0),               -- enforce positive salary
       dept    VARCHAR(20) CHECK (dept IN ('Eng', 'Sales', 'HR'))
   );
   -- CHECK enforces a predicate on each row. Rejected by the DBMS at INSERT/UPDATE time.
   -- Some DBs (MySQL pre-8.0.16) parse but don't enforce CHECK — verify your DB.

4. Q40. What is a view and what is a materialized view?
   Answer:

   A view is a stored query that behaves like a table — referenced like a table but computed on the fly. No data is stored; the query runs each time the view is accessed. A materialized view is a view whose result is physically stored and periodically refreshed. Faster reads (no recomputation), but stale data between refreshes and storage cost. Use materialized views for dashboards and reporting; use regular views for securit

3-Week DBMS Interview Prep Plan

Resources and Next Steps

The authoritative sources listed above (W3Schools, Use The Index Luke, Database Internals by Alex Petrov, PostgreSQL docs) are the canonical references for DBMS fundamentals and interview preparation. For the full 25-question curated Q&A collection at all difficulty levels (Level 1 foundational, Level 2 intermediate, Level 3 advanced), see the TutorsBot DBMS Interview Questions hub. For related interview prep, see our SQL Interview Questions, Normalization in DBMS, and Python Interview Questions guides.