Microsoft SQL Server - Advanced Querying 2 Days
Course Overview
This 2-day course is intended for students who have taken the “Microsoft SQL Server 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.
Microsoft SQL Server - Advanced Querying
Applies to all versions from SQL Server 2012 through to SQL 2022
DURATION
2 days
INTRODUCTION
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.
PREREQUISITES
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 and Relational Database Design and Querying Fundamentals” * course.
Additionally, delegates should have attended the 2 day “Microsoft SQL Server Introduction to Querying” * course, or possess equivalent knowledge gained in the workplace or elsewhere.
COURSE OBJECTIVES
Upon successful completion of this 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)
• Work with Native SQL Server Date and Time Data
• Implement Advanced Data Modification Techniques
• Understand the Role of Transactions in SQL Server
• Understand the Role of Views in SQL Server Databases
COURSE OUTLINE
Using Functions in Queries
Introducing Native SQL Server Functions Getting Help on Functions
Functions Relating to NULL Values Using the CASE Expression
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
Advanced Table Join Techniques
Reviewing Joins
Joining a Table to Itself (Self Join) Using Non-Equi Joins
Aggregating Data at Higher Levels
Reviewing SQL Server Aggregate Functions Using the ROLLUP and CUBE Operators Using GROUPING SETS
Ranking Grouped Data Window Functions that Rank Data Using ROW_NUMBER
Using RANK
Using DENSE_RANK Using NTILE
Analysing Data with Window Functions
Aggregate Window Functions Analytic Window Functions
Restricting Result Sets
Reviewing the UNION and TOP Operators Using the EXCEPT and INTERSECT Operators
Using UNION, EXCEPT, and INTERSECT Together
Advanced Subqueries Reviewing Subqueries Using the APPLY Operator
Temporary Storage Table Variables Temporary Tables
Crosstab Queries
Using the PIVOT Operator Using the UNPIVOT Operator
Common Table Expressions
Introduction to Common Table Expressions Using Common Table Expressions
Recursive Queries Using Common Table Expressions
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
Advanced Data Modification Techniques
Using the OUTPUT Clause, The MERGE Statement
Introduction to Transactions
What is a Transaction?
How SQL Server Modifies Data in Tables
Appendix. Introduction to Views (If Time Allows)
Introducing Views
Creating and Modifying a View Querying Data Through a View
Microsoft SQL Server - Advanced Querying 2 Days
Course Overview
This 2-day course is intended for students who have taken the “Microsoft SQL Server 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.
Microsoft SQL Server - Advanced Querying
Applies to all versions from SQL Server 2012 through to SQL 2022
DURATION
2 days
INTRODUCTION
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.
PREREQUISITES
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 and Relational Database Design and Querying Fundamentals” * course.
Additionally, delegates should have attended the 2 day “Microsoft SQL Server Introduction to Querying” * course, or possess equivalent knowledge gained in the workplace or elsewhere.
COURSE OBJECTIVES
Upon successful completion of this 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)
• Work with Native SQL Server Date and Time Data
• Implement Advanced Data Modification Techniques
• Understand the Role of Transactions in SQL Server
• Understand the Role of Views in SQL Server Databases
COURSE OUTLINE
Using Functions in Queries
Introducing Native SQL Server Functions Getting Help on Functions
Functions Relating to NULL Values Using the CASE Expression
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
Advanced Table Join Techniques
Reviewing Joins
Joining a Table to Itself (Self Join) Using Non-Equi Joins
Aggregating Data at Higher Levels
Reviewing SQL Server Aggregate Functions Using the ROLLUP and CUBE Operators Using GROUPING SETS
Ranking Grouped Data Window Functions that Rank Data Using ROW_NUMBER
Using RANK
Using DENSE_RANK Using NTILE
Analysing Data with Window Functions
Aggregate Window Functions Analytic Window Functions
Restricting Result Sets
Reviewing the UNION and TOP Operators Using the EXCEPT and INTERSECT Operators
Using UNION, EXCEPT, and INTERSECT Together
Advanced Subqueries Reviewing Subqueries Using the APPLY Operator
Temporary Storage Table Variables Temporary Tables
Crosstab Queries
Using the PIVOT Operator Using the UNPIVOT Operator
Common Table Expressions
Introduction to Common Table Expressions Using Common Table Expressions
Recursive Queries Using Common Table Expressions
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
Advanced Data Modification Techniques
Using the OUTPUT Clause, The MERGE Statement
Introduction to Transactions
What is a Transaction?
How SQL Server Modifies Data in Tables
Appendix. Introduction to Views (If Time Allows)
Introducing Views
Creating and Modifying a View Querying Data Through a View
