Summary of the course

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

Chapter 1: Recursion and CTEs

What is recursion?

Recursion is the use of a procedure, subroutine, function, or algorithm that calls itself one or more times until a specified condition is met

Definition of a Common Table Expression (CTE):

WITH CTEtable as (
    <select statement on a table>
)

SELECT *
FROM CTEtable

Specifies a temporary named result set, known as a common table expression (CTE)

Hierarchical and Recursive Queries in SQL Server

Chapter 2: Hierarchical and recursive queries

Definition of a recursive CTE:

WITH cte_name AS (
   -- Anchor member
   <cte_initial_query>
   UNION ALL
   -- Recursive member
   <cte_recursive_query> )

SELECT * 
FROM cte_name

Real-world examples:

  1. Mathematical problems
  2. Hierarchy of an organization
  3. Hierarchy of a family tree
Hierarchical and Recursive Queries in SQL Server

Chapter 3: Creating data models on your own

Manipulating a table:

  • CREATE, INSERT, ALTER, DROP

Relational data model:

  • The relational database model is the most widely used database model.

Hierarchical and networked data model:

  • Represented as tree structure
  • Has one (hierarchy) or many (networked) root element

Hierarchical and Recursive Queries in SQL Server

Chapter 4: Hierarchical queries of real world examples

Common tasks:

  • Create a hierarchy data model
  • Query the hierarchy recursively
  • Get the level of a hierarchy

Travel planning of flight data:

How to assemble a car?

Modeling a power grid

Hierarchical and Recursive Queries in SQL Server

Congratulations!

Hierarchical and Recursive Queries in SQL Server

Preparing Video For Download...