SQL Tutorial: Recap of Common Table Expressions (CTE)

Want to learn more? Take the full course at https://learn.datacamp.com/courses/hierarchical-and-recursive-queries-in-sql-server at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. --- Welcome to the course hierarchical and recursive queries in SQL server. Before we start with this video, I want to give you a short outline of the course and what you can expect of it. Have you ever thought about what is recursion and how to use it? How you could query tables recursively with Common Expression tables or how you could simplify querying hierarchical data structures to minimize writing queries? After this course, you will be able to answer these, and many more questions. You will test your knowledge applying it to Real-World examples such as querying company organizations, assemble a car or planning flight plans. In this lesson, we concentrate on common table expressions (CTEs) needed to create recursive queries. You will learn how to define them and how to use them. So what is a CTE? A CTE specifies a temporary named result set. This is derived from a simple query and defined within the execution scope of a single statement. In general, a CTE is specified with a WITH statement and the name for the temporary result set. In this example, the CTE is defined by the name CTEtable. Within the CTE a query of a table could be executed. The CTE returns always a result set, and can be followed by a SELECT statement. In our example, we select the entire result set. We can use CTEs in the following ways. Firstly, a CTE can be used to manage complicated queries. A CTE can be referenced with the SELECT, the INSERT, the UPDATE, and the DELETE statement and within one WITH statement several CTEs can be defined. These newly defined CTEs can then be combined with a UNION or JOIN. Moreover, a CTE can substitute a view, if, for example, a user doesn't have sufficient rights, a CTE can be used to self-reference a table and finall