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