SQL Hello World: A simple guide to database systems using HSQLDB

In this tutorial, we will learn how to create a simple database and play with its content. The example we will consider is a simple phone book to store names, last names and phone numbers for people.

1) We need to download the DBMS  ( HSQLDB ): [a software that eases and facilitates accessing, managing and controlling the database]

[info]You need to download the DBMS : HSQLDB from here [/info]

2) Double click on the downloaded file ( hsqldb.jar ) to run it.

3) Click [OK]  

4)  You will get the main screen for the DBMS, As shown below : The top section of the screen is for writing SQL queries: the bottom section for showing the results.

5) Let’s initially create the database or schema using the following command:

create schema mybook ;

and then click the button : Execute SQL

6) We need to create a table to store our data. Let’s call the table : contacts.

Every person or contact to be stored in this table should have attributes relate to it. These attributes are for this case: name, lastname, phone. The attribute in database systems should have a type as : varchar, text, int, date …… and so on.

[warning]Note that the syntax of SQL including the attribute types differ from DBMS to DBMS. Example, the HSQLDB don’t have the type TEXT , whilst MySQL does have it[/warning]

The SQL code to create such a simple data table is :

create table contacts(
  name varchar(20),
  lastname varchar(20),
  phone varchar(20)
);

Click [Clear] to delete the top section of the DBMS, and type the SQL code above,   click [Execute SQL] code:

7) We need now to insert some data into the database tab. The code is shown below:

insert into contacts values ('Jerry','Yang','911') ;

Make sure you first click Clear, type the SQL code above and click Execute SQL

8) So, we have some data store, let’s view the data, we use the SELECT query as :

select * from contacts ;