SQL Tutorial: Solve recursive maths problems

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.

---
In this last lesson, we will use SQL to solve recursive problems of mathematics. You are going to solve two problems which you solve in every programming course when learning about recursion.

These 2 mathematical problems are the following. Firstly, we simply count numbers from 1 to the defined termination condition. The second problem is to calculate the sum of potencies. For each problem, the recursive definition is provided and explained. Each problem is defined by an initialization step and the recursive step. Moreover, for all problems a pseudo code definition is provides which is an informal high-level description of the operating principle of the query. The pseudo code uses the structural conventions for the use of SQL, but is intended for human reading rather than machine reading.

The first problem is to count from 1 to the termination condition. This problem could be defined by the initialization of a number with 1 for the first iteration. For all following iterations, 1 is added to this number until the termination condition is met. The pseudo-code looks as follows. You have to define the initial query and the recursive query according to the definition of the recursive function. This can be demonstrated through an example. You simply need to add 1 each iteration. For four iterations, this leads to 1+1+1+1 with a final result of 4.

The next problem is the sum of potencies. As in the previous example, the number for the first iteration is initialized with 1. For the next steps, you calculate for the number of the iteration the power of the iteration and add this value to the previous number. You do this until the termination condition is met. The pseudo-code for this example looks as follows. You have to define the initial query and the recursive query according to the definition of the recursive function. For example for 3 iterations, you calculate the sum of 1 plus 2 to the power of 2 and 3 to the power of 3. The result is then 14.

Now you know how two mathematical problems could be described by recursive functions. The task is now to solve these problems with recursive CTEs. These tasks is uncommon for using databases, but demonstrate very well the principle of recursion. Keep in mind that you will always have to define the anchor member representing the initialization step and the recursive member representing the recursive step. Let's practice.

#DataCamp #SQLTutorial #Hierarchical #Recursive #Queries #SQLServer