SQL SERVER Procedures&Concepts – Raise Above Beginner Level

Course Curriculum

Chapter 1: Introduction

Lecture 1: Introduction

Chapter 2: Procedure 1 – Dynamic Query warm up

Lecture 1: What is Dynamic Query

Lecture 2: Procedure 1 Scenario

Lecture 3: Visualizing data

Lecture 4: Creating the logic of the procedure

Lecture 5: Creating the logic of the procedure – 2

Lecture 6: Setting up body of the Procedure

Lecture 7: Setting up input parameters

Lecture 8: Create IF ELSE Block

Lecture 9: Completing our IF ELSE Logic

Lecture 10: Setting up Dynamic Query

Lecture 11: Placing Parameter inside Dynamic Query

Lecture 12: Learn to TRIM your input parameters

Lecture 13: Bind Parameter and execute Dynamic Query

Lecture 14: Testing the final product

Chapter 3: QUIZ No. 1

Chapter 4: Procedure 2 – XML as input parameter

Lecture 1: Why you need XML?

Lecture 2: Procedure 2 – Scenario

Lecture 3: Learn to make a select from XML – part 1

Lecture 4: Learn to make select from XML – part 2

Lecture 5: Select from XML INTO #tempTable

Lecture 6: Data structure

Lecture 7: Final JOINS and finishing procedure

Lecture 8: Testing the final product

Chapter 5: QUIZ No. 2

Chapter 6: Procedure 3 – XML , Dynamic and Cursor

Lecture 1: What is a temporary table ?

Lecture 2: What is a Cursor ?

Lecture 3: Procedure 3 – Scenario

Lecture 4: Setting up the steps of the Procedure

Lecture 5: Understanding comparison operators inside XML

Lecture 6: Setting up our #tempTables

Lecture 7: Data structure and JOINS for our Dynamic Query

Lecture 8: Create variables for Cursor

Lecture 9: Creating the body of the Cursor

Lecture 10: Setting up Cursor

Lecture 11: Setting up Dynamic Query inside the Cursor

Lecture 12: Understanding quotes inside dynamic query

Lecture 13: Testing our procedure with Debug Mode

Lecture 14: Explain SQL Injection in relation to Dynamic Query

Chapter 7: Quiz No. 3

Chapter 8: Procedure 4 – OUTPUT , OUTER & CROSS APPLY and again Cursor

Lecture 1: Procedure 4 Scenario

Lecture 2: Setting up the steps of the Procedure

Lecture 3: Selecting data with input parameter – learn CONVERT()

Lecture 4: Setting body of the Cursor

Lecture 5: Use OUTPUT clause inside Cursor

Lecture 6: Learning OUTER APPLY concept

Lecture 7: Testing final product and more about OUTER and CROSS APPLY

Chapter 9: Quiz No. 4

Chapter 10: Procedure 5 – Output XML and UPDATE with JOIN clause

Lecture 1: Setting up the steps of the Procedure

Lecture 2: Creating #tempTables

Lecture 3: Learn to UPDATE with JOIN

Lecture 4: SET XML variable for output

Lecture 5: Finish and test final product

Chapter 11: Procedure 6 – Pivot Operator and CTE

Lecture 1: Intro on CTE

Lecture 2: CTE – 2nd part

Lecture 3: Procedure 6 – Scenario

Lecture 4: Pivot Operator Explained

Lecture 5: Pivot Operator – part 2

Lecture 6: Define steps and set up CTE statement

Lecture 7: Setting final step

Lecture 8: Testing the final product

Chapter 12: Procedure 7 – CTE, ROW_NUMBER() and IIF

Lecture 1: Procedure 7 – Scenario

Lecture 2: Define steps for the Procedure

Lecture 3: Draw the main Select

Lecture 4: Finish the Select by adding IIF operator

Lecture 5: Learn to apply ROW_NUMBER() and make Partitions

Lecture 6: Completing first 2 steps of our Procedure

Lecture 7: Time to learn how to assign value to variables with SELECT

Lecture 8: One step away from finalizing

Lecture 9: Placing variables inside our CTE and test the final product

Chapter 13: Procedure 8 – Set up relational Many-to-Many Database

Lecture 1: Define steps and explain concept

Lecture 2: Create main tables – add Primary Keys and Identity

Lecture 3: Create Junction Table and add Foreign Keys

Lecture 4: Create Junction Table and add Foreign Keys -part 2

Lecture 5: Set up body and variables for Procedure

Lecture 6: Create IF ELSE Logic

Lecture 7: Check and Insert new data in main tables

Lecture 8: Check and Insert new data in main tables – part 2

Lecture 9: Testing our procedure

Chapter 14: Bonus Lecture – Master XML Selection

Lecture 1: Select text from XML

Lecture 2: Understand node level

Lecture 3: Select text and attributes from any level

Chapter 15: T-SQL Concepts Explained/Re-explained – Q/A Section

Lecture 1: OUTER APPLY in comparison to LEFT JOIN