Before explaining the details of the database explorer, it's worth giving an introduction to SQL—a central topic in relational database terminology.

SQL, that is, Structured Query Language is the language that lets you access and manipulate databases in a RDBMS.

SQL can be divided into two parts—DDL and DML.

The DDL, that is, Data Definition Language is the branch of the language that basically allows creating or deleting databases and tables.

The following is an example of DDL. It is the DDL statement that creates the CUSTOMERS table.

CREATE TABLE CUSTOMERS
(
CUSTOMERNUMBER INTEGER
, CUSTOMERNAME VARCHAR(50)
, CONTACTLASTNAME VARCHAR(50)
, CONTACTFIRSTNAME VARCHAR(50)
, PHONE VARCHAR(50)
, ADDRESSLINE1 VARCHAR(50)
, ADDRESSLINE2 VARCHAR(50)
, CITY VARCHAR(50)
, STATE VARCHAR(50)
, POSTALCODE VARCHAR(15)
, COUNTRY VARCHAR(50)
, SALESREPEMPLOYEENUMBER INTEGER
, CREDITLIMIT BIGINT
)
;

This DDL statement tells the database to create the table CUSTOMERS with the columns CUSTOMERNUMBER of the type INTEGER, the column CUSTOMERNAME of the type VARCHAR with length 50, and so on.

Note that INTEGER, VARCHAR, and BIGINT are HSQLDB types of data, not PDI ones. The DML, that is, Data Manipulation Language allows you to retrieve data from a database. It also lets you insert, update, or delete data from the database.

The statement you typed in the SQL editor is an example of DML:

SELECT
CUSTOMERNUMBER
, CUSTOMERNAME
, CITY
, COUNTRY
FROM CUSTOMERS

This statement is asking the database to retrieve all the rows for the CUSTOMERS table, showing only CUSTOMERNUMBER, CUSTOMERNAME, CITY, and COUNTRY columns. After you clicked Execute, PDI queried the database and showed you a window with the data you had asked for.

If you were to leave the following statement:

SELECT * FROM CUSTOMERS

the window would have showed you all columns for the CUSTOMERS table.

SELECT is the statement that allows you to retrieve data from one or more tables. It is the most commonly used DML statement and you're going to use it a lot when working with databases in PDI. You will learn more about the SELECT statement in the next section of this chapter.

Other important DML statements are:

It is important to understand the meaning of these basic statements, but you are not forced to learn them as PDI offers you ways to insert, update, and delete without typing any SQL statement.

Although SQL is a standard, each database engine has its own version of the SQL language. However, all database engines support the main commands.

If you are interested in learning more about SQL, there are a lot of tutorials on the Internet. The following are a few useful links with tutorials and SQL references:

Until now, you have used only HSQLDB. In the tutorials to come, you will also work with the MySQL database engine. So, you may be interested in specific documentation for MySQL, which you can find at http://dev.mysql.com/doc/. You can find even more information in books; there are plenty of books available about both SQL language and MySQL databases.

The database explorer allows you to explore any configured database. When you open the database explorer, the first thing you see is a tree with the different objects of the database. As soon as you select a database table, all buttons to the right side become available for you to explore that table. The following are the functions offered by the buttons at the right side of the database explorer: