# 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.16
   Windows |
       java -jar %DERBY_HOME%\lib\derbyrun.jar ij
       ij version 10.16

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.

         ----------------------------------------------------------------
         Wed Nov 28 09:52:36 PST 2018:
         Booting Derby version The Apache Software Foundation - Apache Derby - 10.15.0.0 alpha - (1847657M): instance a816c00e-0167-5b73-7c80-0000033b46b8
         on database directory /Users/rhillegas/sw/z/activity1/DERBYTUTOR/firstdb with class loader jdk.internal.loader.ClassLoaders$AppClassLoader@4f8e5cde
         Loaded from file:/Users/rhillegas/sw/z/db-derby-10.15.0.0-bin/lib/derby.jar
         java.vendor=Oracle Corporation
         java.runtime.version=9+181
         user.dir=/Users/rhillegas/sw/z/activity1/DERBYTUTOR
         os.name=Mac OS X
         os.arch=x86_64
         os.version=10.11.6
         derby.system.home=null
         Database Class Loader started - derby.database.classpath=''
         ----------------------------------------------------------------
         Wed Nov 28 09:55:14 PST 2018: Shutting down Derby engine
         ----------------------------------------------------------------
         Wed Nov 28 09:55:14 PST 2018:
         Shutting down instance a816c00e-0167-5b73-7c80-0000033b46b8 on database directory /Users/rhillegas/sw/z/activity1/DERBYTUTOR/firstdb with class loader jdk.internal.loader.ClassLoaders$AppClassLoader@4f8e5cde
         ----------------------------------------------------------------

   - 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