Creating the database and running SQL

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

Create the database and open a connection to it using the embedded driver.
```
CONNECT 'jdbc:derby:firstdb;create=true';
```

Create a table with two columns using standard SQL.

CREATE TABLE FIRSTTABLE
    (ID INT PRIMARY KEY,
    NAME VARCHAR(12));

0 rows inserted/updated/deleted

Insert three records.

INSERT INTO FIRSTTABLE VALUES 
    (10,'TEN'),(20,'TWENTY'),(30,'THIRTY');

3 rows inserted/updated/deleted

Perform a simple select of all records in the table.

SELECT * FROM FIRSTTABLE;

     ID         |NAME
    ------------------------
    10         |TEN
    20         |TWENTY
    30         |THIRTY

3 rows selected

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

SELECT * FROM FIRSTTABLE
WHERE ID=20;

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

1 row selected

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

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

Exit the ij tool.
```
exit;
```

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.