Introduction
- How to view the structure of a table & how to create your own shortcuts in SSMS
Module1 - SSMS & DML Tricks , Solution Files and Identity etc
- Please download Demonstration Exercises 1
- Dropping multiple tables in one go having a fixed pattern
- Guidelines while performing DML Operations
- How to switch between different SQL Servers within a query window in SSMS
- How to create Solution File in SSMS and what is the need for it
- How to use a single sql script file for different SQL Servers
- How to generate Identity Column
- How to insert a row with old identity value which was deleted
- How to reset table identity
Module 2 - Window Functions (Ranking , Aggregate and Analytical Functions)
- Please download Demonstration Exercises 2
- Theoretical Explanation of Window Functions
- RANK DENSE_RANK ROW_NUMBER NTILE WINDOW FUNCTIONS
- SUM MIN MAX COUNT AVG Window Functions
- FIRST_VALUE LAST_VALUE LAG LEAD Window Function
- Scenarios on Window Functions
- Scenario1 - Solution on Aggregate Window Function
- Scenario2 - Solution on Cumulative totals
- Scenario3 - Solution on Analytical Window Function
- Quiz 1
Module 3 - CTE , Recurssive CTE, Coalesce Function & Merge Commands etc
- Please download Demonstration Exercises 3
- How to use window function in the where clause using CTE and Derived Tables
- How to delete duplicate records from a table
- How to select only duplicate records from a table
- Quiz 2
- How to produce Date Dimension using Recursive CTE
- Hands On Question
- Scenario solved using Coalesce Function
- How to Merge New Changes to an Existing Table
- How to Merge New Changes to an Existing Table using Coalesce Function
- More On Merge Command
Module 4 - Stored Procedure, Error Handling , Batch ,Pagination and Synonyms etc
- Please download Demonstration Exercises 4
- What are the advantages of Stored Procedure
- INPUT OUTPUT DEFAULT RETURN Options in Stored Procedure
- Hands On Questions on Stored Procedure
- What is a Batch and its need
- BEGIN AND COMMIT TRANSACTION IS NOT SUFFICIENT
- Guidelines to write a Stored Procedure
- How to create and store customized errors
- THROW and RAISERROR Differences
- Quiz 3
- Error Handling Exercise for Practice
- Pagination in SQL Server using OFFSET FETCH
- Rename a table with dependencies USING SYNONYMS
- Reducing Roundtrips using Table Valued Stored Procedure
- Bonus Lecture
