Database Management Systems (DBMS) are a crucial part of managing and organizing data efficiently. Whether you're preparing for an interview or looking to strengthen your database knowledge, these 50 top DBMS interview questions and answers will help you understand key concepts.

1.      What is DBMS?

A Database Management System (DBMS) is software that allows users to store, retrieve, and manage data efficiently. It ensures data security, consistency, and integrity while allowing multiple users to access data simultaneously. Examples include MySQL, PostgreSQL, MongoDB, and Oracle.

2.      What are the types of DBMS?

DBMS is categorized into four types:

• Hierarchical DBMS – Organizes data in a tree-like structure (e.g., IBM Information Management System).

• Network DBMS – Uses a graph structure to model relationships (e.g., IDMS).

• Relational DBMS (RDBMS) – Uses tables (relations) to store data (e.g., MySQL, PostgreSQL).

• Object-oriented DBMS (OODBMS) – Stores data as objects (e.g., db4o, ObjectDB).

3.      What is a relational database?

A relational database stores data in structured tables with rows and columns, where relationships between tables are established using primary keys and foreign keys.

4.      What is SQL?

SQL (Structured Query Language) is used to create, retrieve, update, and delete (CRUD) data in relational databases. It includes commands like SELECT, INSERT, UPDATE, DELETE, JOIN, and GROUP BY.

5.      What is the difference between DBMS and RDBMS?

6.      What is a primary key?

A primary key is a unique identifier for each record in a table. It must be unique and cannot contain NULL values.

7.      What is a foreign key?

A foreign key is a column in one table that references the primary key of another table, establishing a relationship between them.

8.      What is normalization in DBMS?

Normalization is the process of organizing data to eliminate redundancy and improve integrity. The different normal forms include:

• 1NF (First Normal Form) – Removes duplicate columns.

• 2NF (Second Normal Form) – Ensures all columns are dependent on the primary key.

• 3NF (Third Normal Form) – Removes transitive dependencies.

9.      What are the ACID properties in DBMS?

ACID stands for:

• Atomicity – Transactions are all-or-nothing.

• Consistency – Ensures valid database state before and after transactions.

• Isolation – Transactions do not interfere with each other.

• Durability – Changes remain permanent even after a system failure.

10.  What is indexing in DBMS?

Indexing improves query performance by creating data structures that enable faster searches. Types include:

• Clustered Index – Rearranges table records for faster access.

• Non-clustered Index – Stores index separately without affecting table order.

11.  What is the difference between DELETE and TRUNCATE ?

In SQL, DELETE and TRUNCATE differ primarily in how they eliminate data from a table. In addition to allowing the use of a WHERE clause to restrict which rows to delete, DELETE is used to remove individual entries. Because it is a logged operation, every deleted row is noted in the transaction log, enabling a rollback if necessary. In comparison, TRUNCATE is a minimally logged action that is much faster but irreversible because it eliminates every entry from a database without the need for a WHERE clause. Therefore, TRUNCATE is the greatest option for rapidly removing all data from a table without the need for a rollback, whereas DELETE is appropriate for selective and recoverable deletions.

12.  What are the advantages of using a DBMS?

A DBMS (Database Management System) helps manage data efficiently by ensuring data integrity, security, consistency, and easy access. It eliminates redundancy through normalization, supports multiple users, and provides backup and recovery features.

13.  What is data abstraction in DBMS?

Data abstraction refers to the process of hiding the complexity of database structures from users while exposing only necessary details. It has three levels: Physical level (storage details), Logical level (structure of data), and View level (user-specific representation).

14.  What is a database schema?

A database schema is the blueprint of a database that defines its structure, including tables, relationships, constraints, and indexes. It is a logical representation that does not contain actual data but describes how the data is organized.

15.  What is a database instance?

A database instance is the actual set of data stored in the database at a particular moment. While the schema remains constant, the database instance keeps changing as data is added, updated, or deleted.

16.  What are tuples and attributes in DBMS?

A tuple is a row (record) in a table, representing a single entity. An attribute is a column in a table, representing a property of an entity. For example, in an "Employees" table, "Employee_ID" is an attribute, and each employee's details form a tuple.

17.  What are the different types of SQL commands?

SQL commands are classified into:

• DDL (Data Definition Language) – CREATE, ALTER, DROP (modifies schema).

• DML (Data Manipulation Language) – INSERT, UPDATE, DELETE (modifies data).

• DCL (Data Control Language) – GRANT, REVOKE (controls access).

• TCL (Transaction Control Language) – COMMIT, ROLLBACK (manages transactions).

18.  What is the difference between HAVING and WHERE clauses?

• WHERE filters rows before aggregation and works with individual records.

• HAVING filters rows after aggregation and works with GROUP BY results.

19.  What is the difference between COUNT, SUM, AVG, MIN, and MAX?

These are aggregate functions in SQL:

• COUNT() – Returns the number of rows.

• SUM() – Adds up numerical values.

• AVG() – Calculates the average value.

• MIN() – Finds the smallest value.

• MAX() – Finds the largest value.

20.  What is a self-join in SQL?

A self-join is a join where a table is joined with itself using aliases. It is used to compare rows within the same table.

21.  What are the advantages of normalization?

Normalization eliminates data redundancy, ensures data consistency, and improves database efficiency. It divides large tables into smaller, related tables and establishes relationships, reducing anomalies like insertion, update, and deletion anomalies.

22.  Explain different normal forms up to BCNF.

• 1NF (First Normal Form) – Removes duplicate columns, ensures atomicity.

• 2NF (Second Normal Form) – Removes partial dependencies, ensuring all non-key attributes depend on the full primary key.

• 3NF (Third Normal Form) – Removes transitive dependencies, ensuring no non-key attribute depends on another non-key attribute.

• BCNF (Boyce-Codd Normal Form) – Ensures that every determinant is a candidate key, eliminating anomalies further.

23.  What is a transaction in DBMS?

A transaction is a logical unit of work in a database that consists of multiple operations (INSERT, UPDATE, DELETE). It follows ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliability and consistency.

24.  What is a dirty read in DBMS?

A dirty read occurs when one transaction reads uncommitted data from another transaction, which may later be rolled back. This can cause inconsistency.

25.  What is two-phase locking (2PL)?

Two-phase locking is a concurrency control mechanism with two phases:

• Growing phase – Transactions acquire locks but do not release them.

• Shrinking phase – Transactions release locks but do not acquire new ones.

  
  

  This prevents conflicts and ensures serializability.

  
  

26.  What is indexing, and why is it important?

Indexing improves query performance by creating a data structure (index) that allows faster retrieval of records. It reduces the number of disk accesses required for a query.

27.  What is the difference between a clustered and non-clustered index?

• Clustered Index – Sorts the actual data in the table (one per table).

• Non-clustered Index – Creates a separate structure pointing to data (multiple allowed).

28.  What is the difference between SQL and NoSQL databases?

SQL databases are relational, structured, and use a predefined schema, while NoSQL databases are non-relational, flexible, and store unstructured or semi-structured data.

29.  What is CAP theorem?

CAP theorem states that a distributed database can achieve only two out of three properties:

• Consistency – Every node has the same data.

• Availability – System remains operational.

• Partition Tolerance – The system works despite network failures.

• 
  

30.  What is OLAP, and how is it different from OLTP? 

• OLAP (Online Analytical Processing) is designed to analyze vast volumes of historical data and run sophisticated queries. It facilitates processes like trend analysis, forecasting, and reporting and is primarily utilized in data warehouses. Due to their read-heavy operating optimization, OLAP systems enable users, including business analysts and decision-makers, to analyze data in a variety of ways (e.g., sales by region, product, and time).

• OLTP (Online Transaction Processing), is utilized in daily company operations and is centered on managing transactional data in real-time. Numerous brief, straightforward transactions can be processed quickly and accurately thanks to its optimization for speedy insert, update, and delete operations. Applications where data consistency and speed are crucial, such as banking, retail, and airline reservation systems, frequently use OLTP systems.

31.  What is SQL injection, and how can it be prevented?

SQL injection is a security attack where malicious SQL code is injected into queries, allowing unauthorized access. It can be prevented using:

• Prepared statements instead of dynamic queries.

• Validating and sanitizing user input.

• Using least-privilege database access.

32.   What are the different types of databases?

Databases can be classified as relational (SQL), non-relational (NoSQL), hierarchical, network, object-oriented, and cloud databases. Relational databases use structured data, while NoSQL databases handle unstructured and semi-structured data.

33.  What is a foreign key?

A foreign key is an attribute in one table that refers to the primary key of another table. It enforces referential integrity by ensuring valid relationships between records.

34.  What is referential integrity?

Referential integrity ensures that a foreign key value in a child table always refers to an existing record in the parent table. It prevents orphan records and maintains consistency.

35.  What is the difference between a file system and a DBMS?

A file system stores data in individual files with no structured relationships, leading to redundancy. A DBMS organizes data efficiently, ensuring consistency, security, and easy retrieval through queries.

36.  What are candidate keys?

Candidate keys are attributes that can uniquely identify a record. One candidate key is chosen as the primary key, while others can be alternate keys.

37.  What is a super key?

A super key is any combination of attributes that uniquely identify a record. A primary key is a minimal super key with no unnecessary attributes.

38.  What is a composite key?

A composite key consists of two or more attributes that together uniquely identify a record, especially when a single attribute cannot be a unique identifier.

39.  What is functional dependency?

A functional dependency occurs when the value of one attribute uniquely determines another. For example, in a table of employees, Employee_ID → Employee_Name means each Employee_ID determines a unique Employee_Name.

40.  What is denormalization?

Denormalization is the process of combining tables to improve query performance by reducing joins. It increases redundancy but enhances read performance in large-scale databases.

41.  What are anomalies in DBMS?

Anomalies occur due to redundancy and poor database design. They include:

• Insertion anomaly – Inability to insert data due to missing values.

• Update anomaly – Redundant data requires multiple updates.

• Deletion anomaly – Deleting one record removes necessary data.

42.  What is the difference between 3NF and BCNF?

3NF removes transitive dependencies, ensuring that non-key attributes depend only on the primary key. BCNF (Boyce-Codd Normal Form) is stricter, ensuring that every determinant is a candidate key, eliminating further redundancy.

43.  What is an entity in DBMS?

An entity is an object with attributes that represent real-world things (e.g., a student, an employee). Entities are stored as records in tables.

44.  What is a deadlock in DBMS?

A deadlock occurs when two transactions wait for each other to release resources, causing a standstill. Deadlock prevention techniques include timeouts and resource ordering.

45.  What is a rollback in DBMS?

A rollback undoes all changes made by a transaction that encounters an error or failure, ensuring database consistency.

46.  What is a savepoint in DBMS?

A savepoint marks a specific point within a transaction, allowing partial rollbacks instead of restarting the entire transaction.

47.  What is serializability?

Serializability ensures that transactions execute in a way that produces the same result as if they were executed sequentially, maintaining consistency.

48.  What is the difference between an index and a view?

An index speeds up searches by creating a data structure, while a view is a virtual table based on query results without storing data separately.

49.  What are B-Trees and B+ Trees?

B-Trees and B+ Trees are used in indexing to organize and retrieve data efficiently. B+ Trees store all data in leaf nodes, while B-Trees store keys at multiple levels.

50.  What is the difference between a materialized view and a regular view?

A materialized view stores query results physically and updates periodically, while a regular view is just a saved SQL query that retrieves data dynamically.

Conclusion

Having a strong grasp of these DBMS interview questions will help you confidently tackle any interview, whether for a data analyst, database administrator, or software developer role.

Looking to enhance your database skills? Join Iota’s Courses and master SQL, NoSQL, and more! 🚀

 [dp1]This answer only contain a table explain it in para also

 [dp2]This is a imp question you can explain it more