Database (DB)Testing - Strategy, Concepts, Checklist with sample test cases

In software testing there will be so many types of testings we will be carried out to deliver the product as per the client needs or as per the requirements. In which database testing or db testing is the one which requires thorough knowledge on sql commands, stored procedures, functions and triggers. There will be so many database software available in the market like SQL Server, Oracle, MySQL, PostgreSQL, Hadoop (Hadoop distributed file system) etc., What ever may be the database software we are using there will be slight difference in SQL query syntax, however most of the things will be same in all software. Database testing or DB testing is a technique of validating the database in which data is getting inserted/updated/deleted in various tables on various databases. Database testing is will differ based on the type of the data you are going to verify. I mean based on the OLTP or OLAP (Online Analytical Processing) the method of database testing will differ. For OLTP (Online Transaction Processing) we are verifying the stored procedures and validating the data is inserted/deleted/modified properly or not. And for OLAP lot of scripts or DW jobs will comes into picture. Having extensive knowledge on database testing I can say database testing should be carried out manually, why because I didn't find any automation testing tool which fulfills the db tsting requirements. Whenever the project started and ready for testing initially we will get the packages, stored procedures, functions etc for QA. In the sense the Database testing concept is divided into two phases.

1. Backend Database Testing
2. Frontend Database Testing

Functions in SQL - Scalar - Mathematical, String, Date and Time functions

A function is a predefined programming segment specially used for specific and well defined task.

Functions in SQL Server:

     1. Scalar functions
     2. Aggregate or group functions

1. Scalar functions:

These scalar functions will take single value as input and returns a single value.

• Mathematical Functions
• String functions
• Date time functions
• System functions
• Security functions
• Confirmation functions
• Cursor functions
• System statistic functions
• Text and image functions

We will concentrate on first three functions now.

Transactions in SQL

Transaction is a logical unit of work in a database.

Types of transactions:

1. Auto commit transactions
2. Explicit transactions
3. Implicit transactions.

1. Auto Commit Transactions:

Every individual DML statement is an auto commit transaction.
Ex:

insert into dept values (50,'export','hyd')

Constraints in Database

• Constraint in a database is a mechanism used by Oracle (or) SQL Server to control the unwanted or invalid data flow into a table. (or)
• Enforcing the rules to maintain the data integrity.
• This mechanism automatically activates when user performance a DML operation.

Types of Constraints:

1. NOT NULL: It will not allows the NULL values, but allow the duplicate values.
2. UNIQUE: It will now allows the duplicate values, but allows NULL values.
3. Primary Key:
1. It is a combination of unique, not null and clustered index.
2. It will not allow NULL values and duplicated values.

     4. Default: It is used to insert default values into the column when user skips the column from insert statement.

     5. Check: It is used to impose a condition rule.

     6. Foreign Key:

1. It is used to establish parent/child or Master/Detailed relationships between the tables.
2. It always linked to primary key column or unique key column of master table.
3. It will not allow NULL values.

Joins in SQL and Oracle

Joins:
Join is a query that combines the rows from two or more queries. 

Types of joins: 

 1. Cross Join 
 2. Inner join or equi join or simple join
 3. Outer join 
 4. Self join 

1. Cross join: 

• In cross join every row in the first table joins with the every row in the second table. 
• The output of cross join is called Cartesian product.

Ex:      select ename,dname 
         from emp, dept
                   or
         select ename,dname 
         from emp cross join dept

Data Integrity in DBMS

The Reliability and accuracy of data is called the data integrity.

Types of Data Integrity in DBMS:

1. Entity integrity
2. Domain integrity
3. Referential integrity
4. User defined integrity

1. Entity Integrity:

The basic idea of entity integrity is that each should have acolumn that uniquely identifies the remaining columns in the table.

2. Domain Integrity:

It refers to the range of values that are allowed for the column.

3. Referential Integrity:

It refers to the relationships between the columns of a table.

4. User Defined Integrity:

It allows the user or administrator to enforce the new business rules which does not falls under above integrity.

                                                                                Back to SQL Tutorial Index

Sub Queries and Correlated SubQueries in SQL and Oracle

Sub Query: It is an select statement in another select statement.

Syntax:

select <column_list> from <table_name> where column operator in (select <column_list> from <table_name>)

Based on the result of sub query or inner query outer query is executed. i.e., outer query depends on inner query.

Q. Write a query to display all employees in king department .

A.select * 
       from emp 
       where deptno=(select deptno 
                     from emp 
                     where ename='KING')