### Example Sequence Options
Source: https://www.h2database.com/html/grammar.html
Demonstrates setting the initial value of a sequence using START WITH and an example of a basic sequence option.
```sql
START WITH 10000
```
```sql
NO CACHE
```
--------------------------------
### CREATE SEQUENCE Examples
Source: https://www.h2database.com/html/commands.html
Examples of creating sequences, including specifying data type and start value.
```sql
CREATE SEQUENCE SEQ_ID;
```
```sql
CREATE SEQUENCE SEQ2 AS INTEGER START WITH 10;
```
--------------------------------
### H2 Console: Start Profiling
Source: https://www.h2database.com/html/tutorial.html
Initiates performance profiling for subsequent SQL statements. The example shows how to start profiling and then execute a function.
```sql
@prof_start;
call hash('SHA256', '', 1000000);
```
--------------------------------
### Create and Connect to an H2 Database via Shell
Source: https://www.h2database.com/html/tutorial.html
This example shows how to create a new H2 database and connect to it using the H2 Shell tool from the command line. It guides through providing the database URL, driver, username, and password.
```shell
> java -cp h2-*.jar org.h2.tools.Shell
Welcome to H2 Shell
Exit with Ctrl+C
[Enter] jdbc:h2:mem:2
URL jdbc:h2:./path/to/database
[Enter] org.h2.Driver
Driver
[Enter] sa
User your_username
Password (hidden)
Type the same password again to confirm database creation.
Password (hidden)
Connected
sql> quit
Connection closed
```
--------------------------------
### CUME_DIST() Examples
Source: https://www.h2database.com/html/functions-window.html
Examples demonstrating CUME_DIST with ordering and partitioning.
```sql
SELECT CUME_DIST() OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT CUME_DIST() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### RANK() Examples
Source: https://www.h2database.com/html/functions-window.html
Examples showing the RANK function with ordering and partitioning.
```sql
SELECT RANK() OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### MODE Example
Source: https://www.h2database.com/html/functions-aggregate.html
Example demonstrating the usage of the MODE function.
```sql
MODE() WITHIN GROUP (ORDER BY X)
```
--------------------------------
### DENSE_RANK() Examples
Source: https://www.h2database.com/html/functions-window.html
Examples demonstrating DENSE_RANK with ordering and partitioning.
```sql
SELECT DENSE_RANK() OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT DENSE_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### SHOW TABLES Example
Source: https://www.h2database.com/html/commands.html
This example shows how to list all tables within the current schema.
```sql
SHOW TABLES
```
--------------------------------
### PERCENT_RANK() Examples
Source: https://www.h2database.com/html/functions-window.html
Examples of PERCENT_RANK usage with ordering and partitioning.
```sql
SELECT PERCENT_RANK() OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT PERCENT_RANK() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### RUNSCRIPT Example from File
Source: https://www.h2database.com/html/commands.html
Shows a basic example of running a SQL script from a local file named 'backup.sql'. Admin rights are required.
```sql
RUNSCRIPT FROM 'backup.sql'
```
--------------------------------
### FIRST_VALUE Example
Source: https://www.h2database.com/html/functions-window.html
Example of using FIRST_VALUE to get the first value ordered by ID. Window functions may require significant memory.
```sql
SELECT FIRST_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
```
--------------------------------
### MEDIAN Example
Source: https://www.h2database.com/html/functions-aggregate.html
Example demonstrating the usage of the MEDIAN function.
```sql
MEDIAN(X)
```
--------------------------------
### Basic Table Creation Example
Source: https://www.h2database.com/html/commands.html
A simple example demonstrating how to create a table named 'TEST' with an integer primary key 'ID' and a string column 'NAME'.
```sql
CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR(255))
```
--------------------------------
### TABLE Command Examples
Source: https://www.h2database.com/html/commands.html
Examples of selecting data from the 'TEST' table, including ordering and fetching specific rows.
```sql
TABLE TEST;
```
```sql
TABLE TEST ORDER BY ID FETCH FIRST ROW ONLY;
```
--------------------------------
### Example of Using the Async File System
Source: https://www.h2database.com/html/advanced.html
Demonstrates how to specify the 'async:' file system in the database URL for potentially faster I/O operations on some operating systems. This is an example of a JDBC connection string.
```sql
jdbc:h2:async:~/test
```
--------------------------------
### JSON_ARRAY Examples
Source: https://www.h2database.com/html/functions.html
Provides examples of using the JSON_ARRAY function. The first example creates an array of numbers. The second example creates an array from JSON formatted data. The third example creates an array from the results of a subquery.
```sql
JSON_ARRAY(10, 15, 20);
```
```sql
JSON_ARRAY(JSON_DATA_A FORMAT JSON, JSON_DATA_B FORMAT JSON);
```
```sql
JSON_ARRAY((SELECT J FROM PROPS) FORMAT JSON);
```
--------------------------------
### CREATE ROLE Example
Source: https://www.h2database.com/html/commands.html
A simple example of creating a role named READONLY.
```sql
CREATE ROLE READONLY
```
--------------------------------
### CREATE SCHEMA Example
Source: https://www.h2database.com/html/commands.html
An example of creating a schema named TEST_SCHEMA with SA as the owner.
```sql
CREATE SCHEMA TEST_SCHEMA AUTHORIZATION SA
```
--------------------------------
### VALUES Command Example
Source: https://www.h2database.com/html/commands.html
This example demonstrates creating a table-like structure with two rows of data using the VALUES command.
```sql
VALUES (1, 'Hello'), (2, 'World');
```
--------------------------------
### LEAD Function Example 1
Source: https://www.h2database.com/html/functions-window.html
Basic usage of the LEAD function to get the value from the next row, ordered by ID. This requires a window ORDER BY clause.
```sql
SELECT LEAD(X) OVER (ORDER BY ID), * FROM TEST;
```
--------------------------------
### Array Constructor Example
Source: https://www.h2database.com/html/grammar.html
An example of creating an array using a query that generates a range of numbers.
```sql
ARRAY(SELECT * FROM SYSTEM_RANGE(1, 10));
```
--------------------------------
### Example: Insert Values
Source: https://www.h2database.com/html/grammar.html
Provides an example of how to specify values for an INSERT statement.
```plaintext
VALUES (1, 'Test')
```
--------------------------------
### H2 CREATE USER Example
Source: https://www.h2database.com/html/commands.html
Example of creating a user named GUEST with a password 'abc'. This command requires administrative rights and commits any open transaction.
```sql
CREATE USER GUEST PASSWORD 'abc'
```
--------------------------------
### CREATE INDEX Example
Source: https://www.h2database.com/html/commands.html
A basic example of creating an index named IDXNAME on the NAME column of the TEST table.
```sql
CREATE INDEX IDXNAME ON TEST(NAME)
```
--------------------------------
### Start H2 Server
Source: https://www.h2database.com/html/advanced.html
Starts the H2 server with default settings. Databases are stored in the current working directory.
```bash
java -cp h2*.jar org.h2.tools.Server
```
--------------------------------
### Start H2 Server Tool from Command Line
Source: https://www.h2database.com/html/tutorial.html
Starts the H2 Server tool with default settings. Use this to manage H2 servers from the command line.
```bash
java -cp h2*.jar org.h2.tools.Server
```
--------------------------------
### PERCENTILE_DISC Example
Source: https://www.h2database.com/html/functions-aggregate.html
Example demonstrating the usage of PERCENTILE_DISC to find the 50th percentile.
```sql
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY V)
```
--------------------------------
### SCRIPT NODATA Example
Source: https://www.h2database.com/html/commands.html
This example demonstrates the SCRIPT command without including INSERT statements, useful for creating a schema-only backup.
```sql
SCRIPT NODATA
```
--------------------------------
### Interval Minute Literal Example
Source: https://www.h2database.com/html/grammar.html
Shows an example of an interval specified only in minutes.
```sql
INTERVAL '10' MINUTE
```
--------------------------------
### Example: Index Column
Source: https://www.h2database.com/html/grammar.html
Shows a simple example of an index column definition.
```plaintext
NAME
```
--------------------------------
### Start H2 Server with Custom Base Directory
Source: https://www.h2database.com/html/advanced.html
Starts the H2 server and specifies a directory for storing databases. Use '~' for the user home directory.
```bash
java -cp h2*.jar org.h2.tools.Server -baseDir ~
```
--------------------------------
### Install Native Image Tool
Source: https://www.h2database.com/html/build.html
Installs the native-image tool from GraalVM. This is a prerequisite for compiling native executables.
```bash
gu install native-image
```
--------------------------------
### Interval Day Literal Example
Source: https://www.h2database.com/html/grammar.html
Provides an example of an interval specified only in days.
```sql
INTERVAL '10' DAY
```
--------------------------------
### INTERVAL MINUTE Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL MINUTE data type.
```sql
INTERVAL MINUTE
```
--------------------------------
### Example Referential Actions
Source: https://www.h2database.com/html/grammar.html
Illustrates the usage of referential actions for managing related data during updates and deletes. CASCADE and SET NULL are shown as examples.
```sql
ON UPDATE CASCADE ON DELETE CASCADE
```
```sql
CASCADE
SET NULL
```
--------------------------------
### INTERVAL DAY Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL DAY data type.
```sql
INTERVAL DAY
```
--------------------------------
### MVStore File Header Example
Source: https://www.h2database.com/html/mvstore.html
An example of the data contained within an MVStore file header. This includes information about the database, block size, chunk details, creation time, format version, and checksum.
```text
H:2,block:2,blockSize:1000,chunk:7,created:1441235ef73,format:1,version:7,fletcher:3044e6cc
```
--------------------------------
### Example: Merge When Matched Clause
Source: https://www.h2database.com/html/grammar.html
Shows examples of the WHEN MATCHED clause for MERGE statements, demonstrating both UPDATE and DELETE actions.
```plaintext
WHEN MATCHED THEN UPDATE SET NAME = S.NAME
WHEN MATCHED THEN DELETE
```
--------------------------------
### LAST_VALUE Example
Source: https://www.h2database.com/html/functions-window.html
Example of using LAST_VALUE to get the last value ordered by ID. Window functions may require significant memory.
```sql
SELECT LAST_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
```
--------------------------------
### TIMESTAMP Examples
Source: https://www.h2database.com/html/datatypes.html
Examples demonstrating the usage of the TIMESTAMP data type with and without specified precision.
```sql
TIMESTAMP
```
```sql
TIMESTAMP(9)
```
--------------------------------
### Field Reference Examples
Source: https://www.h2database.com/html/grammar.html
Provides examples of referencing fields, including nested fields in JSON.
```sql
(R).FIELD1
```
```sql
(TABLE1.COLUMN2).FIELD.SUBFIELD
```
```sql
JSON '{"a": 1, "b": 2}'.
```
--------------------------------
### NTH_VALUE Example
Source: https://www.h2database.com/html/functions-window.html
Example of using NTH_VALUE to get a value at a specific position ordered by ID. Window functions may require significant memory.
```sql
SELECT NTH_VALUE(X) OVER (ORDER BY ID), * FROM TEST;
```
--------------------------------
### INTERVAL DAY TO MINUTE Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL DAY TO MINUTE data type.
```sql
INTERVAL DAY TO MINUTE
```
--------------------------------
### H2 CREATE TRIGGER Example: AS Source Code (Ruby)
Source: https://www.h2database.com/html/commands.html
Example of creating a trigger using AS with Ruby source code. The source must start with '#ruby' and define a method to instantiate an org.h2.api.Trigger object.
```sql
CREATE TRIGGER TRIG_RUBY BEFORE INSERT ON TEST AS '#ruby
Java::MyPackage::MyTrigger.new("constructorParam")';
```
--------------------------------
### CREATE LINKED TABLE Examples
Source: https://www.h2database.com/html/commands.html
Demonstrates creating linked tables with different configurations, including specifying a table, a query, and using JNDI for connection.
```sql
CREATE LINKED TABLE LINK('org.h2.Driver', 'jdbc:h2:./test2',
'sa', 'sa', 'TEST');
```
```sql
CREATE LINKED TABLE LINK('', 'jdbc:h2:./test2', 'sa', 'sa',
'(SELECT * FROM TEST WHERE ID>0)');
```
```sql
CREATE LINKED TABLE LINK('javax.naming.InitialContext',
'java:comp/env/jdbc/Test', NULL, NULL,
'(SELECT * FROM TEST WHERE ID>0)');
```
--------------------------------
### H2 CREATE TRIGGER Example: AS Source Code (JavaScript)
Source: https://www.h2database.com/html/commands.html
Example of creating a trigger using AS with JavaScript source code. The source must start with '//javascript' and define a function to return an org.h2.api.Trigger instance.
```sql
CREATE TRIGGER TRIG_JS BEFORE INSERT ON TEST AS '//javascript
return new (Java.type("org.example.MyTrigger"))("constructorParam");';
```
--------------------------------
### RUNSCRIPT Example from Classpath
Source: https://www.h2database.com/html/commands.html
Demonstrates running a SQL script located in the classpath, using the 'classpath:' prefix. Admin rights are required.
```sql
RUNSCRIPT FROM 'classpath:/com/acme/test.sql'
```
--------------------------------
### H2 Database Example: Single-line Comments
Source: https://www.h2database.com/html/grammar.html
Shows how to use single-line comments starting with '--' or '//' in H2 Database SQL statements.
```sql
-- comment
```
--------------------------------
### Using Off-Heap Storage with MVStore
Source: https://www.h2database.com/html/mvstore.html
Demonstrates how to initialize and open an MVStore using an off-heap storage implementation. This is useful for very large in-memory stores without increasing the JVM heap size.
```java
OffHeapStore offHeap = new OffHeapStore();
MVStore s = new MVStore.Builder().
fileStore(offHeap).open();
```
--------------------------------
### LAG Function Example 1
Source: https://www.h2database.com/html/functions-window.html
Basic usage of the LAG function to get the value from the previous row, ordered by ID. This requires a window ORDER BY clause.
```sql
SELECT LAG(X) OVER (ORDER BY ID), * FROM TEST;
```
--------------------------------
### Get Help for H2 Backup Tool
Source: https://www.h2database.com/html/tutorial.html
Displays help information for the H2 Backup command-line tool. This is a general pattern for getting help for any H2 tool.
```bash
java -cp h2*.jar org.h2.tools.Backup -?
```
--------------------------------
### BINARY LARGE OBJECT Examples
Source: https://www.h2database.com/html/datatypes.html
Shows examples of using BINARY LARGE OBJECT with different size units (K, M, G, T, P). Values are streamed and not kept fully in memory.
```sql
BINARY LARGE OBJECT
```
```sql
BLOB(10K)
```
--------------------------------
### RUNSCRIPT Example with FROM_1X
Source: https://www.h2database.com/html/commands.html
Illustrates running a script generated from an older H2 version (1.*.*) using the FROM_1X flag, which implicitly enables QUIRKS_MODE and VARIABLE_BINARY. Admin rights are required.
```sql
RUNSCRIPT FROM 'dump_from_1_4_200.sql' FROM_1X
```
--------------------------------
### Set Server Cached Objects via System Property
Source: https://www.h2database.com/html/advanced.html
Configure the number of cached objects for the H2 server by setting the `h2.serverCachedObjects` system property when starting the server. This is an example of a command-line setting.
```java
java -Dh2.serverCachedObjects=256 org.h2.tools.Server
```
--------------------------------
### Using H2's Built-In Java Profiler
Source: https://www.h2database.com/html/performance.html
Integrate the H2 Profiler into your Java application to measure and analyze the performance of specific code sections. Start collection before the process and stop after to get performance metrics.
```java
import org.h2.util.Profiler;
Profiler prof = new Profiler();
prof.startCollecting();
// .... some long running process, at least a few seconds
prof.stopCollecting();
System.out.println(prof.getTop(3));
```
```java
import org.h2.util;
...
Profiler profiler = new Profiler();
profiler.startCollecting();
// application code
System.out.println(profiler.getTop(3));
```
--------------------------------
### H2 Database Condition Examples
Source: https://www.h2database.com/html/grammar.html
Provides concrete examples of condition usage in H2 Database, demonstrating comparison, negation, existence checks, uniqueness tests, and geometry intersection.
```sql
ID <> 2
NOT(A OR B)
EXISTS (SELECT NULL FROM TEST T WHERE T.GROUP_ID = P.ID)
UNIQUE (SELECT A, B FROM TEST T WHERE T.CATEGORY = CAT)
INTERSECTS(GEOM1, GEOM2)
```
--------------------------------
### H2 Built-in Connection Pool Usage
Source: https://www.h2database.com/html/tutorial.html
Utilize H2's built-in connection pool for improved performance when frequently opening and closing connections. This example demonstrates creating a pool, getting connections, executing statements, and disposing of the pool.
```java
import java.sql.*;
import org.h2.jdbcx.JdbcConnectionPool;
public class Test {
public static void main(String[] args) throws Exception {
JdbcConnectionPool cp = JdbcConnectionPool.create(
"jdbc:h2:~/test", "sa", "sa");
for (int i = 0; i < args.length; i++) {
Connection conn = cp.getConnection();
conn.createStatement().execute(args[i]);
conn.close();
}
cp.dispose();
}
}
```
--------------------------------
### Example Row Value Expressions
Source: https://www.h2database.com/html/grammar.html
Provides examples of different row value expression syntaxes, including the ROW constructor, a parenthesized list of expressions, and a single expression.
```sql
ROW (1)
```
```sql
(1, 2)
```
```sql
1
```
--------------------------------
### H2 Database Quantified Comparison Examples
Source: https://www.h2database.com/html/grammar.html
Shows examples of quantified comparison predicates in H2 Database, including comparisons against subqueries and arrays.
```sql
< ALL(SELECT V FROM TEST)
= ANY(ARRAY_COLUMN)
= ANY(CAST((SELECT ARRAY_COLUMN FROM OTHER_TABLE WHERE ID = 5) AS INTEGER ARRAY)
```
--------------------------------
### H2 Permissions for Java Web Start
Source: https://www.h2database.com/html/tutorial.html
When using Java Web Start, ensure the .jnlp file includes the necessary security permissions for file system access.
```xml
```
--------------------------------
### AND Condition Example
Source: https://www.h2database.com/html/grammar.html
Provides an example of an AND condition used in a query.
```sql
ID=1 AND NAME='Hi'
```
--------------------------------
### Start and Stop H2 Server Programmatically
Source: https://www.h2database.com/html/advanced.html
Demonstrates starting and stopping the H2 PG server from within a Java application. The base directory is set to the user's home directory.
```java
Server server = Server.createPgServer("-baseDir", "~");
server.start();
...
server.stop();
```
--------------------------------
### Explain Query Using Multi-Column Index (Specific)
Source: https://www.h2database.com/html/performance.html
Demonstrates using `EXPLAIN SELECT` to show how a query with conditions on all indexed columns utilizes the `INDEX_PLACE` index.
```sql
EXPLAIN SELECT PHONE FROM ADDRESS
WHERE CITY = 'Berne' AND NAME = 'Miller'
AND FIRST_NAME = 'John';
```
--------------------------------
### Full Lucene Index Management Example
Source: https://www.h2database.com/html/tutorial.html
Demonstrates initializing Lucene search, creating an index, inserting data, searching by name and specific column, and dropping all indices.
```sql
CREATE ALIAS IF NOT EXISTS FTL_INIT FOR "org.h2.fulltext.FullTextLucene.init";
CALL FTL_INIT();
DROP TABLE IF EXISTS TEST;
CREATE TABLE TEST(ID INT PRIMARY KEY, FIRST_NAME VARCHAR, LAST_NAME VARCHAR);
CALL FTL_CREATE_INDEX('PUBLIC', 'TEST', NULL);
INSERT INTO TEST VALUES(1, 'John', 'Wayne');
INSERT INTO TEST VALUES(2, 'Elton', 'John');
SELECT * FROM FTL_SEARCH_DATA('John', 0, 0);
SELECT * FROM FTL_SEARCH_DATA('LAST_NAME:John', 0, 0);
CALL FTL_DROP_ALL();
```
--------------------------------
### Explain Query for Ordered Results Using Index
Source: https://www.h2database.com/html/performance.html
Demonstrates using `EXPLAIN SELECT` to show how an `ORDER BY` clause on indexed columns can leverage the index for sorted retrieval.
```sql
EXPLAIN SELECT * FROM ADDRESS ORDER BY CITY, NAME, FIRST_NAME;
```
--------------------------------
### Between Predicate Example
Source: https://www.h2database.com/html/grammar.html
An example of a BETWEEN predicate checking if a value is between LOW and HIGH.
```SQL
BETWEEN LOW AND HIGH
```
--------------------------------
### FILE_WRITE Function Example
Source: https://www.h2database.com/html/functions.html
Shows how to write data to a file using FILE_WRITE. This function requires write access to the folder and admin rights.
```sql
SELECT FILE_WRITE('Hello world', '/tmp/hello.txt')) LEN;
```
--------------------------------
### MVStore Configuration with Builder
Source: https://www.h2database.com/html/mvstore.html
Shows how to use the MVStore.Builder for fluid configuration of store options such as file name, encryption, and compression.
```java
MVStore s = new MVStore.Builder().
fileName(fileName).
encryptionKey("007".toCharArray()).
compress().
open();
```
--------------------------------
### Basic MVStore Operations
Source: https://www.h2database.com/html/mvstore.html
Demonstrates how to open an MVStore, create or open a named map, add and retrieve data, and close the store. The store can be in-memory if no file name is provided.
```java
import org.h2.mvstore.*;
// open the store (in-memory if fileName is null)
MVStore s = MVStore.open(fileName);
// create/get the map named "data"
MVMap map = s.openMap("data");
// add and read some data
map.put(1, "Hello World");
System.out.println(map.get(1));
// close the store (this will persist changes)
s.close();
```
--------------------------------
### Boolean Test Predicate Example
Source: https://www.h2database.com/html/grammar.html
A simple example demonstrating a boolean test.
```SQL
IS TRUE
```
--------------------------------
### MVStore Data Storage Example
Source: https://www.h2database.com/html/mvstore.html
Demonstrates how to open an MVStore, store data in a map, commit changes, and close the store. This operation results in the creation of new chunks to store the data versions.
```java
MVStore s = MVStore.open(fileName);
MVMap map = s.openMap("data");
for (int i = 0; i < 400; i++) {
map.put(i, "Hello");
}
s.commit();
for (int i = 0; i < 100; i++) {
map.put(i, "Hi");
}
s.commit();
s.close();
```
--------------------------------
### H2 Binary Number Literal Example
Source: https://www.h2database.com/html/grammar.html
An example of a binary number literal.
```sql
0b101
0b_01010101_10101010
```
--------------------------------
### H2 Octal Number Literal Example
Source: https://www.h2database.com/html/grammar.html
An example of an octal number literal.
```sql
0o664
0o_123_777
```
--------------------------------
### H2 Hex Number Literal Example
Source: https://www.h2database.com/html/grammar.html
An example of a hexadecimal number literal.
```sql
0xff
0x_ABCD_1234
```
--------------------------------
### H2 Date Literal Example
Source: https://www.h2database.com/html/grammar.html
An example of a date literal in the 'yyyy-MM-dd' format.
```sql
DATE '2004-12-31'
```
--------------------------------
### Example Select Order
Source: https://www.h2database.com/html/grammar.html
Demonstrates how to specify sorting criteria for a SELECT statement, including descending order and handling of NULL values.
```sql
NAME DESC NULLS LAST
```
--------------------------------
### NTILE Function Examples
Source: https://www.h2database.com/html/functions-window.html
Demonstrates NTILE usage with and without partitioning. Ensure the number of groups is a positive long value.
```sql
SELECT NTILE(10) OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT NTILE(5) OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### PERCENTILE_CONT Example
Source: https://www.h2database.com/html/functions-aggregate.html
Example demonstrating the usage of PERCENTILE_CONT to find the 50th percentile.
```sql
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY V)
```
--------------------------------
### INTERVAL SECOND Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL SECOND data type.
```sql
INTERVAL SECOND
```
--------------------------------
### Explain Join Query Execution Plan
Source: https://www.h2database.com/html/performance.html
For joins, `EXPLAIN` shows the order in which tables are processed. The example demonstrates how `INVOICE` is processed first, followed by `CUSTOMER`, with specific conditions applied.
```sql
CREATE TABLE CUSTOMER(ID IDENTITY, NAME VARCHAR);
CREATE TABLE INVOICE(ID IDENTITY,
CUSTOMER_ID INT REFERENCES CUSTOMER(ID),
AMOUNT NUMBER);
EXPLAIN SELECT I.ID, C.NAME FROM CUSTOMER C, INVOICE I
WHERE I.ID=10 AND AMOUNT>0 AND C.ID=I.CUSTOMER_ID;
SELECT
I.ID,
C.NAME
FROM PUBLIC.INVOICE I
/* PUBLIC.PRIMARY_KEY_9: ID = 10 */
/* WHERE (I.ID = 10)
AND (AMOUNT > 0)
*/
INNER JOIN PUBLIC.CUSTOMER C
/* PUBLIC.PRIMARY_KEY_5: ID = I.CUSTOMER_ID */
ON 1=1
WHERE (C.ID = I.CUSTOMER_ID)
AND ((I.ID = 10)
AND (AMOUNT > 0))
```
--------------------------------
### LEAD Function Example 2
Source: https://www.h2database.com/html/functions-window.html
Advanced usage of the LEAD function with a specified offset (2), default value (0), and ignoring NULLs, partitioned by CATEGORY and ordered by ID. Requires a window ORDER BY clause.
```sql
SELECT LEAD(X, 2, 0) IGNORE NULLS OVER (
PARTITION BY CATEGORY ORDER BY ID
), * FROM TEST;
```
--------------------------------
### INTERVAL HOUR Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL HOUR data type.
```sql
INTERVAL HOUR
```
--------------------------------
### Explain Query Using Multi-Column Index (Partial)
Source: https://www.h2database.com/html/performance.html
Illustrates using `EXPLAIN SELECT` to show how a query filtering only by `CITY` can still utilize the `INDEX_PLACE` index.
```sql
EXPLAIN SELECT PHONE FROM ADDRESS WHERE CITY = 'Berne';
```
--------------------------------
### INTERVAL MONTH Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL MONTH data type.
```sql
INTERVAL MONTH
```
--------------------------------
### INTERVAL YEAR Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL YEAR data type.
```sql
INTERVAL YEAR
```
--------------------------------
### MVStore Chunk Header and Footer Example
Source: https://www.h2database.com/html/mvstore.html
Illustrates the data fields found in both the chunk header and footer. These fields provide information about the chunk's identity, position, size, content, and checksum.
```text
chunk:1,block:2,len:1,map:6,max:1c0,next:3,pages:2,root:4000004f8c,time:1fc,version:1
chunk:1,block:2,version:1,fletcher:aed9a4f6
```
--------------------------------
### NULLS DISTINCT Examples
Source: https://www.h2database.com/html/grammar.html
Provides examples for specifying how NULL values are treated for uniqueness.
```sql
NULLS DISTINCT
```
```sql
NULLS NOT DISTINCT
```
--------------------------------
### H2 Database Starter Listener for Web Applications
Source: https://www.h2database.com/html/tutorial.html
XML configuration to add H2's DbStarter listener to a web application's web.xml.
```xml
org.h2.server.web.DbStarter
```
--------------------------------
### Type Predicate Example
Source: https://www.h2database.com/html/grammar.html
An example of a type predicate checking for INTEGER or BIGINT types.
```SQL
IS OF (INTEGER, BIGINT)
```
--------------------------------
### LIKE Predicate Example
Source: https://www.h2database.com/html/grammar.html
Illustrates a basic usage of the LIKE predicate for pattern matching.
```sql
LIKE 'a%'
```
--------------------------------
### H2 Boolean Literal Example
Source: https://www.h2database.com/html/grammar.html
An example of using the TRUE keyword as a boolean literal.
```sql
TRUE
```
--------------------------------
### Explain Query Plan for Table Scan
Source: https://www.h2database.com/html/performance.html
Shows how to use `EXPLAIN SELECT` to understand the query execution plan, specifically when a table scan is performed because no suitable index is found.
```sql
EXPLAIN SELECT PHONE FROM ADDRESS WHERE NAME = 'Miller';
```
--------------------------------
### ROW_NUMBER() Examples
Source: https://www.h2database.com/html/functions-window.html
Examples demonstrating the usage of the ROW_NUMBER function with different OVER clause specifications.
```sql
SELECT ROW_NUMBER() OVER (), * FROM TEST;
```
```sql
SELECT ROW_NUMBER() OVER (ORDER BY ID), * FROM TEST;
```
```sql
SELECT ROW_NUMBER() OVER (PARTITION BY CATEGORY ORDER BY ID), * FROM TEST;
```
--------------------------------
### TIME Data Type Example
Source: https://www.h2database.com/html/datatypes.html
Illustrates basic usage of the TIME data type.
```sql
TIME
```
```sql
TIME(9)
```
--------------------------------
### Searched CASE Expression Examples
Source: https://www.h2database.com/html/grammar.html
Provides examples of searched CASE expressions for conditional logic.
```sql
CASE WHEN CNT<10 THEN 'Low' ELSE 'High' END
```
```sql
CASE WHEN A IS NULL THEN 'Null' ELSE 'Not null' END
```
--------------------------------
### MERGE INTO Example with Aliases
Source: https://www.h2database.com/html/commands.html
Demonstrates merging data into a target table using aliases for both target and source tables, with conditions for updating and deleting matched rows, and inserting unmatched rows.
```sql
MERGE INTO TARGET_TABLE AS T USING SOURCE_TABLE AS S
ON T.ID = S.ID
WHEN MATCHED AND T.COL2 <> 'FINAL' THEN
UPDATE SET T.COL1 = S.COL1
WHEN MATCHED AND T.COL2 = 'FINAL' THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (ID, COL1, COL2) VALUES(S.ID, S.COL1, S.COL2);
```
--------------------------------
### Wildcard Expression Examples
Source: https://www.h2database.com/html/grammar.html
Shows basic wildcard usage and how to exclude specific columns from the selection.
```sql
*
* EXCEPT (DATA)
```
--------------------------------
### MERGE INTO Example with DUAL Table
Source: https://www.h2database.com/html/commands.html
Demonstrates using the DUAL table as a source when no specific source table is needed, for inserting or updating a single row based on a condition.
```sql
MERGE INTO TARGET_TABLE USING DUAL ON ID = 1
WHEN NOT MATCHED THEN INSERT VALUES (1, 'Test')
WHEN MATCHED THEN UPDATE SET NAME = 'Test';
```
--------------------------------
### Interval Year to Month Literal Example
Source: https://www.h2database.com/html/grammar.html
Provides an example of an interval spanning years and months.
```sql
INTERVAL '1-6' YEAR TO MONTH
```
--------------------------------
### INTERVAL HOUR TO MINUTE Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL HOUR TO MINUTE data type.
```sql
INTERVAL HOUR TO MINUTE
```
--------------------------------
### Window Specification Examples
Source: https://www.h2database.com/html/grammar.html
Provides various examples of window specifications, from empty to complex ones with partitioning, ordering, and frame clauses. Window functions in H2 may require significant memory for large queries.
```sql
()
(W1 ORDER BY ID)
(PARTITION BY CATEGORY)
(PARTITION BY CATEGORY ORDER BY NAME, ID)
(ORDER BY Y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
```
--------------------------------
### SHOW Command Syntax
Source: https://www.h2database.com/html/commands.html
The SHOW command is used to list schemas, tables within a schema, or columns of a specific table.
```sql
SHOW { SCHEMAS | TABLES [ FROM schemaName ] |
COLUMNS FROM tableName [ FROM schemaName ] }
```
--------------------------------
### Build Benchmark Command
Source: https://www.h2database.com/html/performance.html
This command is used to build the benchmark. Ensure you have the necessary build tools configured.
```bash
build benchmark
```
--------------------------------
### INTERVAL DAY TO HOUR Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL DAY TO HOUR data type.
```sql
INTERVAL DAY TO HOUR
```
--------------------------------
### INTERVAL YEAR TO MONTH Example
Source: https://www.h2database.com/html/datatypes.html
Example of using the INTERVAL YEAR TO MONTH data type.
```sql
INTERVAL YEAR TO MONTH
```
--------------------------------
### MVStore Version Management
Source: https://www.h2database.com/html/mvstore.html
Demonstrates how to manage versions in MVStore. It shows creating a store, adding data, capturing a version, making further changes, and accessing both the current and old versions.
```java
// create/get the map named "data"
MVMap map = s.openMap("data");
// add some data
map.put(1, "Hello");
map.put(2, "World");
// get the current version, for later use
long oldVersion = s.getCurrentVersion();
// from now on, the old version is read-only
s.commit();
// more changes, in the new version
// changes can be rolled back if required
// changes always go into "head" (the newest version)
map.put(1, "Hi");
map.remove(2);
// access the old data (before the commit)
MVMap oldMap =
map.openVersion(oldVersion);
// print the old version (can be done
// concurrently with further modifications)
// this will print "Hello" and "World":
System.out.println(oldMap.get(1));
System.out.println(oldMap.get(2));
// print the newest version ("Hi")
System.out.println(map.get(1));
```
--------------------------------
### FILE_READ Function Examples
Source: https://www.h2database.com/html/functions.html
Illustrates reading file contents using FILE_READ. The first example reads a file and returns its length as a BLOB. The second example reads a file from a URL as a CLOB using UTF-8 encoding.
```sql
SELECT LENGTH(FILE_READ('~/.h2.server.properties')) LEN;
```
```sql
SELECT FILE_READ('http://localhost:8182/stylesheet.css', NULL) CSS;
```
--------------------------------
### PRIMARY KEY Constraint Example
Source: https://www.h2database.com/html/grammar.html
Example of defining a composite primary key constraint on multiple columns.
```sql
PRIMARY KEY(ID, NAME)
```
--------------------------------
### INTERVAL DAY TO SECOND Example
Source: https://www.h2database.com/html/datatypes.html
Demonstrates the usage of the INTERVAL DAY TO SECOND data type.
```sql
INTERVAL DAY TO SECOND
```