This three-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 methodogy, and integrates these concepts into each TSQL topic presented. The goal is to provide a consistent, direct, and purposeful learning path from RDBMS data retrieval through analytical tools such as SQL Server Reporting Services, PowerBI, Excel, R, SAS, and SPSS.

starstarstarstarstar_outline

* Actual course outline may vary depending on offering center. Contact your sales representative for more information.

Learning Objectives

Identify independent and dependent variables and measurement levels in their own analytical work scenarios.
Identify variables of interest in relational database tables.
Choose a data aggregation level and data set design appropriate for the intended analysis and tool.
Use TSQL SELECT queries to produce ready-to-use data sets for analysis in tools such as PowerBI, SQL Server Reporting Services, Excel, R, SAS, SPSS, and others.
Create stored procedures, views, and functions to modularize data retrieval code.

1

  • MODULE 1: INTRODUCTION TO TSQL FOR BUSINESS INTELLIGENCE

  • Two Approaches to SQL Programming

    TSQL Data Retrieval in an Analytics / Business Intelligence Environment

    The Database Engine

    SQL Server Management Studio and the CarDeal Sample Database

    Identifying Variables in Tables

    SQL is a Declarative Language

    Introduction to the SELECT Query

2

  • MODULE 2: TURNING TABLE COLUMNS INTO VARIABLES FOR ANALYSIS: SELECT LIST EXPRESSIONS, WHERE, AND ORDER BY

  • 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

3

  • MODULE 3: COMBINING COLUMNS FROM MULTIPLE TABLES INTO A SINGLE DATASET: THE JOIN OPERATORS

  • Primary Keys, Foreign Keys, and Joins

    Understanding Joins, Part 1: CROSS JOIN and the Full Cartesian 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

4

  • MODULE 4: CREATING AN APPROPRIATE AGGREGATION LEVEL USING GROUP BY

  • Identifying required aggregation level and granularity

    Aggregate Functions

    GROUP BY

    HAVING

    Order of operations in SELECT queries

5

  • MODULE 5: SUBQUERIES, DERIVED TABLES AND COMMON TABLE EXPRESSIONS

  • Non-correlated and correlated subqueries

    Derived tables

    Common table expressions

6

  • MODULE 6: ENCAPSULATING DATA RETRIEVAL LOGIC

  • Views

    Table-valued functions

    Stored procedures

    Creating objects for read-access users

    Creating database accounts for analytical client tools

7

  • MODULE 7: GETTING YOUR DATASET TO THE CLIENT

  • 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

Audience

This course is intended 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. Context knowledge of data analysis and business intelligence scenarios. For example, an understanding of a work-related business intelligence project or need.

Language

English

Prerequisites

Basic knowledge of the 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.

$2,295

Length: 3.0 days (24 hours)

Level:

Not Your Location? Change

Course Schedule:

Schedule select
06
Sep
Wednesday
9:00 AM ET -
5:00 PM ET
Available
Schedule select
13
Dec
Wednesday
9:00 AM ET -
5:00 PM ET
Available
Loading...