55232 Writing Analytical Queries for Business Intelligence

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