Top 30+ Database Testing Interview Questions With Detailed Answers

Our experts have answered the most frequently asked Database Testing interview questions for freshers and experienced candidates:

This is a collection of 30+ top database testing questions with detailed answers. These DB testing questions are mostly asked in QA testing interviews.

Go through these questions and answers if you are preparing for a software testing interview which also requires database skills.

What type of database (SQL) questions are asked in interviews for test engineer positions (not for database testers)?

This depends on many factors, whether these questions are for testing positions at entry-level or for experienced testing professionals. The depth of the database interview questions depends on the experience of the candidate.

Irrespective of the position, the candidate should always be clear and confident about the database concepts. For most software testing positions, you need to have database knowledge to perform some database checks. Almost all applications need an interaction with a database.

Let’s consider these interview questions for entry-level software testing positions.

Following questions can be asked in interviews:

• Basic, and to some extent nested SQL queries to fetch data from database tables.
• Examples of database statements for Create Database, Create table and Drop Table.
• Concept of “Primary Key”, “Foreign Key” and DB index.
• Examples of SELECT, INSERT, DELETE, ALTER, and UPDATE SQL statements.
• SQL joins (Inner Join, Left Join, Right Join and Full join) with examples.

Practice SQL join queries on dummy tables and seeing results.

For experienced level software testing positions, the interview questions depend on the job requirement. For such positions, interviewers expect detailed database knowledge from the candidates.

Note: If you get questions about database SQL queries, never say that “You get all query statements to be executed by developers”. It’s ok to say that you get help from developers to write complex SQL queries, but finally, you manage on your own.

Top Database Testing Interview Questions

Let’s start with the actual database testing questions and answers:

Q #1) What is Database testing?

Answer: Database testing/Back-end testing is a process of checking the impact of back-end database operation on front-end web/desktop applications.

It is divided into the following different categories:

• Data Validity testing: While doing this testing, testers should have good knowledge of SQL queries.
• Data Integrity testing: While doing this testing, testers/developers should know the referential integrity and different constraints.
• Database Performance testing: While doing this testing, testers/developers should be good at designing the structure of the table.
• Testing Procedure, Triggers, and Functions: While doing this testing, testers/developers should have a perfect understanding of the testing procedure, triggers, and functions.

Q #2) Why is database testing important?

Answer: Although we all know the database is not like a small table, it is indeed a big container of many tables and full of data that delivers data at the same time to many web/desktop applications.

The database testing process ensures that the correct and unique data (without bug) is delivered to the correct location. These bugs may cause some serious issues like deadlock, data corruption, poor performance, inconsistency, etc.

Q #3) In the Database Testing process, what do we usually check?

Answer: Database testing requires some in-depth knowledge and a more defined plan of approach to test the data of a particular application.

In this testing process we usually check,

• Field size validation
• Constraints.
• Indexes are done or not (for performance-related issues)
• Stored procedures
• The field size defined in the application is matching with that in the DB.

Q #4) Can you test the database manually, if yes, then how? Explain with an example.

Answer: You need to observe operational behavior. Observe the operations of the front-end and its effect on the back-end database. While adding a record (XYZ) from the front-end to the back-end database, manually check whether the record has been affected on the back-end database or not. Similarly, it will work to delete, update, etc.

For example, to keep a record of students who are giving exams, enter the student’s details from the front-end system and manually check the effects of this addition on the back-end database.

Q #5) How to test database procedures and triggers?

Answer: The process of testing database procedures and triggers requires the knowledge of input parameters, output parameters, and EXEC statements. EXEC statement is helpful in running the procedure and observing the behavior of the tables.

Let’s see how to test database procedures and triggers:

• First, open the database project that will be displayed in the solution explorer.
• Once the desired project is opened, go to the View menu, click on the database schema, and then open the project folder from the Schema View menu.
• Right-click on the object that has to be tested, and then click on the Create Unit Tests. A dialog box “Create Unit Tests” opens and displays all the folders and objects of the database project and the checkbox for the selected object is checked.
• Next, create a new language test project.
• Choose to either insert the unit test into an existing test class or create a new test class and click on OK.
• To configure the project, the Project Configuration dialog box appears to configure the test project settings. The process requires a database connection and data generation plan. The database connection is for executing the test results.
• Finally, configure the project and click on OK.

Q #6) What do you mean by data-driven tests?

Answer: Data-driven testing is a term used in the testing process where test scripts while execution read test data and/or output values from data files (Data pools, Excel files, ADO objects, CSV files, ODBC sources) instead of reading the same hard-coded values each time. The test helps testers in checking the efficiency of the application in handling various inputs.

Q #7) What is the database trigger, how to verify if the trigger is fired or not and can you invoke the trigger on demand?

Answer:

Database trigger: A trigger is basically a stored procedure used to maintain the integrity of the data present in the database. It executes automatically to respond to a certain event of a table/view in a database.

To verify if the trigger is fired or not, use the query of the common audit log which will display the trigger of the data table.
Triggers can’t be invoked on-demand, it is invoked when a table displays an action (INSERT, DELETE & UPDATE) defined on that particular table.

Q #8) After entering the data from the front-end application interface, how do you test whether a database is updated or not?

Answer: It totally depends on the application interface that you are using.

These are the following ways to test:

• You can only verify from the front-end when the application interface shows a view functionality of the data you entered. Mainly, Black box test engineers do this functionality verification test in this manner.
• If the application interface doesn’t provide view functionality of the data you entered, then you can check for database update by using relevant SQL/Oracle Query.
• Checkpoint function of WinRunner/QTP can also be used to check for database update.

Q #9) How to test the Stored Procedures?

Answer: Test engineers need to follow some steps to test the Stored Procedures,

1. First, the test engineer should understand the requirement and also the purpose of a particular Stored Procedure.
2. Next, verify whether all the indexes, joins, updates, deletions are precise in comparison with the tables mentioned in the Stored Procedure and also make sure that the Stored Procedure is in the common standard format, like comments, updated by, etc.
3. Then, for different sets of input parameters, verify the procedure calling name, calling parameters, and expected responses.
4. Manually, run the procedure with database client programs like TOAD, MySQL, or Query Analyzer.
5. To verify results against expected values, re-run the procedure by providing different parameters.
6. Finally, automate the tests with QTP.

Q #10) What are joins and mention different types of joins?

Answer: Join is used to combine two or more tables and displays as a single set of data.

In SQL, there are the following types of joins:

• Inner Join
• Outer Join
• Left Join
• Right Join

In Oracle/PLSQL, there are four different types of joins,

• Inner Join
• Left-Outer Join
• Right-Outer Join
• Full-Outer Join

Q #11) What do you mean by index and explain different types of indexes?

Answer: An Index is a database object, created on a column of the table to find data more quickly and efficiently (or we can say, it is used to speed up the searches/queries).

Different types of indexes are:

• B-Tree index
• Bitmap index
• Clustered index
• Covering index
• Non-unique index
• Unique index

Q #12) What do you mean by DBMS?

Answer: A database is a structured set of data. Database Management System (DBMS) is a computer software application that creates a communication network between users, other applications and the database to store, modify, and extract information from a database.

Famous DBMSs are My SQL, Postgre SQL, Microsoft SQL Server, Oracle, SAP, and IBM DB2.

Q #13) What do you mean by DML?

Answer: Data Manipulation Language (DML) is a member of computer programming language that facilitates us to select, insert, delete and update data in a database. DML is basically involved in Structured Query Language (SQL) that facilitates users to retrieve and use data in a relational database.

Q #14) What do you mean by DCL commands and explain the types of commands used by DCL?

Answer: Data Control Language (DCL) is a component of SQL, which controls the use of data stored in a database.

There are two types of DCL Commands:

• Grant: This command facilitates users to access privileges to database
• Revoke: Stop permitting users to access the database

Q #15) What do you mean by white box testing and black box testing?

Answer:

Black-box testing is a software testing technique to test the functional behavior of an application without getting into its internal structures of an application. Core application’s code/internal structure and programming knowledge are not required for black-box testing, but core knowledge of application functionality is required to test the application.

White Box Testing (WBT) is also known as Code-Based Testing or Structural Testing to test the internal structure of a software application. In the case of white-box testing, the tester should have a good understanding of the internal structure of an application as well as good knowledge of programming skills to design test cases and internal structure of an application.

Q #16) Does QTP support SQL queries, how do you use SQL queries in QTP?

Answer: Although QTP doesn’t carry any built-in function for database connectivity, testers use VBScript language to connect and interact with databases using ADODB objects.
ADODB objects are divided into four different properties/methods which helps in successfully creating a database connection,

• Connection: Useful in forming a connection with a Database
• Command: Useful in executing a SQL command(Queries/Stored Procedures)
• Fields: Useful in retrieving a particular column from a record-set after executing a Query/Stored Procedure.
• Recordset: Used to retrieve data from a database

Q #17) Which SQL statements can be used in Database Testing?

Answer: All DDL, DML, DCL, and TCL statements of SQL can be used in database testing,

(i) DDL (Data Definition Language): All statements related to this can be used in defining the database structure or schema.

Statements: CREATE, ALTER, DROP, TRUNCATE, COMMENT, and RENAME

(ii) DML (Data Manipulation Language): All statements related to this can be used for managing data within schema objects.

Statements: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, and LOCK TABLE.

(iii) DCL (Data Control Language): GRANT Statement and REVOKE Statement.

(iv) TCL (Transaction Control Language): All statements related to this can be used to manage the changes made by DML statements. Using TCL, statements can be grouped together into logical transactions.

Statements: COMMIT, SAVEPOINT, ROLLBACK, and SET TRANSACTION.

Q #18) In database testing, how to do the data load testing?

Answer: For data load testing,

• You need to have knowledge of source database (data tables, columns, associated constraints, data types) and destination database (data tables, columns, data types, and constraints).
• You need to check the compatibility between the source database and destination database via the DTS package.
• You need to Open corresponding DTS package in SQL Enterprise Manager and run the DTS package (if you are using SQL Server).
• Compare the column’s data of Source and the Target.
• Check the number of rows for Source and Target.
• After updating the data in the Source database, check whether the changes have been reflected in the destination database or not.
• Check about junk character and NULLs.
• Database load testing, check the volume of the database server to process the queries and also check the response time of database server and client.

Q #19) Is “a fast database retrieval rate” a testable requirement?

Answer: No. I don’t think so because the requirement appears to be unclear. SRS should clearly display the performance and transaction requirements, i.e. It should mention like; “Database retrieval rate of 5 microseconds”.

Q #20) Without involving database checkpoints, how you can test a SQL Query in QTP?

Answer: The given below script procedure will help us in connecting to the database where we can test both; the database and queries.

a) The script procedure for database connection,
db_connect(“query1”,DRIVER={driver name};SERVER=server_name;

UID=uidname;PWD=password;DBQ=database_name “);

b) The script procedure to execute the query,
db_excecute_query(“query1″,”write query u want to execute”);

-Condition to be mentioned-

c) The script procedure to disconnect the connection with the database,
db_disconnect(“query”);

Q #21) In database testing, what all things are required for writing good test cases?

Answer: Knowledge of the following things is required before writing the database test cases,

1. Understand the application completely and also the functional requirements of the application.
2. Check-out other entities that have been used in an application like back-end database tables, joins between the tables, cursors (if any), triggers (if any), stored procedures (if any), input parameters and output parameters for developing that requirement.
3. After collection of all necessary information, write down the test case with different input values for examining all the resources.

Writing test cases for back-end testing is the opposite of functional testing. In this case, one should use the white box testing technique.

Q #22) What is retesting & how it is different from data-driven testing?

Answer: After the execution of the test in terms of finding the defect that has been already detected and fixed, re-execution of the same test with different input values to confirm the original defect has been successfully removed is called Re-testing or confirmation Testing.

Retesting is also called Data-Driven Testing, but the difference between both is, Retesting is a manual testing process whereas application testing is done with the entire new set of data.

Data-Driven Testing (DDT): It is an automation testing process where an application will be tested with multiple test data. It is simpler and easier than retesting where the tester just sits in front of the system and enters different new input values manually from the front-end interface, it is really a boring technique.

Q #23) What are the types of data-driven testing?

Answer:

There are four types of data-driven testing:

• Dynamic test data submission through the keyboard (key-driven test): In some cases, testers retest a particular application with different input values to validate the calculation via dynamic submission. So, to submit the input value, testers can use the following function in TSL; script– create_input_dialog (“label”);
• Data-Driven Tests via flat files (.txt,.doc): In some cases, testers do the re-testing by considering the data of the flat file. Testers gather these flat files from old databases/customers.
• Data-Driven Tests via front-end objects: In some cases, testers create automation scripts by considering the front-end object values, such as list, menu, table, data window, ocx, etc.
• Data-Driven Tests via excel sheet: In some cases, testers take the help of this data-driven test to run the script for multiple inputs. These multiple inputs reside in columns of an excel sheet. We need to collect this test data from the backend tables.

Q #24) How to write a query to get the second largest value from a given column of a table?

Answer: This is a query to get the second largest value from a given column of a table.

SELECT MAX(COLUMN_NAME) FROM TABLE_NAME
WHERE COLUMN_NAME <
(SELECT MAX(COLUMN_NAME) FROM TABLE_NAME)

For Example,
To get the second-largest marks from “Marks” column of a “Students” table

Select Max(Marks) from Students
Where Marks < (Select Max(Marks) from students)

Q #25) How to write a query to get the 10^th highest salary from an employee table?

Answer: This is a query to get the nth highest salary from an employee table,

SELECT *
FROM Employee Emp1
WHERE ( n ) = (
SELECT COUNT( DISTINCT ( Emp2.Employee_Salary ) )
FROM Employee Emp2
WHERE Emp2.Employee_Salary >= Emp1.Employee_Salary
)

Here, you can replace them with any number.

For example, if you want to get the 10th highest salary, then replace n with 10.

SELECT *
FROM Employee Emp1
WHERE (10) = (
SELECT COUNT( DISTINCT ( Emp2.Employee_Salary ) )
FROM Employee Emp2
WHERE Emp2.Employee_Salary >= Emp1.Employee_Salary
)

Q #26) How to write test cases from requirements, do the requirements signify the exact functionality of AUT?

Answer: Yes, the requirements should signify the exact functionality of AUT.
To do so,

1. Examine the requirements to understand the overall functionality.
2. Next, decide which test design methods are suitable to write test cases – Black Box test design methods like; Equivalence Partitioning, Boundary Value Analysis, Error Guessing and Cause-Effect Graphing.
3. Once you decide which test design method is suitable, write your test cases in the requirement analysis and design phase.

This way, you will ensure that all the requirements are testable.

Q #27) What is the test scenario to test a database migrated from one SQL Server to another?

Answer: First of all, we need to check what all enhancements and changes happened to the SQL Server where we are planning to migrate.
Next, design the test case according to the following consideration,

• The data type that has been used.
• Length of the data field of SQL Server (Server into which we are migrating the data) should be same as the SQL Server from where we are taking out the data.
• Each and every task should be organized correctly.

Q #28) What does the RDBMS stand for and what are the major RDBMS used by SQL?

Answer: RDBMS stands for Relational Database Management Systems. Major RDBMS that is involved with SQL includes Sybase, Oracle, Access, Ingres, Microsoft SQL Server, etc.

Q #29) Explain performance testing and the bottlenecks of it.

Answer: Performance testing is a software testing technique to determine how a system performance in terms of speed, sensitivity, and stability under a heavy workload. This testing requires expensive tools and well-trained and experienced testers for operation.

Q #30) What is CMMI and describe different levels of CMM?

Answer: CMMI stands for Capability Maturity Model Integration is a process development training and evaluation model for various business processes in software engineering.

Five CMM Maturity levels for an organization are:

1. Initial: At this level, organizations don’t have any verified technique and environment, so usually organizations budget and schedule time go over.
2. Repeatable: At this level, organizations have basic techniques and guidelines to limit the cost and schedule time, capable of repeating the same in the next similar project.
3. Defined: At this level, all techniques are well organized and explained in a clear and standard form.
4. Managed: At this level, organizations are much more developed than the Defined level. Here, techniques are contacted using statistical and other quantitive techniques.
5. Optimizing: Organizations determinedly attempt to develop performance via modern technical progress.

Q #31) What is the meaning of the Record in a database?

Answer: In a database, a record is the set of values/fields of a specific entity.

For Example, Office_Employee Record, Book Record, etc.

We hope you like the detailed database interview questions and answers article. Let us know in case of any queries or if you want us to cover more questions.