Database Interview Questions For Freshers And Experienced Professionals (With Sample Answers)
Updated 9 October 2022
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
Making a career in database administration would require you to have a sound knowledge of database management. Based on the exact position you appear for, the interviewer may test your knowledge of database management systems and related technologies like MySQL, Oracle and SQL queries. Having a general idea of the kind of questions you may face can increase your confidence level and your chances of clearing the interview. In this article, we share some of the popular database interview questions, along with sample answers for several of them.
Related: What Are the Different Database Types?
Basic Database Interview Questions For Freshers
Following are some of the beginner-level database interview questions for fresh graduates:
What is RDBMS?
What is data redundancy?
Describe the different types of relationships in a database.
What are the advantages of database normalisation?
Describe different types of normalisation.
What is the difference between a record and a field?
What is the difference between SQL and MySQL?
What is an SQL query?
Describe different types of SQL statements.
What is a sub-query?
What are correlated sub-queries?
Explain to us the terms 'attribute' and 'relations'.
What do you know about data warehousing?
What do you understand by data checkpoint?
Explain the concept of a data dictionary.
What is a primary key? How is it different from a composite key?
Explain the difference between a unique key and a primary key.
Give some examples of DDL commands.
Why do we use a GROUP clause?
What is a B-tree?
What do you understand by transparent DBMS?
Related: How to Introduce Yourself in an Interview
In-Depth Database Interview Questions For Experienced Professionals
Here are some of the advanced-level database interview questions for experienced candidates:
What is a functional dependency?
What do you know about the DML compiler and DDL interpreter?
Explain the entity-relationship model.
What is a weak entity set?
What is a database lock? How does a shared lock differ from an exclusive lock?
What are the different types of joins? How do you join two or more tables?
What is index hunting and how does it improve the performance of queries?
Tell us about the cluster and non-cluster indexes.
Explain the concepts of atomicity and aggregation.
What is database partitioning and why is it important?
What are database triggers?
What are stored procedures?
Explain the difference between DELETE, DROP and TRUNCATE commands.
Explain the four ACID properties of a transaction.
What are scalar functions?
Explain the basic restrictions you would apply while creating database views.
How does a table scan differ from an index scan?
Explain the concept of fill factor in the context of indexes.
What are the different phases of a database transaction?
What is an object-oriented model?
Related: jQuery Interview Questions and Answers: General and Expert Level
Common Database Interview Questions And Answers
Following are some of the common database interview questions and sample answers:
1. What is a database?
This is a basic question. The interviewer may be trying to understand how you perceive a database, but the way you answer this question may also reflect on your communication skills and clarity of thought. You can first define a database and then include a simple example to explain its meaning.
Example: “A database is a collection of data related to a specific task, event or purpose that is organised and stored in a way that makes it easy to retrieve when required. A database usually contains multiple tables. For example, a school may maintain a database of its students organised into several tables based on a unique ID, name, age, address, parents' names, roll number and attendance. We typically store databases electronically using a database management system (DBMS). A DBMS makes it easy to query, control and update databases.”
Related: 11 Common Database Design Interview Questions (With Answers)
2. What are the advantages of using a DBMS?
A DBMS increases the utility of data. You can talk about various advantages like access control, data recovery and data validation.
Example: “Using a DBMS makes it easy to organise, access and use the data. It provides a means to validate the data on the basis of predefined data types. This ensures consistency of data and helps avoid data redundancy. A DBMS also makes it possible to provide restricted access to data based on the role and privileges of the user. Taking backup and recovering data also becomes easy when we use a DBMS.”
Related: What Does A Database Manager Do? (With Skills And Salary)
3. What are normalisation and de-normalisation in database management?
De-normalisation adds redundancy, while normalisation removes it. Describe through the definitions how both these techniques help process complex queries by adding and removing redundant data.
Example: “Normalisation is the process used to remove redundant data while keeping the data integrity intact. It involves splitting the database in an organised manner. Normalisation helps us save storage space. De-normalisation is a technique used to process complex queries. It involves adding redundant data in a database to achieve better performance.”
4. What do you understand by data independence? How many types of data independence are there?
Explain the concept of data independence and how it enables modification of schema definition. Describe physical and logical data independence.
Example: “Data independence refers to the concept that an application is independent of the changes made in the structure and organisation of the data it uses. It enables us to make changes in the schema definition without affecting the schema of the higher level.
There are two types of data independence – physical data independence and logical data independence. Physical data independence refers to the independence of the data stored in the database. For example, the changes that we make in the data kept inside a table is required not to affect the format of the table. Logical data independence refers to the independence of the data structure, i.e., data about the database. For example, any changes that we make in the format of the table is not required to affect the data kept inside the table.”
Related: What Is A Relationship In Database? (Definition And Types)
5. What is a view in a DBMS? What are its advantages and disadvantages?
A view is a virtual table that displays the result of a query. You can talk about how database views help present data from different tables without requiring any physical storage. If the time permits, you can also elaborate on static and dynamic views.
Example: “A view is a virtual table that displays a subset of data from different tables based on a query that we run on the database. Views do not have their own data. They are logical data-independent, meaning that the restructuring of the base tables does not affect them. The main advantage of views is that they do not require any physical location to store the data they display, thus saving us resources. Another advantage is that they eliminate the risk of data deletion and tampering since they do not support commands like insert and update.
The disadvantage of views is that they become irrelevant if we delete the base tables. Also, creating views for large tables may use a substantial amount of memory.”
6. What is a cursor? What do you know about implicit and explicit cursors?
A cursor helps navigate through the records in a database. You can describe how it facilitates data processing tasks like addition, removal and retrieval of records. Be sure to include the fact that implicit cursors work without the user's knowledge, while explicit cursors are required to be defined.
Example: “A database cursor is a mechanism that facilitates sequential processing of individual rows of a result set returned by DBMS queries. It navigates and processes one row at a time. Just like a pointer, we can move a cursor from one row to another. Based on the cursor type, we can also move a cursor to the previous row.
Implicit and explicit cursors are two broad categories of cursors. Implicit cursors start working automatically without our knowledge when we execute certain commands like UPDATE, DELETE AND INSERT. Explicit cursors are user-defined. We declare them while making SELECT statements for multiple rows.”
Related: What Is a SQL Server and Other Frequently Asked Questions
7. What is a database transaction? How can we make transactions more efficient?
Define a transaction and explain how an entire transaction needs to be completed for making any changes in the database. You can also talk about the ACID properties of a transaction. Include the ways to efficiently code a database transaction, such as preventing user input and keeping a transaction as small as possible.
Example: “A transaction is a logical sequence of operations performed on the database as a single unit of work. It groups the commands and statements of a DBMS in such a way that either all of them are required to be executed or no changes occur in the database. If any part of the transaction fails, the system rolls back all the changes to the database made by the said transaction. A DBMS assigns a unique ID to each transaction. All database transactions are required to follow the ACID properties, meaning that they require to meet the test of atomicity, consistency, isolation and durability.
We can make transactions more efficient by following certain rules, such as – keep transactions as small as possible, avoid user input during transactions, keep the transaction segregation level low and access minimal information while transacting.”
Related: What Is a Database Administrator? (With Skills, Salary and FAQs)
Explore more articles
- 5 Kanban Interview Questions (With Sample Answers)
- Essential Database Testing Interview Questions (And Answers)
- Interview Questions For Production Engineer (With Answers)
- Hardware Engineer Interview Questions (With Example Answers)
- 12 Common Data Mining Interview Questions (With Answers)
- Residency Interview Thank You Letter: Format And Samples
- 50 Essential Java Interview Programs
- 5 Work-Life Balance Interview Questions (With Answers)
- 33 Checkpoint Firewall Interview Questions (With Answers)
- Interview Question: 'What Skills Would You Like To Improve?'
- 5 Common Angular 7 Interview Questions (With Sample Answers)
- Scala Interview Questions (With Sample Answers And Tips)