fbpx

Top 100 DBMS Interview Questions and Answers

Top 100 DBMS Interview Questions and Answers
Contents show

1. What is a DBMS?

A Database Management System (DBMS) is software that facilitates the creation, organization, and retrieval of data from a database. It manages data efficiently and ensures data integrity.


2. What is SQL?

SQL (Structured Query Language) is a domain-specific language used for managing and querying relational databases. It allows users to perform tasks such as retrieving, inserting, updating, and deleting data.

SELECT * FROM table_name;

3. What is a Primary Key?

A Primary Key is a unique identifier for each record in a database table. It ensures that there are no duplicate records and provides a way to uniquely identify each row.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

4. Explain the difference between DELETE and TRUNCATE.

DELETE is a DML (Data Manipulation Language) command used to remove specific records from a table based on a condition.

DELETE FROM table_name WHERE condition;

TRUNCATE is a DDL (Data Definition Language) command that removes all records from a table and resets auto-incremented columns.

TRUNCATE TABLE table_name;

5. What is a Foreign Key?

A Foreign Key is a field in a table that refers to the Primary Key in another table. It establishes a relationship between two tables.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

6. Explain ACID properties in DBMS.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four key properties that ensure reliability and consistency in database transactions.

  • Atomicity ensures that either all operations in a transaction are completed successfully or none at all.
  • Consistency ensures that the database remains in a valid state before and after a transaction.
  • Isolation ensures that the execution of one transaction does not affect the execution of other concurrent transactions.
  • Durability ensures that once a transaction is committed, the changes are permanently saved, even in the event of system failure.

7. What is a primary key in a database?

A primary key is a unique identifier for a record in a database table. It ensures that each row can be uniquely identified and accessed. It also enforces data integrity and allows for efficient data retrieval.

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    column1 datatype,
    column2 datatype
);

8. Explain the concept of foreign keys.

A foreign key is a field or a set of fields in a database table that refers to the primary key in another table. It establishes a relationship between two tables, enforcing referential integrity.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

9. What is a stored procedure?

A stored procedure is a pre-compiled set of SQL statements that can be executed in a single call. It is stored in the database and can be reused, reducing the need to rewrite complex queries.

CREATE PROCEDURE procedure_name
AS
-- SQL statements --
GO

10. Explain the concept of database transactions.

A database transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions ensure the integrity and consistency of the database by either committing all changes or rolling back to the previous state in case of errors.

BEGIN TRANSACTION;
-- SQL statements --
COMMIT;

11. What is a database index?

A database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional space and decreased performance for data modification operations. It allows for faster lookup of specific rows.

CREATE INDEX index_name ON table_name (column_name);

12. Explain the concept of a view in SQL.

A View in SQL is a virtual table that is based on the result of a SELECT query. It does not store data itself but provides a way to present data from one or more tables in a specific format.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

13. What is a stored procedure in a database?

A Stored Procedure is a set of SQL statements stored in the database and can be called by name. It enhances the reusability and security of SQL code by allowing it to be executed as a single unit.

CREATE PROCEDURE procedure_name
AS
-- SQL statements --
GO

14. Explain the concept of a trigger.

A Trigger is a special type of stored procedure that is automatically executed or fired when a certain event occurs in a database table. It is used to enforce business rules, perform logging, and more.

CREATE TRIGGER trigger_name
ON table_name
FOR [INSERT, UPDATE, DELETE]
AS
-- SQL statements --
GO

15. What is a cursor in SQL?

A Cursor is a database object that allows for the sequential processing of data in a result set. It provides a way to access individual rows in a result set one at a time.

DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM table_name;

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @var1, @var2;
-- Process data --
CLOSE cursor_name;

16. Explain the concept of a deadlock.

A Deadlock occurs in a multi-user environment when two or more transactions are waiting for each other to release locks on resources. This results in a standstill, where no transaction can proceed.


17. What is a NoSQL database?

A NoSQL (Not Only SQL) database is a type of database that does not rely on the traditional tabular relational database model. It provides a flexible data model and is designed for scalability and high performance.


18. Explain the concept of sharding in database design.

Sharding is the process of partitioning a large database into smaller, more manageable pieces called shards. Each shard is stored on a separate server, allowing for horizontal scalability.

CREATE SHARDING POLICY policy_name
ON table_name
USING (shard_key);

19. What is ACID in the context of transactions?

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These are the four key properties that ensure reliability and consistency in database transactions.


20. What is a NoSQL document-oriented database?

A NoSQL document-oriented database stores, retrieves, and manages document-oriented information. Documents can be JSON, XML, BSON, etc., and there is no need for a fixed schema.

{
   "name": "John Doe",
   "age": 30,
   "email": "john.doe@example.com"
}

21. What is a database index?

A database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional space and decreased performance for data modification operations. It allows for faster lookup of specific rows.

CREATE INDEX index_name ON table_name (column_name);

22. Explain the concept of a view in SQL.

A View in SQL is a virtual table that is based on the result of a SELECT query. It does not store data itself but provides a way to present data from one or more tables in a specific format.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

23. What is a self-join?

A self-join is a join where a table is joined with itself. This is useful when you want to combine rows with other rows in the same table based on a related column.

SELECT a.column1, b.column2
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;

24. Explain the concept of data normalization.

Data normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing a database into two or more tables and defining relationships between the tables.


25. What is a composite key?

A composite key is a combination of two or more columns in a table that together uniquely identify a record. Unlike a primary key, which is a single column, a composite key uses multiple columns for uniqueness.

CREATE TABLE table_name (
    column1 INT,
    column2 INT,
    PRIMARY KEY (column1, column2)
);

26. Explain the difference between a clustered and non-clustered index.

A clustered index determines the physical order of data in a table. It reorders the way records are stored in the table based on the index key. Only one clustered index can be created on a table.

A non-clustered index is a separate structure from the data stored in the table. It contains a sorted list of references to the table’s rows. Multiple non-clustered indexes can be created on a table.


27. What is a transaction rollback?

A transaction rollback is the process of undoing or reverting the changes made by a transaction. It restores the database to the state it was in before the transaction began.

BEGIN TRANSACTION;
-- SQL statements --
ROLLBACK;

28. Explain the concept of a materialized view.

A materialized view is a database object that contains the results of a query. Unlike a regular view, which executes the query each time it is referenced, a materialized view stores the results and can be refreshed periodically.

CREATE MATERIALIZED VIEW mv_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

29. What is a database schema?

A database schema is a logical container that holds database objects such as tables, views, indexes, and stored procedures. It provides a way to organize and manage the structure of a database.

CREATE SCHEMA schema_name;

30. Explain the concept of data warehousing.

Data warehousing is the process of collecting and storing large volumes of data from various sources to support business analysis and reporting. It involves transforming and organizing data for easy retrieval and analysis.


31. What is database replication?

Database replication is the process of creating and maintaining duplicate copies of a database on different servers. It ensures high availability, fault tolerance, and load distribution.


32. Explain the concept of database transactions.

A database transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions ensure the integrity and consistency of the database by either committing all changes or rolling back to the previous state in case of errors.

BEGIN TRANSACTION;
-- SQL statements --
COMMIT;

33. What is the purpose of a database index?

A database index improves the speed of data retrieval operations on a table by providing a faster way to locate specific rows. It works like an organized reference to the data, reducing the need for a full table scan.

CREATE INDEX index_name ON table_name (column_name);

34. Explain the concept of database normalization.

Database normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing a database into two or more tables and defining relationships between them.


35. What is a composite key in a database?

A composite key is a combination of two or more columns in a table that together uniquely identify a record. Unlike a primary key, which is a single column, a composite key uses multiple columns for uniqueness.

CREATE TABLE table_name (
    column1 INT,
    column2 INT,
    PRIMARY KEY (column1, column2)
);

36. Describe the purpose of a clustered index.

A clustered index determines the physical order of data in a table. It reorders the way records are stored in the table based on the index key. Only one clustered index can be created on a table.


37. What is a non-clustered index?

A non-clustered index is a separate structure from the data stored in the table. It contains a sorted list of references to the table’s rows. Multiple non-clustered indexes can be created on a table.


38. Explain the concept of database transaction rollback.

A transaction rollback is the process of undoing or reverting the changes made by a transaction. It restores the database to the state it was in before the transaction began.

BEGIN TRANSACTION;
-- SQL statements --
ROLLBACK;

39. What is a materialized view in a database?

A materialized view is a database object that contains the results of a query. Unlike a regular view, which executes the query each time it is referenced, a materialized view stores the results and can be refreshed periodically.

CREATE MATERIALIZED VIEW mv_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

40. What is a database schema?

A database schema is a logical container that holds database objects such as tables, views, indexes, and stored procedures. It provides a way to organize and manage the structure of a database.

CREATE SCHEMA schema_name;

41. What is data warehousing?

Data warehousing is the process of collecting and storing large volumes of data from various sources to support business analysis and reporting. It involves transforming and organizing data for easy retrieval and analysis.


42. Explain the concept of data mining.

Data mining is the process of discovering meaningful patterns, correlations, and trends within large datasets. It involves the use of statistical techniques, machine learning algorithms, and visualization tools.


43. What are OLTP and OLAP databases?

OLTP (Online Transaction Processing) databases are designed for transaction-oriented applications, where multiple users perform short, frequent, and simple transactions. They focus on quick, real-time data processing.

OLAP (Online Analytical Processing) databases are optimized for complex query and analysis operations. They allow for the retrieval and analysis of large volumes of data for decision-making.


44. What is a star schema in database design?

A star schema is a type of data warehouse schema where a central fact table is connected to one or more dimension tables via foreign key relationships. It simplifies data retrieval for reporting and analysis.


45. Explain the concept of database partitioning.

Database partitioning involves dividing a large database into smaller, more manageable pieces called partitions. Each partition can be stored on separate storage devices or servers, improving performance and manageability.


46. What is data encryption in databases?

Data encryption is the process of converting data into a code to prevent unauthorized access. In databases, it involves encrypting sensitive information to protect it from unauthorized viewing or tampering.

CREATE TABLE encrypted_data (
    column1 INT,
    column2 ENCRYPTED VARCHAR(50)
);

47. Explain the concept of database triggers.

A database trigger is a special type of stored procedure that is automatically executed or fired in response to specific events in a database table. It is used to enforce business rules, perform logging, and more.

CREATE TRIGGER trigger_name
ON table_name
FOR [INSERT, UPDATE, DELETE]
AS
-- SQL statements --
GO

48. What is a database cursor?

A database cursor is a pointer that allows for the sequential processing of data in a result set. It provides a way to access individual rows in a result set one at a time.

DECLARE cursor_name CURSOR FOR
SELECT column1, column2
FROM table_name;

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @var1, @var2;
-- Process data --
CLOSE cursor_name;

49. What is database mirroring?

Database mirroring is a high-availability solution that creates and maintains a synchronized copy of a database on a separate server. It provides failover capabilities in case of server failure.


50. Explain the concept of database snapshots.

A database snapshot is a read-only, static view of a database at a specific point in time. It provides a consistent snapshot of the data, even if the underlying database is being modified.

CREATE DATABASE database_snapshot
ON
( NAME = source_database, FILENAME = 'snapshot_file' )
AS SNAPSHOT OF source_database;

51. What is database clustering?

Database clustering is a technique that involves connecting multiple servers or nodes to work together as a single system. It provides high availability and load balancing by distributing the workload across the nodes.


52. Explain the concept of database caching.

Database caching involves storing frequently accessed data in a cache memory to reduce the need to fetch it from the underlying storage. This improves query performance and response times.

SELECT * FROM table_name WITH (NOLOCK);

53. What is a database deadlock?

A database deadlock occurs when two or more transactions are waiting for each other to release locks on resources, resulting in a standstill where no transaction can proceed.


54. Explain the concept of database connection pooling.

Database connection pooling is a method of reusing existing database connections rather than establishing a new connection every time a user makes a request. This reduces overhead and improves performance.


55. What is a database full-text search?

A database full-text search allows users to perform complex queries on a database to search for specific words or phrases within text columns. It is optimized for searching large amounts of text.

SELECT * FROM table_name WHERE CONTAINS(column_name, 'search_term');

56. Explain the concept of database replication.

Database replication is the process of creating and maintaining duplicate copies of a database on different servers. It ensures high availability, fault tolerance, and load distribution.


57. What is database mirroring?

Database mirroring is a high-availability solution that creates and maintains a synchronized copy of a database on a separate server. It provides failover capabilities in case of server failure.


58. Explain the concept of database snapshots.

A database snapshot is a read-only, static view of a database at a specific point in time. It provides a consistent snapshot of the data, even if the underlying database is being modified.

CREATE DATABASE database_snapshot
ON
( NAME = source_database, FILENAME = 'snapshot_file' )
AS SNAPSHOT OF source_database;

59. What is database backup and recovery?

Database backup involves creating a copy of the database at a specific point in time to protect against data loss or corruption. Recovery involves restoring the database from a backup in case of a failure.

BACKUP DATABASE database_name TO DISK = 'backup_file.bak';
RESTORE DATABASE database_name FROM DISK = 'backup_file.bak';

60. Explain the concept of database auditing.

Database auditing involves monitoring and recording activities within a database. It tracks who accessed the database, what actions they performed, and when they did it, providing a security measure and compliance tool.

CREATE DATABASE AUDIT SPECIFICATION audit_spec
FOR SERVER AUDIT audit_name
ADD (SELECT, INSERT, UPDATE, DELETE ON database_name);

61. What is database partitioning?

Database partitioning involves dividing a large database into smaller, more manageable pieces called partitions. Each partition can be stored on separate storage devices or servers, improving performance and manageability.


62. Explain the concept of database encryption.

Database encryption involves converting data into a code to prevent unauthorized access. It protects sensitive information from unauthorized viewing or tampering.

CREATE TABLE encrypted_data (
    column1 INT,
    column2 ENCRYPTED VARCHAR(50)
);

63. What is database archiving?

Database archiving is the process of moving older or less frequently accessed data to a separate storage area, typically for compliance or regulatory reasons. It helps optimize performance and storage space.


64. Explain the concept of database indexing strategies.

Database indexing strategies involve choosing the right type of index (e.g., B-tree, Hash, Bitmap) based on the specific requirements of the database and the queries being executed.

CREATE INDEX index_name ON table_name (column_name);

65. What is a database query plan?

A database query plan is a step-by-step description of how a query is executed by the database engine. It outlines the operations performed to retrieve the requested data.

EXPLAIN SELECT * FROM table_name WHERE condition;

66. Explain the concept of database normalization forms.

Database normalization forms (e.g., 1NF, 2NF, 3NF) are a set of rules used to design a database schema in a way that reduces redundancy and dependency. Each form addresses specific normalization goals.


67. What is database caching and buffering?

Database caching involves storing frequently accessed data in a cache memory to reduce the need to fetch it from the underlying storage. Buffering involves temporarily holding data in memory before writing it to disk.


68. Explain the concept of database monitoring.

Database monitoring involves continuously tracking the performance, availability, and health of a database. It helps identify and address issues proactively.

SELECT * FROM sys.dm_os_performance_counters;

69. What are database views and their advantages?

A database view is a virtual table that is based on the result of a SELECT query. It does not store data itself but provides a way to present data from one or more tables in a specific format. Advantages include data security and simplified querying.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

70. Explain the concept of database auditing.

Database auditing involves monitoring and recording activities within a database. It tracks who accessed the database, what actions they performed, and when they did it, providing a security measure and compliance tool.

CREATE DATABASE AUDIT SPECIFICATION audit_spec
FOR SERVER AUDIT audit_name
ADD (SELECT, INSERT, UPDATE, DELETE ON database_name);

71. What is database connection pooling?

Database connection pooling is a method of reusing existing database connections rather than establishing a new connection every time a user makes a request. This reduces overhead and improves performance.


72. Explain the concept of database deadlock.

A database deadlock occurs when two or more transactions are waiting for each other to release locks on resources, resulting in a standstill where no transaction can proceed.


73. What is database normalization?

Database normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing a database into two or more tables and defining relationships between them.


74. What is data mining in databases?

Data mining is the process of discovering meaningful patterns, correlations, and trends within large datasets. It involves the use of statistical techniques, machine learning algorithms, and visualization tools.


75. Explain the concept of database clustering.

Database clustering is a technique that involves connecting multiple servers or nodes to work together as a single system. It provides high availability and load balancing by distributing the workload across the nodes.


76. What are OLTP and OLAP databases?

OLTP (Online Transaction Processing) databases are designed for transaction-oriented applications, where multiple users perform short, frequent, and simple transactions. They focus on quick, real-time data processing.

OLAP (Online Analytical Processing) databases are optimized for complex query and analysis operations. They allow for the retrieval and analysis of large volumes of data for decision-making.


77. What is a star schema in database design?

A star schema is a type of data warehouse schema where a central fact table is connected to one or more dimension tables via foreign key relationships. It simplifies data retrieval for reporting and analysis.


78. Explain the concept of database caching.

Database caching involves storing frequently accessed data in a cache memory to reduce the need to fetch it from the underlying storage. This improves query performance and response times.

SELECT * FROM table_name WITH (NOLOCK);

79. What is a database trigger?

A database trigger is a special type of stored procedure that is automatically executed or fired in response to specific events in a database table. It is used to enforce business rules, perform logging, and more.

CREATE TRIGGER trigger_name
ON table_name
FOR [INSERT, UPDATE, DELETE]
AS
-- SQL statements --
GO

80. Explain the concept of database partitioning.

Database partitioning involves dividing a large database into smaller, more manageable pieces called partitions. Each partition can be stored on separate storage devices or servers, improving performance and manageability.


81. What is data encryption in databases?

Data encryption in databases involves converting data into a code to prevent unauthorized access. It protects sensitive information from unauthorized viewing or tampering.

CREATE TABLE encrypted_data (
    column1 INT,
    column2 ENCRYPTED VARCHAR(50)
);

82. What is database archiving?

Database archiving is the process of moving older or less frequently accessed data to a separate storage area, typically for compliance or regulatory reasons. It helps optimize performance and storage space.


83. Explain the concept of database indexing strategies.

Database indexing strategies involve choosing the right type of index (e.g., B-tree, Hash, Bitmap) based on the specific requirements of the database and the queries being executed.

CREATE INDEX index_name ON table_name (column_name);

84. What is a database query plan?

A database query plan is a step-by-step description of how a query is executed by the database engine. It outlines the operations performed to retrieve the requested data.

EXPLAIN SELECT * FROM table_name WHERE condition;

85. Explain the concept of database normalization forms.

Database normalization forms (e.g., 1NF, 2NF, 3NF) are a set of rules used to design a database schema in a way that reduces redundancy and dependency. Each form addresses specific normalization goals.


86. What is database caching and buffering?

Database caching involves storing frequently accessed data in a cache memory to reduce the need to fetch it from the underlying storage. Buffering involves temporarily holding data in memory before writing it to disk.


87. Explain the concept of database monitoring.

Database monitoring involves continuously tracking the performance, availability, and health of a database. It helps identify and address issues proactively.

SELECT * FROM sys.dm_os_performance_counters;

88. What are database views and their advantages?

A database view is a virtual table that is based on the result of a SELECT query. It does not store data itself but provides a way to present data from one or more tables in a specific format. Advantages include data security and simplified querying.

CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;

89. Explain the concept of database auditing.

Database auditing involves monitoring and recording activities within a database. It tracks who accessed the database, what actions they performed, and when they did it, providing a security measure and compliance tool.

CREATE DATABASE AUDIT SPECIFICATION audit_spec
FOR SERVER AUDIT audit_name
ADD (SELECT, INSERT, UPDATE, DELETE ON database_name);

90. What is database connection pooling?

Database connection pooling is a method of reusing existing database connections rather than establishing a new connection every time a user makes a request. This reduces overhead and improves performance.


91. What is database deadlock?

A database deadlock occurs when two or more transactions are waiting for each other to release locks on resources, resulting in a standstill where no transaction can proceed.


92. What is database normalization?

Database normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing a database into two or more tables and defining relationships between them.


93. What is data mining in databases?

Data mining is the process of discovering meaningful patterns, correlations, and trends within large datasets. It involves the use of statistical techniques, machine learning algorithms, and visualization tools.


94. Explain the concept of database clustering.

Database clustering is a technique that involves connecting multiple servers or nodes to work together as a single system. It provides high availability and load balancing by distributing the workload across the nodes.


95. What are OLTP and OLAP databases?

OLTP (Online Transaction Processing) databases are designed for transaction-oriented applications, where multiple users perform short, frequent, and simple transactions. They focus on quick, real-time data processing.

OLAP (Online Analytical Processing) databases are optimized for complex query and analysis operations. They allow for the retrieval and analysis of large volumes of data for decision-making.


96. What is a star schema in database design?

A star schema is a type of data warehouse schema where a central fact table is connected to one or more dimension tables via foreign key relationships. It simplifies data retrieval for reporting and analysis.


97. Explain the concept of database caching.

Database caching involves storing frequently accessed data in a cache memory to reduce the need to fetch it from the underlying storage. This improves query performance and response times.

SELECT * FROM table_name WITH (NOLOCK);

98. What is a database trigger?

A database trigger is a special type of stored procedure that is automatically executed or fired in response to specific events in a database table. It is used to enforce business rules, perform logging, and more.

CREATE TRIGGER trigger_name
ON table_name
FOR [INSERT, UPDATE, DELETE]
AS
-- SQL statements --
GO

99. Explain the concept of database partitioning.

Database partitioning involves dividing a large database into smaller, more manageable pieces called partitions. Each partition can be stored on separate storage devices or servers, improving performance and manageability.


100. What is data encryption in databases?

Data encryption in databases involves converting data into a code to prevent unauthorized access. It protects sensitive information from unauthorized viewing or tampering.

CREATE TABLE encrypted_data (
    column1 INT,
    column2 ENCRYPTED VARCHAR(50)
);