MS SQL Course

Lesson 1 • SQL Server Architecture Overview

Explains SQL Server components: engine, services, and storage. Prepares students to understand how queries are processed and data is persisted.

Lesson 2 • Relational Database Fundamentals

Covers tables, rows, columns, keys, and relationships as the basis of relational design. Establishes vocabulary used throughout the entire course.

Lesson 3 • Installing and Configuring SQL Server

Guides students through installation, instance configuration, and initial security setup. Ensures a working local environment for all subsequent labs.

Lesson 4 • Navigating SQL Server Management Studio

Introduces SSMS interface, Object Explorer, and query editor. Students gain confidence executing basic commands and managing connections.

Lesson 1 • Aggregating Data with GROUP BY

Teaches COUNT, SUM, AVG, MIN, MAX, and HAVING to summarise data by category. Bridges row-level querying to analytical reporting covered in later chapters.

Lesson 2 • Writing Basic SELECT Statements

Teaches SELECT, FROM, and column aliasing to retrieve and label data. Forms the syntactic foundation for every query written in later chapters.

Lesson 3 • Filtering Data with WHERE

Covers comparison operators, logical operators, and NULL handling to filter rows precisely. Directly enables targeted data retrieval in real-world scenarios.

Lesson 4 • Built-in Scalar Functions

Introduces string, numeric, date, and conversion functions for transforming column values. Expands query expressiveness needed in aggregation and reporting chapters.

Lesson 1 • INNER and OUTER Joins

Covers INNER JOIN, LEFT, RIGHT, and FULL OUTER JOIN with practical examples. Students choose the correct join type based on data inclusion requirements.

Lesson 2 • Set Operators: UNION, INTERSECT, EXCEPT

Explains combining result sets vertically using set operators with matching column rules. Completes the toolkit for assembling complex multi-source reports.

Lesson 3 • Self Joins and Cross Joins

Demonstrates joining a table to itself and generating Cartesian products. Addresses hierarchical data and combinatorial reporting scenarios.

Lesson 4 • Understanding Table Relationships

Reviews one-to-one, one-to-many, and many-to-many relationships as the basis for join logic. Ensures students understand why joins work before writing them.

Lesson 1 • Constraints and Data Integrity

Defines PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT constraints. Enforces business rules at the database level, reducing application-layer errors.

Lesson 2 • Normalisation Principles

Explains 1NF through 3NF with practical before-and-after schema examples. Guides students to eliminate redundancy and anomalies in table design.

Lesson 3 • Indexes: Clustered and Non-Clustered

Introduces index types, creation syntax, and their effect on read and write performance. Lays groundwork for the advanced indexing strategies covered later.

Lesson 4 • Data Types in SQL Server

Covers numeric, character, date, binary, and special data types with storage implications. Correct type selection directly affects performance and data accuracy.

Lesson 1 • Inserting Data into Tables

Covers single-row, multi-row, and INSERT...SELECT patterns for loading data. Establishes safe insertion habits used in ETL and application development.

Lesson 2 • Deleting and Truncating Data

Compares DELETE, TRUNCATE, and DROP for removing data at different scopes. Students apply the correct removal method based on recoverability needs.

Lesson 3 • MERGE Statement for Upsert Logic

Demonstrates MERGE for synchronising source and target tables in a single statement. Addresses common ETL upsert patterns efficiently.

Lesson 4 • Transactions and Error Handling

Introduces BEGIN TRANSACTION, COMMIT, ROLLBACK, and TRY...CATCH for safe DML. Ensures data consistency when multiple statements must succeed or fail together.

Lesson 1 • Advanced Filtering and Expressions

Covers CASE expressions, IIF, CHOOSE, and complex predicate logic for conditional output. Extends query expressiveness for business rule implementation inside T-SQL.

Lesson 2 • Dynamic SQL with EXEC and sp_executesql

Explains building and executing SQL strings at runtime for flexible, parameterised queries. Teaches injection prevention techniques essential for secure dynamic code.

Lesson 3 • PIVOT and UNPIVOT Operations

Demonstrates rotating rows to columns and columns to rows for cross-tab reporting. Addresses common business reporting formats not achievable with standard GROUP BY.

Lesson 4 • Common Table Expressions

Teaches WITH clause CTEs for readable query decomposition and recursive data traversal. Replaces nested subqueries with maintainable, named result sets.

Lesson 1 • Creating and Managing Stored Procedures

Teaches CREATE PROCEDURE, parameters, output parameters, and return values. Enables encapsulation of business logic callable from applications and jobs.

Lesson 2 • DDL Triggers and Event Notifications

Covers server- and database-scoped DDL triggers for auditing schema changes. Complements DML triggers to provide full change-tracking coverage.

Lesson 3 • User-Defined Functions

Covers scalar, inline table-valued, and multi-statement table-valued functions. Students choose the right function type based on performance and reuse requirements.

Lesson 4 • DML Triggers: AFTER and INSTEAD OF

Explains trigger creation, the inserted and deleted virtual tables, and firing order. Students automate auditing and enforce complex constraints beyond CHECK.

Lesson 1 • Query Rewriting for Performance

Demonstrates rewriting anti-patterns: implicit conversions, functions on indexed columns, and OR predicates. Translates plan analysis into actionable query changes.

Lesson 2 • Monitoring with DMVs and Extended Events

Introduces dynamic management views and Extended Events for ongoing performance monitoring. Equips students to proactively identify regressions in live systems.

Lesson 3 • Reading Execution Plans

Teaches estimated vs. actual execution plans, operators, and cost percentages in SSMS. Provides the diagnostic foundation for every optimisation technique that follows.

Lesson 4 • Statistics and the Query Optimizer

Explains how statistics guide cardinality estimation and plan selection. Students update and create statistics to correct bad plan choices.