Duration:

3 Days

Audience:

Employees of federal, state and local governments; and businesses working with the government.

Course Description:

This course is about writing TSQL queries for the purpose of database reporting, analysis, and business intelligence.

Learning Objectives:

After completing this course, students will be able to:

  • 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.

Course Outline

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
  • Lab 1: Introduction to TSQL for Business Intelligence
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
  • Lab 1: Write queries
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
  • Lab 1: Write SELECT queries
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
  • Lab 1: Write queries
5 – SUBQUERIES, DERIVED TABLES AND COMMON TABLE EXPRESSIONS
  • Non-correlated and correlated subqueries
  • Derived tables
  • Common table expressions
  • Lab 1: Write queries
6 – ENCAPSULATING DATA RETRIEVAL LOGIC
  • Views
  • Table-valued functions
  • Stored procedures
  • Creating objects for read-access users
  • Creating database accounts for analytical client tools
  • Lab 1: Encapsulating Data Retrieval Logic
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
  • Lab 1: Getting Your Dataset to the Client