Duration: 2 Days
Course Overview
This module introduces the foundation knowledge needed to be able to apply testing to databases, their queries and stored procedures. Without a working knowledge of the structure of a relational database, and of how to write schema definitions, queries or stored procedures in SQL, testers responsible for validating database designs would be severely challenged.
The module is presented using about 40% taught content, with the remainder of the course being hands-on practical activities creating database tables, designing and executing queries and procedures, combined with small group activities based around understanding how to test code written for databases.
How can I attend my course?
Course Content
Introduction
• Database overview
• Relational data storage architecture
• Tables, columns, keys, indexes and constraints
• Querying databases with SQL
Data definition
• Defining tables, columns and types
• Primary key columns and column constraints
• Indexing tables
Data manipulation
• Querying tables with the SQL SELECT statement
• Setting filter predicates with the WHERE clause
• Ordering results with ORDER BY
• Selecting returned columns
• Modifying table data: INSERT, UPDATE and DELETE
Relational tables
• Relating tables with primary and foreign keys
• Executing queries across tables: INNER and OUTER JOIN
• Foreign key constraints and referential integrity
Enhanced SQL
• Using DISTINCT to eliminate duplicate rows
• Grouping results using GROUP BY
• Aggregate functions in SQL
Procedures
• Functions, views, stored procedures, triggers and prepared statements
• Dialectic extensions to SQL: PL/SQL and T-SQL
• Writing and saving procedures in a database
• Invoking procedures
Transactions and concurrency
• Optimistic and pessimistic locking scenarios
• Atomicity and the ACID requirement
• Transaction support within DB
• Distributed transactions
Database access and security
• Accessing databases from code
• Connection strings and providers
• Authentication and authorization mechanisms in databases