Database Testing: Structured Query Language (SQL) Fundamentals for Test Professionals

(CSTP Elective)

This tutorial counts as training towards the Certified Software Test Professional requirements.

Who is this for? Test Engineers required to test an application’s interface with a relational database.

Today's heterogeneous data environments place an increasingly heavy burden on test engineers. Applications, whether web-based or client-server, must be tested for seamless interface with the backend databases. Testers are required to know how to create and use SQL, stored procedures, and other database objects to effectively test today's data driven environments. They also need to know how to successfully test objects such as stored procedures and views in an application's databases. This course will teach the tester the basics of Structured Query Language for use in testing relational databases. Attendees will learn the SQL constructs and features necessary to formulate and implement test cases uncovering bugs in database systems causing data corruption. Common hacks for leaving databases vulnerable to attack will be covered including test cases for protecting against SQL Injection attacks.

Course Outline

Module 1: Database concepts primer
The Database Component : What is a Database Application?
Testing the Application vs. Testing the DBMS
Understanding Data Storage
Knowledge Requirements for Database Test Engineers
Back-end vs. Front End Testing
Data must pass Quality Assurance too!
General database basics
Relational Database Concepts
Exploratory Testing: Reading an ERD (Entity Relationship Diagram)
Ensuring data integrity: field, table and referential data integrity
How lack of data integrity causes bugs
Test Issues for High Performance database systems

Module 2: Basic SQL (Structured Query Language)
SQL Coding Standards -- what you need to know
Data Quality Issues: Assess data for timeliness, consistency, accuracy
SQL Basics: Select, Insert, Update and Delete
Using the Select Statement to Test data
Using the Insert Statement to generate Test data
Testing for application accuracy using the Update and Delete statements
Lab: Basic SQL Practice : Testing for basic data integrity

Module 3: Testing with SQL
Joining tables
Generating high volumes of Test Data
Useful SQL Queries for Data Verification
How Concurrency Issues affect data accuracy
Database objects: Constraints, Views, Stored procedures
Testing Stored procedures using SQL
Creating a stored procedure test harness
Performance Testing using SQL
Additional Areas to Test
Lab: Uncovering corrupt data bugs with SQL
Lab: Testing using stored procedures

Module 4: Using SQL in Scripting Languages
Bypassing the application to verify data
Creating a test harness for database testing Using Perl
Creating a test harness for database testing Using VBScript
Catching common Database application hacks:
test cases protecting against SQL Injection and other common hacks
Session Wrap-Up
Summary
Final Questions