70-461 Session 5: Querying Microsoft SQL Server 2012

Using T-SQL code (queries), we'll look at analytical functions, grouping sets, ranking functions and spatial aggregates.
4.0 (7 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
185 students enrolled
40% off
Take This Course
  • Lectures 28
  • Length 3.5 hours
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works


Find online courses made by experts from around the world.


Take your courses with you and learn anywhere, anytime.


Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2015 English

Course Description

This course is the foundation for the Microsoft Certificate 70-461: "Querying Microsoft SQL Server 2012".

In Sessions 1 and 2, we learned all about dates, strings and number data types and DML statements. In sessions 3 and 4, we created views, procedures, triggers, constraints and combining datasets.

We'll will now be creating aggregate queries, working through objective 9 of the exam 70-461. We'll be reviewing the ranking functions ROW_NUMBER, RANK, DENSE_RANK and NTILE. We'll look at the 8 analytic functions news 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.

What are the requirements?

  • You need to know about data types and functions, DML and DDL statements, creating views, triggers, constraints and procedures, and use UNION.
  • If you don't, then you should take my earlier Sessions first.
  • There is a 30-day money back guarantee of this Udemy course.
  • Why not have a look at the curriculum below and see what you can learn?

What am I going to get from this course?

  • 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.
  • Understand the geography and geometry data types, add points, line, polygons and circles, query these tables, find where lines and shapes intersect, and aggregate them.

Who is the target audience?

  • This course is for you if want a refresher course in SQL, or are learning SQL for the first time.
  • This course is for you if you are working towards the requirements for exam 70-461, or if you just want to expand your knowledge of T-SQL.
  • If you are starting from the beginning, then you should look at my sessions 1 to 4 courses before taking this session 5.
  • If you want more advanced topics, then please look at my later sessions.

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.


Section 1: Introduction

Welcome back. A quick recap of where we are.

Section 2: Objective 9: Implement aggregate queries

We'll introduce the four sets of functions we'll be looking at.


We'll do a simple SUM, and see how it can be transformed with an OVER().


We'll see what happens if we partition and order the OVER() keyword.


We'll do a numeric range, limiting it to just one row preceding and proceeding


We'll further extend it backwards and forwards.


We'll see the difference between RANGE and ROWS, and which one you should, by default, do.


We'll ask the question - what happens if you omit the RANGE or ROW clause?

Section 3: Objective 9d: Ranking functions

We'll introduce the ranking functions ROW_NUMBER, RANK and DENSE over an entire table.


We'll look at NTILE, and see how I think it should be improved.

Section 4: Objective 9a: New analytic functions

We'll find the first and last value within a group of rows.


We'll find data in preceding and succeeding rows. We'll see how it used to be done, and find out how much simpler it is now.


Find the percentage of values in a range which are lower than the current range. We'll see how it used to be done, and see how much easier it is now.


We'll look at this pair of percentile functions.

Section 5: Objective 9b: Grouping sets

Our SELECT statements to date do not have any totals. Let's try to cure that using what we already know, and then order it.


Having got totals, let's do it more simply using the ROLLUP command, and then use functions to find out what are totals and are NULL values.


We then change the ROLLUP to CUBE, to give us all the combinations of totals, and GROUPING SETS, to give us a custom combination of totals. We'll then ORDER the NULLs at the bottom using COALESCE and CASE.

Section 6: Objective 9c: Spatial aggregates

We'll be introduced to the need for geometry and geography spatial types, and look at X and Y co-ordinates.


We'll define a geometry data field in a table, and populate it with POINTs, and see them visually.


Having populated a table with POINTs, we will retrieve data from the table, including co-ordinates and distance between POINTs.


We'll add LINESTRINGs, POLYGON and CIRCULARSTRINGs into the table, and see them visually.


Having populated a table with shapes, we will retrieve data from the table, including co-ordinates and distance between the LINESTRING, POLYGON and CIRCULARSTRINGs


We'll look at the differences between geometry and geography, see different definitions of longitude/latitudes, and look up locations of places in Google Maps. We'll then re-run previous queries.


We'll then achieve objective 9c, by combining different datasets into one collection. We'll also FILTER our table to only show shapes which are contained within another shape.


We've finished Objective 9 - let's review.

Section 7: End of Session 5

Well done for getting this far. Let me say a little thank you.


We'll review what we have learned in Session 5, and look forward to what we will do in Session 6.

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Phillip Burton, Over 29,000 students so far

Phillip is a Computing Consultant providing expert services in the development of computer systems and data analysis. He is a Microsoft Certified Technology Specialist. He has also been certified as a Microsoft Certified Solutions Expert for Business Intelligence, Microsoft Office 2010 Master, and as a Microsoft Project 2013 Specialist.

He enjoys investigating data, which allows me to maintain up to date and pro-active systems to help control and monitor day-to-day activities. As part of the above, he also developed and maintained a Correspondence Database in Microsoft Access and SQL Server, for viewing job-related correspondence (110,000 pdfs in one job) by multiple consultants and solicitors.

He has also developed expertise and programmes to catalogue and process and control electronic data, large quantities of paper or electronic data for structured analysis and investigation.

He is one of 9 award winning Experts for Experts Exchange's 11th Annual Expert Awards and was one of Expert Exchange's top 10 experts for the first quarter of year 2015.

His interests are working with data, including Microsoft Excel, Access and SQL Server.

Ready to start learning?
Take This Course