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.

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.

H2 is a database written in Java. We can easily embed this database to our application by using JDBC. We can run this on many different platforms or any version of Java Runtime Environment. However, before installing the database, there should be Java installed in the system.

Verify Java Installation

If JDK is installed in the system, try the following command to verify the Java version.

java –version

If JDk is successfully installed in the system, then we will get the following output.

java version "1.8.0_91"
Java(TM) SE Runtime Environment (build 1.8.0_91-b14)
Java HotSpot(TM) 64-Bit Server VM (build 25.91-b14, mixed mode)

If JDK is not installed in the system, then visit the following link to .

Install H2 Database

We can run this database on many different platforms. In this chapter, we will learn about H2 Database installation on Windows.

Following are the steps to install H2 Database on Windows operating system.

Step 1: Download H2 Setup File

Download the latest version of from the given link. In this link, you will get the latest version of H2 database in two types. One is Windows Installer type (that is .exe file) and second is Platform-Independent zip file for other operating systems.

Click the Windows installer for downloading the Windows supportable H2 database after downloading the .exe file. In this case, we are using H2 Database with the version 1.4.192.

Step 2: Install H2 Database

After downloading we get the H2 Windows installer file (i.e. h2-setup-yyyy-mm-dd.exe) in the Downloads directory. To start the installation process of H2 Database, double click on the installer file.

The following screen is the first step in the installation process. Provide a path where we want to install the H2 database server as shown in the following screenshot.

As seen in the above screenshot, by default it will take C:ProgramFiles (x86)H2 as the destination folder. Click next to proceed to the next step. The following screen pops up.

In the above screenshot, click the Install button to start the installation process. After installation, we get the following screenshot.

Click Finish to complete the installation process.

Step 3: Verify H2 Database Installation

After installation, let us verify the database installation in the system. Click Windows → type H2 Console → Click H2 console icon. Connect to the URL http://localhost:8082. At the time of connecting, the H2 database will ask for database registration as shown in the following screenshot.

Fill all the details in the above dialog box such as Saved Settings, Settings Name, Driver Class, JDBC URL, User Name, and Password. In the JDBC URL, specify the database is located and the database name. User Name and Password are the fields for user name and password of the database. Click Connect.

The Database welcome page pops up as shown in the following screenshot.

Select command is used to fetch record data from a table or multiple tables. If we design a select query, then it returns data in the form of result table called result sets.

Syntax

The basic syntax of SELECT statement is as follows −

SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...] [ WHERE expression ]
[ GROUP BY expression [,...] ] [ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ] [ ORDER BY order [,...] ]
[ [ LIMIT expression ] [ OFFSET expression ] [ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]

To fetch all the available fields, use the following syntax.

SELECT * FROM table_name;

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 |
+----+----------+-----+-----------+----------+

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 fetch ID, Name and Salary fields of the customers available in the CUSTOMER table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

The above command produces the following result.

+----+----------+----------+
| ID | NAME     | SALARY   |
+----+----------+----------+
|  1 | Ramesh   |  2000.00 |
|  2 | Khilan   |  1500.00 |
|  3 | kaushik  |  2000.00 |
|  4 | Chaitali |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal    |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+----------+

Use the following query to fetch all the fields of CUSTOMERS table.

SQL> 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 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+