SQLServer Training Courses Released Under Creative Commons

by Evan Leybourn on      2 minutes read

If you would like to speak to Evan about this course, please contact him here.

CC BY SA

As part of an ongoing release of all my training material following the launch of my new book, I am releasing "Database and SQL Testing with SQLServer" under a Creative Commons Attribution Share-Alike license. That is, you are free to share, copy, and adapt any part of this training course for your own purposes. All materials, including examples are available for download from this page.

This 4-day workshop is designed for testers responsible for the validation and quality control of database driven applications and who want to improve their testing capabilities, and take advantage of the advanced features within MS SQL Server. This course focuses on SQL Server, but most of the techniques will be applicable to any SQL 2003 compliant database. We will be looking at relational theory and good database design, and how these underlying principles relate to writing database specific test cases. We will also be examining SQL, including some of the more advanced features used to test the performance of complex queries. Finally we will then look at how the SQL Server profiler and execution plan; how it parses SQL and how to debug SQL statements and stored procedures.

Course Notes

Advanced SQL Testing (SQLServer).zip

Participant Benefits

  • Gain a clear understanding of relational theory, and how that affects database queries in practice.
  • Learn how to debug SQL queries and stored procedures.
  • Discover the advanced capabilities of SQL Server and how to make use of them in the context of quality control and testing.
  • Understand typical errors made by developers for more focused testing.
  • Learn how to build synthetic data for testing.

Outline

This is an activity-centric workshop. Participants are expected to bring laptops with versions of SQL Server (Express at a minimum) installed. Participants are also encouraged to bring their own databases, data and example test cases. More than 50% of this course will be spend practicing the concepts and focus areas below.

  1. What to Expect in Database Design
    • Relational Theory
    • Entity Relation Diagrams
    • Referential Integrity
  2. Dealing with Data
    • Large Datasets (100,000,000 + records)
    • Testing Data Quality
    • Geo-spatial Functions (optional)
  3. SELECT Statements
    • Testing NULL Operators
    • Testing Joins
    • Testing Subqueries
    • Testing complex SQL
    • Testing Views
  4. Data Modification
    • Testing Data Modification
    • Testing Data Integrity
  5. SQL Server
    • Information Schema
    • Transactions
    • User-defined Data Types
    • Query Execution Plan
    • SQL Server Profiler and the Plan Cache
    • Testing User Roles, Access Control and Access Privileges
  6. Stored Procedures
    • Testing Stored Procedures
    • Testing Triggers
    • Testing Exceptions
    • Creating Synthetic Data