Database Testing Interview Questions and Answers

Database testing is a major testing which requires tester to expertise in checking tables, writing queries and procedures.

Data base testing is segmented into four different categories.

• Testing of Data Integrity
• Testing of Data Validity
• Data base related performance
• Testing of functions, procedure and triggers

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

In a data-table, to test the multi numbers of data, data-driven test is used.  By using this it can easily replace the parameters at the same time from different locations.

Join is used to display two or more than two table and the types of joins are:

• Natural Join
• Inner Join
• Outer Join
• Cross Join

The outer join is divided again in two:

• Left outer join
• Right outer join

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

The tester will check the standard format of the stored procedures and also it checks the fields are correct like updates, joins, indexes, deletions as mentioned in the stored procedure.

On querying the common audit log you would know, whether, a trigger is fired or not. It is in audit log where you can see the triggers fired.

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 appear in the target or not.
• Check NULLs and junk characters

By writing scripting procedure in VBScript, we can connect to database and can test the queries and database.

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.

Writing test cases is like functional testing. First you have to know the functional requirement of the application. Then you have to decide the parameters for writing test cases 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

The statements like SELECT, INSERT, UPDATE, DELETE are used to manipulate the table, while ALTER TABLE, CREATE TABLE and DELETE TABLE are used to manage table.

To test database procedures and triggers, input and output parameters must be known. EXEC statement can be used to run the procedure and examine the behaviour of the tables.

• Open the database project in solution explorer
• Now in View menu, click the database schema
• Open the project folder from schema View menu
• Right click on the object that has to be tested, and then click on the dialog box that says Create Unit Tests
• After that create a new language test project
• Select either a) Insert the unit test or b) Create a new test and then click OK
• Project that has to be configured will be done by clicking on the Project Configuration dialog box.
• Once it configured click on OK

To write a testcases from requirements, you need to analyse the requirements thoroughly in terms of functionality. Thereafter you think about the appropriate testcases design techniques like Equivalence partitioning, Black box design, Cause effect graphing etc. for writing the testcases.

Yes, the requirements represent exact functionality of AUT.

DBMS stand for Database management system, there are different types of DBMS

• Network Model
• Hierarchical Model
• Relational Model

DML stands for Data Manipulation Language, It is used to manage data with schema objects. It is a subset of SQL.

DCL stands for Data Control Language, it is used to control data.

The two types of DCL Commands are:

• Grant: By using this command user can access privilege to database
• Revoke: By using this command user cannot access the database

Black box testing means testing the software for the outputs on giving particular inputs.  This testing is usually performed to see if the software meets the user’s requirements.  There is no specific functional output expected for running this test.

The white box testing is done to check the accuracy of code and logic of the program.  This testing is done by the programmer who knows the logical flow of the system.

Once the testing is done, QTP will generate a report.  This report will show the checkpoints, system message and error that were detected while testing.  The test results window will show any mismatches encountered at the checkpoints.

QTP testing process is based on following steps:

• Creating GUI (Graphical User Interface) Map files: Identifies the GUI object which has to be tested
• Creating test scripts: Test scripts are recorded
• Debug tests: Test should be debugged
• Run tests: Testcase’s should be run.
• View results: The results reflects the success or failure of the tests
• Report detects: If the test is failed, the reasons will be recorded in the report detect file

To measure the system response, load testing is done.  If the load exceeds the users pattern it is known as stress testing.  Examples of load testing are downloading the set of large files, executing multiple applications on a single computer, subjecting a server to large number of e-mails and allotting many tasks to a printer one after another.

Testing the database manually involves checking the data at the back end and to see whether the addition of data in front end is affecting the back end or not, and same for delete, update, insert etc.

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.

Various techniques can be applied in database testing, including:

• Functional Testing: Verifying the functionality of database operations, such as CRUD operations (Create, Read, Update, Delete), stored procedures, triggers, views, and database constraints.
• Performance Testing: Assessing the database’s performance under different workloads, stress testing, and measuring response times for queries and data retrieval.
• Data Integrity Testing: Validating data integrity constraints, referential integrity, data accuracy, and consistency across tables and relationships.
• Security Testing: Evaluating the database’s security measures, such as user access controls, encryption, and data protection mechanisms.
• Compatibility Testing: Testing the database with different platforms, operating systems, and database management systems (DBMS) to ensure compatibility and portability.
• Scalability Testing: Assessing the database’s ability to handle increasing data volumes, user loads, and transactional concurrency without sacrificing performance.

Testing database performance involves various techniques, such as:

• Executing and analyzing complex queries and joins to assess query optimization.
• Stress testing the database with a high volume of concurrent users and transactions to identify performance bottlenecks and resource limitations.
• Measuring and benchmarking the response times of frequently executed queries and operations.
• Monitoring database metrics, such as CPU usage, memory consumption, and disk I/O, to identify potential performance issues.
• Analyzing database indexes, query plans, and execution statistics to optimize query performance.
• Testing with representative data sets to ensure performance scalability.

Testing database backups and recovery involves the following steps:

• Verifying that regular database backups are performed according to the defined schedule and backup strategy.
• Testing the restoration process by restoring backups to a test environment and validating the recovered data.
• Simulating different failure scenarios, such as hardware failures, system crashes, or data corruption, to ensure the database can be restored to a consistent state.
• Validating that the restored database is functionally and transactionally consistent with the original database.
• Verifying the integrity and completeness of restored data and confirming that all database objects and relationships are intact.

Some challenges in database testing include:

Dealing with complex database schemas and relationships: Understanding the database structure and designing effective test scenarios to cover different data scenarios.

Managing test data: Creating and maintaining test data sets that cover a wide range of data scenarios, including boundary cases, data variations, and edge conditions.

Synchronizing test environments: Ensuring consistency between test environments and production environments, including data replication, schema changes, and database versions.

Handling performance testing: Generating realistic workloads, simulating concurrent user activities, and measuring performance metrics accurately.

Performance testing determines the speed of the computer system performance. It includes the quantitative tests like response time measurement. The problem in performance testing is that you always need a well-trained and experienced man power also the tools you use are expensive.

To define database structure, DDL is used.  DDL stands for Data Definition Language.  The various DDL commands include Create, Truncate, Drop, Alter, Comment and Rename.

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.

Database trigger: A trigger is basically a stored procedure used to maintain the integrity of the data present in the database, executes automatically to response 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.

It is 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 Win Runner/QTP can also be used to check for database update.

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

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.

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

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

A database is a structured set of data and a database management system (DBMS) is a computer software application that create 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.

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.

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

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 an software application. In case of white-box testing, tester should be having good understanding of internal structure of an application as well as good knowledge of programming skills to design test cases and internal structure of an application.

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.
• Record set: Used to retrieve data from a database