T-SQL Training with Real World Scenarios:Tricks of the Trade

Course Curriculum

Chapter 1: Introduction

Lecture 1: How to view the structure of a table & how to create your own shortcuts in SSMS

Chapter 2: Module1 – SSMS & DML Tricks , Solution Files and Identity etc

Lecture 1: Please download Demonstration Exercises 1

Lecture 2: Dropping multiple tables in one go having a fixed pattern

Lecture 3: Theory – Guidelines while performing Update and Delete Operations

Lecture 4: Guidelines while performing Update and Delete Operations

Lecture 5: How to switch between different SQL Servers within a query window in SSMS

Lecture 6: How to create Solution File in SSMS and what is the need for it

Lecture 7: How to use a single sql script file for different SQL Servers

Lecture 8: How to generate Identity Column

Lecture 9: How to insert a row with old identity value which was deleted

Lecture 10: How to reset table identity

Chapter 3: Module 2 – Window Functions (Ranking , Aggregate and Analytical Functions)

Lecture 1: Please download Demonstration Exercises 2

Lecture 2: Theoretical Explanation of Window Functions

Lecture 3: RANK DENSE_RANK ROW_NUMBER NTILE WINDOW FUNCTIONS

Lecture 4: SUM MIN MAX COUNT AVG Window Functions

Lecture 5: FIRST_VALUE LAST_VALUE LAG LEAD Window Function

Lecture 6: Scenarios on Window Functions

Lecture 7: Scenario1 – Solution on Aggregate Window Function

Lecture 8: Scenario2 – Solution on Cumulative totals

Lecture 9: Scenario3 – Solution on Analytical Window Function

Chapter 4: Module 3 – CTE , Recurssive CTE, Coalesce Function & Merge Commands etc

Lecture 1: Please download Demonstration Exercises 3

Lecture 2: How to use window function in the where clause using CTE and Derived Tables

Lecture 3: How to delete duplicate records from a table

Lecture 4: How to select only duplicate records from a table

Lecture 5: How to produce Date Dimension using Recursive CTE

Lecture 6: Hands On Question

Lecture 7: Scenario solved using Coalesce Function

Lecture 8: How to Merge New Changes to an Existing Table

Lecture 9: How to Merge New Changes to an Existing Table using Coalesce Function

Lecture 10: More On Merge Command

Chapter 5: Module 4 – Stored Procedure, Error Handling , Batch ,Pagination and Synonyms etc

Lecture 1: Please download Demonstration Exercises 4

Lecture 2: What are the advantages of Stored Procedure

Lecture 3: INPUT OUTPUT DEFAULT RETURN Options in Stored Procedure

Lecture 4: Hands On Questions on Stored Procedure

Lecture 5: What is a Batch and its need

Lecture 6: BEGIN AND COMMIT TRANSACTION IS NOT SUFFICIENT

Lecture 7: Guidelines to write a Stored Procedure

Lecture 8: How to create and store customized errors

Lecture 9: THROW and RAISERROR Differences

Lecture 10: Error Handling Exercise for Practice

Lecture 11: Pagination in SQL Server using OFFSET FETCH

Lecture 12: Rename a table with dependencies USING SYNONYMS

Lecture 13: Reducing Roundtrips using Table Valued Stored Procedure

Lecture 14: Bonus Lecture