SQL | Recursive CTE | Practical Examples and 5 Use Cases | Sequences | Hierarchies

In this tutorial, we understand the concept of SQL Recursive CTEs and explore its multiple use cases.
Examples in this tutorial -
1) Sequence of Numbers
2) Sequence of Dates
3) Sequence of Alphabets
4) Extracting each character of a string
5) Bill of Material Hierarchy
The Employee Manager Hierarchy tutorial can be found here -
• SQL Interview Question...
Other examples of Recursive CTEs
How to generate date records for a Calendar table?
• SQL Query | How to gen...
How to generate missing date records?
• SQL Query | How to gen...
How to extract Odd/Even numbered characters?
• SQL Query | How to ext...
All SQL statements related to this video tutorial can be found here-
know-star.blog...
Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.ne...
How to install SQL Server for practice?
• How to install SQL Ser...
Check out the complete list of SQL Query Interview Questions -
• SQL Query Interview Qu...
Best Data Science / Analytics / SQL courses
Learn SQL Basics for Data Science Specialization
imp.i384100.ne...
Beginners to Python Programming
skillshare.eqc...
Data Science and Business Analytics with Python
skillshare.eqc...
Get 40% OFF of Skillshare with code FLASH40 - October 2022
skillshare.eqc...
Data Science Fundamentals with Python and SQL Specialization
imp.i384100.ne...
Python for Everybody Specialization
imp.i384100.ne...
Google Data Analytics Professional Certificate
imp.i384100.ne...
Coursera Plus - Data Science Career Skills
imp.i384100.ne...
Please do not forget to like, subscribe and share.
For enrolling and enquiries, please contact us at
Website - knowstar.org/
Instagram - / learn.knowstar
Facebook - / knowstartrainings
Linkedin - www.linkedin.c...
Email - [e-mail]

[@LearnatKnowstar]

Register at the below link to get US $100 off for Coursera Plus membership between Sep 8 - Sep 29, 2022.
The Coursera Plus membership gets you access to unlimited courses and unlimited certifications!
imp.i384100.net/Ke51on

[@hv0032113]

This is a very good option if you are committed to completing multiple training and certifications in a year. Coursera certificates really matter and can give an edge to tour resume.

[@TomaszBI]

Brilliant! Best video on this subject!

[@LearnatKnowstar]

Glad it was helpful!

[@DanPrince2010]

Very helpful! Thank you for breaking it down. This was exactly what I needed to understand CTEs. :)

[@LearnatKnowstar]

Glad it was helpful.

[@kristyowens2284]

very clearly explained and excellent examples. Looking forward to more detailed videos like this 👍

[@LearnatKnowstar]

Thank you. More full course videos coming soon!

[@hv0032113]

Great topic. Superb explanation. You deserve more subscribers !

[@LearnatKnowstar]

Thank you 🙏

[@MethodOverRide]

Great video! Exactly what I was looking for to refresh myself on recursive ctes. Your videos are always helpful. 🤗

[@LearnatKnowstar]

Thank you so much

[@nikolaybaranov2213]

Thank you! Great case with hierarchy!

[@LearnatKnowstar]

Thank you 🙏

[@JulieWork]

This tutorial is amazing! For example, with the auto calendar, given that there's the DECLARE statement at the beginning, is there a way to turn this into a View so it can simply be run when needed? Or, must it be a Stored Proc? Thanks.

[@LearnatKnowstar]

Stored Procedures are best for this type of code.

[@JulieWork]

So, it's basically a while loop but in SQL format. I've been wanting to find out how to do for loop or while loop in SQL for a very long time! Thanks!

[@LearnatKnowstar]

Thank you

[@riddlex]

This is an excellent way to generate date range, however, I don't know if it would work in PostgreSQL/MySQL. A question was asked to me during an interview where the Recursive CTE was to be used. The question was to find the number of Sundays in the month of August 21. I didn't know how to approach it then, but Recursive CTE along with DATENAME function would have done the job.

[@LearnatKnowstar]

Thank you for sharing the scenario. We will cover it in one of our upcoming videos 🙏

[@dasarivenkatachalapathi6311]

Great explanation, can you please give information regarding semicolon in CTE

[@LearnatKnowstar]

Thank you. You can check out the video below - It has important tips about CTEs.
th-cam.com/video/e5WN-_7t7SY/w-d-xo.html

[@bprasad1950]

Hi Ma'am
By using of this cte .
I need rows starts like this type so how to do it..
001,002,003,...010,011,012,013,...,100

[@mangesh22121992]

At 13:04 stream time, Please let me know why output has "Z" included when we already mentioned ""< ''Z" " (should this not exclude "Z")?

[@ravishmahajan9314]

excellent video.

[@LearnatKnowstar]

Thank you

[@itikalaneelu9126]

excellent explaination

[@LearnatKnowstar]

Glad it helped

[@designanddecor8964]

Very helpful. Thanks.

[@LearnatKnowstar]

Thank you 🙏

[@hasanmougharbel8030]

hello there, god bless your efforts..I have a simple enquiry as new sql learner.
How are CTEs different from temporary tables?
Thanks for taking care of this.

[@LearnatKnowstar]

CTEs are not physical tables. They are temporary result sets. Temp tables normally refer to temporary physical tables created in tempdb database.

[@b.k.france78]

Thank you so much

[@LearnatKnowstar]

Glad it was helpful.

[@mankala5895]

I have a usecase as below
There are n number of nodes connected to each other,
Every output link of a node matches with the input link of another node
I have to make them join based on inputlinkid and outputlinkid
But the challenge here is I don't know how many nodes a flow has , so I have to create columns dynamically where each column represents a node name.
Can you please help me how to create dynamic columns while running recursive cte

[@briandennehy6380]

Thanks for this

[@LearnatKnowstar]

Glad it was helpful 🙏

[@darbardarbar9460]

Please make a video two parts 12 minutes

[@LearnatKnowstar]

Sure, will be posting shorter videos as well 👍