Writing Analytical Queries for Business Intelligence (55232)

Course Overview

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

Prerequisites

Setting learning of information examination and business knowledge situations. For instance, a comprehension of a business-related business knowledge venture or need. Fundamental information of the Windows working framework and its center usefulness, including document framework route. Essential comprehension of the reason for social database administration frameworks, for example, SQL Server.