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