Top 30 Commonly Asked DBMS Interview Questions in 2023

Are you preparing for a DBMS interview in 2023? Look no further!

Our comprehensive guide features the commonly asked DBMS interview questions. This is covering everything from basic concepts to advanced topics.

Impress your interviewer and boost your chances of landing your dream job in database management with our expertly curated list of questions. Don’t miss out on the opportunity to enhance your knowledge and confidence with our valuable resource.

Start preparing now and ace your DBMS interview!

DBMS Interview Questions

1. What is a DBMS?

DBMS stands for Database Management System. It is a software system that allows users to manage and organize data in a database. A DBMS enables users to create, update, retrieve and manage data in a database.

It provides an interface between the end-user and the database, allowing users to interact with the database without needing to know the underlying technical details.

A DBMS manages data by organizing it into tables, fields, and records, which can be easily accessed and manipulated.

Examples of popular DBMS include Oracle, MySQL, Microsoft SQL Server, PostgreSQL, and MongoDB.

2. What is a Database?

A Database is an organized, consistent, and logical collection of data that can easily be updated, accessed, and managed.

Database mostly contains sets of tables or objects (anything created using create command is a database object) which consist of records and fields.

Databases can be relational or non-relational. In a relational database, data is organized into tables, and the relationships between the tables are defined using keys. In a non-relational database, data is stored in a more flexible and unstructured format, such as a document or a graph.

3. What is a database system?

A database system is a software system that is designed to manage large amounts of data in an organized and efficient manner. It is a combination of software and hardware components that work together to store, retrieve, and manage data.

A database system consists of four main components:

Data: This is the information that is stored in the database, organized into tables or other structures.
Software: This includes the database management system (DBMS) software, which manages the storage, retrieval, and organization of data.
Hardware: This includes the physical devices such as servers, hard drives, and memory that are used to store and access the data.
Users: These are the people or applications that interact with the database system, including data entry, querying, and reporting.

4. What are advantages of DBMS?

Here are some advantages of using a DBMS:

Data Centralization: A DBMS allows multiple users to access and modify data simultaneously. This centralizes the data and eliminates the need for maintaining separate copies of data.
Data Consistency: A DBMS enforces rules and constraints on the data, ensuring that the data remains consistent and accurate. For example, a DBMS can ensure that a customer’s email address is unique, ensuring that there are no duplicate email addresses in the database.
Data Security: A DBMS provides security features such as access control, authentication, and authorization. Which helps in protecting data from unauthorized access, modification, or deletion.
Improved Data Integrity: A DBMS provides data validation and consistency checks, ensuring that data is accurate, complete, and up-to-date.
Improved Productivity: A DBMS provides features such as data querying, indexing, and optimization, which makes it easier and faster to access and retrieve data. This can improve productivity by reducing the time required to perform common data-related tasks.
Data Backup and Recovery: A DBMS provides backup and recovery mechanisms that allow users to recover data in case of accidental data loss or corruption.
Scalability: A DBMS can handle large amounts of data and can be easily scaled up or down to accommodate changing data requirements.

Overall, a DBMS provides a secure, centralized, and organized way to manage data, which helps in improving data quality, productivity, and decision-making.

Also read the “Top 50+ commonly asked .NET interview questions“

5. What is difference between DBMS and RDBMS?

Here are the main differences between a DBMS and an RDBMS:

DBMS	RDBMS
Manages data in various formats, including relational and non-relational	Manages data using a relational model
Data can be organized in various ways, such as hierarchical, network, or object-oriented models	Data is organized into tables, and relationships between tables are defined using keys
No need for a specific schema	Enforces a specific schema
No support for complex queries	Supports complex queries
No support for transactions	Supports transactions
Limited concurrency control	Robust concurrency control
Limited data integrity constraints	Enforces integrity constraints such as data types, unique constraints, and referential integrity
Examples: Microsoft Access, FileMaker, dBASE	Examples: Oracle, MySQL, Microsoft SQL Server, PostgreSQL

6. What is a DBA, and what are their responsibilities?

A DBA (Database Administrator) is a professional who is responsible for the installation, configuration, and maintenance of a DBMS. The role of a DBA can vary depending on the organization, but some of their typical responsibilities include:

Installing and configuring database software: Setting up the database management system on a server, including configuring options and parameters to optimize performance.
Monitoring and tuning database performance: A DBA monitors database performance, identifying and resolving issues related to slow queries, long response times, and other performance issues.
Ensuring data security: A DBA ensures that data is secure and protected from unauthorized access or loss, by implementing security measures such as user authentication, access controls, and backup and recovery procedures.
Backing up and restoring data: A DBA is responsible for backing up the database and ensuring that data can be restored in case of a disaster or hardware failure.
Creating and maintaining database documentation: A DBA creates and maintains documentation related to the database, including schema diagrams, data dictionaries, and operating procedures.
Managing database users and access: A DBA manages user accounts and privileges, ensuring that users have appropriate levels of access to the database.
Planning for future growth: A DBA plans for future growth of the database, by forecasting usage trends and capacity requirements, and making recommendations for hardware upgrades or software changes.

DBA plays a critical role in the successful operation of a database management system. Their responsibilities are vital to ensuring the availability, reliability, and security of the data that is stored in the system.

7. What are the different types of database models?

There are several types of database models, each with its own way of organizing data. Here are some of the most common types:

Hierarchical Model: Data is organized in a tree-like structure, with each record having a single parent and multiple children. This model is suitable for representing data with a strict parent-child relationship, such as an organizational chart.
Network Model: Data is organized in a more flexible graph structure, with each record having one or more parent and child records. This model is suitable for representing complex data relationships.
Relational Model: Data is organized into tables, and relationships between tables are defined using keys. This is the most widely used model for organizing data, as it provides a flexible and efficient way to manage large amounts of data.
Object-Oriented Model: Data is organized into objects, which can contain attributes and methods. This model is suitable for representing complex real-world objects, such as cars or buildings.
Document Model: Data is organized into semi-structured documents, such as JSON or XML files. This model is suitable for storing and managing data that is frequently changing and has no fixed schema.
Graph Model: Data is organized into nodes and edges, which represent entities and relationships between them. This model is suitable for representing complex, interconnected data, such as social networks or recommendation systems.

Each type of database model has its own strengths and weaknesses, and the choice of model depends on the specific requirements of the application.

8. How do you communicate with an RDBMS?

It would be best if you used Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database, and then after processing the queries database will provide us with the required output.

You can also communicate with an RDBMS by using programming languages such as Java, Python, or C#. Which provide libraries or APIs to connect to and interact with the database.

9. What is the purpose of SQL?

Structured Query Language, commonly known as SQL, is primarily used to interact with relational databases by inserting, updating, and modifying data stored in the database.

10. How many types of database languages are?

There are four types of database languages:

Data Definition Language (DDL): This type of language is used to define the database structure, including creating, altering, and dropping tables, indexes, and other database objects.
- Example: CREATE, ALTER, DROP, TRUNCATE, RENAME, etc.
Data Manipulation Language (DML): This type of language is used to manage data within the database, including inserting, updating, deleting, and querying data.
- Example: SELECT, UPDATE, INSERT, DELETE, etc.
Data Control Language (DCL): This type of language is used to control access to the database, including granting or revoking privileges to users and roles.
- Example: GRANT and REVOKE.
Transaction Control Language (TCL): It is used to manage transactions within a database.
- Example: COMMIT, ROLLBACK, and SAVEPOINT

11. What is Normalization?

Normalization is the process of organizing data in a database in such a way that it reduces redundancy and dependency. The goal of normalization is to eliminate data anomalies and inconsistencies that can occur when data is stored in a database.

Normalization involves breaking down a table into smaller, more manageable tables and creating relationships between them. This reduces data redundancy which occurs when the same data is stored in multiple places. It also eliminates update, insert, and delete anomalies that can occur when data is stored in a denormalized or non-normalized database.

Normalization is typically done in a series of steps, called normal forms, with each normal form building on the previous one. The most commonly used normal forms are:

First Normal Form (1NF): This requires that each table has a primary key and that each column contains only atomic values (i.e., values that cannot be further divided).
Second Normal Form (2NF): This requires that each non-key column is functionally dependent on the entire primary key, and not just on a subset of it.
Third Normal Form (3NF): This requires that each non-key column is functionally dependent on the primary key, and not on other non-key columns.

There are additional normal forms beyond 3NF, such as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF), which provide more advanced levels of normalization.

12. What is 1NF?

First Normal Form (1NF) is a property of a relation in a relational database. A table is said to be in 1NF, if

The data in each column should be atomic. No multiple values, separated by a comma.
The table does not contain any repeating column groups
Identify each record uniquely using the primary key.

For example

Student ID	Name	Courses
1	John Doe	Math, Science, English
2	Jane Doe	History, English

This table violates 1NF because the Courses attribute contains multiple values for each student. To bring this table into 1NF, we can split the Courses attribute into a separate table as follows:

Student ID	Name
1	John Doe
2	Jane Doe

Student ID	Course
1	Math
1	Science
1	English
2	History
2	English

Now, each attribute in the two tables contains a single value, and the tables are in 1NF.

13. What is 2NF?

Second Normal Form (2NF) is a database normalization technique used to minimize data redundancy in a database table.

A table is said to be in 2NF, if

The table meets all the conditions of 1NF
Move redundant data to a separate table
Create relationship between these tables using foreign keys.

Example: Consider a table named “Sales” with the following attributes: Order ID, Product ID, Product Name, Product Description, Customer ID, Customer Name, and Quantity.

The primary key of the table is (Order ID, Product ID). This table violates the 2NF as the non-key attribute “Customer Name” is dependent on only a part of the primary key, that is, “Customer ID”.

To convert the Sales table to 2NF, we need to split the table into two tables: “Orders” and “Customers”.

Orders Table:

Order ID (Primary key)
Product ID (Primary key)
Quantity

Customers Table:

Customer ID (Primary key)
Customer Name

In this case, the Orders table has a composite primary key consisting of both Order ID and Product ID. The Customers table has only one primary key, that is, Customer ID. This split eliminates the partial dependency and hence, the table is in 2NF.

14. What is 3NF?

Third Normal Form (3NF) is a level of database normalization that ensures that non-key attributes of a table are dependent only on the candidate keys and nothing else. It eliminates any transitive dependencies that may exist between non-key attributes.

A table is said to be in 3NF, if the table

Meets all the conditions of 1NF and 2NF
Does not contain columns (attributes) that are not fully dependent upon the primary key

To illustrate, let’s consider a table called “Students” with the following attributes:

Student_ID (Primary Key)
Name
Department
Course
Instructor
Instructor_Room

Assuming that each student can take multiple courses and each course can have multiple instructors, this table is not in 3NF. The reason is that the Instructor and Instructor_Room attributes are dependent on the Course attribute and not directly on the Student_ID.

To bring the table to 3NF, we need to split it into two tables: “Students” and “Courses”. The “Students” table will have the following attributes:

Student_ID (Primary Key)
Name
Department

The “Courses” table will have the following attributes:

Course_ID (Primary Key)
Course
Instructor
Instructor_Room

Now, the Instructor and Instructor_Room attributes are dependent only on the Course attribute, which is a candidate key in the “Courses” table. This eliminates the transitive dependency and puts the tables into 3NF.

15. What is BCNF?

BCMF stands for Boyce-Codd Normal Form. It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF.

A table complies with BCNF if it satisfies the following conditions:

It is in 3NF.
For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.

16. What is Denormalization?

It is the process of intentionally introducing redundant data into a database in order to improve query performance. This is typically done by adding redundant copies of data that are already present in the database. But in a normalized form that requires joining multiple tables to access.

The goal of denormalization is to reduce the number of joins required to satisfy a given query. By duplicating data in a denormalized form, it becomes possible to retrieve all the necessary data for a given query by accessing a single table. Or a small number of tables, rather than having to perform complex join operations across multiple tables.

17. What is a primary key, foreign key, and candidate key?

The keys are an important part of relational database design. It allow for the establishment of relationships between tables and ensure that data is organized efficiently and without duplication.

Primary Key: A primary key is a column in a table that uniquely identifies each row in that table. A primary key must be unique, non-null, and immutable (i.e., its value cannot be changed once it has been assigned). Examples of primary keys include an employee ID number or a product SKU.
Foreign Key: A foreign key is a column in one table that refers to the primary key in another table. The foreign key establishes a relationship between the two tables, allowing data to be shared between them. For example, a customer table might have a foreign key that refers to an order table, allowing customer information to be associated with specific orders.
Candidate Key: A candidate key is a column in a table that could be used as a primary key, but is not currently being used as one. Like a primary key, a candidate key must be unique and non-null, but it may not be immutable. For example, in a table of employees, both a social security number and an employee ID number could be candidate keys, but only one would be chosen as the primary key.

18. What are the main differences between Primary key and Unique Key?

Primary key and unique key are both used to uniquely identify a record in a table, but there are some key differences between them:

Primary Key	Unique Key
Cannot contain null values	Can allow null values
Used to enforce data integrity and maintain the uniqueness of each record	Used to ensure that data in a column or set of columns is unique
Automatically indexed	Optional
Mandatory constraint for every table	Optional constraint
Cannot be altered or deleted if used as a foreign key in another table	Can be modified or deleted without any restrictions

19. What is the concept of sub-query in terms of SQL?

Sub-query is basically the query which is included inside some other query and can also be called as an inner query which is found inside the outer query.

Example

SELECT *
FROM orders
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE country = 'USA'
);

20. What are the differences between DROP, TRUNCATE and DELETE commands?

Command	Purpose	Rollback	Conditions
DROP	Deletes an entire table, including its structure and data.	No	None
TRUNCATE	Removes all rows from a table, but retains its structure.	No	Cannot be used with conditions or WHERE clause
DELETE	Removes one or more rows from a table based on a condition.	Yes	Can be used with conditions or WHERE clause

Here’s a more detailed explanation of each command:

DROP:

Drops an entire table, including its structure and data.
Cannot be rolled back.
Used when you want to completely remove a table and all its data permanently from the database.
Syntax: DROP TABLE table_name;

TRUNCATE:

Removes all rows from a table, but retains its structure.
Cannot be rolled back.
Used when you want to delete all rows from a table quickly and efficiently, without removing the table itself.
Cannot be used with conditions or the WHERE clause.
Syntax: TRUNCATE TABLE table_name;

DELETE:

Removes one or more rows from a table based on a condition.
Can be rolled back if used within a transaction.
Used when you want to remove specific rows from a table based on some condition.
Can be used with conditions or the WHERE clause.
Syntax: DELETE FROM table_name WHERE condition;

21. What is the main difference between UNION and UNION ALL?

Sure, here’s a table summarizing the main differences between UNION and UNION ALL in SQL:

Operator	Purpose	Eliminates Duplicates	Syntax
UNION	Combines the results of two or more SELECT statements into a single result set, and eliminates duplicates.	Yes	`SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2;`
UNION ALL	Combines the results of two or more SELECT statements into a single result set, but does not eliminate duplicates.	No	`SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2;`

Here’s a more detailed explanation of each operator:

UNION:

Combines the results of two or more SELECT statements into a single result set, and eliminates duplicates.
Only unique rows are returned in the result set.
The number of columns in each SELECT statement must be the same, and the data types must be compatible.
The column names in the result set are based on the column names of the first SELECT statement.
Syntax: SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2;

UNION ALL:

Combines the results of two or more SELECT statements into a single result set, but does not eliminate duplicates.
All rows are returned in the result set, including duplicates.
The number of columns in each SELECT statement must be the same, and the data types must be compatible.
The column names in the result set are based on the column names of the first SELECT statement.
Syntax: SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2;

22. What is Join?

Join is a SQL operation that combines data from two or more tables based on a related column between them. The purpose of a join is to allow you to retrieve data that is stored in multiple tables in a single query.

There are two main categories of join operations in SQL: inner joins and outer joins.

Inner joins can be further classified into three types:

Theta join
Natural join
Equi join

On the other hand, outer joins have three different types:

Left outer join
Right outer join
Full outer join

23. What is Left outer join?

Left outer join, also known as left join. It returns all the rows from the left table and matching rows from the right table, and NULL values for non-matching rows in the right table.

Example:

Consider two tables – “Orders” and “Customers”. The “Orders” table contains the order information, while the “Customers” table contains the customer information. The two tables have a common field, “customer_id”, which can be used to join them.

Orders table:

OrderID	CustomerID	OrderDate
1	101	2021-01-01
2	102	2021-02-01
3	101	2021-03-01

Customers table:

CustomerID	CustomerName	ContactName	Country
101	ABC	John	USA
102	XYZ	Smith	UK
103	PQR	Tom	Canada

To perform a left outer join between Orders and Customers on the CustomerID column, the SQL query would be:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Output:

OrderID	CustomerName	OrderDate
1	ABC	2021-01-01
2	XYZ	2021-02-01
3	ABC	2021-03-01

24. What is ACID, and what does it stand for?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It refers to a set of properties that are desirable in a DBMS to ensure reliable processing of transactions.

Atomicity: This refers to the property that a transaction in a database management system is treated as a single, indivisible unit of work. Either all of the changes to the data made by the transaction are committed, or none of them are. If a transaction fails, any changes that were made are rolled back to their original state, so that the database remains consistent.
Consistency: This refers to the property that the data in a database management system is consistent before and after a transaction. This means that the database is in a valid state at all times, and that any changes made by a transaction do not violate any constraints or rules that are defined for the data.
Isolation: This refers to the property that transactions in a database management system are executed in isolation from each other. This means that transactions do not interfere with each other, and that each transaction appears to be executed in its own dedicated space, even if multiple transactions are being executed simultaneously.
Durability: This refers to the property that the changes made by a committed transaction in a database management system are permanent, and will survive any subsequent failures, such as power outages or hardware failures.

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

Clustered Index	Non-Clustered Index
Determines the physical order of the data in a table based on the indexed column(s)	Does not determine the physical order of the data in a table
Only one clustered index can be created per table	Multiple non-clustered indexes can be created per table
Can improve the performance of range scans or sorting on the indexed column(s)	Can improve the performance of queries that involve searching for specific values in the indexed column(s)
Inserting or updating rows can be slower, as the data may need to be physically rearranged	Generally easier to maintain because they do not affect the physical organization of the table
Typically used for tables with frequent range scans or sorting operations	Typically used for tables with frequent searches for specific values in the indexed column(s)

26. What is data abstraction in DBMS?

Data abstraction in DBMS is a process of hiding irrelevant details from users. Because database systems are made of complex data structures so, it makes accessible the user interaction with the database.

For example: We know that most of the users prefer those systems which have a simple GUI that means no complex processing. So, to keep the user tuned and for making the access to the data easy, it is necessary to do data abstraction. In addition to it, data abstraction divides the system in different layers to make the work specified and well defined.

16. What are the different levels of abstraction in the DBMS?

There are typically three levels of abstraction in a DBMS:

Physical level: This level deals with the actual physical storage of data on the storage media such as hard disks, tapes, etc. It involves low-level details such as how data is stored, what file structures are used, what indexing methods are used, etc.
Logical level: This level deals with how data is represented and organized, independent of the physical storage details. It involves defining data structures such as tables, views, indexes, etc., and the relationships between them. The logical level describes what data is stored in the database and what relationships exist among the data.
View level: This level is the highest level of abstraction and is closest to the end user. It deals with how the data is presented to the user. It involves defining views, which are subsets of the data stored in the database, tailored to the needs of specific users or applications. Views can be used to simplify the complexity of the data model, and to provide different views of the same data for different users.

27. What is E-R model in the DBMS?

E-R model is known as an Entity-Relationship model in the DBMS which is based on the concept of the Entities and the relationship that exists among these entities.

28. What is a View?

View is a virtual table that is derived from one or more tables in the database. Views are used to provide a customized representation of the data in the underlying tables, without actually modifying the data itself.

A view does not have its own physical storage, but instead, is created dynamically by the database system when the view is accessed.

Here are some key features of views:

Customization: Views are used to provide a customized representation of the data in the underlying tables. For example, a view might show only specific columns or rows of the underlying table(s), or join multiple tables together into a single virtual table.
Security: Views can be used to restrict access to sensitive data in the underlying tables. For example, a view might be created that shows only the data that is relevant to a particular user or group, while hiding other data in the underlying tables.
Simplification: Views can be used to simplify complex queries by providing a pre-defined query that joins multiple tables and filters the data as needed. This can be especially useful for frequently-used queries that are too complex to write out each time.
Read-only: Views are read-only, meaning that any changes made to the data in the view are not reflected in the underlying tables. If data needs to be updated, it must be done through the original tables.

Example: We could create a view called “employee_details” that combines data from two tables and shows only specific columns. The SQL code to create the view look something like this:

CREATE VIEW employee_details AS
SELECT employees.employee_id, employees.name, departments.department_name, employees.salary
FROM employees
JOIN departments ON employees.department_id = departments.department_id;

When we access the “employee_details” view, it will show only the columns we selected and join data from both tables.

SELECT * FROM employee_details;

29. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements that are stored in the database. It can be called by name to perform a specific task or set of tasks. Stored procedures are commonly used to encapsulate complex business logic and data processing tasks that involve multiple SQL statements or require conditional logic.

Here are some key features of stored procedures:

Precompiled: Stored procedures are precompiled, meaning that the SQL statements are compiled and optimized when the procedure is created, rather than each time it is called. This can improve performance for frequently-used procedures.
Encapsulation: Stored procedures can encapsulate complex business logic and data processing tasks that involve multiple SQL statements or require conditional logic. This can make it easier to manage and maintain the database code.
Security: Stored procedures can be used to restrict access to sensitive data in the database by allowing only authorized users to execute them. For example, a stored procedure might be created that allows only users with a specific role to update or delete certain data.
Reusability: Stored procedures can be called from multiple locations in an application, making it easier to reuse the same code in different parts of the system. This can also improve code consistency and reduce the risk of errors or inconsistencies.

Here’s an example of a simple stored procedure that retrieves all the employees in a given department from a database:

CREATE PROCEDURE get_employees_by_department
    @department_id INT
AS
BEGIN
    SELECT * FROM employees
    WHERE department_id = @department_id;
END;

To call the stored procedure from an application, we would use SQL code like this:

EXEC get_employees_by_department @department_id = 123;

30. What is a trigger?

A trigger is a set of SQL statements that are automatically executed in response to certain events or actions on a table. Actions such as a new row being inserted or an existing row being updated or deleted. Triggers are often used to enforce business rules, maintain data consistency, or log changes to the database.

Here are some key features of triggers:

Event-driven: Triggers are executed automatically in response to certain events or actions on a table, such as an INSERT, UPDATE, or DELETE operation.
Conditional: Triggers can be conditional, meaning that they only execute if certain criteria are met. For example, a trigger might only execute if a certain column in the table is updated to a specific value.
Procedural: Triggers can contain procedural code, such as IF/ELSE statements or loops, in addition to SQL statements. This makes them more flexible than simple SQL statements.
Audit trail: Triggers can be used to maintain an audit trail of changes to the database, such as who made the change, when it was made, and what data was affected.

Here’s an example of a simple trigger that logs changes to a table in a database:

CREATE TRIGGER log_changes
AFTER UPDATE, DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_changes (employee_id, change_type, change_date)
    VALUES (OLD.employee_id, 'UPDATE', NOW());
END;

Preparing for a DBMS interview in 2023 can be daunting. But with our comprehensive guide featuring the top commonly asked DBMS interview questions, you’ll be well-equipped to ace your interview.