T-SQL Querying: Advanced Techniques

Extend your T-SQL knowledge to write optimal queries

Description

You learn to use advanced features of Transact-SQL. This will enable you to consider different solution strategies to solve business questions, leading to a more optimal solution.

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

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 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 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 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 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

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