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.
A CTE is necessary because it is the only way to make recursive queries in SQL.
- Union All
In SQL, a recursion will only return the desired result, if initialization and step case are combined by using the Union All operator.
- Recursive Reference and Join in step case
The seconds Select-Statement in the CTE has to refer to itself in some way (the actual recursion). Additionally, this statement also has to contain a Join to guarantee that the result can actually grow with every step
- Selecting With-Clause
The outer query has to select at least one column of the just calculated recursion. In reality, this point would not be necessary - it is rather to make the user aware, that the final result of the recursion can only be displayed with this query.
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
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)
SELECT * FROM RecRel;
/* 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)
SELECT * FROM RecRel
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
departure:string, destination:string, id:number
'VIE', 'FRA', 0
'FRA', 'LHR', 1
location:string, country:string, iata:string, id:number
'Vienna', 'Austria', 'VIE', 0
'Frankfurt', 'Germany', 'FRA', 1
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.
- Select (Distinct) - From - Where
- And and Or
- Order By
- Aliases with As
- Joins with Using/On
- Left/Right/Full/Cross/Natural Join
- Group By and Having
- Union/Except/Intersect (All)
- Aggregate functions (AVG, COUNT, MIN, MAX, SUM)
- String concatenation with concat() in Select
- Basic calculations and modulo in Select
- Recursive Queries
In order to avoid any lack of clarity, some functions and characteristics of the tool will be further explained:
It is not necessary to always specify the table in queries. If the table can clearly be determined, the name of the column is enough. However, if a column does not have a unique name, it can't be assign definitely to a table automatically and it has to be specified as table.column.
Joins of any form will be executed from left to right. This is important when making queries with multiple joins because a different type of join might return different column names and furthermore lead to a wrong result. For example, a Join with Using only returns one column while a Join with On returns two columns with the respective prefix of its table. It is advised to mix different types of joins when making queries, if possible.
- Group By and Having
Having can only be used in combination with Group By and only if it follows directly after the Group By statement.
- String concatenation with concat()
Any combination of columns and strings can be concatenated in the Select part of the query. This is done by using the concat() function.
SELECT concat("This is flight ",id," to ",destination) FROM flight
- Basic calculations and modulo
Calculations with decimal numbers can directly be declared as a column. Alternatively, a calc() function, similar to concat(), can be used.
SELECT 2*id FROM flight
SELECT calc(2+id%2) FROM flight
Comments can be used just like in SQL.
-- single-line comment
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
- Is (Not) Null
- Complex data types (e.g. DATE)
- Data Definition Language (e.g. CREATE TABLE)
- Data Manipulation Language (e.g. INSERT INTO or UPDATE)
- Added limit for recursive steps (100) and a message if limit is reached
- Calculations for decimal numbers now also work without using calc() function
- Added navigation bar for feedback and help
- Updated step-by-step-solution
- Amount of rows are now displayed for every table (and increases in step-by-step solution automatically)
- Current state of recursion now displayed below buttons
- Slight changes to the buttons, eg 'initialization'-button at the beginning
- Updated 'show full tables': smaller button, max. 10 rows per table, amounts of rows displayed
- Example-buttons are now displayed for each dataset separately
- Updated gist-parser to also include example-queries
- Gist-parser: group, description (optional), example (optional), tables
- Same scheme as multi-line description with double-brackets to open and close
- Multiple examples are separated by a semicolon
- Example of examples:
example[[select * from flight; select * from flight where id > 2]]
- Step-by-step solution no longer disappears when changing query (before clicking submit)
- Multiple joins should now work as intended
- Translated example datasets and example queries from German to English
- Queries with limit >= 10 now work correctly
- Multi-line description for gist-files now finally working (also with markdown-syntax)
- Tables don't have to be specified in queries anymore
- Tool now looks at corresponding 'from'-part of query and tries to add missing tables, if possible
- Works for any query size -> single-select, union (2 selects), recursion (3 selects), etc...
- Added error messages for non-existing columns in selected tables, and for non-unique columns in selected tables
- Alerts for valid non-recursive input are now yellow to better distinguish between recursive/non-recursive input
- Added another step before step-by-step solution shows the final result
- Step-by-step solution now keeps linebreaks of query
- Like and Limit now work correctly again
- Fixed error which made certain Where-conditions not work
- Added success/error alerts when importing gist-files
- Updated gist-parser to parse every datatype (if unknown, its treated as string)
- Added warning when importing dataset with unknown type (example: 2923a30a474fdcb46bee)
- Updated gist-parser to use description with markdown
- Updated db-description below editor to correctly work again
- Extended step-by-step solution by highlighting corresponding part of the code
- Calculations in select not limited in order anymore, ie 1+id and id+1 should work the same
- Aggregate functions are no longer case-sensitive
- Step-by-step solution now ends correctly
- Added SQL-part to the result
- Selecting number now works correctly with 'as'
- Added possibility to select signed numbers (+/-)
- SQL comments can now be used in the editor
- Single-line: -- my comment
- Note: the editor only highlights single-line comments if there is a space after --, but it also works without
- /* my comment */ for multi-line
- Added datatype (if available) to columns
- Minor fixes in SQL- and gist-parser
- Multiple Where-conditions now work again as intended
- Loading gists (same/similar pattern as gists for relax-tool) now possible
- Gists also stored temporarily once loaded
- Tested with gist id 7d1871f79a8bcb4788de
- Displays db-information (if available) below editor
- Changing datasets should no longer cause problems with results
- Added parser for gist/local datasets
- Possible to choose from different datasets
- Improved semantic-check of query (covers alias and with-clauses correctly now)
- Added information on why step-by-step solution is not available
- Implemented more complex step-by-step functionality
- Added possibility to make calculations with +-*/% in select
- Calculate by using numbers or columns
- Start calculation with a number or use calc() (calc() should always work)
- example: calc(1+flug.id) or calc(flug.id+2*flug.id)
- Error messages now with error position
- Buttons for simple step-by-step solution (only for recursion)
- Added concat() function for select, combination of string (double quotes) and table.column as possible parameters
- Added another example to show how concat() works
- Parser update:
- Valid query no longer requires semicolon at the end
- Selecting a string-literal is now valid - double quotes necessary
- selecting a numeric-literal is now valid - decimal numbers combined with +,-,*,/
- Recursive queries should now work
- Fixed error where join-operators were still case-sensitive
- Query result now gets display directly on the page
- Fixed error that caused 'as' not to properly work when used in 'from'
- Non-recursive with-clauses should now work
- Parser now distinguishes between recursive and non-recursive queries
- Extended syntax- and semantic-checks to also include specific cases for with-clauses
- Added basic with-syntax to parser
- Parser: aggregate-functions now also for groupby/having
- Fixed error in SQLike that caused group by not to work on table.column
- groupby/having now usable in queries (having only works if followed directly after group by)
- Parser fixes for groupby/having
- Added aggregate-functions (avg,sum,count,min,max) to parser
- Aggregate function now usable in select
- Parser fixes for like/between
- '(not) between/like' can now be used in queries