CREATE is a generic SQL command used to create Tables, Schemas, Sequences, Views, and Users in H2 Database server.

Create Table

Create Table is a command used to create a user-defined table in the current database.

Syntax

Following is the generic syntax for the Create Table command.

CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
[ ( { columnDefinition | constraint } [,...] ) ]
[ ENGINE tableEngineName [ WITH tableEngineParamName [,...] ] ]
[ NOT PERSISTENT ] [ TRANSACTIONAL ]
[ AS select ]

By using the generic syntax of the Create Table command, we can create different types of tables such as cached tables, memory tables, and temporary tables. Following is the list to describe different clauses from the given syntax.

• CACHED − The cached tables are the default type for regular tables. This means the number of rows is not limited by the main memory.

• MEMORY − The memory tables are the default type for temporary tables. This means the memory tables should not get too large and the index data is kept in the main memory.

• TEMPORARY − Temporary tables are deleted while closing or opening a database. Basically, temporary tables are of two types −

  • GLOBAL type − Accessible by all connections.

  • LOCAL type − Accessible by the current connection.

  The default type for temporary tables is global type. Indexes of temporary tables are kept in the main memory, unless the temporary table is created using CREATE CACHED TABLE.

• ENGINE − The ENGINE option is only required when custom table implementations are used.

• NOT PERSISTENT − It is a modifier to keep the complete table data in-memory and all rows are lost when the database is closed.

• TRANSACTIONAL − It is a keyword that commits an open transaction and this command supports only temporary tables.

Example

In this example, let us create a table named tutorials_tbl using the following given data.

The following query is used to create a table tutorials_tbl along with the given column data.

CREATE TABLE tutorials_tbl (
   id INT NOT NULL,
   title VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   submission_date DATE
);

The above query produces the following output.

(0) rows effected

Create Schema

Create Schema is a command used to create a user-dependent schema under a particular authorization (under the currently registered user).

Syntax

Following is the generic syntax of the Create Schema command.

CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]

In the above generic syntax, AUTHORIZATION is a keyword used to provide the respective user name. This command is optional which means if we are not providing the user name, then it will consider the current user. The user that executes the command must have admin rights, as well as the owner.

This command commits an open transaction in this connection.

Example

In this example, let us create a schema named test_schema under SA user, using the following command.

CREATE SCHEMA test_schema AUTHORIZATION sa;

The above command produces the following output.

(0) rows effected

Create Sequence

Sequence is concept which is used to generate a number by following a sequence for id or any random column values.

Syntax

Following is the generic syntax of the create sequence command.

CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ]
[ MINVALUE long | NOMINVALUE | NO MINVALUE ]
[ MAXVALUE long | NOMAXVALUE | NO MAXVALUE ]
[ CYCLE long | NOCYCLE | NO CYCLE ]
[ CACHE long | NOCACHE | NO CACHE ]

This generic syntax is used to create a sequence. The datatype of a sequence is BIGINT. In this the sequence, values are never re-used, even when the transaction is roll backed.

Example

In this example, let us create a sequence named SEQ_ID, using the following query.

CREATE SEQUENCE SEQ_ID;

The above query produces the following output.

(0) rows effected

MERGE command is used to update the existing rows and insert new rows into a table. The primary key column plays an important role while using this command; it is used to find the row.

Syntax

Following is the generic syntax of the MERGE command.

MERGE INTO tableName [ ( columnName [,...] ) ]
[ KEY ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }

In the above syntax, the KEY clause is used to specify the primary key column name. Along with VALUES clause, we can use primitive values to insert or we can retrieve and store another table values into this table using the select command.

Example

In this example, let us try to add a new record into Customers table. Following are the details of the new record in the table.

Using the following query, let us insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, ''Lokesh'', 32, ''Hyderabad'', 2500);

The above query produces the following output.

Update count: 1

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER;

The above query produces the following output.

Now let us try to update the record using the Merge command. Following are the details of the record to be updated.

Use the following query to insert the given record into the H2 database query.

MERGE INTO CUSTOMER KEY (ID) VALUES (8, ''Loki'', 32, ''Hyderabad'', 3000);

The above query produces the following output.

Update count: 1

Let us verify the records of the Customer table by executing the following query.

SELECT * FROM CUSTOMER;

The above query produces the following output −

DROP is a command taken from the generic SQL grammar. This command is used to delete a database component and its structure from the memory. There are different scenarios with the Drop command that we will discuss in this chapter.

Drop Table

Drop Table is a command that deletes the respective table and its structure.

Syntax

Following is the generic syntax of the Drop Table command.

DROP TABLE [ IF EXISTS ] tableName [,...] [ RESTRICT | CASCADE ]

The command will fail if we are using RESTRICT and the table having dependent views exist. All dependent views are dropped, when we are using CASCADE keyword.

Example

In this example, we will drop a table named test using the following query.

DROP TABLE test;

The above query produces the following output.

(6) row (s) effected

Drop Schema

Drop Schema is a command that drops a respective schema from the database server. It will not work from the current schema.

Syntax

DROP SCHEMA [ IF EXISTS ] schemaName

Example

In this example, we will drop a schema named test_schema using the following query.

DROP SCHEMA TEST_SCHEMA;

The above query produces the following output.

(0) row(s) effected

Drop Sequence

Drop Sequence is a command used to drop a sequence from the table structure.

Syntax

Following is the generic syntax of the Drop Sequence command.

DROP SEQUENCE [ IF EXISTS ] sequenceName

This command commits an open transaction in this connection.

Example

In this example, we will drop a sequence named sequence_id. Following is the command.

DROP SEQUENCE sequence_id;

The above command produces the following output.

(0) row (s) effected

Drop View

Drop View is a command used to drop the existing view. All dependent views are dropped as well if the CASCADE clause is used.

Syntax

Following is the generic syntax of the Drop View command.

DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]

Example

In this example, we will drop a view named sample_view using the following query.

DROP VIEW sample_view;

The above query produces the following output.

(0) row (s) effected

COMMIT is a command from the SQL grammar used to commit the transaction. We can either commit the specific transaction or we can commit the currently executed transaction.

Syntax

There are two different syntaxes for COMMIT command.

Following is the generic syntax for the commit command to commit the current transaction.

COMMIT [ WORK ]

Following is the generic syntax for the commit command to commit the specific transaction.

COMMIT TRANSACTION transactionName

Example 1

In this example, let us commit the current transaction using the following command.

COMMIT

The above command produces the following output.

Committed successfully

Example 2

In this example, we will commit the transaction named tx_test using the following command.

COMMIT TRANSACTION tx_test;

The above command produces the following output.

Committed successfully

ALTER is a command used to change the table structure by adding different clauses to the alter command. Based on the scenario, we need to add respective clause to the alter command. In this chapter, we will discuss various scenarios of alter command.

Alter Table Add

Alter Table Add is a command used to add a new column to a table along with the respective data type. This command commits the transaction in this connection.

Syntax

Following is the generic syntax of the Alter Table Add command.

ALTER TABLE [ IF EXISTS ] tableName ADD [ COLUMN ]
{ [ IF NOT EXISTS ] columnDefinition [ { BEFORE | AFTER } columnName ]
   | ( { columnDefinition } [,...] ) }

Example

In this example, we will add a new column start_date to the table tutorials_tbl. The datatype for start_date is Date. Following is the query to add a new column.

ALTER TABLE tutorials_tbl ADD start_date DATE;

The above query produces the following output.

(6) rows effected

Alter Table Add Constraint

Alter table add constraint is a command used to add different constraints to the table such as primary key, foreign key, not null, etc.

The required indexes are automatically created if they don’t exist yet. It is not possible to disable checking for unique constraint. This command commits an open transaction in this connection.

Syntax

Following is the generic syntax of the Alter table add constraint command.

ALTER TABLE [ IF EXISTS ] tableName ADD constraint [ CHECK | NOCHECK ]

Example

In this example, let us add a primary key constraint (tutorials_tbl_pk) to the column id of the table tutorials_tbl, using the following query.

ALTER TABLE tutorials_tbl ADD CONSTRAINT tutorials_tbl_pk PRIMARYKEY(id);

The above query produces the following output.

(6) row (s) effected

Alter Table Rename Constraint

This command is used to rename the constraint name of a particular relation table. This command commits an open transaction in this connection.

Syntax

Following is the generic syntax of the Alter Table Rename Constraint command.

ALTER TABLE [ IF EXISTS ] tableName RENAME oldConstraintName TO newConstraintName

While using this syntax, make sure that the old constraint name should exist with the respective column.

Example

In this example, we will change the primary key constraint name of the table tutorials_tbl from tutorials_tbl_pk to tutorials_tbl_pk_constraint. Following is the query to do so.

ALTER TABLE tutorials_tbl RENAME CONSTRAINT
tutorials_tbl_pk TO tutorials_tbl_pk_constraint;

The above query produces the following output.

(1) row (s) effected

Alter Table Alter Column

This command is used to change the structure and properties of the column of a particular table. Changing the properties means changing the datatype of a column, rename a column, change the identity value, or change the selectivity.

Syntax

Following is the generic syntax of the Alter Table Alter Column command.

ALTER TABLE [ IF EXISTS ] tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] }
| { RENAME TO name }
| { RESTART WITH long }
| { SELECTIVITY int }
| { SET DEFAULT expression }
| { SET NULL }
| { SET NOT NULL } }

In the above syntax −

• RESTART − command changes the next value of an auto increment column.

• SELECTIVITY − command sets the selectivity (1-100) for a column. Based on the selectivity value we can image the value of the column.

• SET DEFAULT − changes the default value of a column.

• SET NULL − sets the column to allow NULL.

• SET NOT NULL − sets the column to allow NOT NULL.

Example

In this example, we will rename the column of the table tutorials_tbl from Title to Tutorial_Title using the following query.

ALTER TABLE tutorials_tbl ALTER COLUMN title RENAME TO tutorial_title;

The above query produces the following output.

(0) row(s) effected

In a similar way, we can perform different scenarios with the ALTER command.

BACKUP is the command used to take database backup into a separate .zip file. Objects are not locked, and when it takes backup the transaction log is also copied. Admin rights are required to execute this command.

Syntax

Following is the generic syntax of the Backup command.

BACKUP TO fileNameString;

Example

In this example, let us take a backup of the current database into backup.zip file. Use the following command for the same.

BACKUP TO ''backup.zip

On executing the above command, you will get the backup.zip file in your local file system.

CALL is a SQL command which belongs to H2 database server. This command is used to calculate a simple expression. It returns the result of the given expression in a single column field. When it returns an array of results, then each element in the array is displayed as a column value.

Syntax

Following is the generic syntax of the CALL command.

CALL expression;

We can use the arithmetic expression in this syntax.

Example

Let us take an example and execute an arithmetic expression (15 * 25) using call command.

CALL 15*25;

The above command produces the following output.

The SQL INSERT statement is used to add new rows of data to a table in the database.

Syntax

Following is the basic syntax of INSERT INTO statement.

INSERT INTO tableName
{ [ ( columnName [,...] ) ]
{ VALUES
{ ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select } } |
{ SET { columnName = { DEFAULT | expression } } [,...] }

Using this INSERT statement, we can insert a new record or new rows into a table. When using DIRECT clause, the results are directly affected to the target table without any intermediate step. However, while adding values for all the columns of the table, make sure the order of the values is in the same order as the columns in the table.

Example

Let us take an example and try to insert the following given records into the Customer table.

We can get all the given records into the customer table by executing the following commands.

INSERT INTO CUSTOMER VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000);
INSERT INTO CUSTOMER VALUES (2, ''Khilan'', 25, ''Delhi'', 1500);
INSERT INTO CUSTOMER VALUES (3, ''kaushik'', 23, ''Kota'', 2000);
INSERT INTO CUSTOMER VALUES (4, ''Chaitali'', 25, ''Mumbai'', 6500);
INSERT INTO CUSTOMER VALUES (5, ''Hardik'', 27, ''Bhopal'', 8500);
INSERT INTO CUSTOMER VALUES (6, ''Komal'', 22, ''MP'', 4500);
INSERT INTO CUSTOMER VALUES (7, ''Muffy'', 24, ''Indore'', 10000);

EXPLAIN command displays the execution plan for a statement. When we execute a statement using EXPLAIN ANALYZE command, the query plan will include the actual row scan count for each table.

Syntax

Following is the generic syntax of the EXPLAIN command.

EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge}

Along with this syntax we can use select, insert, delete, and merge.

Example

This example explains the query plan details of the customer with ID 1.

EXPLAIN SELECT * FROM CUSTOMER WHERE ID = 1;

The above command produces the following output −

H2 is an open-source lightweight Java database. It can be embedded in Java applications or run in the client-server mode. Mainly, H2 database can be configured to run as inmemory database, which means that data will not persist on the disk. Because of embedded database it is not used for production development, but mostly used for development and testing.

This database can be used in embedded mode or in server mode. Following are the main features of H2 database −

• Extremely fast, open source, JDBC API
• Available in embedded and server modes; in-memory databases
• Browser-based Console application
• Small footprint − Around 1.5MB jar file size

Features of H2 Database

The main features of H2 Database are as follows −

• It is an extremely fast database engine.

• H2 is open source and written in Java.

• It supports standard SQL and JDBC API. It can use PostgreSQL ODBC driver too.

• It has embedded and Server mode.

• H2 supports clustering and multi-version concurrency.

• It has strong security features.

Additional Features

Following are some additional features of H2 Database −

• H2 is a disk-based or in-memory databases and tables, read-only database support, temporary tables.

• H2 provides transaction support (read committed), 2-phase-commit multiple connections, table level locking.

• H2 is a cost-based optimizer, using a genetic algorithm for complex queries, zeroadministration.

• H2 contains scrollable and updatable result set support, large result set, external result sorting, functions can return a result set.

• H2 supports encrypted database (AES), SHA-256 password encryption, encryption functions, and SSL.

Components in H2 Database

In order to use H2 Database, you need to have the following components −

• A web browser
• A H2 console server

This is a client/server application, so both server and client (a browser) are required to run it.