SQL Querying For Data Analysts DURATION 3 Days
Course Overview
SQL Querying for Data Analysts DURATION 3 days INTRODUCTION This 3-day course is intended for students who need a thorough grounding in the various querying techniques available to retrieve data from a database in a data analytics or report authoring environment, whether that be through an implementation of Microsoft Power BI or similar analytics/reporting tool. The training is delivered with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. Examples of how to use SQL to profile, prepare and analyse data is given centre stage.
INTRODUCTION
This 3-day course is intended for students who need a thorough grounding in the various querying techniques available to retrieve data from a database in a data analytics or report authoring environment, whether that be through an implementation of Microsoft Power BI or similar analytics/reporting tool.
The training is delivered with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. Examples of how to use SQL to profile, prepare and analyse data is given centre stage.
No previous knowledge of the SQL language is assumed as the course is designed from the ground up, starting with basic queries and advancing to more complex querying methods, but do note advice given in the Prerequisites section.
On completion of the course students will be able to retrieve data using the SQL language and write effective queries for data analysis.
Course content may vary to keep the training as relevant and up to date as possible.
PREREQUISITES
Before attending this course, attendees 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.
Some previous exposure to SQL may prove beneficial as this is quite an intense course covering introductory to advanced topics in the space of just 3 days. It is very comprehensive in its coverage for such a relatively short course.
The training is delivered on a Microsoft SQL Server platform.
COURSE OBJECTIVES
Upon successful completion of this course, students will be able to:
- Understand SQL Syntax
- Retrieve Data with the SELECT Statement
- Filter Data with the WHERE Clause
- Sort Data with the ORDER BY Clause
- Deduplicate Data
- Work with the SQL Server Date and Time Data Types
- Query Data from Multiple Tables Using Joins
- Summarize Data with Aggregate Functions
- Analyse Data with Ranking Functions
- Understand Window Functions Like LAG and LEAD
- Combine and Limit Result Sets
- Work with Subqueries
- Work with Common Table Expressions
- Pivot and Unpivot Data
COURSE OUTLINE
|
1. Introduction to SQL Server Databases Objects in a SQL Server Database Components of a SQL Server Database Table 2. Introduction to SQL and Transact-SQL ANSI-SQL and Transact-SQL (T-SQL) Categories of SQL Statements T-SQL Scripts Adding Comments to SQL Code 3. Using the SELECT Statement to Retrieve Data Examine the Basic Syntax of the SELECT Statement Retrieving Columns of Data from a Table Using String Literals and Expressions Create Calculated Columns Using Aliases for Column Names 4. Filtering Rows of Data with the WHERE Clause How to Use the WHERE Clause Overview of Operators Filter Data Using Comparison Operators Filter Data Using String Comparisons Filter Data Using Logical Operators Filter Data Using a Range of Values Filter Data Using a List of Values Filter Data with NULLs Filter on Dates 5. Formatting and Sorting Result Sets Sorting Data Using the ORDER BY Clause Eliminating Duplicate Rows with DISTINCT Introducing Native SQL Server Functions Functions Relating to NULLs Using the CASE Expression 6. Working with SQL Server Data Types Native SQL Server Data Types Working with Variables Data Type Precedence Implicit Data Type Conversions Explicit Data Type Conversion Functions Querying and Modifying Date and Time Data Native SQL Server Date and Time Functions 13. Common Table Expressions Introduction to Common Table Expressions Using Common Table Expressions Recursive Queries Using Common Table Expressions |
7. Querying Data from Multiple Tables Using Joins Introduction to Joins Using Inner Joins Using Outer Joins Using Cross Joins Using Aliases for Table Names Joining More than Two Tables Joining a Table to Itself (Self Join) 8. Summarizing Data Using Aggregate Functions Using Aggregate Functions Native to SQL Server Using Aggregate Functions with NULLs Grouping Summarized Data with GROUP BY Filtering Grouped Data Using the HAVING Clause Using the ROLLUP and CUBE Operators Using GROUPING SETS 9. Ranking Data Ranking Data Using Ranking Functions Using ROW_NUMBER Using RANK Using DENSE_RANK Using NTILE 10. Analysing Data with Window Functions Aggregate Window Functions Analytic Window Functions Using the LAG and LEAD Functions 11. Combining and Limiting Result Sets Combining Result Sets Using the UNION Operator Limiting Result Sets Using the TOP Operator Using the EXCEPT and INTERSECT Operators 12. Working with Subqueries Introduction to Subqueries Using Subqueries as Expressions and Derived Tables Introducing Correlated Subqueries Using EXISTS with Correlated Subqueries Using the APPLY Operator Introducing Temporary Tables 14. Crosstab Queries Using the PIVOT Operator Using the UNPIVOT Operator |
SQL Querying For Data Analysts DURATION 3 Days
Course Overview
SQL Querying for Data Analysts DURATION 3 days INTRODUCTION This 3-day course is intended for students who need a thorough grounding in the various querying techniques available to retrieve data from a database in a data analytics or report authoring environment, whether that be through an implementation of Microsoft Power BI or similar analytics/reporting tool. The training is delivered with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. Examples of how to use SQL to profile, prepare and analyse data is given centre stage.
INTRODUCTION
This 3-day course is intended for students who need a thorough grounding in the various querying techniques available to retrieve data from a database in a data analytics or report authoring environment, whether that be through an implementation of Microsoft Power BI or similar analytics/reporting tool.
The training is delivered with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. Examples of how to use SQL to profile, prepare and analyse data is given centre stage.
No previous knowledge of the SQL language is assumed as the course is designed from the ground up, starting with basic queries and advancing to more complex querying methods, but do note advice given in the Prerequisites section.
On completion of the course students will be able to retrieve data using the SQL language and write effective queries for data analysis.
Course content may vary to keep the training as relevant and up to date as possible.
PREREQUISITES
Before attending this course, attendees 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.
Some previous exposure to SQL may prove beneficial as this is quite an intense course covering introductory to advanced topics in the space of just 3 days. It is very comprehensive in its coverage for such a relatively short course.
The training is delivered on a Microsoft SQL Server platform.
COURSE OBJECTIVES
Upon successful completion of this course, students will be able to:
- Understand SQL Syntax
- Retrieve Data with the SELECT Statement
- Filter Data with the WHERE Clause
- Sort Data with the ORDER BY Clause
- Deduplicate Data
- Work with the SQL Server Date and Time Data Types
- Query Data from Multiple Tables Using Joins
- Summarize Data with Aggregate Functions
- Analyse Data with Ranking Functions
- Understand Window Functions Like LAG and LEAD
- Combine and Limit Result Sets
- Work with Subqueries
- Work with Common Table Expressions
- Pivot and Unpivot Data
COURSE OUTLINE
|
1. Introduction to SQL Server Databases Objects in a SQL Server Database Components of a SQL Server Database Table 2. Introduction to SQL and Transact-SQL ANSI-SQL and Transact-SQL (T-SQL) Categories of SQL Statements T-SQL Scripts Adding Comments to SQL Code 3. Using the SELECT Statement to Retrieve Data Examine the Basic Syntax of the SELECT Statement Retrieving Columns of Data from a Table Using String Literals and Expressions Create Calculated Columns Using Aliases for Column Names 4. Filtering Rows of Data with the WHERE Clause How to Use the WHERE Clause Overview of Operators Filter Data Using Comparison Operators Filter Data Using String Comparisons Filter Data Using Logical Operators Filter Data Using a Range of Values Filter Data Using a List of Values Filter Data with NULLs Filter on Dates 5. Formatting and Sorting Result Sets Sorting Data Using the ORDER BY Clause Eliminating Duplicate Rows with DISTINCT Introducing Native SQL Server Functions Functions Relating to NULLs Using the CASE Expression 6. Working with SQL Server Data Types Native SQL Server Data Types Working with Variables Data Type Precedence Implicit Data Type Conversions Explicit Data Type Conversion Functions Querying and Modifying Date and Time Data Native SQL Server Date and Time Functions 13. Common Table Expressions Introduction to Common Table Expressions Using Common Table Expressions Recursive Queries Using Common Table Expressions |
7. Querying Data from Multiple Tables Using Joins Introduction to Joins Using Inner Joins Using Outer Joins Using Cross Joins Using Aliases for Table Names Joining More than Two Tables Joining a Table to Itself (Self Join) 8. Summarizing Data Using Aggregate Functions Using Aggregate Functions Native to SQL Server Using Aggregate Functions with NULLs Grouping Summarized Data with GROUP BY Filtering Grouped Data Using the HAVING Clause Using the ROLLUP and CUBE Operators Using GROUPING SETS 9. Ranking Data Ranking Data Using Ranking Functions Using ROW_NUMBER Using RANK Using DENSE_RANK Using NTILE 10. Analysing Data with Window Functions Aggregate Window Functions Analytic Window Functions Using the LAG and LEAD Functions 11. Combining and Limiting Result Sets Combining Result Sets Using the UNION Operator Limiting Result Sets Using the TOP Operator Using the EXCEPT and INTERSECT Operators 12. Working with Subqueries Introduction to Subqueries Using Subqueries as Expressions and Derived Tables Introducing Correlated Subqueries Using EXISTS with Correlated Subqueries Using the APPLY Operator Introducing Temporary Tables 14. Crosstab Queries Using the PIVOT Operator Using the UNPIVOT Operator |
