PNG.png){kind=tracking}
Microsoft SQL Server 2016 and 2017 Advanced Querying
This 2 day course is intended for students who have taken the “Microsoft SQL Server 2016 and 2017 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 and 2017 Relational Database Design and Querying Fundamentals” course.
Additionally, delegates should have attended the 2 day “Microsoft SQL Server 2016 and 2017 Introduction to Querying” course, or possess equivalent knowledge gained in the workplace or elsewhere.
Upon successful completion of this course, students will be able to:
Introducing Native SQL Server Functions
Getting Help on Functions
Functions Relating to NULL Values
Using the CASE Expression
Native SQL Server Data Types
Data Type Precedence
Implicit Data Type Conversions
Explicit Data Type Conversion Functions
Working with Variables
Using Batches
Reviewing Joins
Joining a Table to Itself (Self Join)
Using Non-Equi Joins
Reviewing SQL Server Aggregate Functions
Using the ROLLUP and CUBE Operators
Using GROUPING SETS
Window Functions that Rank Data
Using ROW_NUMBER
Using RANK
Using DENSE_RANK
Using NTILE
Aggregate Window Functions
Analytic Window Functions
Reviewing the UNION and TOP Operators
Using the EXCEPT and INTERSECT Operators
Reviewing Subqueries
Using the APPLY Operator
Table Variables
Temporary Tables
Using the PIVOT Operator
Using the UNPIVOT Operator
Introduction to Common Table Expressions
Using Common Table Expressions
Recursive Queries Using Common Table Expressions
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
Using the OUTPUT Clause
The MERGE Statement
How SQL Server Modifies Data in Tables
Appendix. Introduction to Views
Introducing Views
Creating and Modifying a View
Querying Data Through a View
Using UNION, EXCEPT, and INTERSECT Together
