1. Solve recursive maths problems
In this lesson, we will use SQL to solve two common mathematical recursive problems.
2. The two maths problems
These two mathematical problems are the following. Firstly, we count numbers from one to the defined termination condition. The second problem is to calculate the sum of potencies. We will go over the recursive definition for both. Each problem will be defined by an initialization step and the recursive step. Moreover, for all problems a pseudo code definition is provided. This 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.
3. Counting numbers
Let's look at counting numbers recursively. We will need to count from one to the termination condition. This problem could be defined by the initialization of a number with one for the first iteration. For all following iterations, one is added until the termination condition is met. The pseudo code looks as follows. We have to define the initial query and the recursive query according to the recursive definition at the top of this slide.
Here is an example of how it would work. We start with one, and add one in each iteration. For four iterations, this leads to one plus one plus one plus one giving us a final result of four.
4. The sum of potencies
The next problem is the sum of potencies. As in the previous example, the number for the first iteration is initialized with one. For the next steps, we calculate for the number of the iteration with the power of the iteration and add this value to the previous number. We do this until the termination condition is met.
The pseudo code for this example looks as follows. We define the initial query as number equals one and the recursive query according to the definition of the recursive function, number equals number plus iteration to the power of the iteration. Considering three iterations, we calculate the sum of one plus two to the power of two resulting in five. Then, five plus three to the power of three. The result is then 32.
5. Let's practice!
Now we know how two mathematical problems could be described by recursive functions. The task is now to solve these problems with recursive CTEs by changing the pseudo code into real code. These tasks are not common for using databases, but demonstrate very well the principle of recursion. Let's practice.