SQL Server video course – Session 5

70-461 Session 5: Querying Microsoft SQL Server 2012

Building on Sessions 1-4, we’ll look at analytical functions, grouping sets, ranking functions and spatial aggregates.

Use the coupon code SQLINTRO to save 50%

About This Course

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

Rather than present one huge course, this course takes some of the basics and then goes in depth. 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.

$20  $10
Take This Course

(That’s $3 per hour!)

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.

Lectures: 28
Video: 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

Preview video 13

(Why not view the below video full screen? Click the YouTube logo in the bottom-right hand corner.)

Want to see more preview videos?

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? If you already know them, please see my later sessions.

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.

What 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.


70-461 Session 5: Querying Microsoft SQL Server 2012 | Udemy

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.

Do you have a question – why not ask it in the box below?

Take This Course

Comments are closed.