SQL Advanced Querying DURATION: 2 Days
Course Overview
SQL Advanced Querying DURATION: 2 days INTRODUCTION This 2-day course is intended for students who have taken the “SQL 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 and gain a deeper understanding of your data. You will learn how to manipulate data in new and meaningful ways for the purposes of data analysis and reporting. Additionally, we will consider best practices for writing efficient queries and avoiding common pitfalls.
INTRODUCTION
This 2-day course is intended for students who have taken the “SQL 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 and gain a deeper understanding of your data. You will learn how to manipulate data in new and meaningful ways for the purposes of data analysis and reporting. Additionally, we will consider best practices for writing efficient queries and avoiding 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.
Course content may vary to keep the training as relevant and up to date as possible.
PREREQUISITES
Delegates should 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 “SQL Relational Database Design and Querying Fundamentals” course.
Additionally, delegates should have attended the 2 day “SQL Introduction to Querying” course or possess equivalent knowledge gained in the workplace or elsewhere.
The training is delivered on a Microsoft SQL Server platform.
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)
- Implement Advanced Data Modification Techniques
- Work with Native SQL Server Date and Time Data
COURSE OUTLINE
|
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 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 Using the OUTPUT Clause The MERGE Statement 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 |
SQL Advanced Querying DURATION: 2 Days
Course Overview
SQL Advanced Querying DURATION: 2 days INTRODUCTION This 2-day course is intended for students who have taken the “SQL 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 and gain a deeper understanding of your data. You will learn how to manipulate data in new and meaningful ways for the purposes of data analysis and reporting. Additionally, we will consider best practices for writing efficient queries and avoiding common pitfalls.
INTRODUCTION
This 2-day course is intended for students who have taken the “SQL 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 and gain a deeper understanding of your data. You will learn how to manipulate data in new and meaningful ways for the purposes of data analysis and reporting. Additionally, we will consider best practices for writing efficient queries and avoiding 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.
Course content may vary to keep the training as relevant and up to date as possible.
PREREQUISITES
Delegates should 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 “SQL Relational Database Design and Querying Fundamentals” course.
Additionally, delegates should have attended the 2 day “SQL Introduction to Querying” course or possess equivalent knowledge gained in the workplace or elsewhere.
The training is delivered on a Microsoft SQL Server platform.
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)
- Implement Advanced Data Modification Techniques
- Work with Native SQL Server Date and Time Data
COURSE OUTLINE
|
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 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 Using the OUTPUT Clause The MERGE Statement 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 |
