DBMS Interview Questions and Answers for 2025

There are three main algorithms for performing JOINs:

1. Nested Loop: This algorithm compares all values in the outer and inner tables with each other. It is the only algorithm that can perform cross-joins (many-to-many joins). This serves as a fallback option in the absence of good algorithms.  
2. Hash Join: This algorithm is the most versatile join method. Because it builds an in-memory hash table for the smaller of the two inputs, reads the larger input, and goes through the in-memory hash table to find a match. Hash joins can only be used to compute equijoins. This is generally more efficient than nested loops, except when the probe side of the join is very small.  
3. Sort-Merge Join: This algorithm first sorts the two tables based on their join attributes. Then find the first match and scroll down the two tables and merge the matching attribute rows. 

Expect to come across this popular DBMS interview question more often than others.

A staple in database interview questions, be prepared to answer this one.

• DELETE Command: This command is required to delete rows from a table based on the WHERE clause conditions. This command deletes only those rows for which the WHERE clause is specified. This command can be reset when necessary. This command also maintains a log protocol that locks table rows before deleting them, which slows things down.
• TRUNCATE Command: It is a command required to remove complete data from a table in the database. It's like a DELETE command without a WHERE clause. It deletes complete data from the table in a database. This command cannot be reset even if necessary (Truncation can be undone in some databases, depending on the version, but it is difficult and can lead to data loss.). Also, this command does not maintain any log protocol and removes the whole table at a time which is why it is fast.

A cursor is a tool that allows the user to return results row by row. This is in contrast to its typical SELECT statement which returns the entire result set. Although we are usually interested in the complete set of rows, applications, especially those which are interactive and online applications, do not always work effectively with the entire result set as a single unit. These applications prefer to process data row by row. Cursors allow us to perform this row-by-row operation.  

In the example below we can understand the whole process of using cursors. 

The Sharding is basically a horizontal split architecture. Each shard/split has the same schema and columns, but the rows are different and independent of each other.  

The main advantage of sharding is scalability. With an auto-sharding architecture, we can easily add machines to the stack as needed. Also, it is useful for offloading existing machines to handle more traffic and faster processing. This is attractive for growing applications.  

There are some advantages of database sharding: 

• It solves scalability issues. 
• It has high availability and speedy query response time. 
• It has more write bandwidth.

One of the most frequently posed DBMS interview questions and answers, be ready for it. 

A checkpoint is a type of mechanism where all previous logs are deleted from the system and permanently stored on the storage disk.  

There are two ways a DBMS can help recover and manage ACID properties. That is, managing the log for each transaction and managing shadow pages. So, when it comes to log-based recovery systems, checkpoints occur. A checkpoint is a point to which the database engine can return after a crash as the specified minimum point at which committed data up to the time of the crash can be recovered using transaction log records.  

The checkpoint is like a picture of the DBMS state. Checkpoints allow the DBMS to reduce the work involved in restarting subsequent crashes. Checkpoints are used to restore the database after a system crash. Checkpoints are used in log-based recovery systems. If the system needs to be rebooted due to a system crash, use a checkpoint at that point. In other words, you don't have to start the transaction from scratch.

1. Normalization: This normalization is the process of reducing redundancy by organizing data into multiple tables. Normalization improves disk space utilization and helps maintain database integrity.
2. Denormalization: This denormalization is the reverse process of normalization, combining normalized tables into a single table for faster data retrieval. A JOIN operation can be used to create a denormalized form of the data by reversing the normalization.

There are majorly seven types of keys in the database. Let us discuss them in more detail as below. 

1. Candidate Key: A candidate key shows a set of properties that can uniquely identify a table. Each table can have more than one candidate key. One key out of all candidate keys can be selected as the primary key. For example, studentId and firstName can be considered candidate keys because they can uniquely identify each tuple.  
2. Super Key: A super key defines a set of attributes that can uniquely identify a tuple. Candidate keys and primary keys are subsets of super keys. So, the super key is a superset of them.  
3. Primary Key: A primary key defines a set of attributes used to uniquely identify each tuple. For example, studentId and firstName are candidate keys and either can be selected as the primary key. Here, studentId is the primary key. 
4. Unique Key: A unique key is very similar to a primary key, except that a primary key does not allow null values in a column, whereas a unique key does. So, the unique key is basically the primary key with its NULL values. 
5. Alternate Keys: All candidate keys not selected as primary keys are considered alternate keys. In the example, firstname and lastname are alternate keys in the database. 
6. Foreign Key: A foreign key defines an attribute that can only retrieve values that exist in one table, along with attributes that exist in another table. In the example, courseId in the Student table is a foreign key to the Course table because both tables contain courseId as one of their attributes.  
7. Composite Key: A composite key refers to the combination of two or more columns that can uniquely identify each tuple in a table. In the example, we can group studentId and firstname to uniquely identify each tuple in the table. 

Expect to come across this popular DBMS viva question, as this tests your basic concepts.

A deadlock is a situation that occurs in the operating system when one process enters a wait state while another waiting process is holding a requested resource. This is a common problem in multiprocessing where multiple processes share certain kinds of mutually exclusive resources called soft-locks.

Example of deadlock:

A real-world example is traffic going in only one direction. Consider the bridges count as a resource here. This is a simple way to solve deadlocks when the car is in reverse (resource pre-allocation and rollback). In the event of a deadlock situation, it may be necessary to reserve a large number of cars. You may be starving at this point.

A DBMS timestamp-based protocol is an algorithm that uses the system time or a logical counter as a timestamp. It helps to serialize the execution of concurrent transactions. This protocol ensures that conflicting reads and writes are performed in timestamp order.  

For example: Suppose we have three transactions such as X1, X2, X3. 

• X1 entered the system at 0001 
• X2 entered the system at 0002 
• X3 entered the system at 0003 
• In this case, transaction X1, transaction X2 and transaction X3 take precedence. 

Here is the list of some restrictions that can be applied while creating a view:

• Only the database which is in use can have views.
• There is no need to change the calculated value in a particular view.
• Integrity constants determine INSERT and DELETE functionality.
• Full-text index definition is not applicable.
• It cannot create a temporary view.
• A temporary table cannot have a view.
• No assignment to DEFAULT definition.
• Triggers such as INSTEAD OF are associated with views.

Hashing is a lookup technique. Basically, it is how to map keys to values. A hash function converts a string into a fixed-length value (usually shorter). This value can be used as an index to store the original element.

Hashing can be used to index and search for items in a database within a given amount of time using the appropriate hash function. This is faster than other search techniques.

Advantage of Hashing:

• Hash tables are ideal data for point searches (aka equality queries), because they can search, insert, and delete data in constant time regardless of the input size and structure.

Disadvantages of Hashing:

• Hashing may not always be the best option. For small data, for example, the cost of a good hash function makes hashing more expensive than a simple sequential lookup.
• Another situation is range scan operations (aka range queries). A B+ tree is the ideal data structure for this operation.
• Next situation is when looking for a substring or prefix match. Hashes are essentially useless for these operations.
• Another drawback of hashing is scalability. Hash table performance degrades as the database grows (more collisions, higher collision resolution costs).

A staple in database interview questions for experienced, be prepared to answer this one.  

B+ Tree is a data structure of the B-Tree family. This data structure and its variants are very popular for indexing purposes. This tree is known as a self-balancing tree and provides a mechanism to ensure that the nodes are at least half full. In a B+ tree, data is stored in leaf nodes, which are sequentially linked. These sequential links between leaf nodes allow sequential access to data without traversing the tree structure. This sequential access allows for quick and efficient range scans.  

B+ trees allow search, sequential access, insertion, and deletion in logarithmic time. At the end of this answer, there is an example of a B+ tree visualization. Also, for better understanding, we can create the chart ourselves using various visualization tools.  

Database systems typically compare a B+ tree data structure to a hash table. Here I'm trying to explain the pros and cons of B+ trees and hash tables. The advantage of B+ trees is in range queries and substring searches with the LIKE command. Hash indexes for equality queries, on the other hand, outperform B+ trees. Another advantage of B+ trees is that they scale easily with the data, making them suitable for storing enormous amounts of data on disk.  

A common follow-up question is always asked that is, what is the difference between B+ trees and Binary Search Trees (BST)? A B+ tree is a generalization of BST that allows tree nodes to have more than two children.  

If someone asks you about the difference between B-trees and B+ trees, there are two things you can say. First, in B+ tree, records are stored only in leaves, and internal nodes store pointers (keys). Unlike B+ trees, B-trees can store keys and records in both internal and leaf nodes. Second, the leaf nodes of the B+ tree are linked together but not in the B tree. You can see the difference between these two in the example below.

We can think of it as a semi-program. A series of SQL statements to perform a specific task. If this task is a common task, we can store this query in a procedure and run it on demand instead of running it every time. Below is the simple structure of the procedure.

CREATE PROCEDURE <Procedure-Name> AS 

Begin 

<SQL STATEMENTS> 

End 

After creating the procedure, we can execute it with the EXECUTE command as needed. 

EXECUTE <Procedure-Name> 

Stored procedures have many advantages. Most important is reusability of SQL code. If the procedure is used frequently, it helps not to write the code multiple times. Another advantage is that using a distributed database reduces the amount of information sent over the network.  

Triggers are stored procedures that are automatically executed before and after an event occurs. These events are database operations such as DML, DDL, DCL, or LOGON/LOGOFF. The trigger syntax is:  

CREATE [ OR ALTER ] TRIGGER [ Trigger-Name ]  
[BEFORE | AFTER | INSTEAD OF]  
{[ INSERT ] | [ UPDATE ] | [ DELETE ]}  
ON [table-name]  
AS 
{SQL Statement} 

The uses of triggers include validating transaction validity, enforcing referential integrity, logging events, auto-generating some derived columns, and security authentication before and after user login.  

When asked about the difference between a trigger and a stored procedure, the answer is that a trigger cannot be called by itself. These are called during events. By contrast, stored procedures are independent queries and can be called independently.  

A staple in DBMS interview questions and answers, be prepared to answer this one.

As we know, there are many levels of normalization. The purpose of normalization is to avoid redundancies and dependencies. However, this goal cannot be achieved in one step. With each normalization step (type), we get closer to the goal.

Let’s start with Unnormalized Data Format (UNF). UNF divides these values into multiple cells so that each table cell has a single value and removes duplicate rows so that every record is unique, so the cells have multiple values (non-atomic). We then converted the UNF to First Normal Form (1NF).

The next step is to introduce primary and foreign keys. In this step, we subdivide the 1NF table into new tables and join them by primary and foreign keys. In these new tables, we arrive at Second Normal Form (2NF) if all non-key attributes work perfectly according to the primary key.

2NF has greatly reduced redundancies and dependencies, but there is still a lot of room for improvement. The next step is to remove transitive function dependencies. This basically means that changing one non-key column may change another non-key column. To unwrap these non-key columns, you need to create a separate table. The third normal form (3NF) is reached when there are no transitive functional dependencies.

In nearly all databases, 3NF is the point at which the database cannot be decomposed into higher normalized forms. However, in some complex databases there are situations where a higher form of normalization can be achieved. Higher-order normalization forms are Boyce-Codd Normal Form (BCNF), 4NF, 5NF, and 6NF.

Two-tier Architecture

A two-tier architecture corresponds to a basic client-server architecture. A two-tier architecture allows a client-side application to communicate directly with a server-side database. For example, the Railway Reservation System, Contact Management System, etc. These are common examples of two-tier architecture.

Three-tier Architecture:

A three-tier architecture includes an additional layer between the client and server. The introduction of the 3-tier architecture is for ease of use as it makes the system secure and provides a GUI that makes it more accessible. In this architecture, client-side applications interact with applications on the server, which in turn communicate with the database system. For example, Registration Form Designing which includes text-box, label, button or a website from internet, etc.

This is one of the most frequently asked database interview questions. 

There are different parameters, and using those parameters logical and physical database designs can be differentiated. Those parameters include task, choice of criteria, and result. Now, let us see the difference between logical and physical database designs in more detail based on these parameters. 

Logical Database System: The task of logical database design is to map or convert conceptual schemas from high-level data models to relational database schemas. This mapping can be done in two stages. The first stage is mapping is system-independent, but the data model is dependent, and the second stage is schema turns for specific DBMS. In this database design, DDL statements in the language can be your DBMS of choice. You can specify schemas at the conceptual and external levels of your database system. However, if the DDL statements contain some physical design parameters, the complete DDL specification should wait until the physical database design phase is completed. 

Physical Database Design: The main task of physical database design is to store the database specifications in terms of physical storage structure, record placement, and indexes. The selection of physical database design is based on some criteria, which include response time, disk space utilization, and transport throughput. The initial determination of database file storage structure and access paths. This corresponds to the internal schema definition for the Data Storage Definition Language. 

Database design is divided into many distinct phases. Logical database design and physical database design are two of them. This separation is generally based on the concept of a three-tier architecture of DBMSs that provides data independence. This separation, therefore, leads to data independence, since the output of the logical database design is the conceptual and external level schema of the database system independent from the output of the physical database design, which is the internal schema.  

Expect to come across this, one of the most popular DBMS interview questions on database design.

Here is the list of different types of failures: 

• Statement Failure 
• Bad Datatype  
  • Insufficient Space 
• Insufficient Privileges 
• User Process Failure 
  • An abnormal disconnect performed by the user. 
  • Abnormal termination of user’s session. 
  • An address exception is raised by user’s program. 
• User Error 
  • When the user drops a table. 
  • Data damage happens when the user does the modification. 
• Instance Failure 
• Media Failure 
• Alert Logs 
  • When it records informational and error messages. 
  • The log records all instance startups and shutdowns.

The RAID is an acronym for Redundant Array of Inexpensive (or Independent) Disks. 

RAID is a method of combining multiple hard drives into one logical entity (grouping two or more hard drives so that they appear as one device to the host system). RAID technology was developed to overcome the fault tolerance and performance limitations of traditional hard disk storage. It can provide fault tolerance and higher throughput than a single disk or group of independent disks. Once considered a complex and relatively specialized storage solution, arrays are now easy to use and essential for a wide range of client/server applications.  

Even if the system crashes before all changes are written to disk, the impact of the transaction remains if the DBMS reports to the user that the transaction completed successfully.

Durability is the word used for this quality. As transactions are written to the database, this durability feature makes sure that the data is stored in non-volatile memory and is immune to system failures. This is the reason this feature is very important in DBMS.