KoddQL Tutorial

Table of Contents

• Selecting data
  • Projection
  • Restriction
  • Join
    • Cross join
    • 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.

Cross join

tableLeft	X	tableRight	->	RESULT

Natural join

tableLeft	[]	tableRight	->	RESULT

Inner join

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

Left inner join

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

Right inner join

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

Left outer join

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

Right outer join

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

Exclusive outer join

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

Full outer join

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

Removing data

Editing table

Editing database

General

Temporary variables

Temporary renaming