Hierarchical and Recursive Queries in SQL Server
Learn how to write recursive queries and query hierarchical data structures.
Start Course for Free4 Hours13 Videos47 Exercises8,411 Learners3800 XP
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.Loved by learners at thousands of companies
Course Description
Do you want to query complex data structures in an iterative way? Do you have access to hierarchical data structures that need to be queried? This course will teach you the tools required to solve these questions. You will learn how to write recursive queries and query hierarchical data structures. To do this, you will use Common Table Expressions (CTE) and the recursion principle on a wide variety of datasets. You will, for example, dig into a flight plan dataset and learn how to find the best and cheapest connection between two airports. After completing this course, you will understand the principle of recursion, and be able to identify and create hierarchical data models.
- 1
Recursion and Common Table Expression (CTE)
FreeIn this chapter, you will learn about recursion and why it is beneficial to apply this technique. You will also refresh your knowledge about Common Expression Tables (CTE).
Recap of Common Table Expressions (CTE)50 xpA CTE is ... Find the wrong fact!50 xpA CTE for IT-positions100 xpA CTE for high-paid IT-positions100 xpIntroduction to recursion50 xpFacts about recursion.50 xpCalculate the factorial of 5100 xpHow to query the factorial of 6 recursively100 xpSolve recursive maths problems50 xpCounting numbers recursively100 xpCalculate the sum of potencies100 xp - 2
Hierarchical and Recursive Queries
In this chapter, you will learn about recursive CTEs, how to query hierarchical datasets, and finally, how to apply recursive CTEs on hierarchical data.
Introduction to recursive CTE50 xpRight or wrong?50 xpCreate the alphabet recursively100 xpCreate a time series of a year100 xpWorking with recursive queries50 xpWho is your manager?100 xpGet the hierarchy position100 xpWhich supervisor do I have?100 xpAnalyze the family tree50 xpGet the number of generations?100 xpGet all possible parents in one field?100 xp - 3
Creating Data Models on Your Own
In this chapter, you will learn how to create and modify database tables. You will learn about relational and hierarchical data models, how they differ, and when each model should be used.
How to work with tables50 xpCreating a table100 xpInserting and updating a table100 xpDeleting data and dropping table100 xpChanging a table structure100 xpWorking with relational data models50 xpDefining primary and foreign keys100 xpInserting data to person and order history100 xpGetting the number of orders & total costs100 xpWorking with hierarchical data models50 xpCreating a hierarchical data model100 xpNetworked and hierarchical models50 xpCreating a networked data model100 xp - 4
Hierarchical Queries of Real-World Examples
In this chapter, you will practice your learnings about hierarchical and recursive querying on real-world problems, such as finding possible flight routes, assembling a car, and modeling a power grid.
Travel planning for flight data50 xpGet the anchor & recursive member50 xpGet all possible airports100 xpAll flight routes from Vienna100 xpHow to assemble a car?50 xpCreate the parts list100 xpCreate a car's bill of material100 xpBuild up a BMW?100 xpModeling a power grid50 xpCreate a power grid100 xpGet power lines to maintain100 xpSummary of the course50 xp
Datasets
Power grid dataEmployee dataDepartment dataSalary dataBill of materials products dataBill of materials dataFlights dataCollaborators
Prerequisites
Functions for Manipulating Data in SQL Server
Dominik Egarter
Data Engineering Enthusiast
Dominik Egarter is IT architecture manager & data engineering enthusiast at Kelag, a power utility company in Austria. He is responsible for the data and information architecture and leads several data science and data engineering projects in the fields of predictive maintenance, telecommunication networks, power grid networks, and customer insights. Dominik regularly uses R and SQL to implement algorithms and apps (R Shiny apps) which could be used to enforce new business insights and makes data easy to use for everyone.
What do other learners have to say?
Join over 10 million learners and start Hierarchical and Recursive Queries in SQL Server today!
Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.