Our experts answered most frequently asked Database Testing interview questions for freshers and experienced candidates.
This is a collection of 30+ top DB testing interview questions with detailed answers. These DB testing questions are mostly asked in QA testing interviews.
Go through these questions and answers if you want to prepare for software testing interview which also requires Database skills.
Top Database Testing Interview Questions
What type of database (SQL) questions asked in interviews for test engineer position (not for database tester)?
Database Testing Interview Questions:
This depends on many factors whether these questions are for testing positions at entry level or for experienced testing professionals. The depth of 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 the database interview questions for entry-level software testing positions. For entry-level testing positions generally following questions can be asked in interviews:
1) Basic and to some extent nested SQL queries to fetch data from database tables.
2) Examples of database statements for Create Database, Create table and Drop Table.
3) Concept of “Primary Key”, “Foreign Key” and DB index
4) Examples of Select, Insert, Delete, Alter and Update SQL statements.
5) SQL joins (Inner Join, Left Join, Right Join and Full join) with examples.
Practice SQL join queries on dummy tables and see results.
For experienced level software testing positions, database interview questions depend on the job requirement. For such positions, interviewers expect detailed database knowledge from candidates.
One more important point – 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.
Let's start with actual DB questions and answers:
Q#1: What is Database testing?
Ans: Database testing/Back-end testing is a process of checking an impact of back-end database operation on front-end web/desktop application.
Database testing is divided into following different categories,
- Data validity testing: While doing this testing, testers should be having good knowledge of SQL queries.
- Data Integrity testing: While doing this testing, testers/developers should know referential integrity and different constraints.
- Database Performance testing: While doing this testing, testers/developers should be good in designing the structure of the table.
- Testing of Procedure, triggers, and functions: While doing this testing, testers/developers should be having a perfect understanding of the testing procedure, triggers and functions.
Q#2: Why database testing is important?
Ans: Although, we all know the database is not as, like a small table, it is a big container of many tables and full of data, delivers data at a time to many web/desktop applications. Database testing process ensures that the correct and unique data (without bug) delivers to the correct location. These bugs may cause some serious issues like; dead-locking, data corruption, poor performance, inconsistency, etc….
Q#3: In the Database Testing process, what do we usually check?
Ans: Database testing requires some in-depth knowledge and more defined plan of approach to test the data of a particular application. In Database Testing process we usually check,
- The field size validation
- Check 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 database manually, if yes; then how, explain with example?
Ans: It needs observing operation; observe the operations of 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 the record has been affected on the back-end database or not. Similarly, it will work for delete, update,… etc.
For Example: To keep the record of students who are giving exam; enter the student's detail from front-end system and manually check the effect of this addition on the back-end database.
Q#5: How to test database procedures and triggers?
Ans: The process of testing database procedures and triggers requires the knowledge of input parameters, output parameters, and EXEC statement. 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 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 in 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 database connection and data generation plan. The database connection is for executing test result.
- At last, configure the project and click on OK.
Q#6: What do you mean by data-driven test?
Ans: Data-driven testing is a term used in the testing process whereas 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 the trigger is fired or not and can you invoke trigger on demand?
Ans: Database trigger: A trigger is basically a stored procedure used to maintain the integrity of the data present in the database, executes automatically to respond to a certain event of a table/view in a database.
To verify, the trigger is fired or not; use the query of common audit log will display the trigger of the data table.
Triggers can't be invoked on demand, it invoked when 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?
Ans: It totally depends on the application interface that you are using. These are some following ways,
- You can verify only from the front-end when application interface shows view functionality of the data you enter. Mainly, Black box test engineers do this functionality verification test in this way.
- If application interface doesn’t provide view functionality of the data you enter, 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?
Ans: Test engineer needs to follow some steps to test the Stored Procedures,
First Step:- First the test engineer should understand the requirement and also the purpose of a particular Stored Procedure.
Second Step:- 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 common standard format, like; comments, updated by, etc.
Third Step:- Then, for different sets of input parameters, verify the procedure calling name, calling parameters, and expected responses.
Fourth Step:- Manually, run the procedure with database client programs like; TOAD, MySQL, or Query Analyzer.
Fifth Step:- To verify results against expected values; re-run the procedure by giving different parameters.
Sixth Step:- Finally, automate the tests with QTP.
Q#10: What are joins and mention different types of joins?
Ans: Join is used to combine two or more than two table and displays as a single set of data.
In SQL, the types of joins are,
- 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?
Ans: 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?
Ans: A database is a structured set of data and a database management system (DBMS) is a computer software applications that create a communication network between users, others applications and 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?
Ans: A data manipulation language (DML) is a member of computer programming language facilitates us to select, insert, delete and update data in a database. DML is basically involved in Structured Query Language (SQL), 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?
Ans: Data Control Language (DCL) is a component of SQL, controls the use of data stored in a database. There are two types of DCL Commands,
Grant = The command facilitates users to access privilege to database
Revoke = Stop permitting users to access the database
Q#15: What do you mean by white box testing and black box testing?
Ans: 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 is 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 case of white-box testing, the tester should be having 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 to use SQL queries in QTP?
Ans: Although QTP doesn't carry any built-in function for database connectivity, tester uses VBScript language to connect and interact with databases using ADODB objects.
ADODB objects are divided into four different properties/methods will help in successfully creating 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?
Ans: All DDL, DML, DCL, and TCL statements of SQL can be used in database testing,
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
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.
DCL (Data Control Language): GRANT Statement and REVOKE Statement.
TCL (Data Manipulation 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?
Ans: 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).
- Then you need to compare the column's data of Source and the Target.
- You have to check the number to rows of Source and Target.
- After updating data in the Source database, you need to check whether the changes have been reflected on destination database or not.
- You need to 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 “A fast database retrieval rate” a testable requirement?
Ans: No. I don’t think so because the requirement appears to be unclear. The SRS should clearly display the performance or 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?
Ans: The given below script procedure help us in connecting to the database where we can test both; the database and queries.
1) The script procedure for database connection,
2) The script procedure to execute the query,
db_excecute_query(“query1″,”write query u want to execute”);
-Condition to be mentioned-
3) The script procedure to disconnect the connection with database,
Q#21: In database testing, what all things are required for writing good test cases?
Ans: Knowledge of following things is required before writing the database test cases,
- At first, understand the application completely and functional requirement of the application.
- Second, check-out other entities that have been used in an application; back-end database tables, joins between the tables, cursors (if any), triggers (if any), stored procedures (if any), input parameter and output parameters for developing that requirement.
- After collection, 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 opposite to functional testing, one should use the white box testing technique.
Q#22: What is retesting & how it is different from data driven testing?
Ans: After 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:- It is a manual testing process whereas application testing done with entire new set of data.
DataDriven Testing(DDT):-It is an Automation testing process where an application will be tested with multiple test data.It is simple and easy than retesting where tester just sits in front of the system and enter different new input values manually from front-end interface, it is really boring technique.
Q#23: What are the types of data-driven testing?
Ans: There are four types of data-driven testing,
- Dynamic test data submission through keyboard (key driven test)
- Data Driven Tests via flat files ( .txt, .doc)
- Data Driven Tests via front-end objects
- Data Driven Tests via excel sheet
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 those 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 objects values, such as; (a) list (b) menu (c) table (d) data window (e) 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 have 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?
Ans: 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)
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 10th highest salary from an employee table?
Ans: This is a query to get nth highest salary from an employee table,
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 10th the highest salary, then replace n with 10.
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?
Ans: Yes, the requirements should signify the exact functionality of AUT.
To do so,
First, examine the requirement to understand overall functionality.
Next, decide which test design methods are suitable to write test case – Black Box test design methods like; Equivalence Partitioning, Boundary Value Analysis, Error Guessing and Cause-Effect Graphing.
Once you decide which test design method is suitable, write your test cases in requirement analysis and design phase. In 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?
Ans: 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,
- A 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?
Ans: RDBMS stands for Relational Database Management Systems. Major RDBMS that are involved with SQL are Sybase, Oracle, Access, Ingres, Microsoft SQL server etc.
Q#29: What is performance testing and the bottlenecks of it?
Ans: Performance testing is a software testing technique to determine that how a system performance in terms of speed, sensitivity, and stability under a heavy workload.
The performance testing requires expensive tools and well-trained and experienced testers for operation.
Q#30: What is CMMI and describe different levels of CMM?
Ans: CMMI stands for Capability Maturity Model Integration is a process development training and evaluation model of 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 that; 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 in repeating the same in the next similar project.
3) Defined: At this level, all techniques are well organized and explained clear and standard form.
4) Managed: At this level, organizations are much more developed than Defined level. Here, techniques are contacted using statistical and other quantitive technique.
5) Optimizing: Organizations determinedly attempt to develop performance via modern technical progress.
Q#31: What is the meaning of Record in a database?
Ans: 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 these detailed Database interview questions with answers. Let us know in case of any queries.