# Creating the database and running SQL

1. Run the Derby ij tool.
   On Windows platforms:

         java -jar %DERBY_HOME%\lib\derbyrun.jar ij

         ij version 10.2

   On UNIX Korn Shell platforms:

         java -jar $DERBY_HOME/lib/derbyrun.jar ij

         ij version 10.2

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

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

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. Preform a qualified select of the record with column ID=20.

       SELECT * FROM FIRSTTABLE
       WHERE ID=20;

           ID         |NAME
           ------------------------
           20         |TWENTY

       1 row selected

7. Load the SQL script `ToursDB_schema.sql` to create the tables and other schema objects (this step is optional).

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

   1. 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;

9. Browse the key files created by the activity.
   - The derby.log message and error log file. Under normal circumstances it will contain a set of startup messages and a shutdown message.

         ----------------------------------------------------------------
         2006-09-21 23:33:37.564 GMT:
          Booting Derby version The Apache Software Foundation
            - Apache Derby - 10.2.1.6 - (452058):
            instance c013800d-0109-7f82-e11f-000000119a68
             on database directory C:\DERBYDBS\FIRSTDB
         Database Class Loader started - derby.database.classpath=''

         2006-09-21 23:44:13.178 GMT:
         Shutting down instance c013800d-0109-7f82-e11f-000000119a68
         ----------------------------------------------------------------

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

Description of connection command: `connect 'jdbc:derby:firstdb;create=true';`
  `connect` - the ij command to establish a connection to a database
  The Derby connection URL enclosed in single quotes:
  - `jdbc:derby:` - JDBC protocol specification for the Derby driver.
  - `firstdb` - the name of the database, this can be any string. Because no filepath is specified the database will be created in the default working directory (`DERBYDBS`).
  - `;create=true` - The Derby URL attribute used to create databases. Derby does not have an SQL create database command.
  `;` (semicolon) - the ij command terminator.

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