Ch 1: SQL Database Job Roles 

• Introduction to Data 
• Database Intro, Types 
• OLTP, DWH, OLAP 
• DBMS Concepts 
• Database Job Roles 
• Data Engineer Job Roles

Ch 2: Database Intro & Installations 

• SQL Server Installations 
• Instance Concepts 
• Authentication Types 
• Authentication Modes 
• SSMS Tool Installation 
• Connections, Authentications

Ch 3: SQL Basics V1 (Commands) 

• Creating Databases (GUI) 
• Creating Tables, Columns (GUI) 
• SQL Basics (DDL, DML, etc..) 
• Creating Databases, Tables 
• Data Inserts (GUI, SQL) 
• Basic SELECT Queries

Ch 4: SQL Basics V2 (Commands, Operators) 

• DDL: Create, Alter, Drop, Add 
• DML: Insert, Update, Delete 
• DQL: Select, Fetch 
• SQL Operators 
• Special Operators

Ch 5: Excel Data Imports 

• Data Imports with Excel 
• Order By: Asc, Desc
• Order By with WHERE 
• TOP & OFFSET 
• UNION, UNION ALL

Ch 6: Schemas & Batches 

• Schemas: Creation, Usage 
• Schemas & Table Grouping 
• Real-world Banking Database 
• 2 Part, 3 Part & 4 Part Naming 
• Batch Concept & “Go” Command

Ch 7: Constraints, Keys & RDBMS 

• Null, Not Null Constraints 
• Unique Key Constraint 
• Primary Key Constraint 
• Foreign Key & References 
• Default Constraint & Usage 
• DB Diagrams & ER Models

Ch 8: Normal Forms & ERD 

• Normal Forms: 1 NF, 2 NF 
• 3 NF, BCNF and 4 NF
• Self Referencing Keys
• Cascading Keys
• Database Diagrams

Ch 9: Joins Queries – Level 1 

• Joins: Table Comparisons 
• Inner Join & Outer Joins 
• Cross Join & Cross Apply 
• Table Combination 
• Table & Column Aliases

Ch 10: Joins Queries – Level 2 

• Group By & Aggregations 
• Joins with Group By 
• 3 Table, 4 Table Joins 
• Join Queries with Aliases 
• WHERE & HAVING 
• Query Execution Order

Ch 11: Sub Queries 

• Distinct & Union, Union All 
• Sub Queries Concept 
• Sub Queries & Aggregations 
• Joins with Sub Queries 
• Correlated Queries

Ch 12: Views & Data Analytics 

• Views: Realtime Usage 
• Storing SELECT in Views 
• DML, SELECT with Views 
• RLS: Row Level Security 
• Data Analytics with Excel 
• Important System Views

Ch 13: Stored Procedures – Level 1 

• Stored Procedures: Realtime Use 
• Procedures with SELECT 
• System Stored Procedures 
• Metadata Access with SPs 
• Stored Procedures, Tuning

Ch 14: Stored Procedures – Level 2 

• Merge Statement 
• Upsert Operations with Merge 
• Merge with OLTP & DWH 
• Matched and Not Matched 
• Merge Statement inside SPs

Ch 15: Functions – Level 1 

• Using Defined Functions (UDF) 
• Scalar Functions in Real-world 
• Table Valued Functions 
• Parameterized Queries 
• Returns and Return 
• SP Versus Functions

Ch 16: Functions – Level 2 

• Aggregated Functions 
• Date & Time Functions 
• String Functions 
• Window Functions 
• Rank, Row_Number 
• DenseRank, Partition By

Ch 17: Triggers & Automations 

• Need for Triggers in Real-world 
• DDL & DML Triggers 
• For / After Triggers 
• Instead Of Triggers 
• Memory Tables with Triggers 
• Disabling DMLs & Triggers

Ch 18: Transactions & ACID 

• Transaction Concepts in OLTP 
• Auto Commit Transaction 
• Explicit Transactions 
• COMMIT, ROLLBACK 
• Lock Hints & Query Blocking 
• READPAST, LOCKHINT

Ch 19: Indexes Basics, Tuning 

• Indexes & Tuning 
• Clustered Index, Primary Key 
• Non Clustered Index & Unique 
• Creating Indexes Manually 
• Composite Keys, Query Optimizer 
• Composite Indexes & Usage

Ch 20: CTEs & Tuning 

• Common Table Expression 
• Creating and Using CTEs 
• CTEs, In-Memory Processing 
• IIF(), CASE Statement 
• Cube( ) and Rollup( ) 
• Sub Totals & Grand Totals 
• Grouping( ) & Usage

Ch 21: Data Types & Variables 

• Integer Data Types 
• Character, MAX Data Types 
• Decimal & Money Data Types 
• Boolean & Binary Data Types 
• Date and Time Data Types 
• SQL_Variant Type 
• Variables in SQL 
• Cursor Variable & Fetch

Ch 22: Temp Tables 

• Local Temp Tables
• Global Temp Tables 
• Testing Temp Tables 
• SELECT..INTO Statement 
• Bulk Copy Operations

Ch 23: SQL Server Architecture 

• Network Protocols 
• Query Execution Engine 
• Parser, Compiler, Checkpoint 
• SQL Manager, DB Manager 
• Storage Engine, Locks
• SQL OS Components

Ch 24: Real-Time SQL Server Case Studies

✅ Healthcare Management System 

• Patient Records Management
• Doctor Appointment Scheduling 
• Billing & Insurance Processing 
• Medical Reports Analysis

✅ E-Commerce Database 

• Customer & Product Management 
• Order Processing 
• Inventory Tracking 
• Sales Reporting

Ch 25: Tuning: Indexes 

• Indexes : Sort Locations 
• Clustered & Online Indexes 
• Non Clustered, Column Store 
• Included Indexes in Realtime 
• Filtered Indexes & Usage 
• Covering Index & Selectivity 
• Indexed Views (Materialized)

Ch 26: Tuning: Partitions 

• Partitions: Performance Tuning 
• Partition Functions & Schemes 
• Partition Un-partitioned Tables 
• Compressions: ROW, PAGE 
• Auditing Partitions 
• Partitions Limitations in OLTP 
• Partitions with DWH

Ch 27: DB Architecture (VLDB) 

• Planning Large Databases 
• Primary, Secondary Data Files 
• Filegroups, Spacing and Sizing 
• Log File: Usage and Precautions 
• Creating Tables with Filegroups 
• Pages and Extents for Storage 
• VLF, MiniLSN & Checkpoint

Ch 28: Lock Management 

• Open Transactions in Realtime 
• Open Transaction, Blocking 
• LOCKS: Types & Audits
• S, X, IX, U and MD Locks 
• Sch-M and Sch-S Locks 
• SP_WHO2, SP_LOCK 
• sysprocesses & Lock Waits

Ch 29: Isolation Levels 

• Lock Hints and Isolation Levels 
• Read Committed, Uncommitted 
• Serializable, Repeatable Read 
• Snapshot Isolation, Versioning 
• Read Committed Snapshot 
• Choosing Correct Isolation Level

Real-Time Case Study For Query Tuning

✅ Healthcare Management System 

• Indexing & Tuning 
• Tuning Tools 
• Partitions, DTA Recommendations 
• Lock Management 
• Tuning Recommendations

Ch 30: Variables & Try. Catch 

• Variables: Declaration & Usage 
• Assigning Values to Variables 
• SELECT & SET Operations 
• Using Variables in SPs 
• Variables Versus Parameters 
• Try.. Catch Block with Variables 
• THROW Statement, Error Handling

Ch 31: Updatable Views 

• Using Triggers with Views 
• Updatable Views, DML 
• Views & Stored Procedures 
• Data Distributions in Tables 
• Transactions with Procedures 
• Conditional Commits in SPs 
• Rollback Options in Realtime

Ch 32: Stored Procedures & TVPs 

• Using TVP with Procedures 
• Creating User Defined Types 
• Big Data Copy & Transactions
• Using SPs & Table Variables 
• Transactional Integrity with SPs 
• Conditional Commits, Rollbacks
• Procedure Recompilations

Ch 33: Stored Procedures, Cursors 

• Cursors Types: Local, Global 
• Using Procedures with Cursors 
• Formatting Queries, Nvarchar 
• WHILE Loop: @Fetch Status 
• Variables with Dynamic SQL 
• sp_executesql Extended SP 
• Dynamic SQL Programming

Ch 34: SPs & Recursive CTEs 

• CTEs: Common Table Expression 
• CTEs For DML Operations 
• Defining Recursive CTEs 
• Anchor Element: Realtime Use 
• Termination Checks and Loops 
• Defining SPs with CTEs 
• Cautions with Recursive CTEs

Ch 35: Functions & Loops 

• Inline, Table Line Functions 
• Multi Line Table Functions 
• Using LOOPs in Functions 
• Variables & Return Values 
• Table Generation Logic 
• Date & Time Data Types 
• Calendar Data Generations

Ch 36: Open Fuzzy Logic, Debugging 

• Using OPENROWSET( ) 
• EDIT_DISTANCE 
• EDIT_DISTANCE_SIMILARITY 
• JARO_WINKLER_DISTANCE 
• MATCH Operator 
• JSON_OBJECT, JSON_ARRAY 
• JSON_ARRAYAGG, JSON_CONTAINS 
• OPENJSON

Ch 37: PIVOT, UNPIVOT 

• Reading Denormalized Data 
• Normalizing Table Data 
• PIVOT Operation with TSQL 
• PIVOT with Aggregates 
• FOR and IN Operators 
• UNPIVOT with TSQL 
• PIVOT with Functions, SPs

Ch 38: Change Data Capture 

• CDC Concept
• Database Level CDC
• Table Level CDC
• CT Tables (Change Tracking)
• DDL & DML Tracking

Real-Time Project

✅ Banking Transaction Management System using SQL Server & T-SQL 

• Customer Account Management 
• Savings / Current Account Tables 
• Deposit and Withdrawal Transactions 
• Fund Transfer Between Accounts 
• Transaction History Report 
• Daily, Monthly, Yearly Statements 
• Loan Application & EMI Tracking 
• Stored Procedures for Banking Operations 
• Views for Customer & Account Reports 
• Functions for EMI, Interest & Balance Calculation 
• Triggers for Audit Tracking