StelsCSV SQL Syntax
StelsCSV
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;
Creates a new table.
CREATE TABLE
tableName (
columnDefinition [,...] )
Example:
CREATE TABLE new_table (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));
Creates a new view.
CREATE VIEW viewName
AS select
Example:
CREATE VIEW v1 AS SELECT * FROM test;
CREATE newIndexName
ON tableName ( indexColumn
[,...] )
indexColumn:
columnName [ ASC | DESC ] [ NULLS {
FIRST | LAST } ]
Example:
CREATE INDEX index_1 ON test(int_col);
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: