DBMS Interview Questions and Answers: DBMS, or Database Management System, is a critical component of modern software applications. Whether you are a seasoned professional or a fresh graduate seeking employment, it is essential to have a good grasp of DBMS concepts and technologies. To help you prepare for your next interview, we have compiled a comprehensive list of the Top 100 DBMS Interview Questions and Answers, including the latest DBMS Interview Questions.
★★ Latest Technical Interview Questions ★★
DBMS Technical Interview Questions
This collection covers a wide range of DBMS Technical Interview Questions, including queries, normalization, indexing, transactions, and more. Additionally, we have included DBMS Interview Questions for Freshers to help you kick-start your career in the field. With this resource, you will be well-equipped to tackle any DBMS-related questions that come your way during your interview.
Top 100 DBMS Interview Questions and Answers 2023
1. What is a DBMS?
Answer: A Database Management System (DBMS) is a software system that enables users to create, define, maintain and control access to a database.
2. What is a database?
Answer: A database is a collection of data that is organized in a specific manner to facilitate easy retrieval and manipulation.
3. What is SQL?
Answer: SQL stands for Structured Query Language, which is used to communicate with databases.
4. Explain a few advantages of a DBMS.
- Data sharing: A DBMS allows multiple users to access the same data simultaneously, which enables easier data sharing and collaboration.
- Data security: A DBMS provides features for enforcing data security, such as access control, authentication, and encryption, which help to protect data from unauthorized access, modification, or deletion.
- Data integrity: A DBMS enforces data integrity constraints, such as primary key, foreign key, and check constraints, which ensure that the data in the database is accurate and consistent.
- Data consistency: A DBMS provides mechanisms for managing concurrent access to data, which helps to ensure that the data remains consistent even when multiple users are accessing it simultaneously.
- Data backup and recovery: A DBMS provides features for creating backups of the database, which can be used to recover data in the event of a system failure or disaster.
5. What are the different types of database models?
Answer: Hierarchical, Network, Relational, Object-Oriented, Object-Relational, and NoSQL.
6. What are the differences between a DBMS and RDBMS?
| DBMS | RDBMS |
| May use different data models such as hierarchical, network, or object-oriented | Based on the relational data model |
| Data is stored in files | Data is stored in tables |
| Supports a range of data types | Supports only predefined data types |
| Doesn’t enforce referential integrity | Enforces referential integrity |
| Doesn’t support transactions | Supports transactions |
| Doesn’t support SQL fully | Supports SQL fully |
| Scalability is limited | Scalable |
7. What is a primary key?
Answer: A primary key is a unique identifier for a row in a table.
8. What is a foreign key?
Answer: A foreign key is a field in a table that refers to the primary key of another table.
9. What is an index?
Answer: An index is a data structure that is used to speed up the retrieval of data from a database table.
10. What is a view?
Answer: A view is a virtual table that is based on the result of a SQL statement.
11. Explain different languages present in DBMS.
- SQL (Structured Query Language): SQL is a standard language used for managing relational databases. It is used for creating, modifying, and querying databases, as well as defining database structures and relationships.
- DDL (Data Definition Language): DDL is a subset of SQL used for creating and modifying database objects such as tables, views, indexes, and constraints.
- DML (Data Manipulation Language): DML is a subset of SQL used for adding, modifying, and deleting data in a database. The most common DML commands are INSERT, UPDATE, and DELETE.
- DCL (Data Control Language): DCL is a subset of SQL used for controlling access to the database. It includes commands such as GRANT and REVOKE, which are used to grant or revoke privileges to users or roles.
- TCL (Transaction Control Language): TCL is a subset of SQL used for managing database transactions. It includes commands such as COMMIT and ROLLBACK, which are used to control the transactional behavior of the database.
- PL/SQL (Procedural Language/Structured Query Language): PL/SQL is a procedural programming language used for creating stored procedures, functions, and triggers in an Oracle database.
12. What is a trigger?
Answer: A trigger is a set of SQL statements that are automatically executed in response to certain events or actions.
13. What is a stored procedure?
Answer: A stored procedure is a pre-compiled SQL code that can be executed repeatedly.
14. What do you understand by aggregation and atomicity?
| Aggregation | Atomicity |
| Aggregation is a process of combining multiple values into a single value, typically by using a mathematical function such as sum, count, or average | Atomicity is a property of database transactions that requires that they be executed as a single, indivisible unit of work |
| Aggregation is used to simplify complex data and generate summary statistics | Atomicity is used to ensure that transactions are either fully completed or fully rolled back if an error occurs |
| Aggregation is typically performed on large datasets | Atomicity is important in ensuring the consistency and integrity of the database |
15. What is ACID?
Answer: ACID is an acronym for Atomicity, Consistency, Isolation, and Durability, which are the four properties that guarantee the reliability of a database transaction.
16. What is Data Warehousing?
Answer: Data warehousing is a process of collecting, managing, and storing large amounts of data from different sources in a centralized location. The primary objective of data warehousing is to provide decision-makers with easy access to consolidated and accurate information that can be used for analysis, reporting, and data mining.
17. What is a deadlock?
Answer: A deadlock is a situation in which two or more transactions are waiting for each other to release a resource.
18. What is a backup?
Answer: A backup is a copy of a database that is made in case the original database is lost or corrupted.
19. What are the commands that comes under DDL?
- CREATE: This command is used to create a new database, table, or view in the DBMS.
Syntax for creating a new database:
| CREATE DATABASE database_name; |
Syntax for creating a new table:
| CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, … ); |
Syntax for creating a new view:
| CREATE VIEW view_name AS SELECT column1, column2, … FROM table_name WHERE condition; |
- ALTER: This command is used to modify the structure of an existing database, table, or view.
Syntax for adding a new column to an existing table:
| ALTER TABLE table_name ADD column_name datatype; |
Syntax for modifying the datatype of an existing column:
| ALTER TABLE table_name ALTER COLUMN column_name datatype; |
Syntax for renaming an existing table:
| ALTER TABLE table_name RENAME TO new_table_name; |
- DROP: This command is used to delete an existing database, table, or view from the DBMS.
Syntax for deleting a database:
| DROP DATABASE database_name; |
Syntax for deleting a table:
| DROP TABLE table_name; |
Syntax for deleting a view:
| DROP VIEW view_name; |
- TRUNCATE: This command is used to remove all data from an existing table without deleting the table structure.
Syntax for truncating a table:
| TRUNCATE TABLE table_name; |
- COMMENT: This command is used to add comments to the database objects such as tables, columns, and views.
Syntax for adding a comment to a table:
| COMMENT ON TABLE table_name IS ‘Comment goes here’; |
Syntax for adding a comment to a column:
| COMMENT ON COLUMN table_name.column_name IS ‘Comment goes here’; |
20. What is a database schema?
A database schema is the blueprint that describes the structure of a database, including tables, fields, and relationships.
21. What are the commands that comes under DML?
- INSERT: This command is used to insert new rows into a table in the DBMS.
Syntax for inserting a single row into a table:
| INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …); |
Syntax for inserting multiple rows into a table:
| INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …), (value1, value2, value3, …), (value1, value2, value3, …); |
- UPDATE: This command is used to modify the data in an existing row or set of rows in a table.
Syntax for updating a single row in a table:
| UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; |
Syntax for updating multiple rows in a table:
| UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; |
- DELETE: This command is used to delete an existing row or set of rows from a table.
Syntax for deleting a single row from a table:
| DELETE FROM table_name WHERE condition; |
Syntax for deleting multiple rows from a table:
| DELETE FROM table_name WHERE condition; |
- SELECT: This command is used to retrieve data from one or more tables in the DBMS.
Syntax for selecting all columns from a table:
| SELECT * FROM table_name; |
Syntax for selecting specific columns from a table:
| SELECT column1, column2, column3, … FROM table_name; |
Syntax for selecting data from multiple tables:
| SELECT column1, column2, column3, … FROM table1 JOIN table2 ON table1.column_name = table2.column_name; |
22. What are the differences between a SQL and NoSQL?
| SQL | NoSQL |
| Uses a relational data model based on tables and columns | Uses a variety of non-relational data models such as document, key-value, column-family, and graph |
| Data is stored in tables with a predefined schema | Data is stored in documents, key-value pairs, or other flexible formats without a predefined schema |
| Supports SQL (Structured Query Language) for querying and managing data | Supports a variety of query languages such as MongoDB Query Language, Couchbase N1QL, and Cassandra Query Language |
| Transactions are typically ACID-compliant | Transactions may not be fully ACID-compliant and may prioritize availability and scalability over consistency |
23. What are the commands that comes under DCL, TCL?
- GRANT: This command is used to grant specific privileges or permissions to a user or role.
Syntax for granting privileges to a user:
| GRANT privilege_name ON object_name TO user_name; |
Syntax for granting privileges to a role:
| GRANT privilege_name ON object_name TO role_name; |
- REVOKE: This command is used to revoke previously granted privileges or permissions from a user or role.
Syntax for revoking privileges from a user:
| REVOKE privilege_name ON object_name FROM user_name; |
Syntax for revoking privileges from a role:
| REVOKE privilege_name ON object_name FROM role_name; |
- COMMIT: This command is used to permanently save the changes made in the database.
Syntax for committing changes:
| COMMIT; |
- ROLLBACK: This command is used to undo changes made to the database since the last COMMIT.
Syntax for rolling back changes:
| ROLLBACK; |
- SAVEPOINT: This command is used to create a point in a transaction to which you can later roll back.
Syntax for creating a savepoint:
| SAVEPOINT savepoint_name; |
- SET TRANSACTION: This command is used to specify characteristics for a transaction, such as isolation level.
Syntax for setting a transaction:
| SET TRANSACTION [ISOLATION LEVEL level_name]; |
24. What is a transaction log?
Answer: A transaction log is a record of all the transactions that have occurred in a database.
25. What is a data dictionary?
Answer: A data dictionary is a collection of metadata that describes the data elements in a database.
26. Explain different levels of data abstraction in a DBMS.
Answer: A DBMS provides different levels of data abstraction to enable different types of users to interact with the database at different levels of complexity. Here are the three main levels of data abstraction in a DBMS:
- Physical level: The physical level is the lowest level of data abstraction, and it describes how data is stored in the database. It includes details such as the storage format, file organization, access methods, and disk allocation.
- Logical level: The logical level is the intermediate level of data abstraction, and it describes how data is organized and structured in the database. It includes the database schema, which defines the tables, columns, keys, relationships, and constraints that make up the database.
- View level: The view level is the highest level of data abstraction, and it describes how users perceive and interact with the data in the database. It includes the views, which are virtual tables that are derived from one or more base tables in the database. Views provide a customized and simplified view of the data that is tailored to the specific needs of different users or applications.
27. What is a schema?
Answer: A schema is a collection of database objects, including tables, views, and stored procedures.
28. What is the syntax for creating a table in a DBMS?
Answer: The basic syntax for creating a table in a DBMS is as follows:
| CREATE TABLE table_name ( column1 datatype1, column2 datatype2, column3 datatype3, ….. ); |
29. What is a clustered index?
Answer: A clustered index is an index that is used to sort and store the data in a table based on the values of one or more columns.
30. What is a non-clustered index?
Answer: A non-clustered index is an index that is used to speed up the retrieval of data from a table by creating a separate data structure that contains the index data.
31. How do you insert data into a table using DBMS syntax?
Answer: The basic syntax for inserting data into a table in a DBMS is as follows:
| INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …); |
32. What is a bitmap index?
Answer: A bitmap index is an index that uses a bitmap to represent the occurrence of a value in a table or column.
33. What is the syntax for updating data in a table using DBMS?
Answer: The basic syntax for updating data in a table using DBMS is as follows:
| UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition; |
34. What are the differences between a Primary Key and Foreign Key?
| Primary Key | Foreign Key |
| A primary key is a column or combination of columns in a table that uniquely identifies each row in the table | A foreign key is a column or combination of columns in one table that refers to the primary key in another table |
| A primary key must be unique and not null | A foreign key can have duplicate values and can be null |
| A primary key is used to enforce data integrity and ensure that each row is unique | A foreign key is used to establish relationships between tables |
| A table can have only one primary key | A table can have multiple foreign keys |
35. What is a data warehouse?
Answer: A data warehouse is a large, centralized repository of data that is used to support business intelligence activities.
36. How do you delete data from a table using DBMS syntax?
Answer: The basic syntax for deleting data from a table using DBMS is as follows:
| DELETE FROM table_name WHERE condition; |
37. What is data mining?
Answer: Data mining is the process of analyzing large data sets to discover patterns and relationships that can be used to make business decisions.
38. What is the syntax for creating a new database using DBMS?
Answer: The basic syntax for creating a new database using DBMS is as follows:
| CREATE DATABASE database_name; |
39. What is a OLAP cube?
Answer: A OLAP (Online Analytical Processing) cube is a multidimensional data structure that allows for fast and flexible data analysis.
40. What is a data mart?
Answer: A data mart is a subset of a data warehouse that is designed to serve a specific business function or department.
41. How do you select data from a table using DBMS syntax?
Answer: The basic syntax for selecting data from a table using DBMS is as follows:
| SELECT column1, column2, … FROM table_name WHERE condition; |
42. What are the differences between a Indexing and Partitioning?
| Indexing | Partitioning |
| Creates data structures that allow data to be retrieved more quickly | Divides a large database into smaller, more manageable sections |
| Improves query performance by reducing the amount of data that needs to be searched | Improves overall system performance by distributing data across multiple physical or logical partitions |
| Uses a variety of data structures such as B-trees, hash tables, and bitmap indexes | Uses a variety of techniques such as range partitioning, hash partitioning, and list partitioning |
| Can be applied to individual columns or sets of columns in a table | Can be applied to entire tables or subsets of tables based on specific criteria |
| Requires additional storage space to store the index structures | Requires additional processing overhead to manage the partitioning scheme |
| Is generally more effective for small to medium-sized datasets or for queries that only touch a small subset of the data | Is generally more effective for large datasets or for systems that need to handle a high volume of transactions or queries simultaneously |
43. What is a data model?
Answer: A data model is a representation of the data structures and relationships in a database.
44. What is the syntax for creating an index in a DBMS?
Answer: The basic syntax for creating an index in a DBMS is as follows:
| CREATE INDEX index_name ON table_name (column1, column2, …); |
45. What is a ER diagram?
Answer: An ER (Entity-Relationship) diagram is a visual representation of the relationships between entities in a database.
46. What is a data type?
Answer: A data type is a classification of data that specifies the type of data that can be stored in a column or variable.
47. What is the syntax for creating a trigger in a DBMS?
Answer: The basic syntax for creating a trigger in a DBMS is as follows:
| CREATE TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name FOR EACH ROW BEGIN … END; |
48. What is a constraint?
Answer: A constraint is a rule that is applied to a column or table to enforce data integrity.
49. What is a user-defined function?
Answer: A user-defined function is a custom function that can be used in SQL statements to perform specific tasks.
50. What is the syntax for creating a trigger in a DBMS?
Answer: The basic syntax for creating a trigger in a DBMS is as follows:
| CREATE TRIGGER trigger_name {BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON table_name FOR EACH ROW BEGIN … END; |
51. What is a scalar function?
Answer: A scalar function is a user-defined function that returns a single value.
52. What is a table-valued function?
Answer: A table-valued function is a user-defined function that returns a table as its result.
53. What is a collation?
Answer: A collation is a set of rules that determine how characters are sorted and compared in a database.
54. What are the differences between a ACID and BASE?
| ACID | BASE |
| Stands for Atomicity, Consistency, Isolation, and Durability | Stands for Basically Available, Soft State, Eventually Consistent |
| Emphasizes data consistency and transactional correctness | Emphasizes availability and partition tolerance |
| Requires that transactions be processed in a serialized manner to ensure data consistency | Allows for concurrent processing of transactions, which can result in data inconsistencies or conflicts that are eventually resolved |
| Provides strong data integrity guarantees, with transactions either fully succeeding or fully failing | Provides weaker data integrity guarantees, with transactions that may partially succeed or have delayed updates |
| Used in traditional relational databases and other systems where data consistency and accuracy are critical | Used in distributed systems and other large-scale applications where availability and scalability are more important than data consistency |
55. What is a blocking?
Answer: Blocking is a situation in which one transaction is preventing another transaction from accessing a resource.
56. What is a recovery?
Answer: Recovery is the process of restoring a database to a previous state after a failure or disaster.
57. How do you grant privileges to a user using DBMS syntax?
Answer: The basic syntax for granting privileges to a user using DBMS is as follows:
| GRANT privileges ON object TO user; |
58. What is a snapshot?
Answer: A snapshot is a copy of a database at a specific point in time.
59. What is a database backup strategy?
Answer: A database backup strategy is a plan for regularly backing up a database to ensure that data can be recovered in case of a failure or disaster.
60. What is a rollback?
Answer: A rollback is a SQL statement that undoes all changes made in a transaction and restores the database to its previous state.
61. What is database security?
Answer: Database security is the protection of data from unauthorized access, use, disclosure, disruption, modification, or destruction.
62. What is the syntax for dropping a table using DBMS?
Answer: The basic syntax for dropping a table using DBMS is as follows:
| DROP TABLE table_name; |
This will delete the entire table along with its data, so use it with caution.
63. What is SQL injection?
Answer: SQL injection is a type of cyber attack in which an attacker injects malicious SQL code into a database query in order to gain unauthorized access to data.
64. What is denormalization?
Answer: Denormalization is the process of adding redundant data to a database to improve performance.
65. What is indexing?
Answer: Indexing is the process of creating an index on a column or set of columns to improve query performance.
66. What is a composite index?
Answer: A composite index is an index that is created on multiple columns in a table.
67. What is a join?
Answer: A join is a SQL operation that combines rows from two or more tables based on a related column.
68. What is a left join?
Answer: A left join is a type of join that returns all rows from the left table and matching rows from the right table.
69. What is a right join?
Answer: A right join is a type of join that returns all rows from the right table and matching rows from the left table.
70. What is a full outer join?
Answer: A full outer join is a type of join that returns all rows from both tables, with NULL values in columns where there is no match.
71. What is a correlated subquery?
Answer: A correlated subquery is a subquery that is dependent on the outer query.
72. What is a commit?
Answer: A commit is a SQL statement that marks the end of a transaction and makes all changes permanent.
73. What is a checkpoint in DBMS?
Answer: The Checkpoint mechanism ensures the removal of previous logs from the system, and they are permanently stored in the storage disk. This process helps maintain the integrity of the database, ensuring that it conforms to the ACID properties.
There are two methods that aid in maintaining the ACID properties: maintaining a transaction log and shadow pages. When using a log-based recovery system, Checkpoints serve as a critical component. Checkpoints act as specific minimal points that the database engine can recover after a crash. Using the transaction log record, Checkpoints help recover all committed data up to the point of the crash, ensuring data integrity.
74. What is a savepoint?
Answer: A savepoint is a point within a transaction that can be used to roll back to a specific point in the transaction.
75. What is a materialized view?
Answer: A materialized view is a view that is stored as a physical table in the database.
76. What is a temporary table?
Answer: A temporary table is a table that is created and used for a specific purpose, and then automatically deleted.
77. What is a CTE?
Answer: A CTE (Common Table Expression) is a named temporary result set that is defined within a SQL statement.
78. What is a window function?
Answer: A window function is a type of function that operates on a set of rows within a table.
79. What is a ranking function?
Answer: A ranking function is a type of window function that assigns a rank or row number to each row in a table.
80. What is an aggregate function?
Answer: An aggregate function is a function that operates on a set of values and returns a single value, such as SUM, AVG, MIN, MAX, and COUNT.
81. What is a database instance?
Answer: A database instance is a copy of a database that is running on a server and is accessible to users.
82. What is a database engine?
Answer: A database engine is the software that manages the storage, retrieval, and modification of data in a database.
83. What is a query plan?
Answer: A query plan is a sequence of steps that a database engine uses to execute a SQL query.
84. What is a database index?
Answer: A database index is a data structure that is used to improve the speed of data retrieval operations in a database.
85. What is a database backup?
Answer: A database backup is a copy of a database that is created to protect against data loss in the event of a system failure or disaster.
86. What is a database restore?
Answer: A database restore is the process of restoring a database from a backup copy.
87. What is sharding?
Answer: Sharding is the process of splitting a large database into smaller, more manageable parts.
88. What is partitioning?
Answer: Partitioning is the process of dividing a table into smaller, more manageable parts.
89. What is the difference between a clustered and non-clustered index?
Answer: A clustered index determines the physical order of data in a table, while a non-clustered index does not.
90. What is the difference between a primary key and a unique key?
Answer: A primary key is a unique identifier for a row in a table, while a unique key ensures that a column or set of columns contains unique values.
91. What is the difference between an inner join and an outer join?
Answer: An inner join returns only the rows that have matching values in both tables, while an outer join returns all rows from one table and matching rows from the other table.
92. What is the difference between a full backup and a differential backup?
Answer: A full backup includes all data in a database, while a differential backup includes only the data that has changed since the last full backup.
93. What is a rollback?
Answer: A rollback is a SQL statement that undoes all changes made in a transaction and restores the database to its previous state.
94. What is normalization?
Answer: Normalization is the process of organizing data in a database to minimize data redundancy and dependency.
95. What is a transaction?
Answer: A transaction is a set of SQL statements that are executed as a single unit of work.
96. What is a subquery?
Answer: A subquery is a SQL query that is nested inside another SQL query.
97. What is the syntax for creating a user in a DBMS?
Answer: The basic syntax for creating a user in a DBMS is as follows:
| CREATE USER username IDENTIFIED BY password; |
98. What is a B-tree index?
Answer: A B-tree index is an index that is used to organize and sort data in a hierarchical structure.
99. What is a database maintenance plan?
Answer: A database maintenance plan is a set of tasks that are performed to ensure the health and performance of a database.
100. What is ETL?
Answer: ETL (Extract, Transform, Load) is a process used to extract data from multiple sources, transform the data to fit a specific schema, and load the data into a target database.
The Top 100 DBMS Interview Questions and Answers provide a comprehensive resource for professionals and freshers alike to ace their DBMS interviews. To gain further knowledge, be sure to follow us at freshersnow.com and stay up-to-date with the latest resources and information.