Relational Databases for Biologists


Session 3: Building and modifying a database with SQL





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                                      for mySQL documentation.

See                          for an SQL tutorial.


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


                  ssh –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;


                  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”;