Write advanced Transact-SQL queries

After completing this module, you should be able to:

• Create and query tables using Transact-SQL
• Create and query views using Transact-SQL
• Create queries using Common Table Expressions (CTE)
• Write queries that use derived tables

After completing this module, you’ll be able to:

• Use the UNION operator.
• Use the INTERSECT and EXCEPT operators.
• Use the APPLY operator.

After completing this module, you’ll be able to:

• Describe window functions.
• Use the OVER clause.
• Use RANK, AGGREGATE, and OFFSET functions.

After completing this module, you'll be able to:

• Write queries that pivot and unpivot result sets.
• Write queries that specify multiple groups with GROUPING SET, CUBE, and ROLLUP.

• Introduction
• Create and query tables
• Create and query views
• Use temporary tables
• Use Common Table Expressions
• Write queries that use derived tables
• Exercise: Create queries with table expressions
• Knowledge check
• Summary

• Introduction
• Use the UNION operator
• Use the INTERSECT and EXCEPT operators
• Use the APPLY operator
• Exercise: Combine query results with set operators
• Knowledge check
• Summary

• Introduction
• Describe window functions
• Use the OVER clause
• Use RANK, AGGREGATE, and OFFSET functions
• Exercise: Use window functions
• Knowledge check
• Summary

• Introduction
• Write queries that pivot and unpivot result sets
• Write queries that specify multiple groupings with grouping sets
• Exercise: Pivoting and grouping sets
• Knowledge check
• Summary