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

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

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

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

Module 5: Subqueries, Derived Tables and Common Table Expressions

• Non-correlated and correlated subqueries
• Derived tables
• Common table expressions

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

​Module 7: Getting Your Dataset to the Client