Advanced Querying Data with Transact-SQL

Subjects

1. Refresher
2. Table Expressions
3. Set Operators
4. Window Ranking, Offset and Aggregate Functions
5. Pivot Tables and Grouping Sets
6. Programming with T-SQL

Refresher

In this module we will make sure everyone remembers the basics of querying and we can continue at the same level. Goals:

• Standard elements of a SELECT query
• Joins
• Grouping & Aggregating
• Subqueries

Table Expressions

Table expressions are SQL elements that are not a table but can (mostly) be used as if they were in fact a table. Goals:

• Views
• Table Valued Functions
• Derived Tables
• Common Table Expressions (CTE)
• Apply
• When to pick which option?

Set Operators

Set Operators allow you to vertically combine and compare two similar result sets. This contrasts joins, where you are horizontally combining data. Goals:

• UNION (ALL)
• EXCEPT / INTERSECT

Window Ranking, Offset and Aggregate Functions

Window functions are ideal for many analytics scenarios like running totals and moving averages. They also allow you to 'time travel' to previous or following values in your result set. Goals:

• Window Functions
• Window
• Frame
• Partition By

Pivot Tables and Grouping Sets

Pivot Tables are often needed to exchange data between different systems. Grouping Sets can help when offering analytic data - in a single query you can cover multiple different queries at once. Goals:

• PIVOT
• UNPIVOT
• GROUPING SETS
• GROUP BY CUBE/ROLLUP

Programming in T-SQL

You can use T-SQL to write queries, but it is also possible to program in t-sql. Why would you need this in a database and what is the syntax? And how do Stored Procedures fit into this? Goals:

• Batches
• Variables
• Using Stored Procedures
• Control of Flow
• Errors and Error Handling
• Transactions