This lab will give you practice creating tables in SQLite, a free lightweight tool that implements a much but not all of the SQL standard. It is powerful enough for our purposes, and simple to install and use. You can download SQLite for your own computer if you like. However, these lab instruction will assume that you are working on beowulf.
Database Creation
One of the advantages of SQLite is that it stores its databases in a single file. This makes it easy to run from the command line, and also makes the data base itself easy to move around. We will be using SQLite version 3, which uses an internal file format that is incompatible with the older version 2 format. Run the program from the command line with the name of the database file you wish to create:
beowulf$ sqlite3 olympics.db3
This will bring you to the SQLite command prompt, where you can issue database commands and also metacommands such as .help and .quit. Note that the metacommands are all preceded by a period.
Let's go ahead and create a table. This will store information about athletes, and have three fields.
sqlite> CREATE TABLE Athletes (name TEXT NOT NULL, country TEXT, athleteId INTEGER PRIMARY KEY);
Now we can start to add tuples to the table.
sqlite> INSERT INTO Athletes VALUES("Michael Phelps",25,NULL);
Oops! We entered Michael's age as the country attribute. If we forgot an attribute that our table should include (hopefully a rare event), we can fix that by ALTERing the table. We should also UPDATE it to fix the country value and add Michael's age. Finally we can use .dump to look at the data now in our table as the result of all these changes. (Note that this comes in the form of more SQL commands that we could have used to create the table somewhat more directly in the first place.)
sqlite> ALTER TABLE Athletes ADD COLUMN age INTEGER; sqlite> UPDATE Athletes SET country="USA", age=23 WHERE name="Michael Phelps"; sqlite> .dump
Now it's your turn! Add the following athletes to the table, and the attribute gender.
| Name | Age | Gender | Country |
|---|---|---|---|
| Natalie Coughlin | 26 | F | USA |
| Kirsty Coventry | 25 | F | Zimbabwe |
| Nastia Liukin | 19 | F | USA |
| Wei Yang | 28 | M | China |
| Yilin Yang | 16 | F | China |
Another Table
Now create another table, Medals. This should have fields for the event, color of the medal, and name of the winner. Fill in some values using the information at this site -- or make up your own; it doesn't really matter. Make sure that you have several medals listed for at least one event, and at least one person with several medals. This will set up your database for queries, which we will explore later.