StelsXML SQL Syntax

 

StelsXML 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:

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 viewName AS select

 

Example:

CREATE VIEW v1 AS SELECT * FROM test;

 

 

CREATE INDEX

Creates a new index.

 

CREATE 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;

 

 

 

Related Documents: 

 

 

 

 

 

 [HOME]   [TOP]