Overview
The 3-day instructor-led course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence. Specifically, this course presents TSQL within the context of data analysis – in other words, making meaning from the data rather than transaction-oriented data-tier application development.
The course starts with a brief discussion of levels of measurement and quantitative research methodology, and integrates these concepts into each TSQL topic presented. The goal is to provide a consistent, direct, and purposeful learning path for RDBMS data retrieval for use in analytical tools such as SQL Server Reporting Services, Power BI, R and Excel.
Prerequisites
Those who wish to take up this course should meet the following prerequisites:
- Context knowledge of data analysis and business intelligence scenarios. For example, an understanding of a work-related business intelligence project or need.
- Basic knowledge of Windows operating system and its core functionality, including file system navigation.
- Basic understanding of the purpose of relational database management systems such as SQL Server.
Who Should Attend?
The course is highly recommended for information workers and data science professionals who seek to use database reporting and analysis tools such as Microsoft SQL Server Reporting Services, Excel, Power BI, R, SAS and other business intelligence tools, and wish to use TSQL queries to efficiently retrieve data sets from Microsoft SQL Server relational databases for use with these tools.
Course Outline
- Two Approaches to SQL Programming
- TSQL Data Retrieval in an Analytics Environment
- The Database Engine
- SQL Server Management Studio and the CarDeal Sample Database
- Identifying Variables in Tables
- SQL is a Declarative Language
- Introduction to SELECT Query
Lab: Lab 1
- Turning Columns into Variables for Analysis
- Column Expressions, Data Types, and Built-in Functions
- Column aliases
- Data type conversions
- Built-in Scalar Functions
- Table Aliases
- The WHERE clause
- ORDER BY
- Lab: Lab 2 – Write queries using:
- Column and table aliases
- DISTINCT
- WHERE
- ORDER BY
- Built-in functions
- Explicit and implicit data type conversion
- Primary Keys, Foreign Keys, and Joins
- Understanding Joins, Part 1: CROSS JOIN and the Full Cartersian Product
- Understanding Joins, Part 2: The INNER JOIN
- Understanding Joins, Part 3: The OUTER JOINS
- Understanding Joins, Part 4: Joining more than two tables
- Understanding Joins, Part 5: Combining INNER and OUTER JOINs
- Combining JOIN Operations with WHERE and ORDER BY
- Lab: Lab 3 – Write SELECT queries using:
- Inner Join
- Left, right, and full join
- Joins of more than two tables
- Join operators, in addition to WHERE and ORDER BY
- Identifying required aggregation level and granularity
- Aggregate functions
- GROUP BY
- HAVING
- Order of operations in SELECT queries
- Lab: Lab 4 – Write queries using:
- Aggregate functions
- Aggregate function with HAVING
- Aggregate function with GROUP BY and HAVING
- Aggregate function with GROUP BY, HAVING, WHERE, and ORDER BY
- Non-correlated and correlated subqueries
- Derived tables
- Common table expressions
- Lab: Lab 5 – Write queries using:
- Non-correlated subqueries
- Correlated subqueries
- Derived tables
- Common table expressions
- Subqueries, derived tables, common table expressions and other topics
- Views
- Table-valued functions
- Stored procedures
- Creating objects for read-access users
- Creating database accounts for analytical client tools
Lab: Lab 6
- Connecting to SQL Server and Submitting Queries from Client Tools
- Connecting and running SELECT queries from:
- Excel
- PowerBI
- RStudio
- Exporting datasets to files using:
- Results pane from SSMS
- The bcp utility
- The Import/Export Wizard
Lab: Lab 7
