iTrain Scotland IT Training Edinburgh image
 

Microsoft SQL Server 2016 Advanced Querying

iTrain Scotland IT Training Edinburgh image

Home > Courses > SQL courses > Microsoft SQL Server 2016 Advanced Querying

Microsoft SQL Server 2016 Advanced Querying

This 2 day course is intended for students who have taken the “Microsoft SQL Server 2016 Introduction to Querying” course or, possess equivalent knowledge gained in the workplace or elsewhere. It may also prove beneficial to delegates wanting to refresh their current knowledge of the subject matter.

Take your querying skills to a new level with this advanced course. Gain a deeper insight into your data and understand how SQL Server internally handles the data you query. Learn how to manipulate data in new and meaningful ways for the purposes of data analysis and reporting. Learn useful techniques for querying temporal data, such as working with date intervals and data islands. Consider best practices for writing efficient queries and, avoid common pitfalls.

You will find that this is a comprehensive course for this level, with plenty of illustrated examples and augmented with practical hands-on exercises.

Students should ideally be familiar with basic programming concepts

They should also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. These prerequisites can be met by taking the 2 day “Microsoft SQL Server 2016 Relational Database Design and Querying Fundamentals” course.

Additionally, delegates should have attended the 2 day “Microsoft SQL Server 2016 Introduction to Querying” course or, possess equivalent knowledge gained in the workplace or elsewhere

Upon successful completion of the full course, students will be able to: 

  • Use Functions Native to SQL Server
  • Manipulate NULL Values in Queries
  • Conditionally Manipulate Data using the CASE Expression
  • Understand Native SQL Server Data Types
  • Implement Variables
  • Implement Advanced Join Techniques
  • Aggregate Data to Higher Grouping Levels
  • Rank Data Using SQL Server Ranking Functions
  • Analyse Data with Window Functions
  • Restrict Data Result Sets
  • Correlate Data with the APPLY Operator
  • Implement Temporary Data Storage
  • Transform Data Using Pivot Operators
  • Understand Common Table Expressions (CTE’s)
  • Implement Advanced Data Modification Techniques
  • Work with Native SQL Server Date and Time Data

1. Using Functions in Queries

Introducing Native SQL Server Functions

Getting Help on Functions

Functions Relating to NULL Values

Using the CASE Expression

 

2. Working with SQL Server Data Types

Native SQL Server Data Types

Data Type Precedence

Implicit Data Type Conversions

Explicit Data Type Conversion Functions

Working with Variables

Using Batches

 

3. Advanced Table Join Techniques

Reviewing Joins

Joining a Table to Itself (Self Join)

Using Non-Equi Joins

 

4. Aggregating Data at Higher Levels

Reviewing SQL Server Aggregate Functions

Using the ROLLUP and CUBE Operators

Using GROUPING SETS

 

5. Ranking Grouped Data

Window Functions that Rank Data

Using ROW_NUMBER

Using RANK

Using DENSE_RANK

Using NTILE

 

6. Analysing Data with Window Functions

Aggregate Window Functions

Analytic Window Functions

 

            7. Restricting Result Sets

Reviewing the UNION and TOP Operators

Using the EXCEPT and INTERSECT Operators

Using UNION, EXCEPT, and INTERSECT Together

Using OFFSET/FETCH NEXT Filters

Using the TABLESAMPLE Operator

 

8. Advanced Subqueries

Reviewing Subqueries

Using the APPLY Operator

 

9. Temporary Storage

Table Variables

Temporary Tables

 

10. Crosstab Queries

Using the PIVOT Operator

Using the UNPIVOT Operator

 

11. Common Table Expressions

Introduction to Common Table Expressions

Using Common Table Expressions

Recursive Queries Using Common Table Expressions

 

12. Advanced Data Modification Techniques

The SEQUENCE Object

Using the OUTPUT Clause

The MERGE Statement

Practical Examples of Using MERGE

 

13. SQL Server Date and Time Data Types

Understanding SQL Server Date and Time Data Types

Querying and Modifying Date and Time Data

Native SQL Server Date and Time Functions

Guidelines and Techniques for Querying Dates 

Prices below are for scheduled courses held in our Edinburgh Training Centre. Major discounts available for onsite or private courses: click General Enquiry button below to enquire about private courses.
Group discount
1 place £ 1500 n/a
2 places £ 1800 n/a
3 places £ 4500 £ 2100
4 places £ 6000 £ 2400
5 places £ 7500 £ 2700
6 places £ 9000 £ 3000
7 places £ 10500 £ 3300
8 places £ 12000 £ 3600