The SELECT statement supported by UnityJDBC has the following syntax.
SELECT [ALL | DISTINCT ] <exprList>
[FROM <tableList>]
[WHERE <condition>]
[GROUP BY <exprList>
[HAVING <condition>]
[ORDER BY <expr> [ASC | DESC],...]
[LIMIT <expr> [OFFSET <expr>]]
An <exprList> is a list of expressions. Each individual
expression <expr> may be a column identifier, a literal constant,
or some expression consisting of operators, functions, constants, and column
identifiers. Recall that a column identifier may often need to be prefixed by its
database name and table name.
A <tableList> is a list of table references. Each table
reference can be aliased using the AS operator. A table reference
may also be a named subquery such as SELECT * FROM (SELECT * FROM T1) AS R
WHERE R.val > 50.
A <condition> is a boolean condition that may contain multiple
subconditions related using AND, OR, and
XOR.
If the GROUP BY clause is used, no attributes should be present
in the SELECT <exprList> that are not in an aggregate function or
are GROUP BY attributes.
The HAVING <condition> filters groups and typically should
contain only aggregate functions.
The ORDER BY clause can order results on
any number of attributes in either ascending or descending
order.
The LIMIT clause allows paging of results.
The OFFSET clause determines the first row of the
result with the first row numbered as 1.
Some examples using the TPC-H schema follow. The database name for
these examples is 'OrderDB'.
Return all nations with their key and name:
SELECT OrderDB.Nation.n_nationkey, OrderDB.Nation.n_name FROM OrderDB.Nation;
Return the nations and their regions. Only return nations in the region name of 'AMERICA'. Note the use of table aliasing using AS.
SELECT N.n_nationkey, N.n_name, R.r_regionkey, R.r_name FROM OrderDB.Nation as N, OrderDB.Region as R WHERE N.n_regionkey = R.r_regionkey AND R.r_name = 'AMERICA';
Calculate the number of countries in each region. Only return a region and its country count if it has more than 4 countries in it. Order by regions with most countries.
SELECT R.r_regionkey, R.r_name, COUNT(N.n_nationkey) FROM OrderDB.Nation as N, OrderDB.Region as R WHERE N.n_regionkey = R.r_regionkey GROUP BY R.r_regionkey, R.r_name HAVING COUNT(N.n_nationkey) > 4 ORDER BY COUNT(N.n_nationkey) DESC;