1. DATABASE TESTING CONCEPTS PREIMER

• The Database Component: What is a Database Application?
• Testing at the Database layer
• Why should Test professionals know DB Basics?
• The DB component: What is a Data-Based Application?
• Back end vs. Front End Testing
• Examining the data’s round trip through the app
• Common problems in Databases that affect the Database Application
• Knowledge Requirements for Database Testing
• Test Plan: Organizing your approach
• Data must pass Quality Assurance too!
• Test Set up

2. Relational Database Basics For Testing

• Why should Test professionals know Relational DB basics?
• What a relational database?
• Realization
• Types of Data Integrity
• Lack of data integrity introduces bugs
• Identifying Design Defects
• Inspecting table structures to reveal design problems
• Exploratory Testing: Reading an ERD
• Table Relationships: 1-1, 1-many, many-to-many
• What to look for when Testing Relational Databases

3.Data Normalization Bugs

• What bugs are caused by improperly normalized databases
• Understanding Normalization: First, 2nd and 3rd Normal Form
• Understanding De Normalization
• Identifying poor design; developing Test cases

4.Introduction To SQL

• Why should Test professionals know Structured Query Language?
• SQL essentials
• Basic SQL statements for Testing

5. STRUCTURAL BACK END TESTS

· Database schema tests

· Databases and devices

· Tables, columns, column types, defaults, and rules

· Keys and indexes

· Stored procedure tests

· Individual procedure tests

· Integration tests of procedures

· Trigger tests

· Update triggers

· Insert triggers

· Delete triggers

· Integration tests of SQL server

· Server setup scripts

· Common bugs

6. FUNCTIONAL BACK END TESTS

Dividing back end based on functionality

Checking data integrity and consistency

Login and user security

Stress Testing

Test back end via front end

Testing the Nightly downloading and Distribution jobs

• Batch jobs
• Data downloading
• Data conversion
• Data distribution
• Nightly time window
• Common bugs

Testing the Interfaces to Transaction APIS

• APIs' queries to back end
• Outputs of back end to APIs

• Common bugs

7. Other Database testing Issues

• Test tips
• Test tools
• Useful queries

8. Test Plan

• Organizing DB Testing approach
• Data must pass Quality Assurance tool?
• Workshop on real time application Test Plan preparation

9. Test Cases

· DB Testing test cases format

· How to incorporate SQL queries

· How to prepare test cases

· How Back End test cases are different from GUI test cases

· How to identify regression suite from back end test cases

· Workshop on real time application Test Cases preparation

· What are the pre-conditions to execute Back end test cases

· Back End test cases execution

· Defect tracking for Back end test case failures

Database Testing

1. SQL Basics Course Content

• Why should Test professionals know Structured Query Language?
• Basic SELECT Statement
• Writing the statement in SQL Plus
• Running the SELECT statement.

• Single Column
• Descending order
• Multiple column sort

3. Conditional retrieval of data

• Working with complex conditions
• AND, OR, NOT, LIKE, BETWEEN commands

4. Working with variables

• Creating and using variables
• Command substitution

5. What are the difference between DDL, DML and DCL commands?

• DDL is Data Definition Language statements.
  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

6. 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
• EXPLAINS PLAN - explain access path to data
• LOCK TABLE - control concurrency

7. 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

8. Pseudo columns and functions

• Pseudo Columns
• Rownum
• Sysdate
• User & UID
• Dual Table

9. Working with character functions

• UPPER, LOWER, INITCAP,
• RPAD(), RTRIM(), SUBSTR(), INSTR(),
• TRANSLATE(), REPLACE()
• GREATEST(), LEAST(), DECODE

10. Working with date functions

• TO_CHAR(), TO_DATE()
• MONTHS_BETWEEN(), ADD_MONTHS()
• LAST_DAY(), NEXT_DAY()

11. Using non-character function

• ROUND(), TRUNC(), SIGN()
• Working with multiple tables
• Different types of Joins
• Writing Outer Joins

12. Using the SET operators

• Union
• Intersect
• Minus

13. Aggregating data using group functions

• GROUP BY
• HAVING

14. Creating Sub queries

• Single Row sub queries
• Multiple row Sub queries

15. Enhancing groups function

• ROLLUP
• CUBE

16. Transaction Control Language

• Rollback
• Commit
• Save point

17. Processing hierarchies

• Creating the Tree structure
• LEVEL
• CONNECT BY

18. Data Manipulation Language

• Queries Using INSERT
• Queries Using UPDATE
• Queries Using DELETE

19. Data Definition Language

• Queries Using CREATE
• Queries Using TRUNCATE
• Queries Using ALTER
• Queries Using DROP
• Queries Using RENAME
• Queries Using DESCRI

20. Using Declarative Constraints

• Not Null Constraint
• Check Constraint
• Unique Constraint
• Primary Key Constraint
• References Constraint
• On Delete Cascade
• On Delete Set Null

21. Other Database Objects

• Views
• Sequences
• Synonyms
• Indexes

22. Database Security

• Object Privileges
• Granting access to objects