Relational Junction XML SQL Syntax

Relational Junction XML JDBC driver supports the following SQL statements and syntax:

SELECT
INSERT
UPDATE
DELETE

SAVE TABLE
SAVE QUERY
CREATE TABLE
CREATE VIEW
CREATE INDEX

DROP TABLE
DROP VIEW
DROP INDEX

SELECT

Selects data from a table or multiple tables. GROUP BY groups the the result by the given expression(s). HAVING filter rows after grouping. ORDER BY sorts the result by the given column(s) or expression(s). UNION combines the result of this query with the results of another query.
LIMIT limits the number of rows returned by the query, OFFSET specified how many rows to skip. SAMPLE_SIZE limits the number of rows read for aggregate queries. Multiple set operators (UNION / INTERSECT / MINUS/ EXPECT) are evaluated from left to right.


select:
SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] 
[ WHERE expression ]
[ GROUP BY expression [,...] ] 
[ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT }select ] 
[ ORDER BY order [,...] ]
[ LIMIT expression [ OFFSET expression ] 
[ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]

selectExpression:
* | expression [ [ AS ] columnAlias ] | tableAlias.*

tableExpression:
{ [ schemaName . ] tableName | ( select ) } [ [ AS ] newTableAlias ]
[ { { LEFT | RIGHT } [ OUTER ] | [ INNER ] |  CROSS | NATURAL }
JOIN tableExpression [ ON expression ] ]

expression:
andCondition [ { OR andCondition } [...] ]

andCondition:
condition [ { AND  condition } [...] ]

condition:
operand [  conditionRightHandSide ] | NOT condition | EXISTS ( select )

conditionRightHandSide: 
compare { { { ALL | ANY | SOME } ( select ) } | operand }
| IS [ NOT ] NULL
| BETWEEN operand AND  operand
| IN ( { select | expression [,...] } )
| [ NOT ] LIKE operand [ ESCAPE string ]
| [ NOT ] REGEXP operand

compare: 
<> | <= | >= | = | < | > | !=

operand:
summand[ { ||summand} [...] ]

summand:
factor [ { { + | - } factor } [...] ]factor[ { { + | - } factor } [...] ]

factor:
term [ { { * | / } term } [...] ]

order:
 { int | expression } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

term: 
value
columnName
| ?[ int ]
| NEXT VALUE FOR sequenceName
| function
| { - | + } term
| ( expression )
| select
| case
| caseWhen
| tableAlias . columnName

name
{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | quotedName

 quotedName: "anything" 

alias: name

case:
CASE expression { WHEN expression THEN expression } [...]
[ ELSE expression ] END

caseWhen:
CASE { WHEN expression THEN  expression } [...]
[ ELSE expression ] END

value:
string | dollarQuotedString |  hexNumber | int | long | decimal | double
| date | time |  timestamp | boolean | bytes | array |
null

string: 'anything'
dollarQuotedString:  $$anything$$
hexNumber: [ + | - ] 0xhex
int: -2147483647 to 2147483647
long: -9223372036854775808 to 9223372036854775807
decimal: [ + | - ] number [ . number ]
double: $$anything$$
date: DATE 'yyyy-MM-dd'
time: TIME 'hh:mm:ss'
timestamp:  TIMESTAMP 'yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]'
boolean: TRUE | FALSE
bytes: X' hex '
array: ( expression [,...] )
null: NULL
number: digit [...]
hex: { {  digit | a-f | A-F } { digit | a-f | A-F } } [...]
digit: 0-9

Example:
SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM test GROUP BY a HAVING AVG(a) > 30;
SELECT name FROM salesreps WHERE (rep_office IN ( 22, 11, 12 )) OR (manager IS NULL AND hire_date >= PARSEDATETIME('01-05-2002','dd-MM-yyyy') OR (sales > quota AND NOT sales > 600000.0);
SELECT city, target, sales FROM offices WHERE region = 'Eastern' AND sales > target ORDER BY city;
SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodid;
SELECT * FROM prices ps, products prod WHERE prod.prodid = ps.prodid;

INSERT

Inserts a new row / new rows into a table.

INSERT INTO tableName [ ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select

Example:

INSERT INTO salesreps (name, age, empl_num, sales, title, hiredate) VALUES (‘Henry Smith’, 35, 111, NULL, ‘Sales Mgr’, PARSEDATETIME(’01:12:2002',’dd:MM:yyyy’));

INSERT INTO test SELECT * FROM test2;

UPDATE

Updates data in a table.

UPDATE tableName [ [ AS ]  newTableAlias ] SET { columnName = { DEFAULT | expression } } [,...]
[ WHERE expression ]

Example:
UPDATE test SET a=1 WHERE id=2;

DELETE

Deletes rows form a table.

DELETE FROM  tableName [ WHERE  expression ]

Example:
DELETE FROM test WHERE a=1 OR b=3;

SAVE TABLE

Writes table records into some XML file with specified parameters. Parameter “fileName” specifies a file which table data will be saved in. Parameter “specName” defines a table specification in the schema file which should be applied to arrange data while writing a file.


SAVE TABLE tableName [ AS fileName ] [ USING SPECIFICATION specName ]

Example:
SAVE TABLE employees AS “employees_html_table.html” USING SPECIFICATION employees_html_table

SAVE QUERY

Writes the result set of a SELECT query exectution into some XML file with specified parameters. Parameter “fileName” specifies a file which table data will be saved in. Parameter “specName” defines a table specification in the schema file which should be applied to arrange data while writing a file.


SAVE ( selectQuery )  AS fileName [ USING SPECIFICATION specName ]

Example:
SAVE (SELECT * FROM employees WHERE title LIKE ‘%programmer’) AS “employees_programmers.xml” USING SPECIFICATION employees

CREATE TABLE

Creates a new table (XML file).

CREATE TABLE  tableName (   columnDefinition  [,...] ) 
CREATE TABLE tableName USING SPECIFICATION

columnDefinition:

columnName  dataType

Example:
CREATE TABLE “new_table.xml” (int_col INT, long_col LONG, float_col REAL, double_col DOUBLE, str_col VARCHAR(20), date_col DATETIME, bool_col BOOLEAN, num_col DECIMAL(15,2));
CREATE TABLE “new_table2.xml” USING SPECIFICATION;

CREATE VIEW

Creates a new view.

CREATE VIEW [IF NOT EXISTS]  viewName AS select

Example:
CREATE VIEW v1 AS SELECT * FROM test;

CREATE INDEX

Creates a new index.

CREATE INDEX [IF NOT EXISTS] newIndexName ON tableName ( indexColumn [,...] )

indexColumn:

columnName [ ASC | DESC ] [ NULLS {  FIRST | LAST } ]

Example:
CREATE INDEX index_1 ON test(int_col);

DROP TABLE

Drops an existing table.

DROP TABLE tableName

Example:
DROP TABLE test;

DROP VIEW

Drops an existing view.

DROP TABLE viewName

Example:
DROP VIEW v1

DROP INDEX

Drops an existing index.

DROP INDEX indexName

Example:
DROP INDEX index_1;