cookies-icon

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings. You can modify your browser settings on your own. For more information see our Privacy Policy.

Course

Advanced

Recursive Queries in MS SQL Server

Master the most challenging type of T-SQL queries. Learn how to process trees and graphs in T-SQL, and how to effectively organize long SQL queries.

Table of contents

Lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

limited to this course only

$29

Buy now

Want a better deal?

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

Get unlimited lifetime access to all 53 present and future courses

Save $420

Unlimited lifetime access

B042DDD8-A1C9-4053-8AF3-463EB31543B8@2x

to all 53 present and future courses

$ 179

$ 599

70% OFF

Buy now Full pricing
112

Interactive exercises

18 h

Estimated time

168

Users enrolled

☆☆☆☆☆
★★★★★

15 ratings

Description Table of contents Reviews Start learning

Description

Common Table Expressions (CTEs), often simply called WITH clauses, are essentially just named subqueries. They are a fairly new feature of T-SQL; with CTEs, you can break a long query into smaller chunks, which makes it more readable. Unlike T-SQL subqueries, CTEs can be recursive, allowing the traversal of hierarchical models of enormous depth.

About the Common Table Expressions in T-SQL Course
This course will cover simple CTEs, nested CTEs, and recursive CTEs. You will learn how to manage your T-SQL queries with CTEs, how and when to nest CTEs, and how to use recursive CTEs to move through hierarchical data models.

This course is intended for intermediate users. We assume the user knows the basics of T-SQL, including:

  • How to select from a single table, including writing complex WHERE conditions
  • How to JOIN tables
  • How GROUP BY and HAVING work
  • How T-SQL subqueries work

What Are the Requirements?

  • A web browser
  • Knowledge of basic SQL, including JOINs and GROUP BY clauses

What Am I Going To Get from This T-SQL Course?

You will discover how to:
  • Understand and use basic Common Table Expressions
  • Split long T-SQL queries with simple and nested CTEs
  • Deploy recursive CTEs
  • Write recursive CTEs that traverse a hierarchical data model

Who Should Take This Course?

  • Beginning database analysts
  • Developers who want to keep their knowledge of T-SQL current
  • Students taking classes in relational databases
  • Anyone who wants to learn Common Table Expressions in T-SQL

Table of contents

Free

Paid content

0%

Course progress

0/112

Exercises completed

1.

Intro Quiz

Test your knowledge of T-SQL

0/10
Start now

Introduction

Welcome
The Recipe table
The Ingredient table
The RecipeIngredient table

Quiz

Exercise 1
Exercise 2
Exercise 3
Exercise 4
Exercise 5

Congratulations

Congratulations

2.

CTE syntax

Your first encounter with Common Table Expressions

0/17
Start now

Introduction

Welcome
The Supporter table
The Project table
The Donation table

Basic CTEs

Basic syntax
Basic syntax – exercise
Syntax with columns
Syntax with columns – exercise
Multiple CTEs
Multiple CTEs – practice 1
Multiple CTEs – practice 2
CTEs vs. subqueries
CTEs vs. subqueries – exercise

Summary

Summary
Exercise 1
Exercise 2
Congratulations

3.

Nested CTEs

You'll learn how to use nested CTEs

0/15

Introduction

Welcome
The Salesman table
The DailySales table
The City table

Nested CTEs

Simple nested CTEs
Simple nested CTEs – exercise
Nested CTEs with subqueries
Exercise 1
Exercise 2
Exercise 3
Exercise 4

Summary

Summary
Exercise 1
Exercise 2
Congratulations

4.

Recursive CTEs

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

0/38

Introduction

Welcome
Agenda

The very basics

Recursion explained: real world examples
Recursion explained: computer science
Recursion explained: syntax 1
Recursion explained: syntax 2
Recursion explained: the number of columns
Basic recursion: exercise
Multiple CTEs
A common recursive CTE error
Use CAST with recursive CTEs

Infinite looping

Mind infinity
MAXRECURSION n
MAXRECURSION 0
TOP keyword

Processing trees with recursive CTEs

Trees in computer science
Trees in computer science – exercise
Hierarchical company 1/3
Hierarchical company 2/3
Hierarchical company 3/3
Exercise 1
Exercise 2
Exercise 3
Additional exercise – new table
Additional exercise – trees

Processing graphs with recursive ctes

Graphs in computer science
Graphs – real life examples
Traveling salesman 1
Traveling salesman 2
Traveling salesman 3
Traveling salesman – exercise 1
Traveling salesman – exercise 2
Graphs – additional exercise

Summary

Summary
Exercise 1
Exercise 2
Exercise 3
Congratulations

5.

CTEs with INSERT, DELETE, and UPDATE

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

0/18

Introduction

Welcome
The GroceryStore table
The Product table
The Customer table
The CustomerOrder table

CTEs with data modifying statements

INSERT statement
CTEs with INSERT statement 1
CTEs with INSERT statement 2
UPDATE statement
CTEs with UPDATE statement
DELETE statement
CTEs with DELETE statement
Nested CTEs with data modifying statements

Summary

Summary
Exercise 1
Exercise 2
Exercise 3
Congratulations

6.

Final Quiz

Test your knowledge in this final quiz

0/14

Introduction

Welcome
The Project table
The Employee table
The Allocation table

Quiz

Single CTE
Multiple CTEs
Nested CTEs
Nested CTEs with a subquery
Simple recursive CTEs
Complex recursive CTE – trees
Complex recursive CTE – graphs
UPDATE with CTE
INSERT INTO with CTE

Summary

Congratulations

Reviews (13)

Average rating

4.66 / 5

☆☆☆☆☆
★★★★★

15 ratings

Details

5 stars

81%

4 stars

6%

3 stars

13%

2 stars

0%

1 stars

0%