HTML
   HTML5
   CSS
   CSS3
   JavaScript
   jQuery
   AngularJS
   Articles
   Tech Articles
   Blog
Google News
logo
Database Testing Interview Questions
1 .
What is Database Testing?
Database testing/Back-end testing is a process of checking the impact of back-end database operation on front-end web/desktop applications. Database testing is a procedure of validating data stored in a database and its related functionality and objects controlling database entities like tables, stored procedures, views, triggers and functions.

It is divided into 4 categories like as : 
 
Data Validity Testing : While doing this testing, testers need to know SQL queries to retrieve data from the database and validate it.
 
Data Integrity Testing : Here, the testers need to validate data against various constraints and referential integrity in the database.
 
Database Performance Testing : Here, the testers need to validate the performance of various triggers, indices, procedures to see how effectively the operations occur. The system also needs to execute database transactions efficiently. Good knowledge of database structures needs to be there while doing this.
 
Testing of Procedure, Triggers, and Functions : While doing this testing, testers/developers should have a perfect understanding of the testing procedure, triggers, and functions.
2 .
What do you understand by data-driven testing?
Data-driven testing is an automated testing framework that involves using data contained in a spreadsheet or table as input values to test scripts. This is done to prevent creating individual test cases with various inputs for the same functionalities. The test scripts' inputs might be in XML, CSV, XLS, and some other data formats.
 
There are 4 main types of data-driven testing based on the source of test data inputs:
 
Key-driven : Here, dynamic data is provided as inputs to the test cases by employing a keyboard to retest the application and validate the business logic.

Flat files : Application retesting is done by taking input data stored in flat files like .doc or .txt.

Front-end object : The test scripts use data from front-end objects like list, table, menu, data window etc.

Spreadsheet/Excel : Here, the test data is taken from data stored in excel sheets or spreadsheets.
3 .
Why database testing is important?
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.
4 .
In Database Testing, What Do We Need To Check Normally?
Normally, the things that we check in database testing are :
 
* Constraint Check
* Validation of a Field size
* Stored procedure
* Matching application field size to database
* Indexes for performance based issues
5 .
Why do we need to perform database testing?
There are multiple reasons why database testing is performed. There is a need to perform data integrity, validation and data consistency check on database as the backend system is responsible to store the data and is accessed for multiple purpose.
 
Some of the common reasons why one needs to perform Database testing are as follows :
 
* To ease the complexity of calls to database backend, developers increase the use of View and Stored Procedures.
 
* These Stored procedures and Views contain critical tasks such as inserting customer details (name, contact information, etc.) and sales data. These tasks need to be tested at several levels.
 
* Black box testing performed on front-end is important, but makes it difficult to isolate the problem. Testing at the backend system increases the robustness of the data. That is why database testing is performed on back end system.
 
* In a database, data comes from multiple applications and there is a possibility that harmful or incorrect data is stored in the database. Therefore, there is a need to check database components regularly. In addition, data integrity and consistency should be checked regularly.
6 .
What are the different types of Normal forms?
There are four types of Normal Forms. They are :
 
* First Normal Form (1NF)
* Second Normal Form (2NF)
* Third Normal Form (3NF)
* Boyce Codd Normal Form (BCNF)
7 .
What is Database Stress Testing?
Database Stress Testing is one of the methods used for gauging the database performance by subjecting it to the heavy load that would potentially cause the system to fail at some point. This is used for identifying the breakdown points of the database application. Before performing this testing, proper planning needs to be done for avoiding the wastage of resources. This testing is also known as fatigue testing. It uses tools like LoadRunner and JMeter for accomplishing this.
8 .
What are joins and mention different types of joins?
Join is used to combine two or more than two tables and displays as a single set of data.
 
In SQL, there are 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
9 .
What are different constraints in SQL?
* NOT NULL Constraint
* CHECK Constraint
* PRIMARY Constraint
* FOREIGN Constraint
* DEFAULT Constraint 
10 .
How many operators are there in SQL?
There are different types of operators in SQL. They are : 
 
* Arithmetic Operators
* Set Operators
* Logical Operators
* Conditional Operators
11 .
What is a join in SQL? What are the different types of joins?
Joins are used to connect two or more tables in some logical manner. Common types of joins include: Inner join, Non-equijoin, Outer join, Self-join, and Cross join.
12 .
How can you test an SQL Query in WinRunner?
Step 1 : Connect to the database :
db_connect(query1 DRIVER {drivername};SERVER server_name;UID uidname;
PWD password;DBQ database_name );
 
Step 2 : Execute the query of the database :
db_excecute_query (write the required query that is to execute); Specify the appropriate condition


Step 3 : Disconnect the database connection by using :

db_disconnect(query);

Using Output database checkpoints, SQL manual queries options must be selected. Here, the select query can be written.

13 .
What are indexes and mention different types of indexes?
Indexes are database objects and they are created on columns. To fetch data quickly they are frequently accessed. Different types of indexes are :
 
* B-Tree index
* Bitmap index
* Clustered index
* Covering index
* Non-unique index
* Unique index
14 .
In data base testing, what are the steps to test data loading?
Following steps need to follow to test data loading
 
* Source data should be known
* Target data should be known
* Compatibility of source and target should be checked
* In SQL Enterprise manager, run the DTS package after opening the corresponding DTS package
* You have to compare the columns of target and data source
* Number of rows of target and source should be checked
* After updating data in the source, check whether the changes appears in the target or not.
* Check NULLs and junk characters
15 .
What is the database trigger, how to verify the trigger is fired or not and can you invoke the trigger on demand?
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 the trigger is fired or not; use the query of the common audit log 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.
16 .
How to test the Stored Procedures?
Test engineer needs to follow some steps to test the Stored Procedures :
 
* First, the test engineer should understand the requirement and also the purpose of a particular Stored Procedure.

* 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.

* Then, for different sets of input parameters, verify the procedure calling name, calling parameters, and expected responses.

* Manually, run the procedure with database client programs like TOAD, MySQL, or Query Analyzer.

* To verify results against expected values, re-run the procedure by giving different parameters.

* Finally, automate the tests with QTP.
17 .
What do you mean by DML?
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.
18 .
What do you mean by DCL commands and explain the types of commands used by DCL?
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
19 .
Explain how to use SQL queries in QTP?
In QTP using output database check point and database check, you have to select the SQL manual queries option. After selecting the manual queries option, enter the “select” queries to fetch the data in the database and then compare the expected and actual.
20 .
What is the way of writing testcases for database testing?
Writing a testcases is like functional testing. First you have to know the functional requirement of the application. Then you have to decide the parameters for writing testcases like
 
Objective : Write the objective that you would like to test

Input method : Write the method of action or input you want to execute

Expected : how it should appear in the database
21 .
Does QTP support SQL queries, how to use SQL queries in QTP?
Although QTP doesn’t carry any built-in function for database connectivity, testers uses 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
22 .
Which SQL statements can be used in Database Testing?
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 (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.
23 .
What do you understand by Non-functional testing in terms of database testing?
Non-functional testing in terms of testing databases involves various testing methods like stress testing, load testing, usability testing, security testing, etc that tests and validate the non-functional business requirements. It helps in the quantification of risks with regards to the database system in terms of how the system performs under heavy load. Addresses some of the following questions which help to mitigate the risks if detected earlier :
 
Does the system break?
Does the system slow down?
Are there any risks to the system as the load or stress on the system increases?
Is the system scalable and usable whenever new business requirements come in?
 
Non-functional testing also helps in identifying what are the minimum system requirements to run effectively without any limitations on hardware or software.
24 .
What do you understand by validation of ACID properties in database testing?
ACID stands for Atomicity, Consistency, Isolation and Durability. Everything refers to a property of database transactions.
 
Atomicity : This refers to that the transactions are atomic which means that the result of the transaction can either be a success or a failure and nothing in between.

Consistency : This property implies that the state of the database has to remain valid after the completion of the transactions.

Isolation : This implies that the presence of multiple transactions does not hinder each other as well as the state of the database.

Durability : This states that post-commit of a transaction, the data should not be lost even if there is a power failure.
25 .
What is load testing and give some examples of it?
Load testing is a technique for determining how well a system works. When a load exceeds a user's pattern, stress testing is carried out. Load testing involves downloading a large number of files, running multiple apps on a single computer, sending a large number of emails to a server, and assigning multiple tasks to a printer one by one.
26 .
How do you validate whether the database table has been updated after you enter data from the front-end application?
This depends on the front-end application’s interface which is being used. We can test by using the following ways :
 
Check for the updated data in the front-end application’s view. The view should be updated with the new value post successful insertion. This kind of testing constitutes black box testing.

If there is no view available, then we can go to the database system, run the select query and check for the data manually.

We can also use the WinRunner or QTP for checking the database update.
27 .
How is stored procedure testing done?
Stored Procedures are similar to user-defined functions that are invoked using Cal Procedure or Execute Procedure statements. The output of these procedures is generally in the form of result sets. The stored procedures are stored in RDBMS and are readily available for the applications.

These can be tested using the following techniques :
 
White box testing : Here, the stubs are used for invoking stored procedures and the results of those are validated against our expected results.

Black box testing : We perform operations on the front-end view of our application in a way that the stored procedures are invoked and we validate the result by observing the changes in the view.
28 .
How does QTP evaluate test results?
When the testing is finished, QTP will prepare a report. This report will show the test results, including checkpoints, system alerts, and failures. The test results box will indicate any mismatches discovered at the checkpoints.
29 .
How can you write test cases from requirements and do the requirements represent the exact functionality of AUT (Application Under Test)?
To write test cases from requirements, you must first conduct a thorough functional analysis of them. Then you evaluate suitable test case design methodologies for building the test cases, such as equivalence partitioning, black box design, cause-effect graphing, and so on. Yes, the specifications detail the AUT's capabilities in great detail.
30 .
Explain with an example how you can test the database manually?
Manual database testing is also a possibility. This necessitates human effort in observing the application's behavior as it is subjected to various manual test cases written by the testers. This can also include seeing how front-end applications behave after the backend database has been altered. Following the execution of the queries, we can manually check whether a record was created, changed, or destroyed in the database tables.
 
Consider a database system for student information. The activities performed on the Student tables database can be manually verified by seeing the status of the records after the query has been executed, or by inspecting the front end interface.
31 .
Without involving database checkpoints, how you can test a SQL Query in QTP?
The given below script procedure help us in connecting to the database where we can test both; the database and queries.
 
* The script procedure for database connection,
db_connect(“query1”,DRIVER={driver name};SERVER=server_name;

UID=uidname;PWD=password;DBQ=database_name “);
 
* The script procedure to execute the query,
db_excecute_query(“query1″,”write query u want to execute”);

-Condition to be mentioned-
 
* The script procedure to disconnect the connection with the database,
db_disconnect(“query”);
32 .
What is retesting & how it is different from data-driven testing?
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 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 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 a boring technique.
33 .
How to write a query to get the second largest value from a given column of a table?
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);
34 .
Why do you think database testing is important in the field of software testing?
Database testing ensures that the data in the database that is received from various sources are correct and as per the business requirements or not. It helps to analyse the risks associated with incorrect data, protect from data losses due to incorrect transactions and also properly check for unauthorized access to the data. In most of the applications, the User Interface is given the most importance while testing to validate the correctness of the data. But in cases where there is a lack of UI or view, it is very much essential to test the quality of the information in the database.
 
Consider the example of a banking application where lots of users use it to perform transactions. While testing the database, it is very much important to note the following points :
 
* Does the application store the relevant transaction information and display the data correctly to the correct user?
* Ensure that information is not lost during the transaction process.
* Ensure that the system doesn’t store partially performed transactions or aborted transactions that are not saved.
* Ensure that only authorized users have access to the relevant information.

To perform these validations, it is very much needed to possess the art of data testing or database testing.
35 .
How to invoke a trigger on demand?
Triggers can’t be invoked on demand. They are invoked when an associated action (insert, delete & update) happens on the table on which they are defined. Triggers are used to apply business rules, auditing and also for the referential integrity checks.
36 .
What is the test scenario to test a database migrated from one SQL Server to another?
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.
37 .
Explain performance testing and the bottlenecks of it.
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.
38 .
What is CMMI and describe different levels of CMM?
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 :
 
Initial : At this level, organizations don’t have any verified technique and environment, so usually organizations budget and schedule time go over.

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.

Defined : At this level, all techniques are well organized and explained clear and standard form.

Managed : At this level, organizations are much more developed than the Defined level. Here, techniques are contacted using statistical and other quantitive technique.

Optimizing : Organizations determinedly attempt to develop performance via modern technical progress.
39 .
What RDBMS stands for and what are the important RDMBS that SQL use?
RDBMS stands for Relational Database Management Systems that use SQL, and the important RDBMS that SQL uses are Sybase, Oracle, Access ,Ingres, Microsoft SQL server etc.
40 .
What is structural database testing?
This testing involves testing database structures that depend on schema, tables, triggers, functions, procedures and also the servers used for databases.

There are the some types of structural testing :
 
Schema Testing : Here, the exact schema name should map between both front-end and backend. The schema validation is very important because in some cases, the schema of the tables would be different from the actual business requirement and the front-end applications. This also involves verifying unmapped tables or views or columns.

Tables Testing : This testing involves testing table names and testing columns. The names mapped to frontend and backend should be the same and the datatype and sizes of the columns should be as per the requirements specified by the business. It also involves testing constraints applied to the tables and columns. Furthermore, testing of indexes based on the property of clustered or non-clustered and their functionality should also be tested accordingly.

Procedure and Function Testing : Here, the testers have to test the procedures and functions available in the database and validate for the list of points below:

* Did the team follow the business requirements correctly?
* Is the code following good practices and proper naming conventions?
* Are the parameters for input and output of these as per the expected requirements?
* Are the exceptions handled?
* Are the procedures and functions inserting data to required tables properly?
* Are the procedures and functions updating/modifying data in the required tables properly?

Trigger testing : The testing rules are similar to the procedure or functional testing. In addition to those rules, we have to check if the triggers are getting triggered/executed at required instants.

Database Server Testing : Test whether the database configurations, RAM, capacity of processors, storage capacity etc based on the business requirements.
41 .
Write the step-by-step process to test the database?
Database testing follows the same methods as other types of testing. The procedures that are followed are as follows :
 
* Prepare the testing environment as well as the scripts for the tests.
* The script should be run
* Examine the results of the exam
* Compare the outcomes to what you predicted.
* The developers and stakeholders should be notified of the validation findings.
42 .
Why are SQL constraints used in a database?
SQL Constraints can be used to provide the rules for the database table's records. The action can be stopped if any constraints are not met. Constraints are defined when database objects are created. ALTER commands can also be used to change it. SQL has six major constraints:
 
Not Null : This constraint is used to say that a column can't have any      Null values in it.

Unique : This constraint ensures that each column has a distinct value, preventing values from being reused.

Primary Key : This constraint also combines NOT NULL and UNIQUE constraints and indicates that one or more combinations having this key is used for uniquely identifying a record in the database table.

Foreign Key : It is used for ensuring the referential integrity of that record in the database table. It matches the value of a column in one table with the value defined as the primary key in the other table.

Check : This command is used to verify that the column values meet the stated criteria.

Default : This constraint is used for adding default values to the column whenever needed. If the user specifies any value in the DEFAULT constraint, then at the time of record creation, if we do not specify values to that column, the default value will be saved in the table.
43 .
How can you test Transactions in SQL Databases?
Transactions require the ACID properties to be satisfied. To achieve that, we use the below statements:
BEGIN TRANSACTION TRANSACTION#
END TRANSACTION TRANSACTION#

ROLLBACK TRANSACTION#
The Rollback command ensures that the database changes are rolled back to the previous stable commit and ensures consistency. Once the transactions are executed, we can make use of SELECT queries to run on the tables where the transaction took place and validate the data.
44 .
In Oracle, what is cold backup and hot backup?
Cold Backup : Cold back is known as taking back up of database files, redo logs, and control file when the instance is shut down. This is a file copy, usually from the disk directly to tape. You must shut down the instance to guarantee a consistent copy.
 
If a cold backup is performed, the only option available in the event of data file loss is restoring all the files from the latest backup. All the changes that are performed after the last backup is lost.
 
Hot Backup : Some databases can’t shut down while making a backup copy of the files, so cold backup is not an available option. For these types of database we use hot backup.
45 .
Name a some objectives of Database security testing.
Database security testing is performed to check the following aspects  :
 
* Authentication
* Authorization
* Confidentiality
* Availability
* Integrity
* Resilience
46 .
What is SQL Injection threat?
SQL Injection threat is the most common type of attack in a database system where malicious SQL statements are inserted in database system and executed to get critical information from database system. This attack takes advantage of loopholes in implementation of user applications. To prevent this user inputs fields should be carefully handled.
47 .
Name a few tools that can be used to perform database security testing.
The following tools can be used to perform database security testing: Zed Attack Proxy, Paros, Social Engineer Toolkit, Skipfish, Vega, Wapiti, and Web Scarab.
48 .
What are the common challenges that you face while performing database testing?
The common challenges that one faces while performing database testing are as follows :
 
* Testing scope is too large
* Scaled-down test database
* Changes in database structure
* Complex Test Plans
* Good understanding of SQL
49 .
What are the key points that should be considered while performing database recovery testing?
The following key points are to be considered while performing database recovery testing :
 
* Time span when changes or modifications occurs in database system.
 
* The period by which you want your recovery plan conducted.
 
* The sensitivity of data in database system. More critical the data is, the more regularly you will need to test the software.
50 .
What are the differences between GUI Testing and Database Testing?
GUI Testing Database Testing
User Interface Testing is also known as Front-end Testing. Backend Testing or also known as data testing.
Tests all testable items that are visible to the user in the application such as forms, buttons, menus etc. Tests all testable items that are not visible to the users but are essential for the proper functioning of the system.
Tester need not know SQL. Tester needs to know the database technologies like SQL to run the queries and validate the data.
Majorly deals with the look and feel of the software application. Mostly deals with the data integrity, validation of schema of the structures where the data resides, validating data duplication, referential integrity and everything that deals with data.
Some of the tools that we use for GUI testing are: Katalon Studio, RAPISE by Inflectra, TestComplete, Abbot Java GUI Test Framework, AutoIt UI testing Some of the tools are Database Rider, Db stress, DbUnit, DB Test Driven etc
51 .
What are the most commonly occurring issues that are faced during database testing and how can they be solved?
To establish the status of transactions, there can be a significant amount of overhead.
Solution : Design a time and cost-effective approach. When it comes to controlling database testing quality and application project duration, a delicate balance must be struck.

After the old test data has been cleaned up, testers must create a new data design.
Solution : Plan a technique and create test cases that can quickly adapt to various data types.

An SQL generator that can help validate data and quickly handle different test scenarios is required to validate SQL queries. If not done appropriately, this maintenance can lead to a tangle of problems that are difficult to manage.
Solution : The overall testing process should be well-planned, and queries should be well-maintained so that continual upgrades are possible.
52 .
How can you validate the tables and columns in the database?
The following test cases can be used to evaluate the tables and columns in databases :
 
* Are the database fields accurately mapped and compliant with the front-end or back-end needs' needed mappings?

* Are the fields labelled correctly and have the correct lengths and sizes?

* Check if there are any unused or unmapped tables and columns.

* Check if the table design and the referential integrities and constraints on the columns are applied effectively and are scalable to different requirements.
53 .
How do you test if your database is updated when data is entered in front-end application?
You can go to the database and run a relevant SQL query. In WinRunner, you can use database checkpoint function. If the application provides view function, then you can verify the same from the front-end.
54 .
Which SQL statements are commonly used to develop test-cases for database testing?
Various SQL statements are used to develop the Test cases. Most common SQL statement which is used to perform DB testing is select statement. Apart from this various DDL, DML, DCL statements can also be used.
 
Example : Create, Insert, Select, Update, etc.