What are the most asked DBMS topics in fresher interviews 2026?

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%), concurrency control and locking (7%). TCS NQT and Infosys InfyTQ each test 10–12 DBMS MCQs and 1–2 SQL coding questions; product companies go deeper on indexing, transactions, and concurrency.

What is the difference between DBMS and RDBMS?

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. Examples of non-relational DBMS: MongoDB (document), Redis (key-value), Neo4j (graph).

What is the difference between primary key and unique key?

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.

What is normalization?

The process of organizing tables to reduce data redundancy and improve data integrity. 1NF: every column holds atomic values (no comma-separated lists), no repeating groups. 2NF: 1NF + every non-key column depends on the WHOLE primary key. 3NF: 2NF + no transitive dependencies (non-key column depending on another non-key column). BCNF: 3NF + every determinant is a candidate key. Reduces update anomalies; may require more JOINs at read time.

What is denormalization and when is it used?

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.

What is the difference between DELETE and TRUNCATE?

DELETE removes specific rows (or all if no WHERE), is transactional (can be rolled back), does NOT reset auto-increment counters, fires triggers, generates one log entry per row. TRUNCATE removes all rows in one operation, is DDL (auto-committed in most DBs, can't be rolled back), resets auto-increment counters, may not fire row triggers, is generally faster on large tables.

How do I prepare for DBMS interviews in 3 weeks?

Week 1: SQL basics + JOINs + GROUP BY + subqueries — 20 practice queries daily on HackerRank. Week 2: normalization (1NF/2NF/3NF with worked examples) + ACID + isolation levels + indexing — 1 worked example + 10 MCQs daily. Week 3: ER modeling + concurrency control + mock interviews with timed query writing.

DBMS Interview Questions 2026 Top 40 with Answers

Q: What are ACID properties?

Atomicity — a transaction either completes entirely or doesn't run at all (no partial state). Consistency — a transaction moves the database from one valid state to another (no constraint violations). Isolation — concurrent transactions don't see each other's uncommitted changes. Durability — committed changes survive crashes (written to disk, not just memory).

Topic Cluster	Frequency in MCQ	Frequency in Coding
SQL basics + JOINs (INNER, LEFT, RIGHT, SELF)	30%	50%
Normalization (1NF/2NF/3NF/BCNF)	20%	5%
ACID + transactions + isolation levels	12%	5%
Indexing + query optimization (EXPLAIN)	8%	15%
Keys + constraints + referential integrity	8%	10%
Subqueries + views + CTEs	8%	10%
ER modeling + normalization rationale	7%	—
Concurrency control + locking + deadlocks	7%	5%

Company Round Formats: DBMS / SQL Coverage

Company Round	DBMS / SQL Format	Difficulty
TCS NQT (2026)	10–12 SQL MCQs + 1–2 SQL coding	Easy to medium
Infosys InfyTQ (2026)	10 SQL MCQs + 1 coding (often 2 JOINs)	Easy to medium
Cognizant GenC (2026)	12–15 SQL MCQs + 1 SQL coding	Easy to medium
Wipro NLTH (2026)	8–10 SQL MCQs	Easy only
Accenture (2026)	10–12 SQL MCQs + 1 coding	Easy to medium
Product companies (Flipkart, PhonePe, Razorpay)	1–2 SQL live-coding rounds	Medium to hard (window functions, indexing, optimization)

DBMS Interview Questions

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.
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.
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).
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).
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.
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.
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)

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;

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;

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.

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;

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);

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;

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)

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.
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.
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.
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.
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.

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.

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.
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)

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.

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)

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.

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

Q26. What is a deadlock?
Answer: Two transactions each hold a lock the other needs, so neither can proceed. Example: T1 locks row A then wants row B; T2 locks row B then wants row A. Modern DBMSs detect deadlocks and roll back one transaction (the cheaper one); the application should retry the rolled-back transaction. Prevention: acquire locks in a consistent order, keep transactions short, use appropriate isolation level.

Q27. What is the difference between optimistic and pessimistic locking?
Answer: Pessimistic: acquire the lock before reading/writing (SELECT ... FOR UPDATE). Prevents conflicts but reduces concurrency. Use when conflicts are likely. Optimistic: read without locking, write only if a version column hasn't changed since the read. Better concurrency, but the write may fail and the application must retry. Use when conflicts are rare.

Q28. What is a savepoint?
Answer:

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)

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.
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.
Q31. What is the difference between a clustered and non-clustered index?
Answer: Clustered: the table data is physically sorted by the index key. One per table (usually the primary key). Faster for range queries on the key. Non-clustered: a separate structure with pointers back to the data rows. Many per table. Faster for lookups on non-key columns but requires an extra hop to fetch the row.
Q32. What is a covering index?
Answer: A non-clustered index that includes all columns referenced in a query — both the filter columns and the selected columns. The query can be answered entirely from the index without touching the table data. Example: CREATE INDEX idx_emp_dept_name ON employees(department, name) covers SELECT name FROM employees WHERE department = ?.

Q33. What is the EXPLAIN plan?
Answer:

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)

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).
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.
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)

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).
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.

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.

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 security (column-level access control) and query simplification.

3-Week DBMS Interview Prep Plan

Week	Focus	Daily Target
Week 1	SQL basics + JOINs + GROUP BY + subqueries	20 practice queries on HackerRank SQL
Week 2	Normalization (1NF/2NF/3NF with worked examples) + ACID + isolation levels + indexing	1 worked example + 10 MCQs
Week 3	ER modeling + concurrency control + mock interviews with timed query writing	1 mock + 15 timed queries

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.

DBMS Interview Questions 2026 Top 40 with Answers

Table of Contents

DBMS Topic Weightage in 2026 Fresher Interviews

Related Articles

SQL Interview Questions 2026 Top 40 with Answers

Which Free AI Interview Preparation Tools Work in 2026?

OOPs Interview Questions Java 2026 Top 40 with Answers

Java Interview Questions 2026 Top 40 with Answers

Try It in the Browser