70-461 Session 5: Querying Microsoft SQL Server 2012
4.1 (10 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.
219 students enrolled
Wishlisted Wishlist

Please confirm that you want to add 70-461 Session 5: Querying Microsoft SQL Server 2012 to your Wishlist.

Add to Wishlist

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.1 (10 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.
219 students enrolled
Created by Phillip Burton
Last updated 3/2017
English
Current price: $10 Original price: $25 Discount: 60% off
6 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3.5 hours on-demand video
  • 1 Article
  • 3 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
Have a coupon?
What Will I Learn?
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.
View Curriculum
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?
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.

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.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 28 Lectures Collapse All 28 Lectures 03:22:17
+
Introduction
2 Lectures 02:39

Welcome back. A quick recap of where we are.

Preview 02:24

Resources
00:15
+
Objective 9: Implement aggregate queries
7 Lectures 49:19

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

Preview 06:55

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

OVER()
09:01

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

PARTITION BY and ORDER BY
08:18

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

RANGE
04:09

We'll further extend it backwards and forwards.

CURRENT ROW and UNBOUNDED
04:30

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

RANGE versus ROWS
09:03

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

Omitting RANGE/ROW?
07:23
+
Objective 9d: Ranking functions
2 Lectures 12:39

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

ROW_NUMBER, RANK and DENSE_RANK
06:15

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

NTILE
06:24
+
Objective 9a: New analytic functions
4 Lectures 32:48

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

FIRST_VALUE and LAST_VALUE
08:30

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.

LAG and LEAD
06:15

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.

CUME_DIST and PERCENT_RANK
08:06

We'll look at this pair of percentile functions.

PERCENTILE_CONT and PERCENTILE_DISC
09:57
+
Objective 9b: Grouping sets
3 Lectures 26:16

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.

Adding Totals
10:21

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.

ROLLUP, GROUPING and GROUPING_ID
05:03

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.

GROUPING SETS
10:52
+
Objective 9c: Spatial aggregates
8 Lectures 01:10:42

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

Introduction
06:24

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

POINT
09:14

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

POINT queries
13:28

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

Line, POLYGON and Circles
08:55

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

Preview 07:54

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.

Geography
09:22

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.

Spatial Aggregates
12:55

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

Summary
02:30
+
End of Session 5
2 Lectures 07:54

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

Bonus Lecture
05:55

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

Well done!
01:59
About the Instructor
Phillip Burton
4.4 Average rating
1,999 Reviews
32,778 Students
22 Courses
Over 32,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.