70-461 Session 5: Querying Microsoft SQL Server (T-SQL code)

Brief Summary

This course dives into the world of SQL, focusing on analytical and ranking functions, grouping techniques, and spatial data. You'll get hands-on with T-SQL and learn essential concepts that will make you a pro at organizing and analyzing your data.

About This Course

Using SQL, create analytical functions, grouping sets, ranking functions, spatial aggregates. SQL Server 2012-2022

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012". Please note - this certificate is no longer being offered by Microsoft. However, the exam requirements allow you to have a good understanding of T-SQL.

We will now be creating aggregate queries, using the OVER() clause, together with PARTITION BY and ORDER BY. We'll also compare and contrast RANGE and ROWS, and how they work with CURRENT ROW and UNBOUNDED.

We'll be reviewing the ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE. We'll look at the 8 analytic functions new to SQL Server 2012, such as LAG, LEAD, FIRST_VALUE and LAST_VALUE.

We'll look at alternative ways of grouping and adding totals, using ROLLUP, CUBE, GROUPING SETS and GROUPING_ID. We'll also look at the geometry and geography data types, plotting locations on a grid, together with functions and aggregates.

No prior knowledge other than what we covered in Sessions 1 to 4 is required. This course builds on the knowledge previously gained in those previous sessions.

There are regular quizzes to help you remember the information.

Once finished, you will know what how to write ranking functions, analytic functions, grouping sets and spatial aggregates, and we'll have expanded on our current knowledge of T-SQL.

• Apply the ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE

• Use the analytic functions new to SQL Server 2012, such as LAG, LEAD, FIRST_VALUE and LAST_VALUE

• Use different ways to group, such as ROLLUP, CUBE, GROUPING SETS, and related functions such as GROUPING_ID.