Database Testing Interview Questions and Answers - Part 2

What is Database Testing?

Database testing is concerned with the following:

1. Integrity of User Interface data with Database Data (Data Integrity testing).
2. Junk data is not available in User Interface other than that stored in Database (Data Validity testing).
3. Execution commands with the input values taken from the database tables (Data Manipulation and Update).
4. Data Migration.

How to test data loading in Database Testing?

For testing of data loading we do following steps:

1. Study the source and target data format (data type and constraints , columns and tables).
2. Compatibility between Source and Target
3. Open corresponding Data transformation Services(DTS) package in SQL and run DTS package( if SQL server is used).
4. Comparisons of columns’s data of source and target.
5. Check number of rows in source and target.
6. Now, Update data in source and examine changes in target.
7. Check null and junk characters.

What is trigger? How to check if trigger is fired or not? How to invoke trigger on demand?

A procedural code that is executed in response to certain events on a particular table or view in a database. Integrity of database is maintain by using trigger. By the help of common audit log we check the trigger is fired or not. Triggers can't be invoked on demand. The trigger 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, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

What is data driven testing?

The Data Driven Testing(DDT) is the data that is external to ouw functional test scripts is loaded and used to extend test cases. Or more simply we can say “ SINGLE TEST WITH MULTIPLE DATA USING SAME TEST”. Ex. Let we want to signup to create an account in gmail for 100 user. For this we pet all the user detail in a single excel sheet aur use a single script.

How to test whether a database is updated when data is entered from front end?

Depending on application interface we have following process.

1. When application enables us View functionality of the entered data. By this verification can be done from front end only.
2. When application has no functionality to view , then we have to check by using relevant SQL query in the database.
3. Checkpoint function in WinRunner can be used.

What is way of writing test cases for database testing?

Following procedures is used for writing the test case

1. Understand functional requirement of application.
2. Need to know the table structure, joined used, cursor used ,trigger used , stored procedure used, input and parameters used.
3. Now we can write the test case with different input values.

What are the different stages involved in database testing?

Following stages are involved in Database Testing:

- To verify whether front-end values are correctly storing in the black-end database by using SQL queries.
- To verify the Constraints( primary key, foreign key and composite key).
- Examine quality of Stored Procedure.
- Checking execution of Triggers.

What steps does a tester take in testing Stored Procedures?

Implementation of Error Handling mechanism in stored procedures is necessary. It’s the tester task to handle the Error handling mechanism.\

Ex. Declaring a variable with ‘varchar(10)’ and now variable is used to assign a value of 15 size, error will thrown saying buffer too small

Now suppose I have 50 records of employee table i want to find the person who is having 22nd highest salary.

By using following SQL query:

Select * from (select *, Dense_Rank()over (order by Emp_sal) SalayRank from Emp_a)A where SalaryRank=22

What is join and explain types of joins?

There are following Types of Join-

1. Inner join: A join that displays only the rows that have a match in both joined tables.
2. Outer join: includes rows even if they do not have related rows in the joined table.

- Full outer join - All rows in all joined tables are included, whether they are matched or not.
- Right outer join - All rows from the second-named table are included
- Left outer join - All rows from the first-named table are included.

3. Cross join: join whose result set includes one row for each possible pairing of rows from the two tables.

Explain DELETE, TRUNCATE, DROP Statements.

- SQL DELETE: is used to delete the rows from a table, WHERE clause is optional. If we do not include the WHERE clause all the rows in the table is deleted.

Syntax: DELELTE from table_name [where condition];

- SQL TRUNCATE: used to delete all the rows from the table and free the space containing the table.
Truncate Statement is used to delete all the rows from the table and free the space containing the table but SQL delete does not free the space containing the table.

Syntax: TRUNCATE TABLE table_name

- SQL DROP: used to remove an object from the database. By drop statement, all the rows in the table is deleted and the table structure is removed from the database.

Syntax: DROP table table_name

What are cursors? Disadvantages of cursors? How can you avoid cursors?

Cursors allow row-by-row processing of the result sets.

Types of cursors:
- Static,
- Dynamic,
- Forward-only,
- Keyset-driven.

Disadvantages of cursors:

- Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one roundtrip, however large the result set is.
- Cursors are also costly because they require more resources and temporary storage (results in more IO operations).
- Further, there are restrictions on the SELECT statements that can be used with some types of cursors.
- Most of the times, set based operations can be used instead of cursors.

How to test a SQL Query in QTP or WinRunner? Without using database checkpoints?

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

The exact process should be:

1) Connect to the database:
db_connect("query1",DRIVER={drivername};SERVER=server_name;
UID=uidname;PWD=password;DBQ=database_name ");

2) Execute the query:
db_excecute_query("query1","write query u want to execute");
-Condition to be mentioned

3) Disconnect the connection:
db_disconnect("query");

What are the types of data driven tests?

There are four types of data driven tests.

1) Dynamic Input submission (key driven test): when tester conducts retesting with different input values to validate the calculation through dynamic submission. For this input submission, tester uses this function in TSL script-- create_input_dialog ("label");

2) Data Driven Files Through FLAT FILES (.txt,.doc): When tester conducts re-testing depending upon the contents of the flat file. He collects these files from Old Version databases or from customer side.

3) Data Driven Tests From FRONTEND GREAVES: when tester creates automation scripts depending upon the front-end objects values such as (a) list (b) menu (c) table (d) data window (e) ocx etc.,

4) Data Driven Tests From EXCEL SHEET: To execute the script for multiple inputs tester follows this type of data driven test. These multiple inputs are stored in columns of an excel sheet.

We collect this test data from the backend tables.

What are the test scenarios to test a database migrated from SQL Server 2005 to SQL Server 2008?

We need to check out what was all the enhancement the SQL Server 2008 has in it.we have to find the differences between the SQL Server 2005 and Server 2008.

Design of test case are done by keeping following things in mind

1) Data type used
2) Length of the data field it should be same as it was in SQL 2005
3) All the jobs should be scheduled properly

Explain challenges you face when introducing Database Regression Testing into Organization

We are likely to face several challenges:

1. Insufficient testing skills: By training good testing skills (pairing a DBA without testing skills and a tester without DBA skills still works), or simply through trial and error.

2. Insufficient unit tests for existing databases: Few organizations have yet to adopt the practice of database testing, and we don’t have a sufficient test suite for your existing database. So start writing your test suite.

3. Insufficient database testing tools: Depend up on the need of the organization we have to select the testing tool like DBFit, Turbo Data, RadView etc.

4. Reticent DM groups: Some data management (DM) groups may see the introduction of database regression testing, and agile techniques such as test-first development (TFD) and refactoring, as a threat.

Will PHPUnit recreate the database schema for each test?

No, all database objects are required by PHPUnit to be present when the suite is started. Before the execution of test suites tables, sequence, trigger and views have to be created. By the help of Doctrine 2 or eZ components tools we can create the database schema from predefined data structure. This has to be linked up into PHPUnit extension to allow automatic database recreation before the execution of test suit. Since each test completely cleans the database we are not required to recreate database for each test-run.

Front End Testing vs. Back End testing.

- Front End Testing is basically Graphical User Interface (GUI) testing or GUI functional testing.

- Back End Testing involves databases or any backend storage. It’s basically testing data while travelling from front to back end or in back end to back end only.

Ex. When we enter some data in front end application and it is getting stored on some database then we have to test it whether it is storing correctly. We can do it by writing relevant SQL queries.

But when application is not Front End (GUI) for ex. developer is creating some table using joining 20 different input or source tables then tester has to test it. In this case we have to write queries to validate source and target data between those table using SQL scripts.

Why back-end testing is required, if we are going to check the Front - End?

The requirement of testing depends on project. Like in Ticket booking system, Front end you will provided with an Interface, where you can book the ticket by giving the appropriate details. It will have a Data storage system (Database or XL sheet etc) which is a Back end for storing details entered by the user. After submitting the details, you might have provided with a correct acknowledgement. But in back end, the details might not updated correctly in Database because of wrong logic development. Then that will cause a major problem and regarding Unit level testing and System testing Unit level testing is for testing the basic checks whether the application is working fine with the basic requirements. This will be done by developers before delivering to the Quality Assurance (QA) team .You will be performing all the checks. Basically this will be carried out by tester.

Give examples of Back End Testing utility.

Few utilities for Back End Testing are:

1. SQL language: Write test scripts to call stored procedures, retrieve data, insert,update.delete records. Most back end test work can be done with the SQL language

2. NT SQL utilities: DOS utilities like isql.exe, bcp.exeWindows applications such as WinQuery, ISQL/w, SQL Administration, SQL ObjectManager, SQL Client Configuration Utility

3. MSAccess: We may take advantage of MSAccess tables, queries, forms, reports, macros and modules.

4. Excel: MSQuery and Q+E are useful for data validation

5. Our own test tools: Several test tools have been developed. For example, stored procedures to logpassed or failed for each test and to present test statistics.

Explain the issues related to login and security of user.

The following things need to be checked:

-Email validation
-SQL user login (user id, password, host name)
-NT server login
-Database access privilege
-Database security hierarchy
-Table access privilege
-Table data access control
-Training account (maybe no password is required)

There are more test cases here:

-Simulate front end login procedure and user with correct login information can login
-Simulate front end login procedure and user with incorrect login information fail to login
-Check concurrent logins (make many users login at the same time.)
-Try to login when a time-consuming query is running to see how long login will take to succeed
-Check for any security-restrict functions and see they are working properly
-See any data view restriction in place, such as, a user can see his data and the data of people who report to him.

We are facing an error in insert trigger. What you will do to remove that error?

Verify the following things and compare them with design specification

-Make sure trigger name spelling
-See if a trigger is generated for a specific table column
-Trigger’s insertion validation
-Insert a record with a valid data
-Insert a record, a trigger prevents, with invalid data and cover every trigger error
-Try to insert a record that already exists in a table
-Make sure rolling back transactions when an insertion failure occurs
-Find out any case in which a trigger should roll back transactions
-Find out any failure in which a trigger should not roll back transactions
-Conflicts between a trigger and a stored procedure or rules

Explain different type of Back End Testing methodology.

There are many test methods that can be applied to back end testing.

- Structural testing: When back end is divided into a finite number of testable pieces based on aback end’s structure. Tests will verify each and every object in a type of structure.

- Functional testing: When back end is divided into a finite number of testable pieces based on application’s functionality. The test focus is on functionality of input and output but not on the implementation and structure. Different projects may have different ways of dividing.

- Boundary testing: Many columns have boundary conditions. For example, in a column for percentages, the value cannot be less than zero and cannot be greater than 100%. We should find out these types of boundary conditions and test them.

- Stress testing: It involves subjecting a database to heavy loads. For incidence, many users heavily access the same table that has a large number of records. To simulate this situation, we need to start as many machines as possible and run the tests over and over.