55232 Writing Analytical Queries for Business Intelligence
- 3 Days Course
- Language: English
Introduction:
How do you transform complex tables into clean, analysis-ready datasets?
This training course teaches you how to write analytical queries using Transact-SQL (T-SQL) for business intelligence (BI) workflows. You’ll learn to manipulate columns, combine tables, aggregate results, and encapsulate logic in reusable views and procedures. With a focus on BI scenarios, the course prepares you to deliver clean, structured datasets to tools like Power BI, Excel, SSRS, and RStudio.
Objectives:
After completing this course, you will be able to build efficient SQL queries tailored for business intelligence and data analysis needs. The training emphasizes reusable logic, data encapsulation, and structured output delivery.
Understand the five types of requirements for BI querying
Combine and reshape data across multiple tables
Define the correct aggregation level with GROUP BY and HAVING
Use CTEs, subqueries, and derived tables for cleaner logic
Create views and functions to streamline data access
Deliver datasets to BI tools like Power BI and Excel
Course Outline:
Module 1: Introduction to T-SQL for Business Intelligence
- SQL programming approaches: transactional vs analytical
- The database engine and SSMS
- Introduction to SELECT queries
- Using the CarDeal sample database
- Lab: Intro to T-SQL for BI
Module 2: SELECT Expressions, WHERE, and ORDER BY
- Column expressions, data types, and built-in functions
- Column/table aliases and conversions
- Using WHERE and ORDER BY clauses
- Lab: Write filtering and sorting queries
Module 3: JOIN Operators and Combining Tables
- Primary/foreign key relationships
- CROSS JOIN, INNER JOIN, OUTER JOIN variations
- Multi-table joins and nested joins
- Combining JOINs with WHERE and ORDER BY
- Lab: Write multi-table SELECT queries
Module 4: Aggregating with GROUP BY
- Defining aggregation level and granularity
- COUNT, SUM, AVG, and other aggregate functions
- GROUP BY and HAVING usage
- Lab: Write grouped and aggregated queries
Module 5: Subqueries, Derived Tables, and CTEs
- Writing correlated and non-correlated subqueries
- Using derived tables in FROM clauses
- Structuring logic with common table expressions (CTEs)
- Lab: Create reusable query logic
Module 6: Encapsulating Data Retrieval Logic
- Creating and using views
- Table-valued functions and stored procedures
- Creating read-access objects for analytical clients
- Lab: Build encapsulated SQL logic
Module 7: Getting Your Dataset to the Client
- Connecting SQL Server to Excel, Power BI, and RStudio
- Exporting with SSMS, bcp utility, and Import/Export Wizard
- Best practices for client-ready data delivery
- Lab: Export datasets and connect to BI tools
Enroll in this course
$1,785.00 – $1,895.00
