Database Management System questions are asked in almost every technical interview because databases are used in nearly all software applications. Interviewers usually start with basic concepts and slowly move toward practical and scenario-based questions. If your answers sound natural and example-driven instead of memorized definitions, you immediately stand out from most freshers.

Table of Contents

---

Basic DBMS Interview Questions

1. What is DBMS?
A DBMS or Database Management System is software that helps users store, organize, retrieve, and manage data efficiently. It acts as an interface between users and databases so that data can be handled in a structured and secure way. Examples of DBMS include MySQL, Oracle, PostgreSQL, and SQL Server.

2. Why is DBMS preferred over file systems?

• A DBMS is preferred over traditional file systems because it reduces data duplication, improves security, supports multiple users, and makes data retrieval much faster.
• File systems become difficult to manage when the amount of data increases, while databases are designed to handle large amounts of information efficiently.

3. What is the difference between DBMS and RDBMS?
A DBMS stores data in files or tables, while an RDBMS stores data in related tables using relationships. RDBMS also supports constraints, normalization, and SQL. Examples of RDBMS are MySQL and PostgreSQL.

4. What is a database schema?
A database schema is the overall structure or blueprint of a database.

• It defines tables, columns, relationships, constraints, and data types.
• It describes how the database is organized.

5. What is a table in DBMS?
A table is a collection of related data organized into rows and columns. Each row represents a record, and each column represents a specific attribute of that record.
Example:

StudentID	Name	Marks
1	Rahul	89

6. What is a primary key?
A primary key is a column or group of columns that uniquely identifies each row in a table. It cannot contain duplicate or NULL values.
Example:

StudentID can be used as a primary key because every student has a unique ID.

7. What is a foreign key?
A foreign key is a column that creates a relationship between two tables. It refers to the primary key of another table and helps maintain data consistency.

8. What is normalization?
Normalization is the process of organizing data to reduce redundancy and improve consistency. It divides large tables into smaller related tables so that duplicate data can be minimized.

9. What is denormalization?
Denormalization is the process of combining tables to improve query performance. It increases redundancy intentionally so that data retrieval becomes faster in large applications.

10. What is SQL?
SQL or Structured Query Language is a programming language used to interact with databases. It is used to insert, update, delete, and retrieve data from tables.
Example:

SELECT * FROM Employees;

11. What is the difference between DELETE and TRUNCATE?
DELETE removes selected rows from a table and can be rolled back. TRUNCATE removes all rows from a table very quickly and usually cannot be rolled back in many databases.

12. What is the difference between CHAR and VARCHAR?
CHAR stores fixed length data, while VARCHAR stores variable length data. VARCHAR is more memory efficient because it uses only the required space.

13. What are constraints in DBMS?
Constraints are rules applied to database columns to maintain data accuracy and integrity.
Examples:

PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints.

14. What is indexing in DBMS?
Indexing is a technique used to improve the speed of data retrieval operations. An index works like the index page of a book where information can be found quickly without checking every page.

15. What is NULL in DBMS?
NULL represents missing, unknown, or undefined data. It does not mean zero or empty text.
Example:

If a student has not provided a phone number, that field may contain NULL.

16. What is a candidate key?
A candidate key is a column that can uniquely identify records in a table. A table can have multiple candidate keys, but only one is selected as the primary key.

17. What is a super key?
A super key is a set of one or more columns that uniquely identifies rows in a table. It may contain extra unnecessary columns in addition to unique columns.

18. What is a composite key?
A composite key is formed when two or more columns together uniquely identify a record.
Example:

 StudentID + SubjectID

19. What is a view in DBMS?
A view is a virtual table created from one or more tables. It does not store data separately and is mainly used for security and simplified access.

20. What are ACID properties?
ACID properties ensure reliable database transactions.

• Atomicity means all operations in a transaction happen completely or not at all.
• Consistency ensures data remains valid before and after transactions.
• Isolation prevents transactions from interfering with each other.
• Durability ensures committed data remains safe even after system failures.

Intermediate DBMS Interview Questions

21. What is the difference between WHERE and HAVING clauses?
The WHERE clause filters rows before grouping, while the HAVING clause filters grouped data after aggregation functions are applied.

22. What are joins in DBMS?
Joins are used to combine data from multiple tables based on related columns. Common joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

23. What is an INNER JOIN?
An INNER JOIN returns only matching records from both tables.
Example:

SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses
ON Students.CourseID = Courses.CourseID;

24. What is the purpose of GROUP BY?
GROUP BY is used to group rows having similar values so aggregate functions like COUNT or SUM can be applied.

25. What is a stored procedure?
A stored procedure is a collection of SQL statements stored in the database that can be executed repeatedly. It improves performance and reduces code duplication.

26. What is a trigger in DBMS?
A trigger is a set of SQL statements that automatically executes when events like INSERT, UPDATE, or DELETE occur in a table.

27. What is referential integrity?
Referential integrity ensures that relationships between tables remain consistent. It prevents invalid data from being inserted into related tables.

28. What is the difference between UNION and UNION ALL?
UNION removes duplicate rows from the result, while UNION ALL includes all rows including duplicates. UNION ALL is generally faster.

29. What is data redundancy?
Data redundancy means storing the same data in multiple places unnecessarily. It wastes storage and may create inconsistency problems.

30. What is a self join?
A self join is a join where a table is joined with itself. It is commonly used when records in the same table are related.

31. What is concurrency control?
Concurrency control manages simultaneous database operations so that multiple users can access data without causing inconsistency.

32. What is deadlock in DBMS?
Deadlock occurs when two or more transactions wait for each other indefinitely and none of them can proceed.

33. What is a cursor?
A cursor is a database object used to process records row by row instead of handling the entire result set at once.

34. What is the difference between clustered and non clustered indexes?
A clustered index stores actual table data in sorted order, while a non clustered index stores pointers to the actual data.

Only one clustered index is allowed per table.

35. What is transaction management?
Transaction management ensures that database operations are executed safely and reliably, especially during failures or multiple user access.

36. Why are indexes not always beneficial?
Indexes improve read performance, but they can slow down INSERT, UPDATE, and DELETE operations because indexes also need to be updated whenever data changes.

37. Why is normalization sometimes avoided in large applications?
Highly normalized databases may require many joins, which can reduce query performance. Large applications sometimes use denormalization to improve speed.

38. What is the difference between OLTP and OLAP?
OLTP systems handle daily transactions like banking or shopping, while OLAP systems are designed for analytics and reporting.

39. What happens if a primary key contains duplicate values?
A primary key cannot contain duplicate values. If duplicate values are inserted, the database throws an error because the uniqueness rule is violated.

40. Why do companies use indexing on frequently searched columns?
Companies use indexes on frequently searched columns because indexes reduce search time and improve query performance significantly.
Example:

Email columns are often indexed in user databases.

Advanced DBMS Interview Questions

41. What is database partitioning?
Database partitioning divides large tables into smaller parts so queries can run faster and maintenance becomes easier.

42. What is query optimization?
Query optimization is the process of improving SQL query performance so that results are retrieved faster using minimum resources.

43. What is sharding?
Sharding is a technique where database data is distributed across multiple servers to improve scalability and performance.

Large companies like Netflix and Amazon use sharding.

44. What is replication in DBMS?
Replication means copying database data from one server to another server to improve availability, backup, and fault tolerance.

45. What is database locking?
Database locking prevents multiple users from modifying the same data simultaneously, which helps maintain consistency.

46. What is an execution plan?
An execution plan shows how the database engine executes a query internally. Developers use it to identify slow operations and optimize queries.

47. What are isolation levels in DBMS?
Isolation levels define how transactions interact with each other during concurrent execution. Higher isolation improves consistency but may reduce performance.

48. What is a phantom read?
A phantom read occurs when a transaction retrieves different rows after another transaction inserts or deletes data during execution.

49. Why are SQL joins considered expensive operations?
SQL joins can become expensive because the database must compare rows from multiple tables. If tables are very large and indexes are missing, joins may slow down queries significantly.

This is a very practical interview question often asked in product based companies.

50. What is the most important DBMS concept for freshers to master?
The most important DBMS concepts for freshers are normalization, joins, indexing, SQL queries, and transactions because these topics are asked in almost every technical interview and are also used heavily in real projects.

Interview Tips for Freshers

Focus Areas	Common Mistakes Freshers Make	Best Way To Prepare
SQL Query Writing	Giving memorized definitions	Practice SQL daily
Joins and normalization	Explaining without examples	Build small database projects
Indexing concepts	Ignoring practical scenarios	Learn concepts with examples
Transactions and ACID properties	Confusing keys and joins	Solve interview questions regularly