Creating a new database

We will start with a new database. Type in the following CREATE DATABASE and CREATE TABLE statements at the rdm-sql prompt.

rdm-sql: CREATE DATABASE bicycle_db; 
rdm-sql: CREATE TABLE manufacturer ( 
rdm-sql> name CHAR(20) NOT NULL, 
rdm-sql> state CHAR(3) NOT NULL, 
rdm-sql> PRIMARY KEY (name) 
rdm-sql> ); 
rdm-sql: 

This will create a new database named bicycle_db and a new table named manufacturer in the bicycle_db database.

The semicolon indicates the completion of a statement. You can press <Enter> in the middle of a statement, in which case the prompt changes to indicate that you are in the middle of a statement.

For the data types that you can use in CREATE TABLE, refer to the Data Types section.

Now let's add some data to the newly created manufacturer table. Type the following statements at the prompt to create rows for three bicycle manufacturers.

rdm-sql: INSERT INTO manufacturer VALUES 'Trek', 'WI'; 
*** 1 row(s) inserted 
rdm-sql: INSERT INTO manufacturer VALUES 'Specialized', 'CA'; 
*** 1 row(s) inserted 
rdm-sql: INSERT INTO manufacturer VALUES 'Cannondale', 'CT'; 
*** 1 row(s) inserted 
rdm-sql: 

Execute the following SQL query to verify the data in the manufacturer table.

rdm-sql: SELECT * FROM manufacturer; 
NAME | STATE 
--------------------+------ 
Cannondale | CT 
Specialized | CA 
Trek | WI 
*** 3 row(s) returned 
rdm-sql: 

Let’s add another table, bicycle, to the bicycle_db database and insert some data into the table.

rdm-sql: CREATE TABLE bicycle (
rdm-sql> id ROWID NOT NULL,
rdm-sql> make CHAR(20),
rdm-sql> model CHAR(20) NOT NULL,
rdm-sql> type CHAR(10) NOT NULL,
rdm-sql> PRIMARY KEY (id),
rdm-sql> FOREIGN KEY (make) REFERENCES manufacturer (NAME)
rdm-sql> );
rdm-sql: 
rdm-sql: INSERT INTO bicycle (make, model, type) VALUES 'Trek', 'Domane', 'Road'; 
*** 1 row(s) inserted
rdm-sql: INSERT INTO bicycle (make, model, type) VALUES 'Trek', 'FX', 'Hybrid'; 
*** 1 row(s) inserted
rdm-sql: INSERT INTO bicycle (make, model, type) VALUES 'Cannondale', 'Synapse', 
rdm-sql> 'Road'; 
*** 1 row(s) inserted 

Again, you can verify the inserted data by executing the following query.

rdm-sql: SELECT * FROM bicycle; 
 ID| MAKE | MODEL | TYPE 
--------------------+---------------------+---------------------+----------- 
 1| Trek | Domane | Road 
 2| Trek | FX | Hybrid 
 3| Cannondale | Synapse | Road 
*** 3 row(s) returned 

To save the new data, execute the COMMIT command as follows.

rdm-sql: COMMIT; 
rdm-sql: 

Quit rdm-sql by typing ".q" at the prompt.

rdm-sql: .q