In my previous articles i have given SQL interview questions in detail. In this article i would like to cover some most important interview questions for Database testing we can call it as Database tesing interview questions in detail. The database testing interview questions are not only the mixture of system table queries and multiple testing interview questions.In this article i would like to give you most important Database testing interview questions in depth.
Question 1 : What is mean by database testing? What are different types of database testing? ( 100% asked Database testing interview questions )
Answer :
Database testing is nothing but the testing of back-end systems and processes where we require to make sure that back end database operations are working fine according to the front end web application or desktop application. We require to check the impact of back-end database operations in detail.
There are multiple types of database testing :
- Database Validity Testing : The most vital type of database testing is to check the validity of the database.
- Database Integrity testing : The database tester is responsible to check all referential intigrities for the databases with all the constraint. These kind of testing is very important at the time of database migration testing.
- Testing of Database objects : The database objects testing and its counting is really another important part of database testing.The multiple database objects meaning the procedures,functions,triggers,packages e.t.c.
- Database Performance testing : The database performance testing is most important testing where we require to test some queries performance which are frequently running at the application side.
Question 2 : What are the basic checks we require to complete in database testing ? (90% asked database testing interview questions )
Answer :
In database testing tester requires to complete the following checks :
- Oracle or MS SQL version : We require to complete the version testing once you get the database for testing.
- Field size validation : The second stage we require to complete the field size validation checks.
- Table count : The database tester needs to check the number of tables in the specified schema.
- Constraints check : The database tester requires to complete the testing of multiple constraints like primary key constraints,foreign key constraints and other check constraints.
- Indexes checks : The index checking is most important testing for performance testing. The tester require to check whether all tables and indexed properly.
- Checking Stored Procedures,Functions and Packages : The database object testing is another important testing for databases where we require to check all the database objects.
Question 3 : What is importance of database testing? ( 70% asked database testing interview questions )
Answer :
The database testing is important because it is heart of the application. We can reduce the number of incidents coming related to the database testing if we can do the database testing correctly.
The database testing ensures that we are delivering the correct data and with that correct data the application will also work fine with less errors. The checking of indexes and partitions will ensure the performance of the application. The database testing will reduce the issues like deadlock,corruption of data or poor performance of the application e.t.c.
Question 4 : How do you test database manually? Explain with example.
Answer :
Yes! we can test the database manually. To test the database manually we require to check the system tables and form tables of the specific forms of the application.
Example :
If we require to complete the testing of the Customer form of the application. There is relation between customer and finance table so we require to complete the testing if the customer form as well as finance table. We require to check relation between the tables and data of the table which has been fetched by the form and table is correct.
Question 5 : What is Difference between functional testing and database testing? (80 % asked database testing interview questions )
Answer :
| Functional testing | Database testing |
| GUI Testing : This type of testing is also known as Graphical User Interface testing or Front-end Testing. | Backend Testing: This type of testing is also known as Backend Testing or data testing. |
| Content testing: This type of testing chiefly deals with all the testable items that are open to the user for viewership and interaction like Forms, Presentation, Graphs, Menus, and Reports, etc. | Database testing: This type of testing is dealing with all the testable items that are generally hidden from the user for viewership. These include internal processes and storage like Assembly, DBMS like Oracle, PostgreSQL, MYSQL, etc. |
| Functional Testing Validation: This type of testing includes validating the text boxes select dropdowns calendars and buttons Page navigation display of images Look and feel of the overall application . | Database Testing Validation: This type of testing involves validating: the schema database tables columns keys and indexes stored procedures triggers database server validations validating data duplication |
| The tester must be thoroughly knowledgeable about the business requirements as well as the usage of the development tools and the usage of automation frameworks and tools. | To be able to perform backend testing, must the tester have a strong background in the database server and Structured Query Language concepts. |
Question 6 : Which are different queries used to select the recent data and which are useful in database testing ?
Answer :
The following queries are used to select the recent data ,
Syntax:
Select top 1 * from table_name order by date/time desc;
Example :
Select top 1 * from Student order by Adminision_date desc;
· Query 2 : Checking duplicate records
Select * from tablename group by keycolumn having count(*) > 1
Example :
Select * from employee group by empid having count(*) > 1
Query 3 : What is query to check the objects are present or not.
Select * from sysobjects where type = “<type>“ (SQL Server);
where <type> can be
U :User table
V :View
P:Stored procedure
TR :Trigger
Query 4 : Oracle query to search Object
Select * from User_Objects where Object_name like ‘Name_of_Object’ ;
Question 7 : What is mean by data driven testing?
Answer :
Definition : Data driven testing is nothing but the testing process where we require to test multiple scripts and execute that to test the output as multiple data files.
The data files will be in different formats like Excel files,ADO objects,CSV files e.t.c.
The data driven testing is actual live data testing instead of using traditional approach of the testing.
The data driven testing is used to test efficiency of the application handling various types of inputs.
Question 8 : What are different types of joins in SQL ?
Answer :
There are following types of joins in SQL :
- Inner Join
- Outer Join
- Cross Join
- Cartesian join
- Left Outer Join
- Right Outer Join
Question 9 : What are different data definition language? (40% asked Database testing interview questions )
Answer :
There are following different statements in Data Definition Language:
1.1.Create: Create Command is used to create new table,new view or any database objects from the table.
1.2.Alter: Alter Statements are used to modify the existing database object such as add new column in the table,remove column from the table,enable disable constraints from the table.
1.3.Drop:Drop statement deletes the entire table,view and other database objects.
Question 10 : Which are different statements in Data Manipulation Language in SQL?(40% asked Database testing interview questions )
Answer:
There are following statements in Data Manipulation Language:
2.1 Update:Update statement of SQL are used to update the records from the table
2.2 Insert: Insert statement of SQL are used to insert the records in the table.
2.3 Delete : Delete statement in SQL are used to delete the records from the table.
Question 11 : Which are different Data Control Statements in SQL?(40% asked Database testing interview questions )
Answer:
There are following data control statements in SQL:
3.1 Grant:Grant command gives the privilege to the user.
3.2 Revoke: Revoke command takes back the privileges from the user.
Question 12 : What is mean by Trigger in SQL? If any trigger is not invoked how to invoke it manually?
Answer:
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.
Question 13 : Explain Distinct in SQL with example.
Answer:
DISTINCT statement is used with the SELECT statement. If the records contain duplicate values then DISTINCT is used to select different values among duplicate records.
Syntax:
SELECT DISTINCT column_name(s)
FROM table_name;
Example:
Select distinct emp_no from Employee;
The above statement will select the distinct employees from table named Employee.
Question 14 : What are different steps in database testing?[100 % asked SQL Interview Questions for Testers ]
Answer:
There are following different steps in database testing:
- Constraint Check
- Validation of a Field size
- Stored procedure
- Matching application field size to database
- Indexes for performance based issues
Question 15 : How to test the Stored procedures?
Answer:
Following steps are important to steps test engineer needs to follow to test the Stored procedures :
- Functional Understanding of Stored Procedure : The first step is to understand the functional requirement of the procedure.
- Verification of other objects : 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,SQL Developer.
- To verify results against expected values, re-run the procedure by giving different parameters.
- Finally, automate the tests with any testing tools like OTP or selenium.
Question 16 : What is mean by SQL Constraints? Which constraints are used in SQL?(80% asked Database testing interview questions )
Answer:
Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it.
Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER TABLE statement.
There are 5 major constraints are used in SQL :
- NOT NULL: That indicates that the column must have some value and cannot be left null
- UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column
- PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one columns to identify the particular record with a unique identity.
- FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key
- CHECK: It is used to ensure whether the value in columns fulfills the specified condition
Question 16 : What is mean by index and what are different types of indexes?(80% asked Database testing interview questions )
Answer :
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).
Syntax:
create index indexname on tablename(columnname);
Example:
Create index IND_Employee_ID on Employee(Employee_ID);
Types of indexes :
- B-Tree index
- Bitmap index
- Clustered index
- Covering index
- Non-unique index
- Unique index
Question 17 : What is mean by Black box and white box testing?How it is used in database testing?
Answer :
Black box testing :
The black box testing technique is technique to test the functional behavior of the application. We can check the application functionality,database checks and knowledge of the application functionality. The black box testing is used to check the application flow and database flow as well from back-end.
White Box Testing :
White box testing is also known as Code-Based Testing or Structural Testing to test the internal structure of a software application. In the case of white-box testing, the tester should have 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.We can check the detailed level database testing with using white box testing.
Question 18 : Explain the data loading steps in database testing?
Answer:
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 NULL and junk characters
Question 19 :How to write the test cases related to databases?
Answer: The following steps are really important to complete and write the test cases related to database.
Step 1 : Application Functionality : The application functionality is most important check tester needs to do.
Step 2 : Need to check joins as per database design : we require to search different joins ,tables,relations between the tables.
Step 3 : Write the test cases according to the different resources.
Step 4 : Make sure that you are using the white box testing technique to write database related test cases.
Step 5 : Writing a 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
Question 20 : What are properties of the transaction?
Answer:
Properties of transaction are known as ACID properties, such as
- Atomicity: Ensures the completeness of all transactions performed. Checks whether every transaction is completed successfully if not then transaction is aborted at the failure point and the previous transaction is rolled back to its initial state as changes undone
- Consistency: Ensures that all changes made through successful transaction are reflected properly on database
- Isolation: Ensures that all transactions are performed independently and changes made by one transaction are not reflected on other
- Durability: Ensures that the changes made in database with committed transactions persist as it is even after system failure
Question 21 :What are different database Environments used in any project?(90% asked in Interview SQL Questions )
Answer:
The Project to project database environment varies.But the following is basic environment structure used for projects.
1.Development Environment:
In Development Environment all developer works and development work is been done on development environment.
2.Test Environment:
Developers does not have access of test environment.After development is done the code is migrated to Test Environment.Testing team is working on Test environment and execute black box as well as white box test cases on this Environment.Sometimes System Integration Testing (SIT) is also done on this Environment.
3.UAT Environment:
UAT stands for User Acceptance Testing.On this Environment the Customer side testers tests the software and executes User Acceptance Test Cases.
4.Performance Testing Environment:
On this environment the performance tester tests all performance related issues on this environment. This environment contains very huge data and performance tester will try to break the system using that big data.
5.Production Environment:
On this Environment actual user works and uses the software..
These are some most important database testing interview questions with its answers. Hope these are helpful. If you have any questions related to the same kindly comment in comments section.