PatMacTech UK Ltd

Expert
Data Database

SSMS & SQL SERVER MASTERY — BEGINNER TO EXPERT

PatMacTechUKLtd PatMacTechUKLtd
Overview
Curriculum
  • 9 Sections
  • 81 Lessons
  • 90h Duration
Collapse All
MODULE 0 — ORIENTATION & SETUP
8 Lessons
  1. Chapter 0.1 — Introduction to SQL Server Ecosystem
  2. What Is SQL Server?
  3. Overview of the SQL Server Ecosystem
  4. OLTP vs OLAP Workloads
  5. SQL Server Editions and Deployment Environments
  6. How SQL Server Compares with Other Databases
  7. Real-World Use Cases of SQL Server
  8. Preparing Your Environment (Installation & First Query)
MODULE 1 — Foundations of Databases & SSMS
12 Lessons
  1. Understanding Databases
  2. SSMS Deep Navigation
  3. Creating Databases, Schemas, and Tables
  4. Understanding Data Types and Nullability
  5. Keys, Constraints, and Relationships
  6. Inserting, Updating, and Deleting Data (DML Basics)
  7. Selecting and Filtering Data (SELECT Basics)
  8. Sorting, Aliasing, and Working With Expressions
  9. Introduction to JOINs (Inner, Left, Right, Full)
  10. Aggregations: GROUP BY, HAVING, COUNT, SUM, AVG, MIN, MAX
  11. Advanced Filtering, Search, and Wildcards (BETWEEN, IN, LIKE, ISNULL, COLLATION)
  12. ORDER BY, OFFSET/FETCH, and Result Pagination
MODULE 2 — INTERMEDIATE SQL & COMPLEX QUERYING
8 Lessons
  1. Subqueries (Scalar, Inline, Correlated)
  2. Common Table Expressions (CTEs): With and Without Recursive Logic
  3. Window Functions (OVER, PARTITION BY, ORDER BY, ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG)
  4. Advanced Joins (Self Joins, Cross Joins, Multi-Joins, Pivoting Basics)
  5. Advanced Filtering Logic (CASE in WHERE, Conditional Joins, Optional Parameters, Dynamic Filtering)
  6. Subquery Alternatives: APPLY (CROSS APPLY & OUTER APPLY)
  7. GROUPING SETS, ROLLUP, CUBE (Advanced Aggregation Controls)
  8. Temporary Tables, Table Variables, and Their Use Cases
Start Module 3 (Advanced SQL Programming)
9 Lessons
  1. Stored Procedures (Parameters, Logic, Output, Best Practices)
  2. User-Defined Functions (Scalar, Inline Table-Valued, Multi-Statement TVFs)
  3. Views (Standard Views, Indexed Views, Security Views, Parameterised Logic)
  4. Dynamic SQL (sp_executesql, SQL Injection Safety, Advanced Patterns)
  5. Transactions, Error Handling, TRY/CATCH, Deadlocks & Concurrency
  6. Cursors, WHILE Loops, and Procedural SQL Logic
  7. Triggers (AFTER, INSTEAD OF, Auditing, Data Enforcement)
  8. Security, Permissions, Roles, and Encryption
  9. SQL Server Performance Tuning & Query Optimisation
MODULE 4 — SQL SERVER ENGINE & INTERNAL ARCHITECTURE
10 Lessons
  1. Chapter 4.1 — SQL Server Engine Architecture Overview
  2. Chapter 4.2 — Storage Engine: Data Files, Pages, Extents, Heaps & B-Trees
  3. Chapter 4.3 — Buffer Manager & Memory Architecture
  4. Chapter 4.4 — Transaction Log & Logging Mechanics (WAL)
  5. Chapter 4.5 — Checkpoints, Recovery Models & Crash Recovery
  6. Chapter 4.6 — Locking, Latching & Concurrency Architecture
  7. Chapter 4.7 — SQL Server Scheduler & CPU Architecture (SOS Scheduler)
  8. Chapter 4.8 — TempDB Internals, Allocation Maps & Contention
  9. Chapter 4.9 — Statistics, Cardinality Estimation & Query Optimizer Internals
  10. Chapter 4.10 — Database Physical Layout, Filegroups & Partitioning
MODULE 5 — SQL SERVER SECURITY, ADMINISTRATION & AUTOMATION
10 Lessons
  1. Chapter 5.1 — SQL Server Security Architecture
  2. Chapter 5.2 — Server Roles & Database Roles
  3. Chapter 5.3 — Authentication & Authorization
  4. Chapter 5.4 — Row-Level Security (RLS)
  5. CHAPTER 5.5 — SQL SERVER AGENT ALERTS & NOTIFICATIONS
  6. CHAPTER 5.6 — AUTOMATING MAINTENANCE PLANS (BACKUPS, CHECKS, INDEX OPTIMISATION)
  7. AUTOMATING SQL SERVER MONITORING (PERFORMANCE, HEALTH, USAGE)
  8. AUTOMATING SQL SERVER AUDITING (SECURITY COMPLIANCE & AUDIT LOGS)
  9. CHAPTER 5.9 — SQL SERVER AUTOMATION WITH POWERSHELL (DBA SCRIPTING & TASKS)
  10. CHAPTER 5.10 — FULL AUTOMATED SQL SERVER MAINTENANCE FRAMEWORK (PUTTING EVERYTHING TOGETHER)
MODULE 6 — SQL PERFORMANCE TUNING & QUERY OPTIMISATION
10 Lessons
  1. CHAPTER 6.1 — UNDERSTANDING HOW SQL SERVER PROCESSES QUERIES (THE FOUNDATION)
  2. CHAPTER 6.2 — EXECUTION PLANS: HOW TO READ, INTERPRET & TUNE THEM (DEEP DIVE)
  3. CHAPTER 6.3 — INDEX TUNING: CLUSTERED, NONCLUSTERED, FILTERED & COVERING INDEXES (DEEP MASTERY)
  4. CHAPTER 6.4 — QUERY TUNING TECHNIQUES (JOINS, PREDICATES, SARGABILITY, CTEs, AGGREGATIONS, SUBQUERIES)
  5. CHAPTER 6.5 — TEMPDB TUNING, MEMORY SPILLS, CONCURRENCY & LOCKING OPTIMISATION
  6. CHAPTER 6.6 — PARAMETER SNIFFING, PLAN CACHING & OPTIMIZER BEHAVIOUR (ADVANCED TROUBLESHOOTING)
  7. CHAPTER 6.7 — ADVANCED PERFORMANCE TUNING WITH DMVs: WAIT STATS, QUERY STORE, IO STATS & BOTTLENECK DIAGNOSIS
  8. CHAPTER 6.8 — INDEX & QUERY TUNING CASE STUDIES (REAL-WORLD SCENARIOS & SOLUTIONS)
  9. CHAPTER 6.9 — WRITING HIGH-PERFORMANCE STORED PROCEDURES (DESIGN, PATTERNS, TUNING RULES)
  10. CHAPTER 6.10 — FULL PERFORMANCE TUNING WORKFLOW (DIAGNOSE → ANALYSE → FIX → VALIDATE)
MODULE 7 — SQL SERVER HIGH AVAILABILITY & DISASTER RECOVERY (HADR)
10 Lessons
  1. CHAPTER 7.1 — BACKUP STRATEGY DESIGN & RECOVERY MODELS (THE FOUNDATION OF HADR)
  2. CHAPTER 7.2 — LOG SHIPPING (WARM STANDBY DISASTER RECOVERY SYSTEM)
  3. CHAPTER 7.3 — DATABASE MIRRORING (HIGH SAFETY & HIGH PERFORMANCE MODES)
  4. CHAPTER 7.4 — ALWAYS ON AVAILABILITY GROUPS (AGs) — ENTERPRISE-GRADE HA & DR
  5. CHAPTER 7.5 — SQL SERVER REPLICATION (SNAPSHOT, TRANSACTIONAL, MERGE)
  6. CHAPTER 7.6 — WINDOWS SERVER FAILOVER CLUSTERING (WSFC) — THE FOUNDATION OF SQL SERVER HA
  7. CHAPTER 7.7 — SQL SERVER DISASTER RECOVERY (DR) RUNBOOK — FULL ENTERPRISE DESIGN
  8. CHAPTER 7.8 — SQL SERVER CORRUPTION DETECTION & REPAIR (DBCC CHECKDB, PAGE-LEVEL RESTORE, EMERGENCY MODE)
  9. CHAPTER 7.9 — SQL SERVER MIGRATION & CLOUD DR STRATEGIES (AZURE, AWS, HYBRID MODELS)
  10. CHAPTER 7.10 — HADR DESIGN PATTERNS, DECISION MATRIX & INTERVIEW REVIEW
MODULE 9 — REAL-WORLD PROJECTS & CASE STUDIES
4 Lessons
  1. PROJECT 9.1 — DESIGNING A COMPLETE HA/DR SOLUTION FOR A FINANCE SYSTEM
  2. PROJECT 9.2 — PERFORMANCE TUNING A SLOW PRODUCTION SYSTEM (END-TO-END CASE STUDY)
  3. PROJECT 9.3 — Designing a Multi-Tenant SaaS Database (Security, Performance & Scaling)
  4. PROJECT 9.4 — DESIGNING A DATA WAREHOUSE / ANALYTICS LAYER ON TOP OF OLTP SQL (FOR POWER BI & REPORTING)

  • This comprehensive course is designed to take you from no SQL experience to advanced, job-ready T-SQL skills using SQL Server Management Studio (SSMS) as your main tool.

    You’ll start with the absolute fundamentals—what a database is, how tables relate to each other, and how to use SSMS confidently. From there, you’ll learn how to write powerful T-SQL queries to extract, filter, sort, group, and transform data. Step by step, you’ll progress into joins, subqueries, window functions, stored procedures, functions, views, transactions, and error handling.

    Beyond just running queries, this course teaches you how to think like a SQL developer or data analyst: how to design tables, enforce data quality with constraints, use indexes to improve performance, and read execution plans so your queries run efficiently on real systems.

    Everything is taught in a practical, lab-driven style. You’ll follow guided exercises inside SSMS, working with realistic datasets (sales, customers, finance, housing, or operations data) and building queries that look like what you’d find in a real organisation. By the end of the course, you’ll be able to confidently use SSMS and T-SQL to solve real business problems and support reporting, analytics, and application backends.

    This is not just a theory course. It is a full, structured path from beginner to advanced T-SQL, suitable for people who want to work with data, reporting, BI, software development, or database administration.

Who This Course Is For

  • Beginners with no previous SQL experience

  • Data Analysts / BI Analysts who want strong SQL foundations for reporting & dashboards

  • Developers who need to query SQL Server databases efficiently

  • IT / Support / Ops staff who work with systems that sit on SQL Server

  • Students & Graduates looking for practical, job-ready SQL skills

  • Anyone who wants to understand and control data, not just click reports in Excel or Power BI

Prerequisites

  • No prior SQL experience required

  • Basic computer literacy (open programs, use a browser, work with files)

  • Helpful but not required: familiarity with Excel or any programming language

You will use SQL Server Management Studio (SSMS) throughout the course. Installation guidance can be provided in the first module.

 

Deleting Course Review

Are you sure? You can't restore this back

Course Access

This course is password protected. To access it please enter your password below: