Microsoft SQL Server Transact-SQL (T-SQL) Window Functions

What you'll learn
Apply powerful window functions in T-SQL to solve both simple and complex problems significantly faster than before
Learn how to apply calculations against sets of rows in a flexible, clear, and efficient manner
Overcome the limitations of grouped aggregate functions; use window functions to mix detail and aggregate elements in the same query
Practice writing window function queries using our interactive, custom user interface powered by Visual Basic for Application (VBA)
Requirements
Intermediate to advanced T-SQL programming skills are required for this course
At least 12-18 months of T-SQL programming
Must be familiar with joins, set-based queries, table expressions(e.g. derived tables and Common Table Expressions) and subqueries
Instance of SQL Server 2012 or higher and Microsoft Excel
Description
This course is designed and intended for Transact-SQL (T-SQL) developers who are new to window functions and wish to go beyond the traditional query approaches to express set calculations more efficiently using the powerful window functions. Students are not expected to have and knowledge of window functions. The primary purpose of the course is to teach window functions from scratch. This course is designed to bring an understanding of the new dimension that window functions bring to the SQL language.
At the end of the course you will understand the concepts and practical applications of window functions including
Background of window functions (description of window functions, set-based vs. iterative/cursor programming)
Elements of window functions (Partitioning, Ordering, Framing)
Query elements supporting window functions (logical query processing, clauses supporting window functions, circumventing the limitations)
Aggregate window functions (description of aggregate window functions, supported windowing elements)
Offset window functions (supported window elements, LAG and LEAD functions, FIRST_VALUE and LAST_VALUE functions)
Students are expected to have intermediate to advanced T-SQL querying skills and comfortable with
Extracting data from multiple tables with joins, subqueries, derived tables, and table expressions (e.g. Common Table Expressions CTEs).
A good understanding of Common Table Expressions (CTEs) will be advantageous. Where window functions on their own cannot achieve a task, we teach how to combine window functions with CTEs to overcome complex data analysis tasks.
At the end of the course, you will be able to
Replace any existing complex and lengthy codes involving joins and subqueries with a few lines of code using window functions
Bridge the skill gap between iterative/cursor programming and set-based programming
Use window functions to achieve elegant solutions to complex data analysis tasks
The course is video-based using an interactive and animated, custom designed user interface powered by VBA. Analysis are performed using animated, step-by-step processes to explain concepts
All resources needed for the course are included in the last lesson for download
AcotexDB.sql (open and run in SSMS to create the sample database)
Quizzes
Coding Exercises - Interactive exercises powered by VBA to provide you with a unique learning experience
Solutions to coding exercises - Word document containing the solutions for all the coding exercises
Who this course is for
This course is intended for SQL Server developers and database administrators (DBAs); those who already write T-SQL codes but have little or no skills using window functions
Homepage
https://www.udemy.com/course/microsoft-sql-server-transact-sql-t-sql-window-functions/