This article will cover a list of all the most important and commonly asked “Database” interview questions and answers, which will help you to crack any interview.
Organizational skills, strong problem-solving capability, attention to detail, and a high level of accuracy are some of the essential qualities that a person should possess in order to be in the “Database” field.
Table of Contents:
Quiz on Database Interview Questions to Sharpen Your DB Skills
Take this ultimate quiz on Database Interview Questions to assess your skills & knowledge on Database. Demonstrate your expertise confidently and ace your next technical interview in minutes.
Basic DBMS Interview Questions And Answers
While you should be updated with the latest trends in the industry, your basics should also be strong. In order to start your career in databases, the essential things that you need to have are knowledge of DBMS (Database Management System) and SQL (Structured Query Language).
Given below is a list of the most popular Database interview questions and answers for your reference.
Q #1) What do you understand by ‘Database’?
Answer: A Database is an organized collection of related data where the data is stored and organized to serve some specific purpose.
For example, a librarian maintains a database of all the information related to the books that are available in the library.
Q #2) Define DBMS.
Answer: DBMS stands for Database Management System. It is a collection of application programs that allow the user to organize, restore, and retrieve information about data efficiently and as effectively as possible.
Some of the popular DBMSs are MySQL, Oracle, Sybase, etc.
Q #3) Define RDBMS.
Answer: Relational Database Management System(RDBMS) is based on a relational model of data that is stored in databases in separate tables, and they are related to the use of a common column. Structured Query Language (SQL) allows easy access to data from the relational database.
Q #4) Enlist the advantages of DBMS.
Answer: The advantages of DBMS include:
- Data is stored in a structured way, and hence redundancy is controlled.
- Validates the data entered and provides restrictions on unauthorized access to the database.
- Provides backup and recovery of the data when required.
- It provides multiple user interfaces.
Q #5) What do you understand by Data Redundancy?
Answer: Duplication of data in the database is known as data redundancy. As a result of data redundancy, duplicated data is present at multiple locations, hence it leads to wastage of the storage space and the integrity of the database is destroyed.
Q #6) What are the various types of relationships in a Database? Define them.
Answer: There are 3 types of relationships in a database:
- One-to-one: One table has a relationship with another table that has a similar kind of column. Each primary key relates to only one or no record in the related table.
- One-to-many: One table has a relationship with another table that has primary and foreign key relations. The primary key table contains only one record that relates to none, one or many records in the related table.
- Many-to-many: Each record in both tables can relate to many numbers of records in another table.
Q #7) Explain Normalization and De-Normalization.
Answer:
Normalization is the process of removing redundant data from the database by splitting the table in a well-defined manner in order to maintain data integrity. This process saves much of the storage space.
De-normalization is the process of adding up redundant data to a table in order to speed up the complex queries and thus achieve better performance.
Q #8) What are the different types of Normalization?
Answer: Different types of Normalization are:
- First Normal Form (1NF): A relation is said to be in 1NF only when all the entities of the table contain unique or atomic values.
- Second Normal Form (2NF): A relation is said to be in 2NF only if it is in 1NF and all the non-key attributes of the table are fully dependent on the primary key.
- Third Normal Form (3NF): A relation is said to be in 3NF only if it is in 2NF and every non-key attribute of the table is not transitively dependent on the primary key.
Q #9) What is BCNF?
Answer: BCNF is the Boyce Code Normal form. It is a higher version of 3Nf, which does not have any multiple overlapping candidate keys.
Q #10) What is SQL?
Answer: Structured Query Language, SQL, is an ANSI(American National Standards Institute) standard programming language that is designed specifically for storing and managing data in the relational database management system (RDBMS) using all kinds of data operations.
Q #11) How many SQL statements are used? Define them.
Answer: SQL statements are basically divided into three categories: DDL, DML, and DCL.
They can be defined as:
- Data Definition Language (DDL) commands are used to define the structure that holds the data. These commands are auto-committed i.e. changes done by the DDL commands on the database are saved permanently.
- Data Manipulation Language (DML) commands are used to manipulate the data of the database. These commands are not auto-committed and can be rolled back.
- Data Control Language (DCL) commands are used to control the visibility of the data in the database like revoke access permission for using data in the database.
Q #12) Enlist some commands of DDL, DML, and DCL.
Answer: Data Definition Language (DDL) commands:
- CREATE to create a new table or database.
- ALTER for alteration.
- TRUNCATE to delete data from the table.
- DROP to drop a table.
- RENAME to rename a table.
Data Manipulation Language (DML) commands:
- INSERT to insert a new row.
- UPDATE to update an existing row.
- DELETE to delete a row.
- MERGE for merging two rows or two tables.
Data Control Language (DCL) commands:
- COMMIT to permanently save.
- ROLLBACK to undo the change.
- SAVEPOINT to save temporarily.
Q #13) Define DML Compiler.
Answer: DML compiler translates DML statements in a query language into low-level instruction,s and the Query Evaluation Engine can understand the generated instructions.
Q #14) What is a DDL interpreter?
Answer: DDL Interpreter interprets the DDL statements and records the generated statements in the table containing metadata.
Q #15) Enlist the advantages of SQL.
Answer: Advantages of SQL are:
- Simple SQL queries can retrieve large amounts of data from the database very quickly and efficiently.
- SQL is easy to learn, and almost every DBMS supports SQL.
- It is easier to manage the database using SQL as no large amount of coding is required.
Database Interview Questions for Freshers
Q #16) Explain the terms ‘Record’, ‘Field’ and ‘Table’ in terms of databases.
Answer:
Record: Record is a collection of values or fields of a specific entity. For example, an employee, a Salary account, etc.
Field: A field refers to an area within a record that is reserved for specific data. For example, Employee ID.
Table: Table is a collection of records of specific types. For example, the Employee table is a collection of records related to all the employees.
Q #17) What do you understand by Data Independence? What are its two types?
Answer: Data Independence refers to the ability to modify the schema definition at one level in such a way that it does not affect the schema definition in the next higher level.
The 2 types of Data Independence are:
- Physical Data Independence: It modifies the schema at the physical level without affecting the schema at the conceptual level.
- Logical Data Independence: It modifies the schema at the conceptual level without affecting or causing changes in the schema at the view level.
Q #18) Define the relationship between ‘View’ and ‘Data Independence’.
Answer: View is a virtual table that does not have its data on its own; rather, the data is defined from one or more underlying base tables.
Views account for logical data independence as the growth and restructuring of base tables are not reflected in views.
Q #19) What are the advantages and disadvantages of views in the database?
Answer: Advantages of Views:
- As there is no physical location where the data in the view is stored, it generates output without wasting resources.
- Data access is restricted as it does not allow commands like insertion, updation, and deletion.
Disadvantages of Views:
- The view becomes irrelevant if we drop a table related to that view.
- Creating the view for large tables occupies much memory space.
Q #20) What do you understand by Functional dependency?
Answer: A relation is said to be in functional dependency when one attribute uniquely defines another attribute.
For example, R is a Relation, X and Y are two attributes. T1 and T2 are two tuples. Then,
T1[X]=T2[X] and T1[Y]=T2[Y]
Means, the value of component X uniquely defines the value of component Y.
Also, X->Y means Y is functionally dependent on X.
Q #21) When is a functional dependency said to be fully functional dependent?
Answer: To fulfill the criteria of fully functional dependency, the relation must meet the requirement of functional dependency.
A functional dependency ‘A’ and ‘B’ are said to be fully functional dependent when the removal of any attribute, say ‘X’ from ‘A’, means the dependency does not hold anymore.
Q #22) What do you understand by the E-R model?
Answer: E-R model is an Entity-Relationship model which defines the conceptual view of the database.
The E-R model shows the real-world entities and their association/relations. Entities here represent the set of attributes in the database.
Q #23) Define Entity, Entity type, and Entity set.
Answer:
Entity can be anything, be it a place, class or object, which has an independent existence in the real world.
Entity Type represents a set of entities that have similar attributes.
Entity Set in the database represents a collection of entities having a particular entity type.
Q #24) Define a Weak Entity set.
Answer: Weak Entity set is one whose primary key comprises its partial key and the primary key of its parent entity. This is the case because the entity set may not have sufficient attributes to form a primary key.
Q #25) Explain the terms ‘Attribute’ and ‘Relations’
Answer:
Attribute is described as the properties or characteristics of an entity. For example, Employee ID, Employee Name, Age, etc., can be attributes of the entity Employee.
Relation is a two-dimensional table containing several rows and columns, where every row represents a record of the relation. Here, rows are also known as ‘Tuples’ and columns are known as ‘Attributes’.
Database Interview Questions for Experienced
Q #26) What are VDL and SDL?
Answer: VDL is View Definition Language, which represents user views and their mapping to the conceptual schema.
SDL is Storage Definition Language, which specifies the mapping between two schemas.
Q #27) Define Cursor and its types.
Answer: Cursor is a temporary work area that stores the data, as well as the result set, that occurs after manipulation of data retrieved. A cursor can hold only one row at a time.
The 2 types of Cursor are:
- Implicit cursors are declared automatically when DML statements like INSERT, UPDATE, DELETE are executed.
- Explicit cursors have to be declared when SELECT statements that return more than one row are executed.
Q #28) What is a Database transaction?
Answer: Sequence of operations performed that changes the consistent state of the database to another is known as a database transaction. After the completion of the transaction, either the successful completion is reflected in the system or the transaction fails and no change is reflected.
Q #29) Define Database Lock and its types.
Answer: Database lock basically signifies the transaction about the current status of the data item i.e. whether that data is being used by other transactions or not at the present point of time.
There are two types of Database lock: Shared locks and Exclusive locks.
Q #30) What is Data Warehousing?
Answer: The storage and access to data that is being derived from the transactions and other sources, from a central location in order to perform the analysis, is called Data Warehousing.
Q #31) What do you understand by Join?
Answer: Join is the process of deriving the relationship between different tables by combining columns from one or more tables that have common values in each. When a table joins with itself, it is known as Self Join.
Q #32) What do you understand by Index hunting?
Answer: Index hunting is the process of boosting the collection of indexes, which helps in improving the query performance and the speed of the database.
Q #33) How to improve query performance using Index hunting?
Answer: Index hunting helps in improving query performance by:
- Using a query optimizer to coordinate queries with the workload.
- Observing the performance and effect of index and query distribution.
Answer: Clustered index alters the table and reorders how the records are stored in the table. The clustered index makes data retrieval faster.
A non-clustered index does alter the records that are stored in the table but creates a completely different object within the table.
Q #35) What are the disadvantages of a query?
Answer: Disadvantages of a Query are:
- Indexes are not present.
- Stored procedures are excessively compiled.
- Difficulty in interfacing.
Q #36) What do you understand by Fragmentation?
Answer: Fragmentation is a feature that controls the logical data units, also known as fragments, that are stored at different sites of a distributed database system.
Scenario-Based Advanced DBMS Interview Questions
Q #37) Define Join types.
Answer: Below are the types of Join, which are explained concerning the tables as an example.
employee table:
employee_info table:
a) Inner JOIN: Inner JOIN is also known as a simple JOIN. This SQL query returns results from both tables where the rows have a common value.
SQL Query:
SELECT * from employee, employee_info WHERE employee.EmpID = employee_info.EmpID ;
Result:
| EmpID | EmpName | EmpID | Address |
| 1000 | Rohan | 1000 | Delhi |
| 1001 | Shruti | 1001 | Mumbai |
| 1002 | Nikhil | 1002 | Delhi |
| 1003 | Naveen | 1003 | Kolkata |
b) Natural JOIN: This is a type of Inner JOIN that returns results from both the tables having the same data values in the columns of both tables to be joined.
SQL Query:
SELECT * from employee NATURAL JOIN employee_info;
Result:
Cross JOIN returns the result as all the records where each row from the first table is combined with each row of the second table.
SQL Query:
SELECT * from employee CROSS JOIN employee_info;
Result:
Let us make some modifications in the above tables to understand Right JOIN, Left JOIN, and Full JOIN.
employee table:
employee_info table:
a) Right JOIN: Right JOIN is also known as Right Outer JOIN. This returns all the rows as a result from the right table even if the JOIN condition does not match any records in the left table.
SQL Query:
SELECT * from employee RIGHT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);
Result:
b) Left JOIN: Left JOIN is also known as Left Outer JOIN. This returns all the rows as a result of the left table even if the JOIN condition does not match any records in the right table. This is exactly the opposite of Right JOIN.
SQL Query:
SELECT * from employee LEFT OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);
Result:
c) Outer/Full JOIN: A full JOIN returns the results of both the Left JOIN and Right JOIN.
SQL Query:
SELECT * from employee FULL OUTER JOIN employee_info on (employee.EmpID = employee_info.EmpID);
Result:
Q #38) What do you understand by ‘Atomicity’ and ‘Aggregation’?
Answer: Atomicity is the condition where either all the actions of the transaction are performed or none. This means, when there is an incomplete transaction, the database management system itself will undo the effects of the incomplete transaction.
Aggregation is the concept of expressing the relationship with a collection of entities and their relationships.
Q #39) Define Phantom deadlock.
Answer: Phantom deadlock detection is the condition where a deadlock does not exist, but due to a delay in propagating local information, deadlock detection algorithms identify the deadlocks.
Q #40) Define checkpoint.
Answer: Checkpoint declares a point before which all the logs are stored permanently on the storage disk, and is the inconsistent state. With crashes, the amount of work and time is saved as the system can restart from the checkpoint.
Q #41) What is Database partitioning?
Answer: Database partitioning is partitioning tables, indexes into smaller pieces to manage and access the data at a finer level.
This process of partitioning reduces the cost of storing a large amount of data as well as enhances the performance and manageability.
Q #42) Explain the importance of Database partitioning.
Answer: The importance of Database partitioning is:
- Improves query performance and manageability.
- Simplifies common administration tasks.
- Acts as a key tool for building systems with extremely high availability requirements.
- Allows accessing a large part of a single partition.
Q #43) Explain the Data Dictionary.
Answer: Data dictionary is a set of information describing the content and structure of the tables and database objects. The job of the information stored in the data dictionary is to control, manipulate, and access the relationships between database elements.
Q #44) Explain the Primary Key and Composite Key.
Answer: Primary Key is that column of a table whose every row of data is uniquely identified. Every row in the table must have a primary key, and no two rows can have the same primary key. Primary key value can never be null, nor can it be modified or updated.
Composite Key is a form of the candidate key where a set of columns uniquely identifies every row in the table.
Q #45) What do you understand by the Unique key?
Answer: A Unique key is the same as the primary key whose every row data is uniquely identified with a difference of null value, i.e. Unique key allows one value as a NULL value.
Q #46) What do you understand by Database Triggers?
Answer: A set of commands that automatically get executed when an event like Before Insert, After Insert, On Update, or On Delete of a row occurs in a table is called a Database trigger.
Q #47) Define Stored procedures.
Answer: A Stored procedure is a collection of pre-compiled SQL Queries, which, when executed, denotes a program that takes input, processes, and gives the output.
Q #48) What do you understand by B-Trees?
Answer: B-Tree represents the data structure in the form of a tree for external memory that reads and writes large blocks of data. It is commonly used in databases and file systems where all the insertions, deletions, sorting, etc., are done in logarithmic time.
Q #49) Name the different data models that are available for database systems.
Answer: Different data models are:
- Relational model
- Network model
- Hierarchical model
Q #50) Differentiate between ‘DELETE’, ‘TRUNCATE’, and ‘DROP’ commands.
Answer: After the execution of the ‘DELETE’ operation, COMMIT and ROLLBACK statements can be performed to retrieve the lost data.
After the execution of the ‘TRUNCATE’ operation, COMMIT, and ROLLBACK statements cannot be performed to retrieve the lost data.
‘DROP’ command is used to drop the table or key, like the primary key/foreign key.
Q #51) Based on the given table, solve the following queries.
Employee table
a) Write the SELECT command to display the details of the employee with empid as 1004.
SQL Query:
SELECT empId, empName, Age, Address from Employee WHERE empId = 1004;
Result:
b) Write the SELECT command to display all the records of the table Employees.
SQL Query:
SELECT * from Employee;
Result:
c) Write the SELECT command to display all the records of the employee whose name starts with the character ‘R’.
SQL Query:
SELECT * from Employee WHERE empName LIKE ‘R%’;
Result:
d) Write a SELECT command to display id, age, and name of the employees with their age in both ascending and descending order.
SQL Query:
SELECT empId, empName, Age from Employee ORDER BY Age;
Result:
SELECT empId, empName, Age from Employee ORDER BY Age Desc;
Result:
e) Write the SELECT command to calculate the total amount of salary for each employee from the Emp table below.
Emp table:
SQL Query:
SELECT empName, SUM(Salary) from Emp GROUP BY empName;
Result:
Conclusion
These are the set of Database interview questions and answers that are mostly asked in the interview.
Mostly the basics of every subject are questioned in the interviews. To everyone, if your basics are clear, you can reach top heights.
However, there may be some more tricky questions. Be confident and approach each question with clear subject knowledge.
Further reading:
- Top SQL Interview Questions
- Top Database Testing Interview Questions
- Top Data Modeling Interview Questions
- Top SQL Server Interview Questions
- Top MongoDB Interview Questions
Was this helpful?
Recommended Reading
-
This article will cover a list of all the most important and commonly asked “Database” interview questions and answers, which will help you to crack any interview. Organizational skills, strong problem-solving capability, attention to detail, and a high level of accuracy are some of the essential qualities that a person…
-
A Comprehensive List of Data Testing Tools With Features and Comparison: We have covered almost all categories of open source and commercial DB test tools - Test data generator tools, SQL-based tools, database load, and performance testing tools, UI enhanced tools, test data management tools, data privacy tools, DB unit…
-
A Complete Guide to Database Testing with Practical Tips and Examples: Computer applications are more complex these days with technologies like Android and also with lots of Smartphone apps. The more complex the front ends, the more intricate the back ends become. So it is all the more important to…
-
The Review and Comparison of The Best Free and Licensed Database Management Software Systems: A database is a collection of information that is organized in tables and stored on a computer system. This information can be updated or modified as required. We can also say it’s like a room in…
Great Experience