SQL Server for Data Analysis Course

Lesson 1 • SQL Server Architecture Overview

Covers the database engine, instances, and storage structures analysts interact with. Establishes the mental model needed for all subsequent query work.

Lesson 2 • Understanding Sample Databases

Uses AdventureWorks and similar practice databases to ground exercises in realistic business data. Builds familiarity with common analytical schemas.

Lesson 3 • Filtering and Sorting Data

Applies WHERE conditions and ORDER BY clauses to control which rows are returned and in what sequence. Directly enables focused data exploration.

Lesson 4 • Writing Your First SELECT Queries

Introduces SELECT syntax, column selection, and result-set reading. Forms the baseline query skill that every later technique extends.

Lesson 1 • Grouping Sets and Rollup Summaries

Introduces ROLLUP, CUBE, and GROUPING SETS for multi-level subtotals. Supports hierarchical summary reports without multiple separate queries.

Lesson 2 • Filtering Aggregated Results with HAVING

Distinguishes HAVING from WHERE and applies it to filter grouped output. Enables threshold-based reporting such as top-performing segments.

Lesson 3 • Grouping Results with GROUP BY

Applies GROUP BY to segment aggregates by category, date, or dimension. Connects summarization to dimensional analysis used in dashboards.

Lesson 1 • Relational Keys and Join Concepts

Explains primary keys, foreign keys, and cardinality before any JOIN syntax. Ensures analysts understand why joins work, not just how to write them.

Lesson 2 • Set Operations for Data Comparison

Uses UNION, INTERSECT, and EXCEPT to compare and combine result sets. Complements join techniques for data reconciliation and gap analysis.

Lesson 3 • INNER and OUTER JOIN Techniques

Covers INNER, LEFT, RIGHT, and FULL OUTER JOINs with business use cases for each. Builds the join vocabulary used in virtually every analytical query.

Lesson 4 • Self-Joins and Cross Joins

Applies self-joins for hierarchical data and cross joins for combinatorial analysis. Extends join skills to less common but analytically valuable patterns.

Lesson 1 • EXISTS and NOT EXISTS Patterns

Uses EXISTS for semi-join logic and NOT EXISTS for anti-join filtering. Provides efficient alternatives to IN-based subqueries for large datasets.

Lesson 2 • Scalar and Inline Subqueries

Introduces subqueries in SELECT, WHERE, and FROM clauses for single-value and table-level results. Establishes the nesting concept that CTEs later simplify.

Lesson 3 • Applying CTEs in Analytical Workflows

Combines CTEs with aggregations, joins, and window functions in realistic multi-step queries. Demonstrates how modular query design accelerates analytical problem-solving.

Lesson 4 • Recursive CTEs for Hierarchical Data

Builds recursive CTEs to traverse parent-child structures like org charts and bill-of-materials. Unlocks hierarchical analysis not possible with standard joins.

Lesson 1 • Window Function Fundamentals

Explains the OVER clause, partitioning, and ordering as the foundation of all window functions. Distinguishes window functions from GROUP BY to prevent common misuse.

Lesson 2 • Ranking and Numbering Functions

Applies ROW_NUMBER, RANK, DENSE_RANK, and NTILE to rank and segment data. Enables top-N analysis, tie handling, and percentile bucketing.

Lesson 3 • Running Totals and Moving Averages

Uses SUM, AVG, and COUNT with frame clauses to compute cumulative and rolling metrics. Supports trend analysis and performance tracking over time.

Lesson 4 • Combining Window Functions in Reports

Integrates multiple window functions in a single query to produce executive-level summary reports. Demonstrates real-world analytical patterns used in BI dashboards.

Lesson 1 • Conditional Logic with CASE

Builds searched and simple CASE expressions to create derived categories and conditional metrics. Replaces multiple queries with single-pass conditional transformations.

Lesson 2 • Date and Time Manipulation

Covers DATEPART, DATEDIFF, DATEADD, and FORMAT for extracting and computing date values. Enables time-based segmentation and period calculations central to reporting.

Lesson 3 • String Functions for Data Cleaning

Applies TRIM, REPLACE, SUBSTRING, CHARINDEX, and CONCAT to standardize text fields. Addresses the data quality issues analysts encounter in source system data.

Lesson 4 • NULL Handling Functions

Uses ISNULL, COALESCE, and NULLIF to manage missing values in analytical calculations. Prevents silent errors caused by NULL propagation in formulas.

Lesson 1 • Temporary Storage for Performance

Compares temp tables, table variables, and CTEs for staging intermediate results efficiently. Helps analysts choose the right temporary storage pattern for each scenario.

Lesson 2 • Optimizing Joins and Aggregations

Applies join order guidance, statistics awareness, and aggregate pushdown to reduce query cost. Targets the most common performance issues in analytical workloads.

Lesson 3 • Reading Execution Plans

Interprets graphical and text execution plans to identify costly operators. Gives analysts the diagnostic skill to pinpoint performance bottlenecks independently.

Lesson 4 • Index Fundamentals for Analysts

Explains clustered and non-clustered indexes and how they affect query speed. Enables analysts to request or create appropriate indexes for their workloads.

Lesson 1 • Dynamic SQL in Analytical Procedures

Builds dynamic SQL strings for flexible, parameter-driven analytical queries. Addresses scenarios where column names or filters cannot be hardcoded.

Lesson 2 • Creating and Managing Views

Defines views as saved SELECT statements that simplify complex queries for end users. Establishes the abstraction layer between raw tables and reporting tools.

Lesson 3 • Introduction to Stored Procedures

Writes parameterized stored procedures to encapsulate and reuse analytical query logic. Enables consistent, secure data access for reporting applications.

Lesson 4 • Indexed Views for Precomputed Results

Creates indexed views to materialize expensive aggregations and speed up repeated queries. Targets high-frequency analytical queries that benefit from precomputation.