KoddQL Tutorial

Table of Contents

• Selecting data
  • Projection
  • Restriction
  • Join
    • Cartesian product
    • Natural join
    • Inner join
    • Left outer join
    • Right outer join
    • Full outer join
    • Equijoin
    • Tips on join
• Updating data
• Removing data
• Table
  • Creating table
    • From columns
    • From existing table
  • Editing table
    • Adding column
    • Editing column
    • Removing column
  • Removing table
• 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 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

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	MINUS	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.

Table

From KoddQL's perspective, tables are a set of columns. Columns consist of name, datatype and several or none constraints.

Datatype	Description
BOOLEAN	Logical datatype with NULL value
BYTE	1 byte signed number
SHORT	2 byte signed number
INTEGER	4 byte signed number
LONG	8 byte signed number
SINGLE	4 byte floating point number
DOUBLE	8 byte floating point number
CHAR(letter size)[string length]	String data
BLOB(letter size)[string length]	Binary data

Constraint	Description
NOT NULL	Values of this column cannot be set to NULL.
UNIQUE	Values of this column cannot repeat.
PRIMARY KEY	Columns marked with this constraint are supposed to uniquely represent this table.
FOREIGN KEY	This column is supposed to reference PRIMARY KEY from another table.
DEFAULT("VALUE")	Value this column will have, if not assigned directly.

Creating table

From columns

<	name	|	datatype	|	(constraint,)	>	->	table

<	name	|	datatype	|	(constraint,)	>	UNION	<	name	|	datatype	|	(constraint,)	>	->	table

From existing table

<	name	|	datatype	|	(constraint,)	>	UNION	existingTable	->	table

Editing table

Adding column

<	name	|	datatype	|	(constraint,)	>	UNION	table	->	table

Editing column

<	name	|	datatype	|	(constraint,)	>	->	table	[name]

Removing column

table	[name]	->	REMOVE

Removing table

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

Database

General

Temporary variables

Temporary renaming