Data Private Public

Writing Analytical Queries for Business Intelligence with T-SQL (55232)

3 days

Learn to write analytical T-SQL queries, combine and aggregate SQL Server data, reuse query logic, and deliver clean datasets to Power BI, Excel, and SSRS.

Talk to a Learning Consultant Register for a Public Class Request a Private Class

Register or Request Training

Course Overview

Learn to write analytical Transact-SQL (T-SQL) queries that transform SQL Server data into clean, structured datasets for business intelligence and analysis. You will manipulate columns, filter and sort results, combine tables, aggregate data, and organize complex query logic with subqueries, derived tables, and common table expressions.

You will also learn to encapsulate data retrieval logic in views, table-valued functions, and stored procedures, then deliver datasets to analytical tools such as Power BI, Excel, SQL Server Reporting Services, and RStudio. This course is intended for information workers and data professionals who retrieve relational data for reporting and analysis.

Course Benefits

Recognize the requirements and querying approaches used in business intelligence scenarios.
Write T-SQL queries that manipulate, filter, sort, and reshape SQL Server data.
Combine data from multiple tables using appropriate join operations.
Define the required level of detail with aggregate functions, GROUP BY, and HAVING.
Structure query logic with subqueries, derived tables, and common table expressions.
Create views, table-valued functions, and stored procedures that support reusable data access.
Prepare and deliver structured datasets to tools such as Power BI, Excel, and RStudio.

Delivery Methods

Public Class
Live expert-led online training from anywhere. Guaranteed to run .

Private Class
Delivered for your team at your site or online.

Course Outline

Introduction to T-SQL for Business Intelligence
1. Transactional and analytical SQL programming approaches
2. The database engine and SQL Server Management Studio
3. Introduction to SELECT queries
4. Using the CarDeal sample database
5. Lab: Introduction to T-SQL for business intelligence
SELECT Expressions, WHERE, and ORDER BY
1. Column expressions, data types, and built-in functions
2. Column and table aliases
3. Data type conversions
4. Using WHERE and ORDER BY
5. Lab: Write filtering and sorting queries
JOIN Operators and Combining Tables
1. Primary-key and foreign-key relationships
2. CROSS JOIN, INNER JOIN, and OUTER JOIN variations
3. Multi-table and nested joins
4. Combining joins with WHERE and ORDER BY
5. Lab: Write multi-table SELECT queries
Aggregating with GROUP BY
1. Defining aggregation level and granularity
2. COUNT, SUM, AVG, and other aggregate functions
3. Using GROUP BY and HAVING
4. Lab: Write grouped and aggregated queries
Subqueries, Derived Tables, and Common Table Expressions
1. Correlated and non-correlated subqueries
2. Derived tables in FROM clauses
3. Structuring logic with common table expressions
4. Lab: Create reusable query logic
Encapsulating Data Retrieval Logic
1. Creating and using views
2. Table-valued functions and stored procedures
3. Creating read-access objects for analytical clients
4. Lab: Build encapsulated SQL logic
Getting Your Dataset to the Client
1. Connecting SQL Server to Excel, Power BI, and RStudio
2. Exporting with SQL Server Management Studio, the bcp utility, and the Import/Export Wizard
3. Best practices for client-ready data delivery
4. Lab: Export datasets and connect to business intelligence tools

Class Materials

Each student receives a comprehensive set of materials, including course notes and all class examples.

Class Prerequisites

Experience in the following is required for this Microsoft SQL Server class:

Basic knowledge of the Windows operating system, including file-system navigation.
A fundamental understanding of the purpose of relational database management systems such as SQL Server.

Experience in the following would be useful for this Microsoft SQL Server class:

Familiarity with data analysis and business intelligence scenarios, such as a workplace reporting or BI project.

Prerequisite Courses

Courses that can help you meet these prerequisites:

Introduction to SQL Databases

Have questions about this course?

We can help with curriculum details, delivery options, pricing, or anything else. Reach out and we’ll point you in the right direction.

Talk to a Learning Consultant