Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Microsoft SQL Server Transact-SQL (T-SQL) Window Functions
Rating: 4.5 out of 5(3 ratings)
26 students

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

Go beyond traditional query approaches to express set calculations more efficiently with window functions
Created bySamuel Osei-Tutu
Last updated 12/2021
English

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)

Course content

4 sections • 17 lectures • 3h 0m total length

  • Introduction to Window Functions7:28

    1. A look at the anatomy of window functions and their applications

    2. A detailed explanation of how to specify the window for the computation using the OVER clause

  • Further discussion of window function concepts using the RANK() window function4:41

    You will understand the different window function categories and their application:

    • Aggregate functions: SUM(), MAX(), MIN(), AVG() etc.

    • Ranking functions: RANK(), DENSE_RANK(), ROW_NUMBER() etc.

    • Offset functions: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

  • Set-based querying versus Iterative programming10:04

    1. You will be able to understand set-based querying versus iterative/cursor programming

    2. Learn you you can transit from iterative programming to set-based programming with the help of window functions

    3. Understand the concepts of the relational model and how SQL Server uses this model to achieve set-based T-SQL querying

  • Input and output of a query with a window function9:29

    1. After this lecture, you will be able to understand the difference between the ORDER BY clause as part of the OVER clause specification and a presentation ORDER BY clause

    2. Using animation and the RANK() window function for demonstration, you learn how window functions are applied to the window with respect to the current row

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:


  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

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

Report abuse