Hands-On Advanced SQL Server: Strategies and Techniques

2h 41mAdvanced2025-07-09

Authors

Walter Shields

Tech Educator and Best-Selling Author

Course details

Advanced SQL skills are essential for database developers, engineers, and administrators who need to write complex queries, automate processes, and improve performance. In this course, tech educator and bestselling author Walter Shields dives deep into stored procedures, user-defined functions, and dynamic SQL. Learn how to make your database queries more powerful and flexible. Explore control flow techniques, transaction management, and advanced query structures like Common Table Expressions (CTEs) and pivot tables. By the end of the course, you'll be able to write highly efficient SQL scripts, optimize database transactions, and ensure data integrity with triggers and constraints. This course is designed for intermediate and advanced SQL users looking to take their skills to the next level.

Learning objectives
Learn about SQL Server and SQL Programming
Define stored procedures and functions
Highlight transactions and data integrity
Identify dynamic SQL and cursors
Define Common Table Expressions (CTEs)
Learn about temporary tables and table variables
Explore indexing, triggers, and performance optimization
Identify ways to use pivot tables and aggregation

Skills covered

SQL ServerCloud StorageDatabase AdministrationBusiness IntelligenceDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesCloud ComputingData ScienceBusiness Analysis and StrategyBusiness Software and ToolsMicrosoftSoftware DevelopmentOne-Off

Concepts

0. Introduction

01 - Advanced server programming
02 - What you should know
03 - Final project overview
04 - GitHub Codespaces explainer video
05 - Why SQL Server

1. Introduction to Advanced SQL

06 - Stored procedure basics
07 - Stored procedure parameters

2. Working with Variables and Control Flow

08 - Variables
09 - Output parameters and return values
10 - IF statements
11 - WHILE Loops

3. Advanced SQL Programming with Functions and Expressions

12 - User-defined functions
13 - Table-valued functions
14 - Common Table Expressions (CTEs)

4. Temporary Data Storage and Management

15 - Temporary tables
16 - Table variables

5. Advanced Query Execution and Optimization

17 - Cursors
18 - Dynamic SQL

6. Transactions and Data Integrity

19 - Transactions
20 - DML triggers
21 - DDL triggers

7. The Pivot Operator

22 - The Pivot operator
23 - Dynamic Pivot tables

8. Final Project - SQL Movie Intelligence Dashboard

24 - Final project instructions
25 - Final project solutions

Conclusion

26 - Next steps