StelsXML SQL Syntax
StelsXML
JDBC driver
supports
the following SQL statements and syntax:
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 [ 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 ]
* | expression [ [ AS ] columnAlias ] | tableAlias.*
{ [ schemaName. ] tableName
| ( select ) } [ [ AS ] newTableAlias ]
[ { { LEFT | RIGHT } [ OUTER ] | [ INNER ] |
CROSS | NATURAL }
JOIN tableExpression [ ON
expression ] ]
expression:
andCondition [ { OR
andCondition } [...] ]
condition [ { AND condition } [...] ]
operand [ conditionRightHandSide ] | NOT condition | EXISTS ( select )
compare { { { ALL | ANY | SOME } (
select ) } | operand }
| IS [ NOT ] NULL
| BETWEEN operand AND
operand
| IN ( { select | expression
[,...] } )
| [ NOT ] LIKE operand [ ESCAPE
string ]
| [ NOT ] REGEXP operand
<> | <= | >= | = | < | > | !=
summand [ { || summand } [...] ]
summand:
factor [ { { + | - } factor }
[...] ]
term [ { { * | / } term } [...] ]
{ int | expression } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
value
| columnName
| ?[ int ]
| NEXT VALUE FOR sequenceName
| function
| { - | + } term
| ( expression )
| select
| case
| caseWhen
| tableAlias.columnName
{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | quotedName
quotedName: "anything"
CASE expression
{ WHEN expression THEN
expression } [...]
[ ELSE expression ] END
CASE { WHEN
expression THEN
expression} [...]
[ ELSE expression ] END
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
array: ( expression [,...] )
null: NULL
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;
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;
Updates data in a table.
UPDATE tableName [ [ AS ]
newTableAlias ] SET { columnName = { DEFAULT
| expression } } [,...]
[ WHERE expression ]
Example:
UPDATE test SET a=1 WHERE id=2;
Deletes rows form a table.
DELETE FROM tableName [ WHERE expression ]
Example:
DELETE FROM test WHERE a=1 OR b=3;
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
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
Creates a new table (XML file).
CREATE TABLE
tableName (
columnDefinition [,...] )
CREATE TABLE tableName
USING SPECIFICATION
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;
Creates a new view.
CREATE VIEW viewName
AS select
Example:
CREATE VIEW v1 AS SELECT * FROM test;
Creates a new index.
CREATE newIndexName
ON tableName ( indexColumn
[,...] )
indexColumn:
Example:
CREATE INDEX index_1 ON test(int_col);
columnName [ ASC | DESC ] [ NULLS {
FIRST | LAST } ]
Drops an existing table.
DROP TABLE tableName
Example:
DROP TABLE test;
Drops an existing view.
DROP
TABLE
viewName
Example:
DROP VIEW v1
Drops an existing index.
DROP
INDEX indexName
Example:
DROP INDEX index_1;
Related Documents: