Writing Advanced Transact-SQL Queries

DSOC0021—One Day Training Class

Introduction

Note: You are viewing a Preliminary Course Outline; parts of the course are currently in development and subject to change.

This course will provide students with good exposure to advanced Transact-SQL querying techniques, such as are in common use with Microsoft SQL Server 2000 or 2005.

Audience

IT Professionals, Applications Developers, and Report Writers who are already comfortable with basic SQL syntax.

This course is designed people who want to master the more-sophisticated capabilities of SQL.

Prerequisites

We suggest attending our one-day ANSI SQL Querying Primer, but as a minimum:

Students must be comfortable Writing the following SQL clauses:

• SELECT
• FROM
• WHERE
• GROUP BY
• HAVING
• ORDER BY

Students must be comfortable with following SQL Concepts:

• Aliasing Column and Table Names
• Simple Table JOIN Statements
• Subquerying

Training Module 1: Combining Resultsets with UNION

• The UNION Statement
• The UNION ALL Statement
• Aggregating UNIONed Resultsets
• Ordering UNIONed Resultsets

Training Module 2: JOIN Fundamentals Review

• The Myriad of JOIN Types
• INNER JOIN Clause
• LEFT JOIN Clause
• Joining Literals
• Compound Joins

Training Module 3: Scripting Complex Joins

• Detailed Join Syntax
• Joining to Subqueries
• Joining to Ranges of Values
• Joining to Nested Ranges of Values
• More...

Training Module 4: Subqueries Everywhere

• SubQueries In the SELECT clause
• SubQueries In the FROM clause
• SubQueries In the WHERE clause
• Correlated SubQueries
• 2005 Specific: Using CTEs (Common Table Expressions)

Training Module 5: Scripting Pivot Table Queries

• Pivoting the Old-Fashioned Way
• Pivoting with Power of SQL Server 2005
• More...

Training Module 6: Using Cursors to Iterate Through Records

• When to use Cursors
• ANSI Cursor Syntax
• Transact SQL Cursor Syntax
• Iterating Through Records Using a Cursor
• More...

Training Module 7: Some Common (but Complex) Queries

• Showing all the details (columns) for aggregated rows
• Removing duplicate rows programmatically (with SQL)
• Joining to Nested Ranges of Values
• More...

Training Module 8: Data Modification Statements

• The INSERT Statement 
• The UPDATE Statement
• The DELETE Statement

Training Module 9: Working with Transactions

• Acid Transactions
• Implicit Transactions
• Explicit Transactions
• Distributed Transactions

Training Module 10: 2005 Specific: Querying XML Data

• Overview of XML
• Overview of XML DataType
• Querying the XML DataType