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

SHOW is a command used to display the list of Schemas, Tables, or Columns of the table.

Syntax

Following is the generic syntax of the SHOW command.

SHOW { SCHEMAS | TABLES [ FROM schemaName ] |
COLUMNS FROM tableName [ FROM schemaName ] }

Example

The following command can be used to get the list of tables in the current database.

SHOW TABLES;

The above command produces the following output.

Grant is a command coming from the SQL grammar used to grant the rights to a table, to a user, or to a role. Admin rights are required to execute this command. This command commits an open transaction in this connection.

In this chapter, we will discuss the different scenarios of Grant command.

Grant Right

Grant Right is a command to provide admin rights to a table, to a user, or to a role.

Syntax

Following is the generic syntax of the Grant command.

GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } [,...] ON
{ { SCHEMA schemaName } | { tableName [,...] } }
TO { PUBLIC | userName | roleName }

Example

In this example, we will grant the test table as read-only using the following command.

GRANT SELECT ON TEST TO READONLY

The above command produces the following output.

Grant successfully

Grant Alter Any Schema

Grant Alter Any Schema is a command to grant schema altering rights to a respective user.

Syntax

Following is the generic syntax of the Grant Alter Any Schema command.

GRANT ALTER ANY SCHEMA TO userName

Example

In this example, we will grant altering privileges of a schema to a user named test_user. Make sure that test_user exists. Following is the query to grant altering privileges.

GRANT ALTER ANY SCHEMA TO test_user;

The above query produces the following output.

Granted successfully to test_user

SAVEPOINT is a command used to temporarily save the transaction. It is better to maintain savepoints in your transaction as it is helpful to roll back the transaction to the respective Savepoint whenever necessary.

Syntax

Following is the generic syntax of the Savepoint command.

SAVEPOINT savepointName

Example

In this example, we will create a Savepoint named Half_Done using the following command.

SAVEPOINT Half_Done;

The above command produces the following output.

Savepoint created

The UPDATE query is used to update or modify the existing records in a table. We can use WHERE clause with UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

Following is the basic syntax of the UPDATE query.

UPDATE tableName [ [ AS ] newTableAlias ] SET
{ { columnName = { DEFAULT | expression } } [,...] } |
{ ( columnName [,...] ) = ( select ) }
[ WHERE expression ] [ ORDER BY order [,...] ] [ LIMIT expression ]

In this UPDATE syntax, we can combine more than one condition by using AND or OR clauses.

Example

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If you want to get the customer table along with the given data, execute the following queries.

CREATE TABLE CUSTOMER (id number, name varchar(20), age number, address varchar(20),
   salary number);
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);

The following command is an example, which would update ADDRESS for a customer whose ID is 6 −

UPDATE CUSTOMERS SET ADDRESS = ''Pune'' WHERE ID = 6;

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

The above query produces the following result.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | Pune      |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

To modify all ADDRESS and SALARY column values in CUSTOMERS table, we need not use the WHERE clause. The UPDATE query would be as follows −

UPDATE CUSTOMERS SET ADDRESS = ''Pune'', SALARY = 1000.00;

Now, CUSTOMERS table would have the following records. We can check the customer table records by executing the following query.

SELECT * FROM CUSTOMERS;

The above query produces the following result −

+----+----------+-----+---------+---------+
| ID | NAME     | AGE | ADDRESS | SALARY  |
+----+----------+-----+---------+---------+
|  1 | Ramesh   |  32 | Pune    | 1000.00 |
|  2 | Khilan   |  25 | Pune    | 1000.00 |
|  3 | kaushik  |  23 | Pune    | 1000.00 |
|  4 | Chaitali |  25 | Pune    | 1000.00 |
|  5 | Hardik   |  27 | Pune    | 1000.00 |
|  6 | Komal    |  22 | Pune    | 1000.00 |
|  7 | Muffy    |  24 | Pune    | 1000.00 |
+----+----------+-----+---------+---------+

The SQL DELETE query is used to delete the existing records from a table. We can use WHERE clause with DELETE query to delete selected records, otherwise all the records will be deleted.

Syntax

Following is the generic query syntax of the delete command.

DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]

The above syntax deletes the rows from a table. If TOP or LIMIT is specified, at most the specified number of rows are deleted (no limit if null or smaller than zero).

Example

Consider the CUSTOMER table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following command will delete the details of the customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

After execution of the above command, check the Customer table by executing the following command.

SELECT * FROM CUSTOMERS;

The above command produces the following output −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

If we want to DELETE all the records from CUSTOMERS table, we do not use WHERE clause. The DELETE query would be as follows.

DELETE FROM CUSTOMER;

After executing the above command, no records will be available in the Customer table.