# Creating a Derby database and running SQL statements

Now, you will use the Derby `ij` tool to load the Derby database engine. You will use the Derby embedded driver to create and connect to the `firstdb` database. You will also use a few basic SQL statements to create and populate a table.

1. Run the Derby `ij` tool.
   If you included the `DERBY_HOME/bin` directory in your PATH environment variable, type:

       ij

   Otherwise, you can use the `java` command to start the `ij` tool, as shown in the following table.

   Table 1. Command to start the `ij` tool
   Operating System | Command
   --- | ---
   UNIX (Korn Shell) |
       java -jar $DERBY_HOME/lib/derbyrun.jar ij
       ij version 10.13
   Windows |
       java -jar %DERBY_HOME%\lib\derbyrun.jar ij
       ij version 10.13

2. Create the database and open a connection to the database using the embedded driver.

       CONNECT 'jdbc:derby:firstdb;create=true';

   Description of connection command:
     `connect`
       The `ij` command to establish a connection to a database. The Derby connection URL is enclosed in single quotation marks. An `ij` command can be in either uppercase or lowercase.
     `jdbc:derby:`
       The JDBC protocol specification for the Derby driver.
     `firstdb`
       The name of the database. The name can be any string. Because no filepath is specified, the database is created in the default working directory (`DERBYTUTOR`).
     `;create=true`
       The Derby URL attribute that is used to create a database. Derby does not have an SQL `create database` command.
     `;`
       The semicolon is the `ij` command terminator.
3. Create a table with two columns using standard SQL.

       CREATE TABLE FIRSTTABLE
           (ID INT PRIMARY KEY,
           NAME VARCHAR(12));
       0 rows inserted/updated/deleted

4. Insert three records.

       INSERT INTO FIRSTTABLE VALUES
           (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');
       3 rows inserted/updated/deleted

5. Perform a simple select of all records in the table.

       SELECT * FROM FIRSTTABLE;
       ID         |NAME
       ------------------------
       10         |TEN
       20         |TWENTY
       30         |THIRTY

       3 rows selected

6. Perform a qualified select of the record with column ID=20.

       SELECT * FROM FIRSTTABLE WHERE ID=20;
       ID         |NAME
       ------------------------
       20         |TWENTY

       1 row selected

7. Optional: Create and populate additional tables and other schema objects.
   1. Load the SQL script `ToursDB_schema.sql`.

          run 'ToursDB_schema.sql';

          ij> ...
          CREATE TABLE AIRLINES
             (
                AIRLINE CHAR(2) NOT NULL ,
                AIRLINE_FULL VARCHAR(24),
                BASIC_RATE DOUBLE PRECISION,
             ...
          0 rows inserted/updated/deleted
             ... Other output messages not shown ...

   2. Populate the tables with data by running the script `loadTables.sql`.

          run 'loadTables.sql';

          ij> run 'loadCOUNTRIES.sql';
          ij> insert into COUNTRIES values ( 'Afghanistan','AF','Asia');
          1 row inserted/updated/deleted
          ij> insert into COUNTRIES values ( 'Albania','AL','Europe');
          1 row inserted/updated/deleted
             ... Other output messages not shown ...

8. Exit the `ij` tool.

       exit;

   You should be returned to the `DERBYTUTOR` directory.
9. Browse the most important files created by this activity:
   - The `derby.log` file. This file is a message and error log that, under normal circumstances, contains a set of startup messages and a shutdown message.

         ----------------------------------------------------------------
         Thu Sep 13 09:52:15 EDT 2012:
         Booting Derby version The Apache Software Foundation - Apache
           Derby - 10.10.0.0 - (1384314):
           instance a816c00e-0139-bfe6-bff8-000000a155b8
         on database directory C:\DERBYTUTOR\firstdb
           with class loader sun.misc.Launcher$AppClassLoader@9931f5
         Loaded from file:C:\db-derby-10.10.0.0-bin\lib\derby.jar
         java.vendor=Oracle Corporation
         java.runtime.version=1.7.0_07-b11
         user.dir=C:\DERBYTUTOR
         os.name=Windows XP
         os.arch=x86
         os.version=5.1
         derby.system.home=C:\DERBYTUTOR
         Database Class Loader started - derby.database.classpath=''
         ----------------------------------------------------------------
         Thu Sep 13 09:53:21 EDT 2012: Shutting down Derby engine
         ----------------------------------------------------------------
         Thu Sep 13 09:53:21 EDT 2012:
         Shutting down instance a816c00e-0139-bfe6-bff8-000000a155b8 on
           database directory C:\DERBYTUTOR\firstdb
           with class loader sun.misc.Launcher$AppClassLoader@9931f5
         ----------------------------------------------------------------

   - The `firstdb` database directory. Within the directory are the subdirectories `seg0` (containing the data files) and `log` (containing the transaction log files).

Parent topic: Activity 1: Run SQL using the embedded driver