Database Management Systems (DBMS) play a crucial role in handling and managing data efficiently. If you’re preparing for a DBMS interview, Here are the Top 50 DBMS Interview Questions And Answers to help you ace your interview.
1. What is DBMS?
DBMS (Database Management System) is software that enables users to store, retrieve, and manage data efficiently.
2. What are the types of DBMS?
- Hierarchical DBMS
- Network DBMS
- Relational DBMS (RDBMS)
- Object-Oriented DBMS
3. What is the difference between DBMS and RDBMS?
DBMS manages data as files, while RDBMS stores data in tabular form with relationships using primary and foreign keys.
4. What are ACID properties in DBMS?
- Atomicity: Ensures complete transactions.
- Consistency: Maintains database integrity.
- Isolation: Ensures concurrent transactions do not interfere.
- Durability: Ensures committed data is never lost.
5. What is a primary key?
A primary key is a unique identifier for a table, ensuring that no duplicate values exist.
6. What is a foreign key?
A foreign key in one table references the primary key in another table to maintain relationships.
7. What is normalization?
Normalization is the process of organizing data to reduce redundancy and improve data integrity.
8. What are the different normal forms?
- 1NF (First Normal Form): Removes duplicate columns.
- 2NF (Second Normal Form): Ensures all attributes depend on the primary key.
- 3NF (Third Normal Form): Eliminates transitive dependencies.
- BCNF (Boyce-Codd Normal Form): Enhances 3NF by removing more anomalies.
9. What is denormalization?
Denormalization is the process of adding redundancy to improve read performance in a database.
10. What is an index in DBMS?
An index is a database object used to speed up data retrieval operations.
11. What are the types of indexes?
- Clustered Index
- Non-Clustered Index
- Unique Index
- Composite Index
12. What is a view in SQL?
A view is a virtual table based on a query that simplifies data access.
13. What is the difference between DELETE and TRUNCATE?
- DELETE removes specific records and allows rollback.
- TRUNCATE removes all records but cannot be rolled back.
14. What is a cursor in DBMS?
A cursor is a database object used to retrieve row-by-row data processing.
15. What are triggers in DBMS?
Triggers are automatic procedures executed before or after an event in the database.
16. What is the difference between UNION and JOIN?
- UNION combines result sets from multiple queries.
- JOIN merges data based on common columns between tables.
17. What are the types of JOINs?
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
- CROSS JOIN
18. What is a stored procedure?
A stored procedure is a precompiled SQL script stored in a database to perform a specific task.
19. What is the difference between WHERE and HAVING clause?
- WHERE filters records before aggregation.
- HAVING filters records after aggregation.
20. What is a subquery?
A subquery is a query inside another query used to retrieve data dynamically.
21. What is the EXISTS clause?
EXISTS checks whether a subquery returns any rows, returning true or false.
22. What is a deadlock in DBMS?
A deadlock occurs when two or more transactions hold resources and wait for each other to release them.
23. How to prevent deadlocks?
- Use appropriate locking mechanisms.
- Ensure consistent resource ordering.
- Implement timeout mechanisms.
24. What is data integrity?
Data integrity ensures the accuracy and consistency of stored data.
25. What is a transaction?
A transaction is a unit of work that follows ACID properties to maintain database consistency.
26. What are the types of transactions?
- Commit
- Rollback
- Savepoint
27. What is the difference between INNER JOIN and OUTER JOIN?
- INNER JOIN returns only matching records.
- OUTER JOIN returns all records from one table and matching records from another.
28. What is an SQL injection attack?
SQL injection is a hacking technique that manipulates SQL queries to gain unauthorized access to a database.
29. What is data replication?
Data replication is copying data from one location to another to improve availability.
30. What is sharding in DBMS?
Sharding is the process of distributing large databases across multiple servers for scalability.
31. What is indexing?
Indexing speeds up query execution by maintaining sorted records.
32. What is a materialized view?
A materialized view stores the result of a query physically for faster retrieval.
33. What is database partitioning?
Partitioning divides large tables into smaller, manageable parts for better performance.
34. What is a schema?
A schema is the logical structure of a database, defining tables, views, indexes, and procedures.
35. What is the difference between OLTP and OLAP?
- OLTP (Online Transaction Processing) handles real-time transactions.
- OLAP (Online Analytical Processing) is used for data analysis and reporting.
36. What are surrogate keys?
A surrogate key is a unique identifier, typically an auto-generated number.
37. What is an ETL process?
ETL (Extract, Transform, Load) is a data integration process that extracts data, transforms it, and loads it into a database.
38. What is the purpose of rollback?
Rollback undoes changes if a transaction fails.
39. What is the difference between CHAR and VARCHAR?
- CHAR has a fixed length.
- VARCHAR has a variable length.
40. What is database mirroring?
Database mirroring is maintaining a copy of a database to ensure high availability.
41-50: Advanced DBMS Concepts
These include NoSQL databases, distributed databases, CAP theorem, Big Data integration, cloud databases, and more.
Conclusion
Mastering these DBMS interview questions can help you confidently answer technical queries in job interviews. Keep practicing, and good luck with your DBMS interview!