Top 30+ Database Interview Questions And Answers for 2024

Introduction

A Database Management System is a system tool that allows us to store, access, manipulate, and share data efficiently and conveniently. It makes the storage of related data and accesses easy. It includes various functionalities like Data Dictionary Management, Database Access Languages, Data Transformation and Presentation, Data Storage Management, Security Management, Multi-user Access Control, Data Integrity Management, Database Communication Interfaces, Application Programming Interfaces, Backup and Recovery Management. 

In this article, we will discuss the most commonly asked database interview questions that will help you prepare for interviews with big MNCs. The DBMS interview questions are normally asked from keys, constraints, functional dependencies, normalization, transactions, joins, and SQL queries.

Let's continue with some of the top database interview questions.

Commonly Asked DBMS Interview Questions

Q1. What is meant by Database?

A database is a structured set of ordered data that is electronically stored and accessed. It is made to manage, store, and retrieve information effectively. Data is arranged in databases into tables, which are made up of rows and columns. Each column denotes a certain property or field of a single record, while each row denotes a single record.

Databases are essential to a wide range of applications, such as websites, programs, businesses, and more. They offer a methodical approach to data management and manipulation, facilitating effective information storage, retrieval, and manipulation.

Q2. What are the 4 types of database?

The 4 types of database are as follow as:

• Relational Database: Data is arranged in tables with established relationships. using SQL to organize data. For instance, MySQL and PostgreSQL
   
• NoSQL Database: It handles unstructured or partially organized data. provides adaptable data models. Document-oriented and key-value stores, for example, are categories. MongoDB and Cassandra are two examples
   
• Object-Oriented Database: Data is represented as objects, making it suited for applications with intricate inheritance structures
   
• Graphs Database: Data is stored as graphs (nodes and edges), which is excellent for scenarios that highlight the connections between the data elements. Neo4j and Amazon Neptune are examples

Q3. What is a Database Management System?

A database management system (DBMS) is a piece of software that makes it easier to store, arrange, manage, and retrieve structured data. Working with databases is made simpler by the capabilities it offers for data protection, integrity, and user-friendly data access.

Q4. How is a Database management system better than the traditional file system?

Following are the difficulties of the traditional file system that are overcome by using a database management system:

• Difficulty in accessing data
• Data redundancy (duplication of information in various files) and inconsistency
• Atomicity of updates  
• Data security 
• Recovery and Backup
• Concurrent access by multiple users
• Data isolation (multiple files and formats)
   

Q5. Explain Data Redundancy?

When the same piece of data is saved many times within one database or across several databases, data redundancy occur. This redundancy can cause inefficiencies in maintenance and storage, as well as raise the possibility of data inaccuracies or inconsistencies.

Redundancy is produced, for instance, when a customer's address is kept in various tables in a database containing client information. The risk of inconsistency rises if the customer moves because several locations would need to change their address.

Q6. What is RDBMS?

RDBMS also known as relational database management systems, are a subset of database management systems that correspond to the relational model's fundamental ideas. In an RDBMS, data is arranged into rows and columns of tables, and linkages between the tables are made based on shared characteristics.

Q7. How is a candidate key different from a super key?

A super key is a collection of one or more attributes that uniquely identifies any tuple in a Database. A candidate is a minimal super key. It is a set of one or more attributes that are necessary to uniquely identify any tuple. A candidate key is a minimum necessary subset of a super key. 
 

Q8. How are triggers and stored procedures different from each other?

Stored procedures are like functions that can be called directly or indirectly, whereas triggers can only be called through queries. We cannot call a trigger directly.
 

Q9. How do you create an index on a table in SQL?

To create an index, we use the create index keyword. Following is the syntax of creating an index index1 on the student table using attribute roll_number.

CREATE INDEX index1

ON students (roll_number);
 

Q10. What is a livelock, and how is it different from a deadlock?

Live lock is when two or more processes are changing their status repetitively, and they do no valuable work. It is different from a deadlock as not all the processes are waiting simultaneously. They are running concurrently but not producing any useful work.
 

Q11. Why do we use cursors in embedded SQL?

A cursor object stores the output of a query in embedded SQL. SQL command works on all the rows at a time, but the cursor works on one row at a time. We can store the result of a query that can have multiple rows in a cursor and then access them row-by-row with the help of a cursor object in embedded SQL.
 

Q12. What are nested subqueries in SQL?

When we have a SQL query within a SQL query, then such a query is called a nested subquery. It is used to solve complex queries where instead of writing separate queries and storing the results in different objects or tables, we can nest the queries to get the desired results.
 

Q13. How is a drop command different from a truncate command?

Drop command is a DDL(data definition language) command used to delete a table or a view in a database. It removes the logical schema of the table or view from the database. In contrast, the truncate command is used to empty the table or view. In the truncate command, the table's schema is not removed from the table.

Q14. What is Normalization in DBMS?

In DBMS, normalization is the process of structuring and organizing a database to lessen data redundancy and reliance by separating data into different tables. On the basis of their attributes, it includes building associations between tables. Thus, storage space is reduced while data integrity is guaranteed.

Q15. What is denormalization in DBMS?

Denormalization in DBMS is a technique of adding redundant data in a table to avoid costlier joins. Joins can be costly if we have a highly normalized table, so denormalization acts as an optimization technique to avoid this.
 

Q16. What are the two constraints associated with the primary key?

The two constraints associated with the primary key are NOT NULL and UNIQUE. This ensures that a primary key can never be null and should always have a unique value.
 

Q17. How is the drop command different from the delete command in SQL?

Drop command is a DDL(data definition language) command used to delete a table or a view in a database. This command can not be rolled back. Delete is a DML(data manipulation language) command used to delete one or more existing records of a table or a view. This command can be rolled back.
 

Q18. What do you mean by a checkpoint in DBMS?

A checkpoint or a savepoint is a point in a database up to which the database is consistent, and all transactions are committed. It defines a point from which the recovery starts in case of system failure instead of starting from the beginning. All logs before the checkpoint are removed from the database.
 

Q19. What do you mean by Functional Dependency?

If the value of a set of one or more attributes(determinant) determines the value of another value, then such a dependency is called functional dependency. If each value of attribute A determines the value of attribute B, then we say that A functionally determines B. The representation of this expression is A -> B.
 

Q20. What are partial and transitive functional dependencies?

Partial functional dependency: It is called partial functional dependency when a non-key attribute is dependent on the part of a key attribute(not the entire key).

Transitive functional dependency: When a non-key attribute is dependent on another non-key attribute, then such a dependency is called transitive functional dependency.
 

Q21. What are the different normal forms for normalization in DBMS, and how are they achieved?

The different normal forms for normalization in DBMS includes:

• 1NF (first normal form): It is achieved by removing the multivalued attributes that are all attributes that should be atomic in 1NF form.
   
• 2NF (second normal form) is achieved by removing partial dependencies from the 1NF normal form.
   
• 3NF(third normal form) is achieved by removing the transitive dependencies from the already existing 2NF database.
   
• BCNF(Boyce-Codd Normal Form): Once we have the database in 3NF, we ensure that all the determinants are candidate keys to move the database to BCNF.
   
• 4NF(fourth normal form): A table is said to be in 4NF if it is already in BCNF form, and we remove the multivalued dependencies.
   

Q22. What is a weak entity set?

A weak entity set is an entity set that does not have any primary key that can uniquely identify each record of a table. It needs the help of another strong entity set called its identifying set.
 

Q23. Give the difference between schema and instance.

A schema gives the logical structure of the table, that is, what attributes it has, what are the domains of the attributes, and what is the primary key and foreign key in a table. An instance is the real content of the database at a particular point in time. Instances are regularly changed/updated, whereas schemas are rarely changed.
 

Q24. What is data abstraction?

Data abstraction is hiding unnecessary or irrelevant data from the users and only showing the significant information upfront. It also ensures easy database interaction as only relevant data is shown. It also ensures data security as we have the mechanisms to hide the data from certain users.
 

Q25. How is table scan different from index scan?

It is called table scan when we iterate over all the table items while accessing or retrieving data from a table. In contrast, it is called index scan when we iterate over only the index items. An index scan is usually faster than a table scan. 
 

Q26. What is Database partitioning, and what are its benefits?

Database partitioning is dividing the large logical database into smaller independent units. The benefits of partitioning are following:

• It improves query performance, as the queries that access only a section of data from a large table is easier with the partitioning.
   
• We can use slower or cheaper storage devices to store the data.
   
• Access to large parts of a specific section becomes fast.
   
• It Improves the availability and manageability of the database.
   

Q27. What is an intension and extension in DBMS?

Intension: It describes the database, that is, the definitions of the data structures of a database. It is also called the database schema. It rarely changed.

Extension: It is the actual content or the number of records available in the database at any instance of time. It changes/updates frequently as we manipulate(insert, update, delete) data in the database.
 

Q28. Differentiate between an exclusive lock and a shared lock.

Exclusive lock: In this type of lock, a transaction is granted the right to read and write to a data item. With this lock, a transaction can update a data item. It is granted to only one transaction at a time for a particular data item.

Shared lock: In this type of lock, a transaction is granted with only the right to read a data item. With this lock, a transaction can not update the data item. It can be granted to more than one transaction at a time for a particular data item if all transactions have a shared lock.
 

Q29. What are some manipulation functions in SQL?

Some of the manipulation functions in SQL include:

1. Lower: This function takes a string and returns the same string with all the letters in lowercase.
    
2. Upper: This function also takes a string and returns the same string with all the letters in uppercase.
    
3. INITCAP: It takes a string and returns the first letter capitalized and the remaining letters in lowercase.
    

Q30. What do you understand by the term transaction management?

A transaction is defined as a set of operations that performs a single logical function in a database. Transaction management is the mechanism that maintains two cases.

1. Transaction failure or any other system failure.
2. Concurrent use by multiple users.
    

Q31. What are roles in DBMS, and how do you assign them to users?

We can create a role specifying the authorization or privileges of that role, and then we can assign this role to many users, thereby giving those users the defined privileges. We can also assign one role to another role. The later role inherits all the privileges of the first role.
 

Q32. What is static hashing, and what are its disadvantages?

Static hashing is when a function h maps the search key values to a fixed set of B (number of buckets) addresses. The following are the disadvantages of static hashing:

• If B is very small, then a lot of overflows will be there.
   
• If too large, then space wastage.
   
• The database shrinks and grows with time, so if the database shrinks, then again space wastage, and if it grows, then more overflows.
   

Q33. What is dynamic hashing, and what are its advantages and disadvantages?

Dynamic hashing is when the hash function h maps the search key values to variable B (number of buckets) addresses. It is suitable for a database that grows or shrinks with time.

Advantages of dynamic hashing:

• Hash performance does not degrade with the growth of the file.
• Minimal Space overhead(wastage).

Disadvantages:

• Extra level of indirect (bucket address table).
• The bucket address table may itself become large.
   

Q34. What is a data model, and what are some data models?

A data model is a collection of tools for describing data, data relationships, data semantics and data constraints. Some of the data models are:

• Relational Database Model
   
• Entity-relation Database Model
   
• Object-oriented database model
   
• Hierarchical database model
   
• Network Database model
   

Q35. What do you mean by surrogate key?

If none of the keys is a superkey in a table, we introduce or derive a new key such as a serial number to uniquely identify each tuple in a table; such a key is a surrogate key.
 

Q36. What are the operations in relational DBMS?

Following are the operations in RDBMS.

1. Select operation is used to select rows based on a condition. 
    
2. The projection operation takes a table and returns a table with one or more columns deleted. It shows only the distinct tuples in the result.
    
3. Union: it takes two tables and returns the set union of those two tables. The number should be the same in both tables.
    
4. Set difference: returns the set difference between two tables.
    
5. Cartesian Product: It takes two tables and returns the join of two tables. 
    
6. Rename: To rename a table.
    

Q 37. What is order by clause in SQL?

Order by clause in SQL is used to sort the result of a query in a specified manner on any specified column or columns. We can sort in either ascending or descending order. By default, the order is ascending. The keyword ASC is used to specify the ascending order, and the keyword DESC is used to specify the descending order.