H2 Database - Select

Feb 3, 2022 - 01:51

0 20

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

Syntax

The basic syntax of the 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 get all available fields, use the following syntax.

SELECT * FROM table_name;

example

Consider a CUSTOMER table that has 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 data, run 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 that retrieves the 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 output.

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