Relational Databases for Biologists

 

Session 3: Building and modifying a database with SQL

 

Exercises

 

 

Goals: Generate SQL to create tables and input data. Use SQL to query, modify, and delete data in your database.

 

See /education/bioinfo2005/db4bio/ for the course page.

See http://www.mysql.com/documentation/                                      for mySQL documentation.

See http://www.mysql.com/doc/en/Tutorial.html                          for an SQL tutorial.

 

1. Login to Hebrides and go to your home directory with the commands:

 

                  ssh hebrides.wi.mit.edu –l YOURUSERNAME

cd (cd = Òchange directoryÓ in Unix)

 

Since youÕll be in your home directory, any data or SQL files located there can be piped/imported/loaded into MySQL.

 

Log in to your new MySQL database (named after your last name) on hebrides:

 

mysql –u username –D username_db -p

(replace username_db with your TRUE username, e.g. guest1_db)

 

2. Manually create a new table by typing the following at the mysql prompt:

 

                  CREATE TABLE Targets

(

                                    gbId                        VARCHAR(20)              NOT NULL,

                                    affyId                     VARCHAR(20)              NOT NULL,

                                    species                   VARCHAR(20),

                                    PRIMARY KEY (affyId)

);

 

                  Notice the use of two attributes to represent the primary key.

 

3. Perform the following command from the shell prompt (not MySQL prompt) to copy an SQL script to your home directory:

 

                  cp /home/latek/create.sql .

                  (thatÕs a necessary period preceeded by a space)

 

Automatically create the rest of the db4bio tables by loading the create.sql script, now located in your home directory:

 

                  mysql > SOURCE create.sql

 

 

4. Check that your tables were created as you wanted:

 

show tables;

describe table_name; (replacing table_name with each table in turn)

 

6. Load data into a table

 

                  INSERT INTO Data VALUES(Ò1000_atÓ,Ómy_experimentÓ,156);

 

7. Use SELECT on this table to check that your data was entered properly. Then modify the data:

                 

                  UPDATE Data SET level=358;

                  SELECT * FROM DATA;

 

What happened?

 

8. Remove a table

 

                  SHOW TABLES;

DROP TABLE Targets;

                  SHOW TABLES;

 

Notice the difference?

 

9. Remove data from your Data table:

 

DELETE FROM Data WHERE affyId=Ó1000_atÓ;

                  SELECT * FROM Data WHERE affyId=Õ1000_atÓ;