Library

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

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

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

This online T-SQL video training course on Microsoft SQL Server Technology includes a lot of real world scenario which i learnt in my professional work. I have covered the course contents with the real life situation example for every topic.

Real Life situations are as follow

  • How to view the structure of a table 
  • How to create your own shortcuts in SSMS
  • Dropping multiple tables in one go having a fixed pattern
  • Guidelines while performing DML Operations
  • How to create Solution File in SSMS and what is the need for it
  • How to insert a row with old identity value which was deleted
  • How to reset table identity
  • How to delete duplicate records from a table
  • How to select only duplicate records from a table
  • How to produce Date Dimension using Recursive CTE Query
  • How to Merge New Changes to an Existing Table
  • What are the advantages of Stored Procedure
  • BEGIN AND COMMIT TRANSACTION IS NOT SUFFICIENT
  • How to produce running totals or cumulative totals using sql server query
  • How to compare current year sales with the previous year sales 
  • How to compare current year sales since the start of the business year sales
  • How to switch between different SQL Servers within a query window in SSMS
  • How to use a single sql script file for different SQL Servers
  • Reducing Roundtrips using Table Valued Stored Procedure
  • Pagination in SQL Server using OFFSET-FETCH
  • BEGIN AND COMMIT TRANSACTION IS NOT SUFFICIENT
  • Relevance of Batch in SQL Server
  • How to create and store customized errors
  • Guidelines of writing a Stored Procedure

Following are the major topics covered 

  • Deep dive into Window Functions
  • Ranking Functions - (Rank, Dense_Rank Row_Number, NTile )
  • Aggregate Functions - (Sum,Min,Max,Avg,Count)
  • Analytical Functions-(Lead , Lag, First_Value, Last_Value)
  • Stored Procedures
  • Input Parameters
  • Output Parameters
  • Default Parameters
  • Return
  • Table Valued Parameters
  • Common Table Expressions
  • Recursive Common Table Expressions
  • Synonyms
  • Identity Columns
  • Transactions
  • Raiserror/Throw
  • Error Handling (Try….Catch)
Basic knowledge
  • Elementary knowledege of Transact SQL is needed like SSMS,Database Tables, Select , Join , Group By Commands ,Primary Key, Foreign Key
What you will learn
  • Participant's knowledge will be deepened to handle real life situations in T-SQL
  • Please watch the course description to see more details
Curriculum
Number of Lectures: 43
Total Duration: 06:01:36
Module 1
  • How to view the structure of a table & how to create your own shortcuts in SSMS  
  • Dropping multiple tables in one go having a fixed pattern  
  • Please download Demonstration Exercises 1  

    Please download the following file and extract it in your computer 

  • 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
  • Please download Demonstration Exercises 2  

    Please download the following file and extract it in your computer 

  • 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  
  • Hands On Questions on Window Functions  

    Please try the questions by yourself and then you can verify the answers from the solutions file.

    Please download the zip file

Module 3
  • Please download Demonstration Exercises 3  

    Please download the following file and extract it in your computer 

  • 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  

    Please try the question by yourself and then you can verify the answers from the solutions file.

    Please download the zip file

  • 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
  • Please download Demonstration Exercises 4  

    Please download the following file and extract it in your computer 

  • What are the advantages of Stored Procedure  
  • INPUT OUTPUT DEFAULT RETURN Options in Stored Procedure  
  • Hands On Questions on Stored Procedure  

    Please download the zip file and after extraction you can practice the script. Please run the initial scripts for the lab setup

  • What is a Batch and its need  
  • BEGIN AND COMMIT TRANSACTION IS NOT SUFFICIENT  
  • Guidelines to write a Stored Procudure  
  • How to create and store customized errors  
  • THROW and RAISERROR Differences  
  • Quiz 3  
  • Error Handling Exercise for Practice  

    Please extract the following zip file

  • Pagination in SQL Server using OFFSET FETCH  
  • Rename a table with dependencies USING SYNONYMS  
  • Reducing Roundtrips using Table Valued Stored Procedure  
Reviews (0)