KoddQL Tutorial

Table of Contents

• Selecting data
  • Projection
  • Restriction
  • Join
    • Cartesian product
    • Natural join
    • Inner join
    • Left inner join
    • Right inner join
    • Left outer join
    • Right outer join
    • Exclusive outer join
    • Full outer join
    • Equijoin
    • Tips on join
• Updating data
• Removing data
• Editing table
• Editing database
• General
  • Temporary variables
  • Temporary renaming

Selecting data

All selection queries must end with forwarding operator (->) pointing to RESULT constant.

Plain selection

To select all data from the table, you need to just specify table's name and forward it to RESULT.

table	->	RESULT

Projection

If you only need a part of table's columns, you can apply projection.

table	[field1, field2]	->	RESULT

Restriction

To select data by criteria, you need to use restriction.
Only those rows that match the condition will be returned.

Note: You are not limited to only one expression, you can unite multiple by using logical operators between them.

table	[field1 θ field2]	->	RESULT

List of operations:

• Comparisions
  • (>, <, ==, <>, >=, <=)
• Arithmethic
  • (+, -, *, /, %)
• Logical
  • (&, |, ^)
• Grouping
  • ( "expr0" θ ("expr1" θ "expr2") )
• Similarity
  • Like ("string" ~ "wildcard")
  • In range ("number" ~ "lower limit" & "higher limit")
• Relational division
  • ( : )

Join

To select data from multiple tables, you need to join them beforehand.

Note: You can access tables' columns by using L and R constants.

Cartesian product

Returns ordered pairs of all rows from each table.

tableLeft	X	tableRight	->	RESULT

Natural join

Joins tables based on their column names.

tableLeft	[]	tableRight	->	RESULT

Inner join

Joins tables and returns only those rows that match the condition.

tableLeft	<[L.field θ R.field]>	tableRight	->	RESULT

Left inner join

Joins tables and returns left table with rows from the right that do not match the condition.

tableLeft	>[L.field θ R.field]	tableRight	->	RESULT

Right inner join

Joins tables and returns right table with rows from the left that do not match the condition.

tableLeft	[L.field θ R.field]<	tableRight	->	RESULT

Left outer join

Joins tables and returns left table with rows from the right that match the condition.

tableLeft	[L.field θ R.field]>	tableRight	->	RESULT

Right outer join

Joins tables and returns right table with rows from the left that match the condition.

tableLeft	<[L.field θ R.field]	tableRight	->	RESULT

Exclusive outer join

Joins tables and returns rows that do not match the condition.

tableLeft	>[L.field θ R.field]<	tableRight	->	RESULT

Full outer join

Joins tables and returns all rows. Values of the rows that do not match will be set to NULL.

tableLeft	[L.field θ R.field]	tableRight	->	RESULT

Equijoin

Equijoin differs from normal join by only using equality comparision as it's criteria.
As such, equijoins are specified by a list of column names, by which tables will be joined. Every type of inner and outer joins have their respective equijoin versions.

tableLeft	[field0, field1]	tableRight	->	RESULT

Tips on join

To join more then two tables, you need to join them consequently:

tableFirst	[L.field θ R.field]	tableSecond	[L.field θ R.field]	tableThird	...	->	RESULT

Set operations

Union

tableLeft	UNION	tableRight	->	RESULT
tableLeft	|	tableRight	->	RESULT

Intersection

tableLeft	INTERSECTION	tableRight	->	RESULT
tableLeft	&	tableRight	->	RESULT

Difference

tableLeft	COMPLEMENT	tableRight	->	RESULT
tableLeft	\	tableRight	->	RESULT

Symmetric difference

tableLeft	DIFFERENCE	tableRight	->	RESULT
tableLeft	^	tableRight	->	RESULT

Updating data

By criteria

[field1, field2]	{value1, value2}	->	table	[θ]

All records

[field1, field2]	{value1, value2}	->	table

Creating data

Single row

table	AND	[field1, field2]	{value1, value2}	->	table

Multiple rows

table	AND	[field1, field2]	{	{value1, value2}	{value1, value2}	}	->	table

Removing data

By criteria

table	[θ]	->	DELETE

All data

table	[]	->	DELETE

Note: Beware to not write table -> DELETE, as it removes the table itself.

Editing table

Editing database

General

Temporary variables

Temporary renaming