Ch 1: DATABASE INTRODUCTION

• Data, Databases and RDBMS Software
• Database Types : OLTP, DWH, OLAP
• Microsoft SQL Server Advantages, Use
• Versions and Editions of SQL Server
• SQL : Purpose, Real-time Usage Options
• SQL Versus Microsoft T-SQL [MSSQL]
• Microsoft SQL Server - Career Options

• SQL Server Components and Usage
• Database Engine Component and OLTP
• BI Components, Data Science Components
• ETL, MSBI and Power BI Components
• Course Plan, Concepts, Resume, Project
• 24 x 7 Online Lab for Remote DB Access
• Software Installation Pre-Requisites

Ch 7: JOINS & LINKED SERVERS

• JOINS - Table Comparisons Queries
• INNER JOINS For Matching Data
• OUTER JOINS For (non) Match Data
• Left Outer Joins with Example Queries
• Right Outer Joins with Example Queries
• FULL Outer Joins - Realtime Scenarios
• Join Queries with "ON" Conditions 

• CROSS JOIN and CROSS APPLY
• One-way, Two way Data Comparisons
• Linked Servers Configurations
• Linked Servers: RPC Settings & Tests
• Linked Servers, Remote Joins in TSQL
• Multi Server Connections, DB Access
• 2 Part, 3 Part, 4 Part Name Conventions

Ch 13: STORED PROCEDURES & TVP

• Variables: Declaration, Realtime Usage
• Variables: Value Assignment, Reporting
• Identity Property - Missing ID Values
• Data Validations, DMLs in Stored Procs
• Dynamic Data Insertions with SPs
• TRY..CATCH and THROW Operations
• Error Handling, Rollback in SQL Server

• Table Valued Parameters (TVP), Usage
• ReadOnly Parameters, Stored Procedures
• Output Parameters, Stored Procedures
• User Data Types and Real-time Use
• Table Cloning, Inserts @ Table Variables
• Table Variables Usage in T-SQL
• Output Parameters, Stored Procedures

Ch 2: SQL SERVER INSTALLATIONS

• System Configuration Checker Tool
• Versions and Editions of SQL Server
• SQL Server and SSMS Installation Plan
• SQL Server Pre-requisites : S/W, H/W
• SQL Server 2022 / 2019 Installation
• SQL Server 2017 Installation
• Instance Name and Server Features 

• Instances : Types and Properties
• Default Instance, Named Instances
• Port Numbers, Instance Differences
• Service and Service Account Use
• Authentication Modes and Logins
• Windows Logins and SQL Logins
• FileStream and Collation Properties

Ch 8: VIEW, SPs, FUNCTIONS BASICS

• Database Objects: Overview & Usage
• Views: Types, Usage in Real-time
• System Predefined Views and Audits
• Listing Databases, Tables, Schemas
• Functions: Types, Usage in Real-time
• System Predefined Functions, Audits
• DBId, DBName, ObjectID, ObjectName

• Using Parameters in SQL Server
• Dynamic Joins for Database Audits
• Procedures: Types, Usage in Real-time
• User & System Predefined Procedures
• Parameters and Dynamic SQL Queries
• Sp_help, Sp_helpdb and sp_helptext
• sp_pkeys, sp_rename and sp_help

Ch 14: STORED PROCEDURES & Triggers

• DML Triggers and DDL Triggers
• FOR and INSTEAD OF Triggers
• Magic Tables : Inserted, Deleted
• Views on Tables - SCHEMABINDING
• ENCRYPTION and CHECK OPTION
• Cascaded Views, Encrypted Views
• Updatable Views, Joins with Triggers

• Stored Procedures @ Triggers, Views
• FOR and INSTEAD OF Triggers
• Magic Tables : Inserted, Deleted
• ENCRYPTION and CHECK OPTION
• Cascaded Views, Encrypted Views
• Updatable Views, Joins with Triggers
• Stored Procedures @ Triggers, Views

Ch 3: SSMS Tool, SQL BASICS - 1

• SQL Server Management Studio
• Local and Remote Connections
• System Databases: Master and Model
• MSDB, TempDB, Resource Databases
• Creating Databases : Files [MDF, LDF]
• Creating Tables in User Interface
• Data Insertion & Storage. Limitations 

• SQL : Purpose and Real-time Usage
• SQL Versus T-SQL : Basic Differences
• DDL, DML, SELECT, DCL and TCL
• Creating Tables using SQL Scripts
• Data Storage, Inserts - Basic Level
• Table Data Verifications with Select
• SELECT Statement for Table Retrieval

Ch 9: Triggers & Transactions

• Triggers - Purpose, Real-world Usage
• FOR/AFTER Triggers - Real time Use
• INSTEAD OF Triggers - Real time Use
• INSERTED, DELETED Memory Tables
• Using Triggers for Data Replication
• Enable Triggers and Disable Triggers
• Database Level, Server Level Triggers

• Transactions : Types, ACID Properties
• Transaction Types and AutoCommit
• EXPLICIT & IMPLICIT Transactions
• COMMIT and ROLLBACK Statements
• Open Transaction Scenarios & Cause
• Query Blocking Scenarios @ Real-time
• NOLOCK and READPAST Lock Hints

Ch 15: STORED PROCEDURES & Cursors

• Cursors - Benefits, Cursors in SProcs
• Using Cursors in Real-world Scenarios
• Cursors : Declaring Variables, Life Cycle
• Declaration, Open / Close Cursors
• Cursor Types: Forward Only, Scroll
• Cursor Types: Static and Dynamic
• Keyset Driven Cursors & Indexes

• Default Cursor Types and Usage
• FETCH operation and Data Retreival
• Keyset Cursors and @@FetchStatus
• Nesting of Stored Procedures - Dynamic
• Data Formatting and WHILE Loops
• Using Temporary Tables for Formatting
• Loading Data from SPs to Temp Tables

Ch 4: SQL BASICS - 2

• Creating Databases & Tables in SSMS
• Single Row Inserts, Multi Row Inserts
• Rules for Data Insertion Statements
• SELECT Statement @ Data Retrieval
• SELECT with WHERE Conditions
• Batch Concept and Go Statement
• AND and OR Operators Usage

• IN Operator and NOT IN Operator
• Between, Not Between Operators
• LIKE and NOT LIKE Operators
• UPDATE Statement & Conditions
• DELETE & TRUNCATE Statements
• Logged and Non-Logged Operations
• ADD, ALTER and DROP Columns

Ch 10: DB Architecture & Group By

• Database Architecture: Data Files
• Database Architecture: Log Files
• Primary and Secondary Data Files
• Log Files Creation and Realtime Use
• Creating and Using Filegroups
• Linking Tables with Filegroups
• Scripting Database and Objects

• GROUP BY: Importance, Realtime Use
• GROUP BY Queries and Aggregations
• Group By Queries with Having Clause
• Group By Queries with Where Clause
• Using WHERE and HAVING in T-SQL
• Using Group By in Data Audits
• Using Group By with Joins, Audits

Ch 16: STORED PROCEDURES & CTEs

• CTE: Common Table Expressions
• Real-time Scenarios with CTEs - Usage
• Using CTEs for Data Retreival, SELECT
• Using CTEs for Data Manupulations
• Sub Queries with Self Joins : Issues
• CTEs for Avoiding Self Joins, Tuning
• CTEs for Avoiding Sub Queries, Tuning

• Window Functions: RowNumber()
• Rank() and Dense_Rank() Functions
• ROW_NUMBER() with CTE Queries
• Identifying Duplicated Rows
• Remving Duplicate Rows with CTEs
• Recursive CTEs and ANCHOR Element
• Termination Checks in Recursive CTEs

Ch 5: SQL Basics - 3, T-SQL INTRO

• Database Objects : Tables and Schemas
• Schemas : Group Tables in Database
• Schemas : Security Management Object
• Creating Schemas & Batch Concept
• Using Schemas for Table Creation
• Data Storage in Tables with Schemas
• Data Retreival and Usage with Schemas

• Table Migrations across Schemas
• Import and Export Wizard in SSMS
• Data Imports with Excel File Data
• Performing Bulk Operations in SSMS
• Temporary Tables : Real-time Use
• Local and Global Temporary Tables
• # and ## Prefix, Scope of Usage

Ch 11: TSQL Queries: Group By, Joins

• Joins with Group By Queries in TSQL
• Joining 3 Tables with Group By
• Joining  4 Tables with Group By
• Multi Table Joins with Table Aliases
• Table & Column Aliases with Joins
• Joins with HAVING Conditions
• Joins with WHERE & Aggregations

• Joins with Sub Queries, Formatting
• Joins with IIF() Function, Conditions
• Joins with CASE Statement Conditions
• UNION and UNION ALL Operator
• Storing Queries in Database Views
• Office Data Connections, Excel Reports
• Manual Data Refresh & Reports

Ch 17: FUNCTIONS Level 2, XML & JSON

• Functions: Types, Real-world Usage
• Scalar Value Returning Functions
• Inline Table Value Functions
• Multi-Line Table Value Functions
• WHILE Loops and Iterations in T-SQL
• Table Variables Usage in T-SQL
• Data Type Conversions with Functions

• Composite Keys , Computed Columns
• Self Referencing Keys, Self Joins
• Adding Keys to Existing Tables
• XML AUTO, XML RAW and XML PATH
• BULK INSERT, BULKCOLUMN, JSON
• OPENROWSET, PIVOT and UNPIVOT
• JSON Files - Data Import into SQL DB

Ch 6 : CONSTRAINTS, INDEXES

• Constraints and Keys - Data Integrity
• NULL, NOT NULL Property on Tables
• UNIQUE KEY Constraints: Importance
• PRIMARY KEY Constraint: Importance
• FOREIGN KEY Constraint: Importance
• REFERENCES, CHECK and DEFAULT
• Candidate Keys and Identity Property 

• Database Diagrams and ER Models
• Relationships Verification and Links
• Indexes : Basic Types and Creation
• Index Sorting and Search Advantages
• Clustered and NonClustered Indexes
• Primary Key and Unique Key Indexes
• Need for Indexes - working with Keys

Ch 12 : ER MODELS, NORMAL FORMS

• First Normal Form and Atomicity
• Second Normal Form, Candidate Keys
• 3rd Normal Form Multi Value Dependency
• Boycee-Codd Normal Form : BNCF
• Fourth Normal Form Realtime Advantages
• Self Reference Keys and 4 NF Usage
• 1:1, 1:M, M:1, M:M Relationship Types

• MERGE Statement - Comparing Tables
• WHEN MATCHED and NOT MATCHED
• Incremental Load with MERGE Statement
• UPSERT Operations with MERGE
• Stored Procedures for Merge Statement
• DML Operations with ON Keyword
• Tuning / Optmizing Merge Statement

• Understanding Project Requirements
• End to End Project Work Flow
• Naming Conventions in Real-time
• Primary (mdf) and Secondary (ndf) Files
• Implmenting FileGroups For Performance
• Table Schemas : Creation and Use
• Implementing Normal Forms (OLTP)

• Computed Columns and Data Types
• SQL_Variant, Bit, sysname Data Types
• Test Data Insertions, Options
• Email and Phone Number Validations
• Data Types Conversions, Validations
• Data Validations and Schema Validations

• Join Types and Join Options For Reports
• Views with JOIN Options For Query Store
• Implementing Functions for Calculations
• Using PIVOT Tables in Queries
• Dynamic Conditions in Queries
• Parameterized Queries in T-SQL
• Dynamic Joins and Conditions

• Using User Defined Functions (UDF)
• Using Stored Procedures for T-SQL
• Merge Option for Faster Queries
• Using Lock Hints inT-SQL Queries
• Creating and Using Office Data Connections
• Excel Pivot Tables and Pivot Charts

• Event Handling , Error Handling
• Stored Procedures with Transactions
• Error Handling, Event Handling Options
• Transaction Nesting, Save Points
• Stored Procedures with Tables
• Stored Procedures with Views
• Stored Procedures with Functions

• Automating DML with Triggers
• Project Deployments, Project FAQ
• Project Solution Explanation
• Resume Points from the Project
• Interview FAQs from Project
• Interview FAQs For Each Concept