Chapter 6. SQL Syntax

Index:

1. Select
2. Insert
3. Update
4. Delete
5. CREATE CATALOG
6. CREATE DATABASE
7. DROP DATABASE
8. FLUSH DATABASE
9. COPY DATABASE
10. CLONE DATABASE
11. CREATE TABLE
12. DROP TABLE
13. ALTER TABLE
14. TRUNCATE TABLE
15. LOCK TABLE
16. UNLOCK TABLE
17. CREATE VIEW
18. DROP VIEW
19. CREATE SEQUENCE
20. DROP SEQUENCE
21. ALTER SEQUENCE
22. SET TRANSACTION
23. START TRANSACTION
24. COMMIT
25. ROLLBACK
26. SAVEPOINT
27. RELEASE SAVEPOINT
28. Call Procedure
29. EXPLAIN
30. Pseudo Tables
31. Pseudo Columns
32. Pseudo Variables
33. Split Multivalue Column Into Rows
34. Declare Variable
35. SET Variable
36. Common Table Expression
37. Comment Syntax
38. SQL States

Use ";" to separate multi sql statements. "reserved word" , `reserverd word`, [reserverd word] or {v 'reserved word'} is used to quote a column with reserved word name in SQL statement. The HXTT PDF supports using DATE, TIME, TIMESTAMP, GROUP, ORDER, KEY, DESC, SEQUENCE, INCREMENT, MINVALUE, MAXVALUE, CACHE, CHECK, CYCLE, OTHER, SET, INT, UNIQUE, LEVEL, RELEASE, INDEX, TOP, PACK, CALL, CONNECT, START, PRIMARY, COMMENT, TRANSACTION, REFERENCES, OFFSET, LIMIT, and UPDATE directly in SQL, although they're reserved words too.

SELECT [ALL | DISTINCT [ ON ( expression [, ...] ) ] ] | DISTINCTROW [TOP n [PERCENT]] select_list [INTO variable [, ...] | INTO OUTFILE 'outfileName' ] FROM table_reference_list [WHERE condition_expression] [[START WITH initial-condition] CONNECT BY [NOCYCLE] recurse-condition] [group_by_clause] [HAVING condition_expression] [union_clause] [order_by_clause] [fetch_clause] [FOR UPDATE]

select_list: { expression [ [AS] columnAlias] | table.* | * } [,...]

table_reference_list: {table_reference | table_join} [,...]

table_reference: { { table_name | subquery | json_table(jsonContent,xpath) | xml_table(xmlContent,xpath) | (table_join) | (VALUES expression[, ...] ) AS tableName(columnName[,...])} [ [AS] tableAlias] } [pivot_clause] [unpivot_clause]

table_name: { [catalog.]tableName} | {UNC path}

table_join: table_reference join_clause [join_clause,...]

join_clause: [NATURAL] { INNER | { [ LEFT | RIGHT | FULL] [OUTER] } } JOIN table_reference [ ON condition_expression | USING(column1,column2,...) ]

condition_expression: an expression which should return a boolean value.

pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_expr IN (columnValue_as_columnName_list) ) [ [AS] tableAlias ] . Pivot Samples, which includes dynamical column, ILIKE pattern, expression calculation.

unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (columnName_as_columnValue_list) ) [ [AS] tableAlias ] . Unpivot Samples, which includes dynamical column, ILIKE pattern, expression calculation.

group_by_clause: GROUP BY expression [,...] [WITH ROLLUP | CUBE]

union_clause: { UNION | INTERSECT | EXCEPT | MINUS } [ ALL ] select_statement [ union_clause ...]

order_by_clause: ORDER BY expression [ASC|DESC] [,...]

fetch_clause: [LIMIT [skipped_rows,] rows_count ] | [ [ OFFSET skipped_rows {ROW | ROWS} ] [ FETCH { FIRST | NEXT } rows_count { ROW | ROWS } ONLY ] ]

DISTINCT specifies that duplicate rows are discarded. A duplicate row is when each corresponding select_list column has the same value. DISTINCT has no effect on constant value, and pseudo columns(__rowid__).

expression: a complicated expression which can include parentheses, logical operator(NOT, AND, OR), positives/minus sign(+, -), arithmetical operator(+,-,*,/,%), string operator(|| (left string concat right string), +(left string concat right string), -(trim left string then concat rightstring), $(check whether left string is contained in right string), comparison operator(>, >=, =, ==, <=, <, !=, <>), bitwise logical operator(&, |, ~, ^, <<, >>), [NOT] LIKE pattern {escape 'escape_character'},[NOT] ILIKE pattern {escape 'escape_character'}, IS [NOT] NULL, BETWEEN ... AND ..., [NOT] IN, [NOT] EXISTS, [ALL|ANY|SOME] (subquery), [NOT] CASE WHEN expr THEN result [WHEN expr THEN result ...] [ELSE expr] END, CASE expr WHEN compare_expr THEN result [WHEN compare_expr THEN result ...] [ELSE result] END, SQL Escape Syntax({d 'yyyy-mm-dd'}, {t 'hh:mm:ss'}, {ts 'yyyy-mm-dd hh:mm:ss.f...'},{v 'reserved_word'}, {fn functionExpression}, {escape 'oneEcapeCharacter'}, {"varbinary" 'string'}), function(more than 200), aggregate function(MAX, MIN, AVG, COUNT, SUM, STD, STDDEV), constant(null, true, false, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, date, time, timestamp, number, string), column, parameter(?), subquery(single-row subquery, multirow subquery, multiple-column subquery, inline views, correlated subquery) and so on.

"SELECT select_list" can be used to get some calculated values through an one-row ResultSet. Column can be used in all sql except for "SELECT select_list". Parameter(?) can only be used in PreparedStatement.

For instance:

select val('123.222')
SELECT top 8 percent * FROM sz9010.page1;
select STRING_AGG(textline,'\r\n') as content from testpdf.page1; /* it will return the content for page 1*/
select * from testpdf.page4 where CoordinateY=120 and Words#>'{1,text}'='Not';
select CONVERT('123',SQL_INTEGER) as a,TTOC({d '1999-10-10'},1) as b, IFNULL(1,33) as c, 123 in(456,123,789,'abc') as d, EXTRACT(DECADE FROM '2001-02-16 20:38:40'), '88'+IIF(3<6,'1','0')
select encode('adsdfsdf');
select decode(encode('adsdfsdf'))+'';
select *,words from testpdf where CoordinateY<=100;
select PageNo,CoordinateX,CoordinateY,textline,array_ndims(words),words,words[1]->'x',words[1]->'y',words[1]->'text' from testpdf.page1;
select * from testpdf;
select * from "HXTT JDBC Products Corel Paradox Order Invoice";
select * from "001test";
select * from "001test".Page1;
select * from "001test".Page2;

Pivot Samples

pivot_clause: PIVOT ( aggregate_function(value_column) FOR pivot_expr IN (columnValue_as_columnName_list) ) [ [AS] tableAlias ]

HXTT PDF PIVOT supports dynamical column, ILIKE pattern, and expression calculation

pivot_clause is complicated to understand, and HXTT PDF will try to fix your sql so that you can get expected rersult. The following sql has different format but can get the similar result

LIKE pattern "%[a-c][^ac][12345]" can be used in column_list, and __value__, is a pseudo column as pivot value, which means column value.
With LIKE pattern and __value__, it's possible to produce any dynamic column name . For instance, quarter in ('%' as ’Q'+ __value__) . HXTT PDF allows to pivot multi values into a column, which means that you can get an ARRAY column. For instance, quarter in ('%' as 'Q') .

create table if not exists pivotdemo2(id int,name varchar(20),quarter int,profile int);
insert into pivotdemo2 values(1,'John',1,1000);insert into pivotdemo2 values(1,'John',2,2000);insert into pivotdemo2 values(1,'John',3,4000);insert into pivotdemo2 values(1,'John',4,5000);insert into pivotdemo2 values(1,'John',4,5000);insert into pivotdemo2 values(2,'Jack',1,3000);insert into pivotdemo2 values(2,'Jack',2,3500);insert into pivotdemo2 values(2,'Jack',3,4200);insert into pivotdemo2 values(2,'Jack',4,5500);
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4));
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as ’Q'+ __value__)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2" from pivotdemo2 pivot (sum(profile)for quarter in (1,2)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2" from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2",[3] as "quarter3",[4] as "quarter4" from pivotdemo2 pivot (sum(profile)for quarter in (1,2,3,4)) as pvt;
select id,name, [1] as "quarter1", [2] as "quarter2",[3] as "quarter3",[4] as "quarter4" from pivotdemo2 pivot (sum(profile)for quarter in ([1],[2],[3],[4])) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as 'Q')) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in ('%' as 'Q'+ __value__)) as pvt;
select id,name, * from pivotdemo2 pivot (sum(profile)for quarter in (1 as spring, '%' as 'Q'+ __value__)) as pvt;

CREATE TABLE if not exists PIVOT_TEST( EMP_NAME VARCHAR(10), RATE DECIMAL(8, 2) , HOURS DECIMAL(8, 2));
INSERT INTO PIVOT_TEST VALUES('Tom', 1.00, 1.5);INSERT INTO PIVOT_TEST VALUES('Tom', 1.33, 8);INSERT INTO PIVOT_TEST VALUES('Tom', 1.33, 1);INSERT INTO PIVOT_TEST VALUES('Jerry', 1.33, 8);INSERT INTO PIVOT_TEST VALUES('John', 1.00, 2);INSERT INTO PIVOT_TEST VALUES('John', 1.33, 1);INSERT INTO PIVOT_TEST VALUES('John', 1.33, 1.5);INSERT INTO PIVOT_TEST VALUES('John', 1.66, 4);
SELECT * FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT;
SELECT EMP_NAME,* FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT;
SELECT * FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT GROUP BY EMP_NAME ORDER BY EMP_NAME;
SELECT EMP_NAME FROM PIVOT_TEST PIVOT ( SUM(HOURS) FOR RATE IN ([1.00], [1.33], [1.66]) ) AS PVT GROUP BY EMP_NAME ORDER BY EMP_NAME;
SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT;

SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT ORDER BY EMP_NAME;
SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE);
SELECT * FROM (SELECT EMP_NAME, 'Rate ' + RTRIM(RATE) AS RATE_NAME, SUM(HOURS ) AS SUM_HOURS FROM PIVOT_TEST GROUP BY EMP_NAME, 'Rate ' + RTRIM(RATE) ) AS X PIVOT ( SUM(SUM_HOURS) FOR RATE_NAME IN ([Rate 1.00], [Rate 1.33], [Rate 1.66]) ) AS PVT;

create table if not exists emp_phone(name varchar(50), type tinyint, phone varchar(50));
insert into emp_phone values('Tom', '1', '1234-5678');insert into emp_phone values('Tom', '2', '3219-6066');insert into emp_phone values('Tom', '3', '5365-9583');insert into emp_phone values('Jerry', '1', '6837-2745');insert into emp_phone values('Jerry', '3', '2649-5820');insert into emp_phone values('Mike', '1', '5838-9002');insert into emp_phone values('Mike', '2', '2749-5580');insert into emp_phone values('John', '2', '9876-3453');
select * from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile));
select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));

Unpivot Samples

unpivot_clause: UNPIVOT ( value_column FOR pivot_column IN (columnName_as_columnValue_list) ) [ [AS] tableAlias ]

HXTT PDF UNPIVOT supports dynamical column, ILIKE pattern, and expression calculation

LIKE pattern "%[a-c][^ac][12345]" can be used in column_list, and __name__, is a pseudo column as unpivot value, which means column name.
With LIKE pattern and __name__, it's possible to produce any dynamic value for any column name. For instance, "off%" as substring(__name__,2) .

create table if not exists emp_phonePVT select name,* from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) pvt; select * from emp_phonePVT; select name,phone,type from emp_phonePVT unpivot(phone for type in (home , office , mobile)); select name,phone,type from emp_phonePVT unpivot(phone for type in (home as 1, office as 2, mobile as 3)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , office , mobile)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home as 1, office as 2, mobile as 3)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , "off%" , mobile)) pvt3; select * from emp_phonePVT unpivot(phone for type in (home , "off%" as substring(__name__,2), mobile)) pvt3;

INSERT INTO table_name [ [AS] tableAlias] [ ( column_identifier [,...] ) ] { VALUES ( expression [, ...] ) | VALUES expression [, ...] | VALUES ( expression [, ...] ),... | SELECT query | ? }

column_identifier = columnName | "reserved_word" | {v 'reserved_word'}

For instance:

insert into testpdf.page1 (textline) values('A demo line');/* inser into page 1, append after the last text line */
insert into testpdf (PageNo,CoordinateY,textline) values(1,100,'New demo line for page1');/* inser into page 1 */
insert into testpdf (PageNo,CoordinateY,textline) values(3,100,'New demo line for page3');/* inser into page 3 */
insert into testpdf (PageNo,CoordinateY,CoordinateX,textline) values(1,120,50,'Not');
insert into testpdf.page1 (textline) values('Another demo line');
insert into testpdf.page2 (textline) values('demo line for page2');
insert into testpdf (PageNo,CoordinateY,textline) values(3,300,'demo line for page3');
insert into testpdf (PageNo,CoordinateY,CoordinateX,textline) values(3,120,12,'Why');
insert into testpdf (PageNo,CoordinateY,CoordinateX,textline) values(3,120,50,'Not');

UPDATE table_name [ [AS] tableAlias] SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]
UPDATE table_reference_list SET [column_identifier | (column_identifier,...) ] = expression [,...] [WHERE condition_expression]

For instance:

update testpdf.page1 set textline=textline+'?' where CoordinateY<=100;
update testpdf.page3 set Words#>'{1,text}'='not' where CoordinateY=120 and Words#>'{1,text}'='Not';
update testpdf.page3 set textline=textline+'?' where CoordinateY<=100;

DELETE FROM table_name [ [AS] tableAlias] [WHERE condition_expression]

Removes rows in a table according to condition_expression.
DELETE sql is used to clear text content, but it will keep still old font information. The following SELECT sql will see an empty string for deleted row

For instance:

delete from testpdf.page3; /* remove all textline in page 3*/
delete from testpdf.page1 where textline='A demo line' ;

CREATE CATALOG [IF NOT EXISTS] catalogName

Create a subdirectory to contain database files.

For instance:

create catalog if not exists data222;

CREATE DATABASE [IF NOT EXISTS] databaseName

Create a new PDF document, the URL format must be assigned as a directory, for example,"jdbc:pdf:////usr/data"

create database if not exists testpdf;

DROP DATABASE [IF EXISTS] compressed-file-name

The DROP DATABASE statement is used to delete a database. IF that database doesn't exist without using IF EXIST, an SQLException will be thrown.

FLUSH DATABASE [IF EXISTS] databaseName

Flush all cached modications to database file.

For instance:

flush DATABASE db1;

COPY DATABASE [IF EXISTS] sourceDatabase TO destinationDatabase

Create a destination database, and copy all tables from source database.

For instance:

copy DATABASE db1 to db2;

CLONE DATABASE [IF NOT EXISTS] destinationDatabase FROM sourceDatabase

Create a destination database, and copy all tables from source database.

For instance:

clone DATABASE db4 from db3;

CREATE TABLE [IF NOT EXISTS] table_name [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )] [ [AS] SELECT query | ? ]

data_type: CHAR(n) | CHARACTER(n) | VARCHAR[(n)] | BINARY (n) | VARBINARY (n) | NUMERIC(n1[,n2]) | DEC[IMAL](n1[,n2]) | INT[EGER] [ AUTO_INCREMENT] | SMALLINT | FLOAT [(n)] | REAL | DOUBLE | BIT | BOOLEAN | DATE [(dateFormat)] | TIME [(dateFormat)] | TIMESTAMP [(dateFormat)] | LONGVARCHAR [(n)] | LONGVARBINARY [(n)] | JAVA_OBJECT [(n)] | CLOB | BLOB| OTHER(type_name [,n]) | NUMBER[(n1[,n2])]

n, n1,n2: positive integer, n2 can be 0

constraint: [NULL| NOT NULL] [UNIQUE] [DEFAULT expression] [PRIMARY KEY] [COMMENT 'string']

constraint_clause: [ CONSTRAINT constraint_name ] PRIMARY KEY (column1, column2, . column_n) | CONSTRAINT constraint_name FOREIGN KEY (column1, column2, ... column_n) REFERENCES parent_table (column1, column2, ... column_n) [ON DELETE CASCADE] [ON UPDATE CASCADE] | [ CONSTRAINT constraint_name ] UNIQUE (column1, column2, . column_n) | CONSTRAINT constraint_name CHECK (column_name condition)

Temporary table is only visible in the current connection.

Table name allows only pageN format, and column(size,rotate) in CREATE TABLE sql is used to set page size and rotate direction. You can assign page size and rotate direction through variable(CURRENT_Page).

For instance:

create table if not exists testpdf.page1 (size varchar(2) default 'A5' );
create table if not exists testpdf.page4 (nothing int);
create table if not exists testpdf.page2 select 'A demo line for create page2' as textline;
create table if not exists testpdf.page3 (textline longvarchar) select 'A demo line for create page3';
create table if not exists testpdf.page4 (textline longvarchar) select 'A demo line for create page4';

DROP TABLE [IF EXISTS] table_name

Removes a table, and its indexes from the database. IF that table doesn't exist without using IF EXIST, an SQLException will be thrown.

If all pages have been dropped, then that PDF file will be removed. You should take care DROP TABLE sql, because the following page 5 will become the new page 4 if you remove a page 4.

For instance:

drop table if exists testpdf.page4;

ALTER TABLE [IF EXISTS] table_name alter_specification [,...]

alter_specification: {{ADD|MODIFY} column_identifier data_type [constraint]}| DROP column_identifier | RENAME column_identifier 1 TO column_identifier 2 | RENAME TO table_name2 | ADD constraint_clause

For instance:

alter table testpdf.page3 modify size varchar default 'A4';/*for page 3*/
alter table testpdf modify size varchar default 'A4';/* for all pages*/

TRUNCATE TABLE [IF EXISTS] table_name

Remove all table rows.

For instance:

truncate table testpdf.page3;/* page 3 become a blank page with nothing*/

LOCK TABLE table_name

lock the table. Returns 1 if sucess, 0 if failed to lock a table.

For instance:

lock table page2;

UNLOCK TABLE table_name

unlock the table. Returns 1 if sucess, 0 if failed to unlock a table.

For instance:

unlock table page2;

CREATE [OR REPLACE] [ TEMP | TEMPORARY ] VIEW table [ ( column_identifier [,...] ) ] [AS ] SELECT query

Creates a virtual table whose contents (columns and rows) are defined by a query. Temporary view is only visible in the current connection.

For instance:

create or replace temporary view abcv (aaa) select FEC_ANAL FROM ANALISIS;

DROP VIEW [IF EXISTS] table

Drop a virtual table.

For instance:

drop view if exists abcv;

CREATE SEQUENCE [IF NOT EXISTS] sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [START [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

sequence_name: [catalog.]sequenceName

The optional clause START WITH n allows the sequence to begin anywhere. The default starting value is minvalue for ascending sequences and maxvalue for descending ones. The optional clause INCREMENT BY n specifies which value is added to the current sequence value to create a new value. A positive value will make an ascending sequence, a negative one a descending sequence. The default value is 1. The optional clause MINVALUE n determines the minimum value a sequence can generate. If this clause is not supplied or NO MINVALUE is specified, then defaults will be used. The defaults are 1 and -128(-32768,0x80000000,0x8000000000000000L) for ascending and descending sequences, respectively. The optional clause MAXVALUE n determines the maximum value for the sequence. If this clause is not supplied or NO MAXVALUE is specified, then default values will be used. The defaults are 127(32767,0x7fffffff,0x7fffffffffffffffL)and -1 for ascending and descending sequences, respectively. The optional clause CACHE cache specifies how many sequence numbers are to be preallocated and stored in memory for faster access. The minimum value is 1 (only one value can be generated at a time, i.e., no cache), and this is also the default. The maximum value for cache is 65535. The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue has been reached by an ascending or descending sequence respectively. If the limit is reached, the next number generated will be the minvalue or maxvalue, respectively. If NO CYCLE is specified, any calls to nextval after the sequence has reached its maximum value will throw an exception. If neither CYCLE or NO CYCLE are specified, NO CYCLE is the default.

For instance:

create sequence if not exists userID start WITH 100 increment by 2 maxvalue 2000 cache 5 cycle;

DROP SEQUENCE [IF EXISTS] sequence_name

Removes a sequence from the database. IF that sequence doesn't exist without using IF EXIST, an SQLException will be thrown.

For instance:

drop sequence if exists userID;

ALTER SEQUENCE sequence_name [AS {INT|SMALLINT|TINYINT|BIGINT}] [RESTART [WITH] n] [INCREMENT [BY] n] [MINVALUE n | NO MINVALUE] [ MAXVALUE n | NO MAXVALUE ] [ CACHE n | NO CACHE] [ [ NO ] CYCLE ]

ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameter not specifically set in the ALTER SEQUENCE command retains its prior setting.

For instance:

alter sequence userID restart WITH 100 increment by 1 maxvalue 5000;

SET TRANSACTION transaction_mode [, ...]

transaction_mode: { ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } | { READ WRITE | READ ONLY }

Sets the transaction characteristics of the current transaction. It effects any subsequent transactions in the same connection. java.sql.Connection.setTransactionIsolation(int level) and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION [ transaction_mode [, ...] ]

Begins a new transaction block. java.sql.Connection.setAutoCommit(false), java.sql.Connection.setTransactionIsolation(int level), and java.sql.Connection.setReadOnly(boolean readOnly) can do the same task.

For instance:

START TRANSACTION;

COMMIT [WORK]

Terminates the current transaction and makes all changes under the transaction persistent. It commits the changes to the database. java.sql.Connection.commit() can do the same task.

For instance:

commit;

ROLLBACK [WORK] [ TO [ SAVEPOINT ] savepoint_name]

Without savepoint_name, terminates the current transaction and rescinds all changes made under the transaction. It rolls back the changes to the database. With savepoint_name, rolls back all commands that were executed after the savepoint was established. java.sql.Connection.rollback() can do the same task of ROLLBACK [WORK] sql.

For instance:

rollback;

SAVEPOINT savepoint_name

SAVEPOINT establishes a new savepoint within the current transaction. java.sql.Connection.setSavepoint(String name) and java.sql.Connection.setSavepoint() can do the same task.

For instance:

savepoint t1;

RELEASE SAVEPOINT savepoint_name

Destroys a savepoint previously defined in the current transaction. java.sql.Connection.releaseSavepoint(Savepoint savepoint) can do the same task.

For instance:

release savepoint t1;

{ [ ? = ] call procedure_name [ ( ? [, ? [ , ... ]] ) ] }

java.sql.CallableStatement can be used to call stored procedure.

For instance:

{call myview(?)};

EXPLAIN [SQL] anySQL

EXPLAIN SQL anySQL provides SQL syntax analysis feature like INFORMATION_PARSER.sql table

For instance:

explain sql select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)='4567812';

EXPLAIN anySQL shows how the tables involved in the statement will be scanned by index scanned or sequential scan.

For instance:

explain select RECNO(),vrrdnr,Vrrdvnr,STR(vrrdnr),STR(Vrrdvnr,2),STR(vrrdnr)+STR(Vrrdvnr,2),* from Voorraad where STR(vrrdnr)+STR(Vrrdvnr,2)=' 4567812';

Pseudo Tables

The INFORMATION_SCHEMA implementation include SCHEMATA, TABLES, COLUMNS, and DATABASES. For instance,

SELECT TABLE_SCHEM FROM INFORMATION_SCHEMA.SCHEMATA;
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE';
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='TABLE' AND TABLE_CAT='aDatabaseFileName';;
SELECT column_name,data_type,column_default,is_nullable FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE t.table_name='TABLE-NAME';
SELECT column_name, data_type, character_maximum_length, numeric_precision, column_default, is_nullable FROM information_schema.tables as t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schem=c.table_schem AND t.table_name=c.table_name WHERE c.table_schema='TABLE-SCHEMA' AND c.table_name='TABLE-NAME';

The INFORMATION_PARSER.sql provides SQL syntax analysis feature. For instance,

            sql =  "SELECT * FROM INFORMATION_PARSER.sql WHERE SQL_COMMAND = ? and OBJECT_TYPE='TABLE'";
            sql =  "SELECT SQL_TYPE,OBJECT_TYPE,OBJECT_NAME FROM  INFORMATION_PARSER.SQL WHERE SQL_COMMAND= ?";


            pstmt = con.prepareStatement(sql);

             pstmt.setString(1, "select * from a.test out; SELECT efg,3+2 as dd FROM ABC;update a set a=3;");
      //      pstmt.setString(1, "select EFG from a.test, test2 where test.b=test2.c");
//            pstmt.setString(1, "select EFG from (select * from abc);select EFG from (select * from abc) as o;");
    //        pstmt.setString(1, "select EFG from a.test left join test2 where test.b=test2.c; SELECT A FROM B UNION SELECT C FROM D;");
            ResultSet rs = pstmt.executeQuery();

Pseudo Columns

PageNo: the page number
CoordinateX: the coordinate of x axis for text
CoordinateY: the coordinate of y axis for text
textline: a text line
words: a JOSN array contains some words for a text line.
texts: a JOSN array contains only text for a text line.
FormNo: the form number if text is in form
For instance, select PageNo,CoordinateX,CoordinateY,textline,words,words#>'{0}',words->0->'x',words#>'{0,x}',words#>'{0,y}',words#>'{0,text}' from testpdf.page3;

__rowid__, is a pseudo column as primary key. A __rowid__ identifies a row in a table.

__values__, is a pseudo read-only column for all values in a data row.

level is a pseudo column that can be used in hierarchical queries (start with .. connect by). For records that appear in the root, level is 1, for their (direct) __children__, level is 2 and so on.
select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3 order by parentId;

Pseudo Variable

CURRENT_DATE is a pseudo variable which returns the current date.

CURRENT_TIME is a pseudo variable which returns the current time.

CURRENT_TIMESTAMP is a pseudo variable which returns the current timestamp.

_CURRENT_ is a pseudo catalog(or schema name) which returns the current catalog (or schema).

CURRENT_Password is a pseudo variable which used to set password for pdf document

DECLARE CURRENT_Password '{"testpasswordl":"password2"}';
set CURRENT_Password='{"p189l.pdf":"p189"}';/*same result*/
set CURRENT_Password='{"password256":"password256"}';
set CURRENT_Password='{"passwordrc4":"passwordrc4"}';
set CURRENT_Password='{"testcrypt.pdf":"password256"}';
set CURRENT_Password='{"p189l":null}';/* remove it from map*/

CURRENT_Page is a pseudo variable which used to set page size and rotate direction for pdf page
Size: A0, A1, A2, A3, A4, A5, A6, Legal, Letter, nxn (for instance, 8.5x1.4 means 8.5" x 14")
Rotate integer (Optional; inheritable) The number of degrees by which the page shall be rotated clockwise when displayed or printed. The value shall be a multiple of 90. Default value: 0.

select CURRENT_Page;
DECLARE _Page_Demo '{"size":"A4"}';
set _Page_Demo->'size'='A2';
set CURRENT_Page=_Page_Demo;
DECLARE CURRENT_Page '{"size":"A4","rotate":0}';
select CURRENT_Page;

CURRENT_Font is a pseudo variable which used to set font information for current text operation
Name: Courier, Courier-Bold, Courier-BoldOblique, Courier-Oblique, Helvetica, Helvetica-Bold, Helvetica-BoldOblique, Helvetica-Oblique,Times-Roman, Times-Bold, Times-BoldItalic, Times-Italic, and so on

DECLARE CURRENT_Font '{"name":"Courier"}';
DECLARE _Font_Demo '{"name":"Times-Roman"}';
set CURRENT_Font=_Font_Demo;

CURRENT_TextFormat is a pseudo variable which used to set text format information for current text operation

DECLARE CURRENT_TextFormat '{"fontSize":12}';

Split Multivalue Column Into Rows

If each row has multiple multi value columns, a special subquery table can be used in special join sql. For instance,

select User,Role from aTable,(select split(aTable.Roles,',') as Role) AS bTable;

select User,Role,Year from aTable,(select split(aTable.Roles,',') as Role,split(aTable.Years,',') as Year) AS bTable;

DECLARE variable_name[,...] type [DEFAULT expression]
DECLARE var_name[,...] 'jsonText'
DECLARE table_variable_name TABLE [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )]
DECLARE TABLE table_variable_name [(column_identifier data_type [constraint] [,...] [, constraint_clause [,...] ] )]

Variable is visiable only in the same connection. @local_variable is a preferable format to avoid name conflict.
A table variable object is in memory for currection connection, and can be dropped by DROP TABLE.

For instance:

DECLARE abc CHAR(20) DEFAULT 'Hello';
DECLARE x, y INT;
DECLARE _Page_Demo '{"size":"A4","rotate":0}';
select _Page_Demo; select _Page_Demo->'size';

SET variable_name[.propertyName] = expression [,...]

expression can be a complicated expresion. BTW, INTO variable[,...] clause of SELECT syntax can set selected columns directly into variables.

For instance:

SET x = 1+int(55.5),y=2;
SELECT name,id INTO x,y FROM table1 WHERE id=33;
SELECT date(),pi() INTO x,y;
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
set _Page_Demo->'size'='A2';

Common Table Expression

WITH <common_table_expression> [ ,...n ] ]   
<common_table_expression>::=  
    expression_name [ ( column_name [ ,...n ] ) ]  
    AS  
    ( CTE_query_definition )      

Common table expression (CTE) is derived from a simple query and defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE or CREATE VIEW statement. A common table expression can include references to itself. This is referred to as a recursive common table expression, which is similar to CONNECT BY clause. Having clause with Level (a pseudo column) can be used to limit recursive depth.

For instance:

    
DECLARE table hierarchy(ID  int not null ,ParentID int not null,name varchar(100) not null);
insert into hierarchy values(1,0,'a1'),(2,1,'b2'),(3,2,'c3'),(4,3,'d4'),(12,3,'d12'),(13,3,'d13'),(5,1,'b5'),(6,5,'c6'),(7,6,'d7'),(15,1,'b15'),(16,15,'c16'),(17,1,'b17'),(18,17,'c18');
select recno(),* from hierarchy ;

select * from hierarchy order by ParentID;

select * from hierarchy where ParentID=1;

select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3  order by parentId;

select * from hierarchy START WITH id=1 CONNECT BY prior id=parentId;

select * from hierarchy START WITH id=1 CONNECT BY prior id=parentId;

select *,level from hierarchy START WITH id=1 CONNECT BY prior id=parentId having level<=3  order by parentId;

with cte(Id,ParentID,Name) as
(
select * 
from hierarchy 
where id=1

union all
select h.* 
from hierarchy h
inner join cte c on h.ParentID=c.id 
)
select *
from cte
order by ParentID;

with cte(Id,ParentID,Name) as(select * from hierarchy where id=1 union all select h.* from hierarchy h inner join cte c on h.ParentID=c.id) select * from cte order by ParentID;

with cte(Id,ParentID,Name) as(select *,level from hierarchy where id=1 union all select h.* from hierarchy h inner join cte c on h.ParentID=c.id having level<=3 ) select * from cte order by ParentID;


DECLARE @t1 TABLE (itmID INT, itmIDComp INT);  
INSERT INTO @t1 VALUES (1,10), (2,10);   
SELECT * FROM @T1;

select * FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t ;


DECLARE @t2 TABLE (itmID INT, itmIDComp INT);   
INSERT INTO @t2 VALUES (3,10), (4,10);   

WITH vw AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )  SELECT * FROM vw;  

WITH vw (a,b) AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )  SELECT a,b FROM vw;  

WITH r AS   (      SELECT t.itmID AS itmIDComp             , NULL AS itmID            ,0 AS N             ,1 AS Lvl      FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t  )   SELECT itmIDComp,Lvl, N FROM r;  

WITH vw AS   (      SELECT itmIDComp, itmID      FROM @t1      UNION ALL      SELECT itmIDComp, itmID      FROM @t2  )   ,r AS   (      SELECT t.itmID AS itmIDComp             , NULL AS itmID            ,0 AS N             ,1 AS Lvl      FROM (SELECT 1 as itmID UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t  )   SELECT Lvl, N FROM r;  


declare table example (user_id int, application varchar(1),content longvarchar);
insert into example values (1,'A','abc'),(2,'B','efg'), (1,'B','CCC');

with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1, alias2
where alias1.user_id = alias2.user_id;

with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1 join alias2 on alias1.user_id = alias2.user_id;



with
alias1 as (select * from example where application = 'A'),
alias2 as (select * from example where application = 'B')
select *
from alias1 as a1, alias2 as a2
where a1.user_id = a2.user_id;

Comment Syntax

#one-line comment
--one-line comment
/*multiline comment*/

For instance:

select * /* column list */ from test;#This is a select sql.

SQL States