Supported functionality related to Data Manipulation Language of SQL.

Syntax is case insensitive following this template:

KEYWORDS required_input [optional_input];

If you need to escape database or table or column, because it is named the same as our keyword, (e.g. if you have column named 'count' in your table and COUNT() is also a function), use brackets `[]`

to escape it. See the example below:

SELECT t.[count] FROM My_Table AS t LIMIT 10;

Shows loaded databases in memory.

SHOW DATABASES;

Shows tables from database. It is **not** needed to use database to execute this query, but it is possible.

SHOW TABLES FROM my_database;

Also this syntax is valid:

SHOW TABLES IN my_database;

Shows columns from table. It is needed to use database to execute this query.

SHOW COLUMN FROM my_table;

Also this syntax is valid:

SHOW COLUMNS IN my_table;

SELECT expression [AS alias] [, expression [AS alias] ...]FROM table [AS alias][WHERE expression][GROUP BY expression, ...][ORDER BY <expression> [ ASC | DESC ] , ...][LIMIT number [OFFSET number]];

Allows you to tag column or table with alias - custom string that you can use from that point instead of original name.

Allows you to group result rows into groups with the same values of the columns defined after GROUP BY keywords. Often used with aggregate functions.

Allows you to get the specified number of records from the whole result.

Allows you to skip the specified number of records from the result.

Sorts the result of a query according to the columns defined after ORDER BY keyword in ascending or descending order (can be specified). Default is ascending order.

Allows you to filter the records according to the specified conditions.

The INNER JOIN keyword selects records that have matching values in both tables. This is the default type of JOIN so it is not needed to write keyword 'INNER'. This implementation of INNER JOIN is very slow, because it is implemented as nested loop join. Also, there are some bugs, e.g. in some combinations of data types, it returns wrong results. We have decided not to improve this type of join but to rewrite it completely so it is much faster and bug free. The completely new type of JOIN will be released in December 2019 (see roadmap).

SELECT column_1 [, column_2, ...]FROM table1[INNER] JOIN table2ON table_1.column_name = table_2.column_name;

INSERT INTO my_table (column_1 [, column_2, ...])VALUES (value_1 [, value_2, ...]);

When inserting string values via INSERT INTO, use double quotation marks.

INSERT INTO my_table (string_column) VALUES ("string value");

Operator | Functionality |

+ | Add |

- | Subtract |

* | Multiply |

/ | Divide |

% | Modulo |

& | Bitwise AND |

| | Bitwise OR |

^ | Bitwise XOR |

= | Equal |

> | Greater |

< | Less |

>= | Greater Or Equal |

<= | Less Or Equal |

<> | Not Equal |

!= | Not Equal |

>> | Left Shift |

<< | Right Shift |

AND | Logical AND |

OR | Logical OR |

NOT | Logical NOT |

Geospatial functions currently work in planar (Euclidean) mode. Supported types are Point and Polygon. When geo column is selected, it is returned and displayed in well known text format, e.g. `POINT(10.8 14)`

, `POLYGON((30.89 10, 40 40.2, 20 40, 10 20, 30.89 10))`

.

Note: Point and Polygon are keywords in SQL so tables or columns have to be named differently.

Returns bool value whether point is in polygon. Both combinations are valid: `GEO_CONTAINS(wkt_point_or_column, wkt_polygon_or_column)`

`GEO_CONTAINS(wkt_polygon_or_column, wkt_point_or_column)`

.

Example:

SELECT geo_point FROM geo_tableWHERE GEO_CONTAINS(POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),geo_point);

Calculates intersection of two polygons. Meaning that every point in the intersection appears in both first and second polygon. Returning type is Polygon.

`GEO_INTERSECT(wkt_polygon_or_column, wkt_polygon_or_column)`

Example:

SELECT GEO_INTERSECT(POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),geo_polygon)FROM geo_table;

Calculates union of two polygons. Returning type is polygon.

`GEO_UNION(wkt_polygon_or_column, wkt_polygon_or_column)`

Typical usage:

SELECT GEO_UNION(POLYGON((17.0779 48.1303, 17.0912 48.1303, 17 48, 17.0779 48.1303)),geo_polygon)FROM geo_table;

Calculates the absolute value of the numeric expression or number.

ABS(numeric_expression)

Calculates arc cosine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ACOS(numeric_expression)

Calculates arc sine of the numeric expression or number. This numeric expression or number needs to be greater than -1 and less than 1.

ASIN(numeric_expression)

Calculates arc tangent of the numeric expression or number.

ATAN(numeric_expression)

Calculates arc tangent of two numeric expressions or numbers.

ATN2(numeric_expression_1, numeric_expression_2)

Calculates average value of the numeric expression.

AVG(numeric_expression)

Calculates an integer value from the numeric expression or number, so the new value is the smallest integer value that is greater or equal to the input numeric expression or number.

CEIL(numeric_expression)

Counts the number of records.

COUNT(expression)

Calculates cosine of the numeric expression or number.

COS(numeric_expression)

Calculates cotangent of the numeric expression or number.

COT(numeric_expression)

Calculates Euler's number raised to the power of the input numeric expression or number.

EXP(numeric_expression)

Calculates an integer value from the numeric expression or number, so the new value is the greatest integer value that is less or equal to input numeric expression or number.

FLOOR(numeric_expression)

Calculates the natural logarithm of the input numeric expression or number or if there are two input numeric expressions or numbers, calculates logarithm of the first numeric expression or number to the base defined by the second numeric expression or number. The input numeric expression or number needs to be greater than 0 and base numeric expression or number needs to be greater than 1.

LOG(numeric_expression[, base])

Calculates the logarithm of the input numeric expression or number to the base of 10. The input numeric expression or number needs to be greater than 0.

LOG10(numeric_expression)

Returns the maximum value.

MAX(numeric_expression)

Returns the minimum value.

MIN(numeric_expression)

Provides the value of π (3.141592F).

PI()

Calculates the first input numeric expression or number raised to the power of the second numeric expression or number.

POW(base, exponent)

Rounds the input numeric expression or number to the number of decimal input.

ROUND(numeric_expression, decimal)

Calculates the n-th root from the input numeric expression or number.

ROOT(numeric_expression, n)

Returns the sign of the numeric expression or number.

SIGN(numeric_expression)

Calculates sine of the numeric expression or number.

SIN(numeric_expression)

Calculates square root of the input numeric expression or number.

SQRT(numeric_expression)

Calculates square of the input numeric expression or number.

SQUARE(numeric_expression)

Calculates the sum of the input numeric expression.

SUM(numeric_expression)

Calculates tangent of the input numeric expression or number.

TAN(numeric_expression)

Converts a value to a specified data type. The possible casting combinations are:

Bool → Bool, Double, Float, Int, Long, String

Double → Bool, Double, Float, Int, Long, String

Float → Bool, Double, Float, Int, Long, String

Int→ Bool, Double, Float, Int, Long, String

Long → Bool, Double, Float, Int, Long, String

String → Double, Float, Geo_Point, Int, Long, String

Geo_Point → String

Geo_Polygon → String

CAST(some_value AS data_type)

**Two more casting combinations will be added in the near future (string to bool, string to geo_polygon).**

Allows you to get the not null values.

SELECT column_1 [, column_2, ...]FROM tableWHERE column IS NOT NULL;

Allows you to get the null values.

SELECT column_1 [, column_2, ...]FROM tableWHERE column IS NULL;

**FORMAT OF THE DATE TYPE:***year-month-day hour:minute:second*

Returns the datetime in human readable format (*year-month-day hour:minute:second*) from Integer64 data type.

DATE(datetime_represented_as_integer64)

Also addition of two datetimes represented as Integer64 would work inside this function like this:

DATE(datetime1_represented_as_integer64 + datetime2_represented_as_integer64)

Returns the day from the input date, which is in the range from 1 to 31.

DAY(date)

Returns the hour from the input date, which is in the range from 1 to 24.

HOUR(date)

Returns the minute from the input date, which is in the range from 1 to 60.

MINUTE(date)

Returns the month from the input date, which is in the range from 1 to 12.

MONTH(date)

Returns the current date and time.

NOW()

Returns the seconds from the input date, which is in the range from 1 to 60.

SECOND(date)

Syntax consists from:

KEYWORDS required_input [optional input];

Merges two strings together.

CONCAT(string_1, string_2)

Extracts specified number of chars from left side from the input string.

LEFT(string, charCount)

Returns the length of the input string.

LEN(string)

Transforms the input string to lower case.

LOWER(string)

Removes leading spaces from the input string.

LTRIM(string)

Reverses the input string.

REVERSE(string)

Extracts the specified number of characters from the right side from the input string.

RIGHT(string, characters_count)

Removes trailing spaces from the input string.

RTRIM(string)

Transforms the input string to upper case.

UPPER(string)