Tutorial - user

Tutorial - maintainer

SQL reference

Unsupported SQL

External Resources


Tutorial - user


The goal of this tool is to provide a platform to help practicing the recursion in SQL. All calculations are done directly in the browser, therefore the tool does not require a connection to a database. It supports most of the commonly used functions and commands of SQL, including the recursion. For a list of all supported functions and an explanation on how to use them, please refer to the SQL reference below.

Recursion in SQL and in the tool

SQL:1999 introduced Common Table Expressions (CTE). A CTE is a temporary set which contains the result of a query. You can refer to this set with a query or it can refer to itself respectively. By referring to itself it is possible to make recursive queries. For a more detailed look at recursive queries in SQL, please refer to the official documentation by Microsoft.

In this tool, a recursive query can be executed step by step. While doing so, the current step, the corresponding code as well as the involved tables will be highlighted. This should help provide the user with a better understanding on how such a query works. It should also highlight the fact, that a recursion should not be considered as an incomprehensible construct but rather as a combination of its separate components, which are, usually, simple queries.

A query needs to meet a certain criteria to be recognized as a recursive query by the tool. If any of the following points is not fulfilled, the tool will not recognize the query as recursive and thus the step-by-step solution will not be available.

Tutorial - maintainer

Anybody can create and share data sets that can be used within the tool. This can be done via GitHub Gists, a free platform to share code snippets. Once created, such a data set can be imported into the tool by using its unique ID. It can be found at the end of the URL or at the top of the page as gist:xxxxxxxxxxxxxx.

Creating a data set

A data set is specified as a simple text file that is created as a Github Gist (the name of the gist does not matter). The RelaX - relational algebra calculator uses the same syntax for its data, therefore gists can be used in both tools.

The already included flight-database will be used as an example on how to create a compatible gist file for this tool. The full file can also be found here.

First, a group attribute has to be defined. It serves as the title of the database and is displayed in the 'Select Database' menu.

This attribute can consist of any character and is read until the first line break.

group: Flights simple

The next attribute is the description. It is optional and does not have to be included.

It is displayed below the editor and should be used to further describe your data set and to provide additional links to sources.

It can either be a single-line description, with syntax just like the in group attribute, or a multi-line description where brackets have to be used to indicate the start and end of the description.

Furthermore, the Markdown-Syntax is supported to easily convert the description to HTML code

Example 1 is a simple recursion to calculate all airports that can be reached from Vienna.
Example 2 is basically the same query that additionally counts the steps and calculates the path taken.

Next, one or more examples can be defined. This attribute is optional as well and can be omitted

If a data set includes examples, they can be inserted into the editor by using the respective button on the bottom right of the editor

The syntax with the brackets for multi-line descriptions is used again. To separate multiple examples, a semi-colon is used

The examples are inserted into the editor with the same format as used in the gist-file, including line breaks and tabulators

/* This query calculates all airports that can be reached from Vienna. */
WITH RecRel(departure,destination) AS
SELECT departure, destination FROM flight WHERE departure = "VIE"
SELECT step.departure, step.destination
FROM RecRel AS rec JOIN flight AS step ON (rec.destination = step.departure)

/* Calculates all airports reachable from Vienna, counts the steps and calculates the path taken. */
WITH RecRel(step,path,departure,destination) AS
SELECT 1, concat(departure," - ",destination),departure, destination FROM flight WHERE departure = "VIE"
SELECT rec.step+1, concat(rec.path," - ",step.destination),step.departure, step.destination
FROM RecRel AS rec JOIN flight AS step ON (rec.destination = step.departure)

Finally, the actual data has to be defined. This is similar to defining variables in several programming languages.

The name of the variable is in this case the name of the table and on the inside, the columns are defined.

The first line defines the name and the data type of the column. The data type is optional, so each column is either defined as name or name:data type.

Note that only 'string' and 'number' are supported data types, everything else is treated as a string.

In the following rows the entries of the table can be defined, where single quotes should be used for strings

flight =
departure:string, destination:string, id:number
'VIE', 'FRA', 0
'FRA', 'LHR', 1

airport =
location:string, country:string, iata:string, id:number
'Vienna', 'Austria', 'VIE', 0
'Frankfurt', 'Germany', 'FRA', 1

SQL reference

This tool calculates queries by itself, therefore not all functions from SQL can be used within the tool. In the following, every possible function and command will be listed and explained further, if necessary.

Further Explanation

In order to avoid any lack of clarity, some functions and characteristics of the tool will be further explained:

Unsupported SQL

For completeness, here is a list of functions that are not supported by the tool. Mostly because they are not needed or due to technical reasons

External resources