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:

1. Background of window functions (description of window functions, set-based vs. iterative/cursor programming)

2. Elements of window functions (Partitioning, Ordering, Framing)

3. Query elements supporting window functions (logical query processing, clauses supporting window functions, circumventing the limitations)

4. Aggregate window functions (description of aggregate window functions, supported windowing elements)

5. 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:

1. Extracting data from multiple tables with joins, subqueries, derived tables, and table expressions (e.g. Common Table Expressions CTEs).

2. 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:

1. Replace any existing complex and lengthy codes involving joins and subqueries with a few lines of code using window functions

2. Bridge the skill gap between iterative/cursor programming and set-based programming

3. 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:

  1. AcotexDB.sql (open and run in SSMS to create the sample database)

  2. Quizzes

  3. Coding Exercises - Interactive exercises powered by VBA to provide you with a unique learning experience

  4. Solutions to coding exercises - Word document containing the solutions for all the coding exercises