Audience
The course is designed for analysts and developers who want to familiarise themselves with the more advanced analysis capabilities of SQL Server.
Prerequisites
An understanding of the fundamentals of querying SQL Server, or any other database, is necessary to attending this course.
Duration
2 days. Hands on.
This course is available on site only. Please call for details.
Course Objectives
This course deals with the use of nested queries, advanced aggregation, and in particular the use of advanced analytic functions, with an emphasis on the new efficient analysis capabilities in SQL Server. It also covers additional functionality such as cursors, CTEs, hierarchical queries, stored procedures and functions. The end of the course is dedicated to working with SQL Server Analysis Services - a tool for fast and flexible analysis of large amounts of data in real time - as well as its integration with common applications.
Upon successful completion of this course, students will be able to:
- Use all forms of subqueries
- Implement advanced SQL aggregation
- Use ranking functions
- Make more efficient analyses using the analytic functions in SQL Server
- Replace cursor-based analyses with new windowing extensions to analytic functions
- Implement standard analytic techniques like functions and cursors
- Implement advanced analytic techniques like CTEs and hierarchical queries
- Create basic OLAP cubes and implement them to their applications
- Describe how to get hidden information from data using data mining
Course Content
Subqueries
Basic subqueries queries in SELECT, WHERE, ORDER BY and HAVING clauses
Nested queries in the FROM clause (inline views)
Multiline subqueries (operators ALL, ANY, IN, EXISTS)
Correlated subqueries
Advanced Aggregation
Queries with ROLLUP clause
Using the CUBE clause
Using the GROUPING SETS clause
GROUPING () and GROUPING_ID () functions
Using the COMPUTE and COMPUTE BY clauses
Analytic Functions
The usage and processing of analytic functions
Ranking functions
The ROW_NUMBER function
RANK and DENSE_RANK functions
The NTILE function
Cumulative and percentage distribution - CUME_DIST and PERCENT_RANK
Inverse Percentile - PERCENTILE_CONT and PERCENTILE_DISC
New features for working with a window of data in SQL Server 2012
Cumulative and moving aggregate functions (windowing functions)
Cumulative and running totals
Moving averages
LAG and LEAD
FIRST_VALUE and LAST_VALUE
Other Techniques to Work with SQL queries
Simple control of logic with CASE clause
Common Table Expressions – simplifying queries
Hierarchical queries
Cursors
Functions and stored procedures
Data Analysis Using SQL Server Analysis Services
Introduction to Analysis Services
Creating OLAP cubes
Adjusting the OLAP cubes
Working with OLAP cubes directly in Excel
Analytical dimensions and their hierarchies
Semi-additive analytic functions
Custom modifications of presentation of data in cubes
Introduction to MDX
Introduction to Data Mining
Integration with reporting