Database Testing Interview Questions

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. This testing is widely used in applications that use databases. With modern technologies and advancements in software development, databases are widely used to store data which is why checking the correctness and quality of data matters. It is divided into 4 categories such as:

• Data Validity Testing: In 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 triggers, functions and procedures present in the database to validate the logic associated with them.

Data-driven testing is an automation testing framework that involves testing data stored in a spreadsheet or a table and using that stored data as input values to the test scripts. This is done to avoid writing individual test cases of the same functionalities with different inputs. The inputs to the test scripts can be stored in XML, CSV, XLS and 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.

Trigger testing involves testing the database triggers that answers the following questions:

1. Are the coding conventions followed while defining and coding the triggers?
2. Are the triggers executing the DML transactions successfully by satisfying all conditions?
3. Are the triggers updating the data correctly post the execution of queries?
4. Is the application functionality working fine post the data updation, insertion or deletion?

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.

A database transaction is defined as a task sequence that is to be performed on the database to achieve the required results logically. The task can include creating, deleting, updating the records in the database tables. The tasks upon successful completion affect the records in the database. A transaction provides 4 controls as listed below:

• COMMIT: This is used for saving all changes done utilizing the transaction. We generally use this post INSERT, UPDATE, DELETE statements.
• ROLLBACK: This is used for rolling back the transaction to the previous state before it was operated. We generally use this post INSERT, UPDATE, DELETE statements.
• SAVEPOINT: This is used for setting the point where the transaction is supposed to roll back.
• SET TRANSACTION: This is used for setting the name of the transaction.

The keys and indexes testing in a database is a form of database testing which answers the below questions:

• Have the required primary keys and foreign keys constraints been created on the targetted tables?
• Are the foreign key references valid?
• Is the data type of the primary key of one table and the corresponding foreign key in the other table the same?
• Are the naming conventions being followed for all indexes and keys?
• Are the lengths and sizes of the required indexes and fields expected?
• Are the indexes clustered or non-clustered working as expected and is according to the business requirements?

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. It addresses 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.

We can use the statements like INSERT, UPDATE, SELECT, DELETE for manipulating the table content. Statements like ALTER TABLE, DELETE TABLE, CREATE TABLE are used for creating and managing the tables.

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.

Retesting refers to the method of executing the same test script using different input data every time. It helps in finding faults in the system when subjected to different inputs. The faults can be fixed and once again the system can be subjected to testing using the same scripts with the same or different inputs to check if it works fine.

Retesting is different from data-driven testing. This is because retesting is mostly a manual process that involves using a whole new data input set. Data-driven testing is an automated testing framework that has the test data and the scripts take the data and run it automatically.

Database testing can be done manually as well. This requires human effort in observing the behaviour of the application when subjected to different manual test cases drafted by the testers. This can also involve observation of the behaviour in front-end applications when the backend database has changed. We can also manually check whether the record has been created, updated or deleted in the database tables post the execution of the queries.

Consider an example of a database system that keeps track of student details. The operations run on the Student tables database can be validated manually by looking at the state of the records post query execution or can be validated in how the front end interface looks like.

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.

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.

To know if a trigger is fired, we will have to enable the audit logs of the table. You can refer to the steps to access logs here.

Following are the SQL statements categories that can be used in database testing:

• DDL – Data Definition Language:
  • CREATE: Used for creating databases and their objects like tables, views etc.
  • ALTER: Used for altering the properties of database objects.
  • DROP: Used for dropping the database and objects of the database.
  • TRUNCATE: Used for removing all records from a table without touching its structure.
  • RENAME: Used for renaming the database objects.
• DML – Data Manipulation Language:
  • SELECT: Used for selecting specific records or data from the database objects.
  • INSERT: Used for inserting new data or records into the database tables.
  • UPDATE: Used for updating existing records.
  • DELETE: Used for deleting existing records.
• DQL – Data Query Language:
  • SELECT queries come under data query language to query the data based on any given conditions.
• DCL – Data Control Language:
  • Used for creating roles, granting permissions and controlling accesses to the database and the objects of the database. The commands that fall under DCL are:
    • GRANT: Used for providing the user access permissions.
    • DENY: Used for denying the user permissions.
    • REVOKE: Used for removing the user access.
• TCL – Transaction Control Language:
  • COMMIT: Used for writing and storing changes to the database.
  • ROLLBACK: Used for restoring the changes to the database to the last commit.

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.

The process to perform database testing is very similar to the steps that we follow for testing any other applications. Following are the steps that are followed:

1. Prepare the test environment and test scripts
2. Run the script
3. Check for the test results
4. Validate the results against the expected results.
5. Report the validation findings to the developers and the stakeholders.

This testing involves testing database structures that depend on schema, tables, triggers, functions, procedures and also the servers used for databases. Following are the 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.

For testing the database triggers and procedures, it is required to know the input parameters to these functionalities and know what would be the expected output for these. The EXEC statement can be used for determining the behaviour of the tables when the procedures or triggers are run.

You can also achieve this by creating SQL unit tests for checking the database objects modified due to the execution of the triggers or procedures. The SQL unit tests follow the 3 rules as shown below:

The test case should check whether the objects exist in the database or not. It should check or validate what the normal outputs would be for success scenarios. It should also check for the behaviour of the system under the influence of negative test cases.

Firstly, when migration happens, we need to be aware of all the changes and enhancements are done in the new SQL server. Based on this information, design your test case suite by considering the following points:

• What is the data type used?
• What is the length of the fields used in the server?
• Are the database objects created as expected in the new SQL server?
• Perform stress testing on the new SQL server to identify any risks associated with the migrated environment.

SQL Constraints are used for setting the rules for the records in the database table. If any constraints are not satisfied, then the action can be aborted. The constraints are defined when we create the database objects. It can also be altered by using the ALTER commands. SQL has 6 major constraints, they are:

1. NOT NULL: This constraint is used for specifying that a column cannot have NULL values.
2. UNIQUE: This constraint specifies that each column has a unique value, meaning the values are not repeated.
3. 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.
4. 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.
5. CHECK: It is used for ensuring whether the column values fulfil given specified conditions.
6. 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.

Data load testing requires the testers to know about source databases and the destination databases and their objects like tables, columns, design, constraints etc. While performing tests, we need to validate the compatibility of the source and destination databases using the DTS package in SQL Enterprise Manager. We can follow the below steps for the same:

• Open the DTS package and run it.
• Compare the column data of the source and destination tables.
• Check for the number of rows in source and destination tables.
• Post updating data in source tables, check if the same changes are reflected in the destination database.
• Check about the data quality whether the destination data received any junk characters and NULL values.
• Check for the maximum processable volume on the servers and evaluate the response times and speed of data load from the source to destination.

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.

Consider a simple SQL code below:

CREATE TABLE ACID_DEMO (X INTEGER, Y INTEGER, CHECK (X + Y = 50));

We will test for the ACID properties for two columns X and Y. There is also a constraint added on the table that the sum of values in columns X and Y should always be 50.

• Atomicity: Here, we test that the transactions done on the table is either successful or failed. No records should be updated if the transactions fail.
• Consistency: Here, we test that the values in columns X and Y are updated correctly by following the constraint that the sum of these two values is always 50. The insertion or updation should not be allowed if the sum is not equal to 50.
• Isolation: Here, in the presence of multiple transactions, we need to test they are happening in isolation.
• Durability: Here, the test cases should consider that if a transaction has been committed, it should remain even after the incidents of power losses, crashes and errors. If we are using sharded or distributed database applications, rigorous testing needs to be done to ensure the data is not lost.

Whenever there are different modules in the application that are using the same data and also perform different operations on them, it is very much necessary to have the latest data to be shown everywhere. The most recent value of the data should be used by the systems. This is called Data Integrity. Following image shows 5 characteristics of data following data integrity:

We can apply the following test cases for testing out the integrity of the data:

• Are all the triggers working fine to update the records in the reference tables?
• Are there any invalid data in the columns of each database?
• Insert wrong data into the tables and see how the insertion behaves.
• What would happen if you try to insert the child record before inserting the parent record in the main table?
• Is there any failure if you are trying to delete records referenced by another table?
• Are the replicated servers in sync? The data should not be out of sync in two or more replicated servers as it would defy the intent of replication.

• Significant overhead can be involved to determine the state of transactions.
  • Solution: Plan the process that is efficient in both time and cost. A fine balance needs to be present while managing the quality of the database testing and the application project duration.
• Post the clean up of old test data, testers need to come up with a design of new data.
  • Solution: Plan a methodology and design test cases that can easily take up different data sets without much effort.
• To validate the SQL queries, there is a need for an SQL generator that can help to validate the data and can easily help in handling different test cases. This maintenance, if not done correctly, can result in messy and unmanageable scenarios.
  • Solution: Overall process of testing should be well planned and the queries should be well maintained in a way that continuous updates are supported.

Following are some good practices that need to be followed while performing database testing:

• Validate every single piece of data that includes metadata and functional data based on the requirement specifications.
• Test data should be verified and created after consultation with the development team and only then the validation needs to be done.
• Output data needs to be validated by using both automated and manual processes.
• Perform boundary value analysis, graphing techniques, equivalence partitioning techniques to get the required test data inputs.
• The referential integrity of the data in the tables also needs to be carefully evaluated.
• The selection of default values in the table needs to be validated too to check for consistency of data.
• Check if appropriate logging events have been tracked and added to the database.
• Are the jobs scheduled to run are executed on time?
• Ensure that timely backup of the test database is taken to ensure test cases do not fail.
• Ensure that the test server is periodically refreshed with close to real-time data so that testing can be as accurate as possible.

We can test the tables and columns in the databases by applying the following test cases:

• Are the database fields mapped correctly and is compatible with the required mappings in the front-end or backend requirements?
• Are the fields following proper naming conventions and have lengths and sizes as per the requirements?
• 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.

We can test using the following methods:

• At compilation time, check if there are any compile-time errors.
• Add proper comments and loggers that would give you hints about the stages completed and help you narrow down the region of error.

No. A table can allow one composite primary key that consists of two or more columns. But it does not allow for multiple primary keys.

The result should have 21 and 25.

We can do it easily by using the SELECT and EXCEPT keywords as shown below:

SELECT * FROM TableA EXCEPT SELECT * FROM TableB;

Conclusion

The need for correct data has grown exponentially in the software development field. It is very crucial to use the right data, perform the right set of operations on it and show the results to the right set of people. Due to this, database testing helps to validate the correctness, integrity and truthfulness of the data. In this article, we have seen the most commonly asked questions in database testing that deals with RDBMS database systems to both freshers and experienced people.

Useful Resources:

• DBMS Interview
• SQL Interview
• MYSQL Interview
• Selenium Tester Interview Questions
• Automation Testing
• Manual Testing Interview
• Database Testing
• DBMS Tutorial
• Technical Interview Questions