Database Creation, How To

From SwinBrain

In this how to we will use the Apache Derby database management system, a free, open source, DBMS written in the Java programming language.

This is a How To article designed to give step-by-step instructions. Search SwinBrain and the external links if you require more detailed information about this topic.
The content of this section was created for Derby 10.3. This may not be relevant for different versions of this software.

Getting Started

In this article we will use Apache Derby as the DBMS. You will need to install and setup the Apache Derby on your machine before you start this walk through. The Installing Apache Derby article covers the basics of downloading and installing the required tools. Once you have Derby installed you will need to launch the ij tool and connect to a new database...

home:~ acain$ ij
ij version 10.2
ij> connect 'jdbc:derby:test;create=true';
ij>

Using the Database

Quick Guide
Need a SQL refresher? Use this SQL Quick Guide.

Lets start by creating a table for Supplier and Product information. The ij tool is not the best for working with SQL dynamically, so create your scripts in a text editor and then copy them into the ij tool to execute. We will use the following SQL commands to create these two tables.

--Create a Supplier table
CREATE TABLE Supplier
(
    SupplierCode VARCHAR(10) NOT NULL PRIMARY KEY,
    Supplier VARCHAR(20) NOT NULL
);
 
--Create a product table
CREATE TABLE Product
(
    ProductCode VARCHAR(10) NOT NULL PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Cost DECIMAL(7,2) NOT NULL,
    SupplierCode VARCHAR(10) REFERENCES Supplier(SupplierCode)
);

Run these in the ij tool to create the two tables in the test database. To check that this worked use the show tables; command to view the tables that have been created. Show tables will indicate that both of these tables are in the App schema.

Now lets add some data into the tables. The following script can be used to insert some sample data into these tables.

--Add Suppliers
INSERT INTO Supplier VALUES ('Fred1', 'Freds Supplies');
INSERT INTO Supplier VALUES ('Stuff', 'We Supply Stuff');
 
--Add Products
INSERT INTO Product VALUES ('W123-a', 'Widget type 3',0.25, 'Fred1');
INSERT INTO Product VALUES ('C156-e', 'Large cog', 5.40, 'Stuff');
INSERT INTO Product VALUES ('W871', 'Small Widget type 7', 0.05, 'Stuff');

Now to test that this has all worked run the following select statements.

SELECT * FROM Product, Supplier;
 
SELECT * FROM Product, Supplier 
WHERE Product.SupplierCode = Supplier.SupplierCode;
 
SELECT Supplier, ProductCode, Cost
FROM Product, Supplier
WHERE Product.SupplierCode = Supplier.SupplierCode;
 
SELECT Supplier, ProductCode, Cost
FROM Product INNER JOIN Supplier ON Product.SupplierCode = Supplier.SupplierCode;

Summary

In this how to you have installed and configured derby. You have used SQL statements to create, insert, and select data from a derby database.

[edit]Links: Topic Brains
Return to Swin Brain