Advanced SQL and Data Quality Testing (Hands-On) (CSTAS #4) or (CSTP Elective) or (CTM Elective)

This tutorial covers area 4 of the Certified Software Test Automation Specialist requirements. This tutorial is available for on-site training only. To bring this course to your test team at your location, contact our Education and Professionals Services Group.

Concepts

Databases are an integral part of most software applications; for many of them, database is the major component. Thus, knowing how to tests database management applications is of utmost importance. SQL, or Simple Query Language, is the tongue almost all modern databases can understand, and therefore real database testing is impossible without the knowledge of SQL.

Many test engineers know the basics of SQL and relational databases and can write simple SELECT queries. However, a lot is to be gained from learning how to construct more advanced SQL statements. Armed with those, Data Quality Testing can be much more effective and efficient.
The course will focus on three main subjects: advanced SQL statements, data quality testing, and utilizing Excel for data quality testing.
Multiple exercises will be provided during the course: there will be at least one exercise for every SQL-related topic.

Participants will learn:

• How to construct advanced SQL statements
• Where to look for SQL help
• The principles of Data Quality Testing
• How to use Excel to empower Data Quality Testing

Pre-requisites

Prior to taking part in this course, the participants are expected to:

• Understand the principles behind relational databases
• Understand the concepts of querying and updating databases
• Be familiar with the components of query statements
• Be able to construct basic SELECT, UPDATE, INSERT, and DELETE queries
• Know how to write basic JOIN queries

Outline

• What the course is about
  • Scope: what do we cover in depth, what do we glance over, what we do not cover
  • Goals: what will we learn
  • Administrative items
• SQL: From basic to advanced
  • Data Definition Language - Overview
  • Data Manipulation Language – In Depth
  • SELECT
    • Predicates
    • Field aliases
    • Grouping and ordering
    • SELECT INTO
  • Wildcards
  • JOIN
    • INNER
    • OUTRT
    • LEFT and RIGHT
  • UNION
  • Aggregate functions
    • Why aggregate functions are important
    • Avg, Count, Min, Max, Statistical functions, Sum
  • Scalar functions
    • String functions
    • Numeric functions
    • Date & time functions
    • Data type conversion
  • Expressions
    • BETWEEN, LINE, IN
  • UPDATE, INSERT, DELETE
  • SQL Data Types
    • Why data types are so relevant for testing
    • SQL Server data types
    • Oracle data types
    • Other data types and where to look for them
  • Subqueries
  • Where to look for help?
• Data Quality Testing
  • What is data quality testing
  • What should be tested
  • What kinds of tests to consider
  • Data quality tests in the software life cycle
  • Tools for data quality testing
• Using Excel for Data Quality Testing
  • Why Excel
  • Connecting the database
  • Querying data
  • Validating data