Recursive Queries in MS SQL Server

Table of contents

1. 1. Intro Quiz

   0/10

   Test your knowledge of T-SQL

   List of exercises Start now
   1. Introduction
      1. Welcome
      2. The Recipe table
      3. The Ingredient table
      4. The RecipeIngredient table
   2. Quiz
      1. Exercise 1
      2. Exercise 2
      3. Exercise 3
      4. Exercise 4
      5. Exercise 5
   3. Congratulations
      1. Congratulations

2. 2. CTE syntax

   0/17

   Your first encounter with Common Table Expressions

   List of exercises Start now
   1. Introduction
      1. Welcome
      2. The Supporter table
      3. The Project table
      4. The Donation table
   2. Basic CTEs
      1. Basic syntax
      2. Basic syntax – exercise
      3. Syntax with columns
      4. Syntax with columns – exercise
      5. Multiple CTEs
      6. Multiple CTEs – practice 1
      7. Multiple CTEs – practice 2
      8. CTEs vs. subqueries
      9. CTEs vs. subqueries – exercise
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

3. 3. Nested CTEs

   0/15

   You'll learn how to use nested CTEs

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. The Salesman table
      3. The DailySales table
      4. The City table
   2. Nested CTEs
      1. Simple nested CTEs
      2. Simple nested CTEs – exercise
      3. Nested CTEs with subqueries
      4. Exercise 1
      5. Exercise 2
      6. Exercise 3
      7. Exercise 4
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Congratulations

4. 4. Recursive CTEs

   0/38

   You'll learn what recursion is and how to use it in T-SQL queries

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. Agenda
   2. The very basics
      1. Recursion explained: real world examples
      2. Recursion explained: computer science
      3. Recursion explained: syntax 1
      4. Recursion explained: syntax 2
      5. Recursion explained: the number of columns
      6. Basic recursion: exercise
      7. Multiple CTEs
      8. A common recursive CTE error
      9. Use CAST with recursive CTEs
   3. Infinite looping
      1. Mind infinity
      2. MAXRECURSION n
      3. MAXRECURSION 0
      4. TOP keyword
   4. Processing trees with recursive CTEs
      1. Trees in computer science
      2. Trees in computer science – exercise
      3. Hierarchical company 1/3
      4. Hierarchical company 2/3
      5. Hierarchical company 3/3
      6. Exercise 1
      7. Exercise 2
      8. Exercise 3
      9. Additional exercise – new table
      10. Additional exercise – trees
   5. Processing graphs with recursive ctes
      1. Graphs in computer science
      2. Graphs – real life examples
      3. Traveling salesman 1
      4. Traveling salesman 2
      5. Traveling salesman 3
      6. Traveling salesman – exercise 1
      7. Traveling salesman – exercise 2
      8. Graphs – additional exercise
   6. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

5. 5. CTEs with INSERT, DELETE, and UPDATE

   0/18

   You'll learn how to use INSERT, DELETE, and UPDATE statements with CTEs

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. The GroceryStore table
      3. The Product table
      4. The Customer table
      5. The CustomerOrder table
   2. CTEs with data modifying statements
      1. INSERT statement
      2. CTEs with INSERT statement 1
      3. CTEs with INSERT statement 2
      4. UPDATE statement
      5. CTEs with UPDATE statement
      6. DELETE statement
      7. CTEs with DELETE statement
      8. Nested CTEs with data modifying statements
   3. Summary
      1. Summary
      2. Exercise 1
      3. Exercise 2
      4. Exercise 3
      5. Congratulations

6. 6. Final Quiz

   0/14

   Test your knowledge in this final quiz

   List of exercises Buy course
   1. Introduction
      1. Welcome
      2. The Project table
      3. The Employee table
      4. The Allocation table
   2. Quiz
      1. Single CTE
      2. Multiple CTEs
      3. Nested CTEs
      4. Nested CTEs with a subquery
      5. Simple recursive CTEs
      6. Complex recursive CTE – trees
      7. Complex recursive CTE – graphs
      8. UPDATE with CTE
      9. INSERT INTO with CTE
   3. Summary
      1. Congratulations

Get the Certificate of Competency in SQL

This certificate will confirm your practical SQL problem-solving skills

Obtain the Certificate Learn more