✅ Key Topics to Cover
1. Basic DBMS Concepts
2. Data Models
3. Normalization & Denormalization
4. SQL (DDL, DML, DCL, TCL)
5. Joins & Subqueries
6. Indexes
7. Transactions & ACID Properties
8. ER Model & Schema Design
9. Relational Algebra
10.Stored Procedures, Triggers, Views
11.Concurrency Control & Locking
12.NoSQL vs SQL
🧠 DBMS Interview Questions (with Answers)
🔹 1. What is DBMS?
Answer:
A Database Management System (DBMS) is software that allows users to define, create,
maintain, and control access to a database. Examples: MySQL, PostgreSQL, Oracle,
MongoDB.
🔹 2. What is the difference between DBMS and RDBMS?
Answer:
Feature DBMS RDBMS
Data Storage Files Tables
Relationships No Yes
Example File System MySQL, PostgreSQL
🔹 3. What are the types of keys in DBMS?
Answer:
● Primary Key: Uniquely identifies a row.
● Candidate Key: Eligible to be a primary key.
● Foreign Key: Refers to primary key in another table.
● Composite Key: Combination of columns to form a unique identifier.
● Alternate Key: Candidate keys not chosen as primary.
🔹 4. Explain Normalization.
Answer:
Normalization is the process of organizing data to reduce redundancy and improve data
integrity.
Forms include:
● 1NF: Atomic values.
● 2NF: Remove partial dependency.
● 3NF: Remove transitive dependency.
● BCNF: A stronger version of 3NF.
🔹 5. What is a join? Types of joins?
Answer:
Joins combine rows from two or more tables based on a related column.
Types:
● INNER JOIN – matching records in both tables.
● LEFT JOIN – all records from left + matched from right.
● RIGHT JOIN – all records from right + matched from left.
● FULL OUTER JOIN – all records when there is a match in one of the tables.
● CROSS JOIN – Cartesian product.
🔹 6. What are ACID properties?
Answer:
● Atomicity: All-or-nothing execution.
● Consistency: Data remains valid before and after transaction.
● Isolation: Transactions do not interfere.
● Durability: Changes are permanent after commit.
🔹 7. What is an index in DBMS?
Answer:
An index improves the speed of data retrieval. It's like a table of contents.
Types: Single column, Composite index, Unique index, Clustered/Non-clustered.
🔹 8. What is a view?
Answer:
A View is a virtual table based on a SQL query. It doesn't store data itself but presents it
from one or more tables.
🔹 9. Explain differences between DELETE, TRUNCATE, and DROP.
Command Deletes Data Removes Rollback
Structure Possible
DELETE Yes (row-wise) No Yes
TRUNCATE Yes (all rows) No No
DROP Yes Yes No
🔹 10. What is the difference between WHERE and HAVING?
Answer:
● WHERE filters rows before grouping.
● HAVING filters groups after GROUP BY.
Awesome! Let’s move to the Advanced DBMS Interview Questions, covering deep
concepts like transactions, concurrency, query optimization, indexing strategies, etc.
🔥 Advanced DBMS Interview Questions (with Answers)
🔹 11. What is a transaction? Explain its states.
Answer:
A transaction is a sequence of operations performed as a single logical unit of work.
States:
1. Active – Executing operations.
2. Partially Committed – Final operation executed.
3. Committed – Changes are permanently saved.
4. Failed – Error occurred.
5. Aborted – Rolled back.
🔹 12. Explain concurrency control. Why is it important?
Answer:
Concurrency control ensures that multiple transactions can occur simultaneously without
interfering with each other.
It prevents:
● Lost Updates
● Dirty Reads
● Non-repeatable Reads
● Phantom Reads
🔹 13. What is two-phase locking protocol (2PL)?
Answer:
It’s a concurrency control method with two phases:
1. Growing Phase – Transaction acquires all the locks.
2. Shrinking Phase – Transaction releases locks.
Ensures serializability but may cause deadlocks.
🔹 14. What is deadlock? How can it be prevented?
Answer:
Deadlock occurs when two or more transactions wait indefinitely for each other to release
resources.
Prevention Methods:
● Timeout
● Wait-Die / Wound-Wait protocols
● Resource ordering
🔹 15. What is shadow paging?
Answer:
A recovery technique that keeps two copies of the database pages:
● Shadow Page (original)
● Current Page (updated)
On commit, current pages become shadow pages.
🔹 16. What are the differences between clustered and non-clustered indexes?
Feature Clustered Index Non-Clustered Index
Data Order Sorted physically Logical order
One per Yes Many allowed
table
Access Faster for range Slower
speed queries
🔹 17. What is query optimization?
Answer:
It is the process of choosing the most efficient way to execute a SQL query using
techniques like:
● Join ordering
● Index usage
● Predicate pushdown
● Cost-based optimization
🔹 18. Explain the difference between OLTP and OLAP.
Feature OLTP OLAP
Use Day-to-day Analytical queries
Case transactions
Data Small Huge
Size
Speed Fast insert/update Fast read/aggregate
Example ATM system Business Intelligence tool
🔹 19. What is a materialized view?
Answer:
Unlike a regular view, a materialized view stores the result set physically and updates it
periodically. Useful for expensive queries.
🔹 20. What is normalization vs denormalization?
Normalization removes redundancy and improves consistency.
Denormalization adds redundancy for faster read performance (e.g., joins are avoided).
🔹 21. What is a B+ Tree? Why is it used in indexing?
Answer:
A B+ Tree is a self-balancing tree where all values are found at leaf level and internal nodes
store only keys.
Efficient for range searches, used in most DBMSs for indexing.
🔹 22. What are phantom reads? How can they be avoided?
Answer:
Phantom reads happen when a transaction re-executes a query and gets new rows
(phantoms).
Can be avoided using:
● Serializable isolation level
● Predicate locking
🔹 23. What are stored procedures and triggers?
● Stored Procedure: Precompiled SQL code that can be reused.
● Trigger: Executes automatically in response to DML events (INSERT, UPDATE,
DELETE).
🔹 24. How do you ensure data integrity in DBMS?
Answer:
● Primary and Foreign Keys
● Unique Constraints
● Not Null Constraints
● Check Constraints
● Transactions (ACID)
🔹 25. Difference between schema and instance?
Term Schema Instance
Definition Structure of DB Snapshot of DB
Changes Rare Frequently
🔹 26. What is the difference between logical and physical data independence?
Type Definition Example
Logical Change in logical schema doesn’t affect Adding a new column
Independence application
Physical Change in physical storage doesn’t affect Moving to SSD from
Independence schema HDD
🔹 27. What is a surrogate key?
Answer:
A surrogate key is a system-generated key (usually numeric) used as a substitute for a
natural primary key.
Example: Auto-increment ID in MySQL.
🔹 28. What are aggregate functions in SQL?
Answer:
Functions that perform a calculation on a set of values:
● SUM(), AVG(), MAX(), MIN(), COUNT()
🔹 29. Explain the difference between EXISTS and IN in SQL.
Feature EXISTS IN
Returns Boolean List of values
Performanc Faster with large data Slower with subqueries
e
Use Correlated Small fixed lists
subqueries
🔹 30. What is a composite key?
Answer:
A key formed by combining two or more columns to uniquely identify a row.
🔹 31. How do transactions work in SQL (BEGIN, COMMIT, ROLLBACK)?
sql
CopyEdit
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;
-- If error occurs
ROLLBACK;
🔹 32. What is the difference between a view and a table?
Feature Table View
Data Stores data Does not store data (virtual)
Storage
Update Directly editable Sometimes read-only
Performance Fast Slightly slower
🔹 33. What are isolation levels in DBMS?
Answer:
1. Read Uncommitted – Dirty reads allowed
2. Read Committed – No dirty reads
3. Repeatable Read – No dirty, no non-repeatable
4. Serializable – Strictest, no phantoms
🔹 34. Explain Referential Integrity.
Answer:
It ensures that foreign keys always refer to valid primary keys.
Example: You can’t insert an order with a customer_id that doesn’t exist in the customers
table.
🔹 35. What is the use of the GROUP BY clause in SQL?
Answer:
Used to group rows that have the same values in specified columns.
sql
CopyEdit
SELECT department, COUNT(*) FROM employees GROUP BY department;
🔹 36. What is cascading in DBMS?
Answer:
Actions triggered automatically when a referenced row is updated or deleted:
● ON DELETE CASCADE
● ON UPDATE CASCADE
🔹 37. What is a data dictionary?
Answer:
A metadata repository that stores definitions, types, constraints, and relationships of
database objects.
🔹 38. What is a recursive query in SQL?
Answer:
Used to query hierarchical data (e.g., employee-manager tree).
sql
CopyEdit
WITH RECURSIVE emp_cte AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS
NULL
UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e
JOIN emp_cte c ON e.manager_id = c.id
SELECT * FROM emp_cte;
🔹 39. What is horizontal vs vertical partitioning?
Partition Type Description
Horizontal Rows are split across tables (e.g., region-wise data)
Vertical Columns are split (e.g., frequently accessed columns in separate
table)
🔹 40. What is write-ahead logging (WAL)?
Answer:
A method where changes are first written to a log before applying them to the database.
Ensures durability and recoverability.
🔹 41. What is a checkpoint in DBMS?
Answer:
A checkpoint is a mechanism to reduce the time required for recovery. It records the state of
the database and ensures all committed changes are written to disk.
🔹 42. What is the difference between a hot backup and a cold backup?
Backup Type Description
Hot Backup Taken while the database is running
Cold Backup Taken when the database is shut
down
🔹 43. What is a shadow copy in DBMS recovery?
Answer:
A backup of the data pages before a transaction modifies them. Used for recovery in case
of failure.
🔹 44. What is the purpose of the HAVING clause in SQL?
Answer:
Used to filter groups created by GROUP BY:
sql
CopyEdit
SELECT dept, COUNT(*) FROM employees GROUP BY dept HAVING COUNT(*) >
5;
🔹 45. What is the difference between UNION and UNION ALL?
Feature UNION UNION
ALL
Duplicates Remove Keeps
s
Performanc Slower Faster
e
🔹 46. What are the different types of database users?
● DBA (Admin)
● Application Programmer
● End User
● System Analyst
🔹 47. What is the difference between strong and weak entity sets?
Entity Description
Strong Has a primary key
Entity
Weak Entity Depends on another entity for identification; uses a foreign key and
discriminator
🔹 48. What is a transaction log?
Answer:
A record of all transactions and database modifications. Essential for recovery and rollback.
🔹 49. What is the purpose of ROLLBACK TO SAVEPOINT in SQL?
Answer:
Allows partial rollback to a predefined point in a transaction.
sql
CopyEdit
SAVEPOINT A;
-- some queries
ROLLBACK TO A;
🔹 50. What is a distributed database system?
Answer:
A database that is spread across multiple physical locations but appears as a single logical
database to the user.
🔹 51. Explain the CAP theorem.
Answer:
In distributed systems:
● Consistency – All nodes show the same data.
● Availability – Every request gets a response.
● Partition Tolerance – System works even if nodes are partitioned.
You can only guarantee two out of the three.
🔹 52. What is optimistic vs pessimistic concurrency control?
Control Type Description
Pessimistic Locks data before use
Optimistic Assumes minimal conflict; validates before commit
🔹 53. What are synonyms and aliases in SQL?
Answer:
● Alias is a temporary name used in queries.
sql
CopyEdit
SELECT e.name AS employee_name FROM employees e;
● Synonym is a permanent alternate name for a table/view (used in Oracle).
🔹 54. What is a bitmap index?
Answer:
An index that uses bitmaps for each distinct value of a column. Efficient for low-cardinality
columns (e.g., gender, yes/no).
🔹 55. What is query plan or execution plan?
Answer:
A visual representation of how a query will be executed. It helps in performance tuning.
🔹 56. What are integrity constraints in DBMS?
Answer:
● Primary Key
● Foreign Key
● Unique
● Not Null
● Check
Ensure correctness and consistency of data.
🔹 57. What is a catalog in DBMS?
Answer:
A catalog (or data dictionary) is a central repository of metadata that stores information
about database structure.
🔹 58. What is the difference between a file system and a DBMS?
Feature File System DBMS
Data High Low
Redundancy
Querying Manual SQL
Transactions Not Supporte
supported d
🔹 59. What is a self-join?
Answer:
A join where a table is joined with itself.
sql
CopyEdit
SELECT A.name, B.name FROM employees A JOIN employees B ON
A.manager_id = B.id;
🔹 60. What are some common SQL performance tuning techniques?
Answer:
● Use indexes appropriately
● Avoid SELECT *
● Use EXPLAIN PLAN
● Use joins over subqueries (in many cases)
● Filter early using WHERE clauses
🔹 61. What is the difference between schema and subschema?
Answer:
● Schema: Logical structure of the entire database (tables, relationships, constraints).
● Subschema: Subset of the schema, tailored for application or user needs (like
views).
🔹 62. What is a cursor in SQL?
Answer:
A cursor allows row-by-row processing of query results. Used when row-level operations
are needed.
sql
CopyEdit
DECLARE cursor_name CURSOR FOR
SELECT * FROM employees;
🔹 63. What is normalization? List all normal forms briefly.
Normal Form Condition
1NF Atomic values
2NF 1NF + No partial dependency
3NF 2NF + No transitive dependency
BCNF Stronger 3NF (every determinant is a candidate
key)
4NF No multi-valued dependencies
5NF No join dependency
🔹 64. What is the difference between stored procedure and function in SQL?
Feature Stored Procedure Function
Return Type May not return a value Always returns a value
Use in SQL Called explicitly Can be used in SQL
statements
Transaction Can use them Usually not
s
🔹 65. What is a covering index?
Answer:
An index that contains all the columns required by a query, so the database doesn't need to
access the actual table (index-only scan).
🔹 66. What is data fragmentation in DBMS?
Answer:
Breaking a database into smaller parts (fragments) to improve performance and distribution:
● Horizontal – by rows
● Vertical – by columns
● Mixed
🔹 67. Explain the concept of foreign key constraint.
Answer:
A foreign key enforces a link between the data in two tables. It ensures the value in one
table must exist in another.
🔹 68. What is the difference between static and dynamic views?
● Static View: Fixed data snapshot; does not reflect changes.
● Dynamic View: Reflects real-time data changes in the underlying tables.
🔹 69. Explain the difference between TRIGGER and CHECK constraint.
● TRIGGER: Procedural logic executed on events (insert/update/delete).
● CHECK: Enforces validation on a single column.
🔹 70. What is de-normalization? When is it used?
Answer:
The process of introducing redundancy to improve read performance. Used in data
warehousing or reporting systems.
🔹 71. What is a hash index? How is it different from B-tree?
Feature Hash Index B-tree Index
Use Equality search Range & equality
Performanc Fast for exact Good overall
e match
Ordered? No Yes
🔹 72. What is a view? Can we update data through a view?
Answer:
Yes, but with restrictions:
● The view must refer to a single base table.
● It must not use GROUP BY, DISTINCT, or aggregation.
● All columns being updated must exist in the view.
🔹 73. What is the purpose of EXPLAIN or EXPLAIN PLAN?
Answer:
Used to analyze and visualize how SQL queries are executed and help optimize
performance by showing:
● Index usage
● Join methods
● Estimated cost
🔹 74. What is a temporary table?
Answer:
A table created temporarily for a session or a specific query, deleted automatically at
session end.
sql
CopyEdit
CREATE TEMPORARY TABLE temp_emp (...);
🔹 75. What are ACID vs BASE properties?
Property ACID (SQL) BASE (NoSQL)
Consistenc Strong Eventually consistent
y
Availability Medium High
Use Case Transactions Scalability/Big Data
🔹 76. What is the difference between DELETE, TRUNCATE, and DROP again in context
of constraints and rollback?
Command Rollback Respects FK Logging
Constraints
DELETE Yes Yes Yes
TRUNCATE No No Minimal
DROP No N/A No (removes object)
🔹 77. What are materialized views used for?
Answer:
To store the result of a complex query physically and refresh it periodically — used for
performance optimization in analytics.
🔹 78. What is a race condition in DBMS?
Answer:
Occurs when two transactions access the same data concurrently and the final result
depends on the order of execution.
🔹 79. What is a deadlock? How to detect and resolve it?
Detection:
● Wait-for graph
● Timeout mechanisms
Resolution:
● Abort one transaction
● Rollback
● Retry logic
🔹 80. What are the different types of file organization in DBMS?
1. Heap File – Unordered.
2. Sorted File – Ordered by key.
3. Hashed File – Uses hash function for address calculation.
🔹 81. What is write skew anomaly?
Answer:
Occurs in snapshot isolation where two concurrent transactions read the same data and
then update disjoint parts, leading to an inconsistent state despite isolation.
🔹 82. What is the difference between horizontal and vertical scaling?
Scaling Type Description
Horizontal (Scaling Out) Add more machines/nodes
Vertical (Scaling Up) Increase resources (CPU, RAM) on a single
machine
🔹 83. Explain eventual consistency. Where is it used?
Answer:
In distributed systems (like NoSQL), eventual consistency means all replicas will converge
to the same value, but not immediately.
Used in systems like DynamoDB, Cassandra.
🔹 84. What are the different types of replication in DBMS?
● Master-Slave: One primary writes, others replicate.
● Master-Master: Multiple nodes can write (conflict resolution needed).
● Snapshot Replication: Periodic copying.
● Transactional Replication: Real-time sync of transactions.
🔹 85. What is the difference between strong and eventual consistency?
Property Strong Eventual
Consistency Consistency
Read Immediate Eventually
Accuracy
Latency High Low
Use Case Banking Social Media Likes
🔹 86. What is sharding? Why is it used?
Answer:
Sharding splits large databases into smaller, faster parts (shards) stored on different
servers. It improves performance and scalability.
🔹 87. What is query rewriting in DBMS?
Answer:
Optimization technique where queries are transformed into more efficient equivalents
without changing the result. Often done automatically by optimizers.
🔹 88. What is the difference between eager and lazy evaluation in DBMS?
Type Description
Eager Evaluation Executes subqueries immediately
Lazy Evaluation Delays execution until needed (more efficient in some
cases)
🔹 89. What is the difference between OLTP and OLAP databases in architecture?
Feature OLTP OLAP
Data Volume Low High
Transaction Read-write Read-only
Type
Structure Normalized De-normalized
Examples Banking Data
systems warehouses
🔹 90. What is a heap table in SQL Server/MySQL?
Answer:
A table that does not have a clustered index. Data is not stored in any particular order.
🔹 91. Explain redo and undo logs.
● Redo Log: Re-applies committed transactions (recovery).
● Undo Log: Rolls back uncommitted transactions.
🔹 92. What is a semi-join and anti-join?
● Semi-Join: Returns rows from the left table where a match exists in the right.
● Anti-Join: Returns rows from the left where no match exists.
sql
CopyEdit
-- Anti Join Example
SELECT * FROM A WHERE NOT EXISTS (SELECT 1 FROM B WHERE A.id =
B.id);
🔹 93. What is an execution engine in DBMS?
Answer:
The component that interprets and runs query plans generated by the optimizer. It
performs actual data retrieval and manipulation.
🔹 94. What is query pipelining?
Answer:
A method where output of one operator is fed directly as input to the next without
intermediate storage. Increases query performance.
🔹 95. What are integrity constraints and why are they important?
Answer:
Rules to ensure valid and consistent data, such as:
● Primary Key
● Foreign Key
● Check
● Unique
● Not Null
They enforce business logic at the database level.
🔹 96. What are LSM Trees and where are they used?
Answer:
Log-Structured Merge Trees are write-optimized data structures used in write-heavy
systems like Cassandra and LevelDB. They buffer writes in memory and flush them to disk.
🔹 97. What are the disadvantages of indexes?
● Slows down inserts/updates
● Takes additional disk space
● Needs maintenance
🔹 98. What is multi-version concurrency control (MVCC)?
Answer:
Technique to manage concurrent access by maintaining multiple versions of data. Used
in PostgreSQL and Oracle.
Prevents:
● Dirty reads
● Non-repeatable reads
🔹 99. What is cardinality in DBMS?
Answer:
Refers to the number of distinct values in a column. High cardinality = many unique
values (e.g., ID), Low cardinality = few (e.g., gender).
🔹 100. What is a foreign key with cascade delete?
Answer:
If a row in the parent table is deleted, the corresponding rows in the child table are also
deleted automatically.
sql
CopyEdit
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
