Microsoft SQL Server Transact-SQL (T-SQL) Window Functions
Go beyond traditional query approaches to express set calculations more efficiently with window functions
What you will 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)
Why take this course?
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
Screenshots
Charts
Price
Rating
Enrollment distribution
