Recap of Common Table Expressions (CTE)

1. Recap of Common Table Expressions (CTE)

Welcome to the course!

2. Outline of the course

Have you ever thought about recursion and how to use it? How to query tables recursively with Common Expression Tables, or how to simplify querying hierarchical data structures to minimize writing queries? After this course, we'll be able to answer these questions and many more. We'll apply the knowledge to real-world examples such as querying company organizations, assembling a car, and planning flight plans. In this lesson, we'll concentrate on the common table expressions, or CTE, needed to create recursive queries by learning how to define and use them.

3. What is a CTE?

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, it is specified with a WITH statement and the name for the temporary result set. In this example, we define it with the name CTEtable. We can execute a query within the CTE, which returns a result set. This result set can be followed by a SELECT statement. In our example, we select the entire result set.

4. Use of CTEs

We can use CTEs in the following ways. Firstly, a CTE can be used to manage complicated queries. It can be referenced with the SELECT, INSERT, UPDATE, and the DELETE statement, and several CTEs can be defined within one WITH statement. These newly defined CTEs can then be combined with a UNION or JOIN. A CTE can substitute a view, if, for example, a user doesn't have sufficient rights. It can be used to self-reference a table, and finally, it can be used for recursion.

5. Define a CTE for an IT-organization

Let's apply CTEs to a real example to query a table of an IT-organization. In this example, we will use a multiple CTEs definition in a single query. The first line creates the CTE named JOBS with the result set ID, SurName, and JobTitle. It's possible to set the result set of a CTE by specifying the fields inside parentheses at the WITH AS definition or just by querying them inside the CTE. Next, we select the ID, Name, and Position of all employees having a position with Manager in the job title. Similar to creating a view, we could select a table and correspond the selected fields to the result set of the CTE. The selected table in the CTE could be used for selecting, filtering, or aggregating data later on.

6. Define a CTE for an IT-organization

Next, we define a second CTE. This one is used to query the salary table to get all salaries above 10000. It is defined by the name SALARIES and has the result set ID and Salary. We have now defined the two CTEs, JOBS and SALARIES. The final task is to combine them.

7. Define a CTE for an IT-organization

Here we have the Jobs and Salaries CTEs we already created. As we can see, we are using an inner join on the results of CTEs to get only matching records of both tables. The join is performed on the ID of the employees to get the names of the employee with their job titles and salary. The result could look like this. We have 3 Managers. Paul Smith, IT Manager, Adam Peterson Sourcing Manager and Anna Nilson Portfolio Manager, with salaries of 15000, 12500 and 10500.

8. Let's practice!

We have now seen how to use CTEs. Now let's practice!