Tutorial Database Testing using SQL | SQL for Testers

The demand for "all round" testers, i.e. being able to test the system's functionality through traditional testing methods and being able to show some technical knowledge is growing.
Basics of Database testing contains the following:

1. How to connect to the database?
2. Ability to write simple queries to retrieve data and manipulate the data using DML operations.
3. Functional flow should be very well known!
4. Good knowledge on table level, column level constraints, ability to understand and execute complex queries related to joins is added advantage.

Contents of this tutorial:
1. INTRODUCTION to Database Testing

• 1.1 Why back end testing is so important
• 1.2 Characteristics of back end testing
• 1.3 Back end testing phases
• 1.4 Back end test methods

2. STRUCTURAL BACK END TESTS
2.1 Database schema tests

• 2.1.1 Databases and devices
• 2.1.2 Tables, columns, column types, defaults, and rules
• 2.1.3 Keys and indexes

2.2 Stored procedure tests

• 2.2.1 Individual procedure tests
• 2.2.2 Integration tests of procedures

2.3 Trigger tests

• 2.3.1 Update triggers
• 2.3.2 Insert triggers
• 2.3.3 Delete triggers

2.4 Integration tests of SQL server
2.5 Server setup scripts
2.6 Common bugs
3. FUNCTIONAL BACK END TESTS

• 3.1 Dividing back end based on functionality
• 3.2 Checking data integrity and consistency
• 3.3 Login and user security
• 3.4 Stress Testing
• 3.5 Test back end via front end
• 3.6 Benchmark testing
• 3.7 Common bugs

4. Testing The Nightly downloading and Distribution jobs

• 4.1 Batch jobs
• 4.2 Data downloading
• 4.3 Data conversion
• 4.4 Data distribution
• 4.5 Nightly time window
• 4.6 Common bugs

5. Testing the Interfaces to Transaction APIS

• 5.1 APIs' queries to back end
• 5.2 Outputs of back end to APIs
• 5.3 Common bugs

6. Other Database testing Issues

• 6.1 Test tips
• 6.2 Test tools
• 6.2 Useful queries

Before going through chapters 2 to 6, one should know the basics of SQL:
1. What are the difference between DDL, DML and DCL commands?
DDL is Data Definition Language statements. Some examples:
•CREATE - to create objects in the database
•ALTER - alters the structure of the database
•DROP - delete objects from the database
•TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
•COMMENT - add comments to the data dictionary
•GRANT - gives user's access privileges to database
•REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. Some examples:

•SELECT - retrieve data from the a database

•INSERT - insert data into a table
•UPDATE - updates existing data within a table
•DELETE - deletes all records from a table, the space for the records remain
•CALL - call a PL/SQL or Java subprogram
•EXPLAIN PLAN - explain access path to data
•LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
•COMMIT - save work done
•SAVEPOINT - identify a point in a transaction to which you can later roll back
•ROLLBACK - restore database to original since the last COMMIT
•SET TRANSACTION - Change transaction options like what rollback segment to use
Download the “SQL For Testers” tutorial from here

Read SQL for Testers - Part 2