exercise3_answers.txt

# Relational Databases for Biologists

# Session 3: Building and modifying a database with SQL

#

#Exercise 3 SQL and query solutions

#

#Numbers refer to questions of Exercises

# 3 One possible solution is as follows:

CREATE TABLE Data

(

affyId varchar(30) NOT NULL,

exptId varchar(10) NOT NULL,

level int(5),

PRIMARY KEY (affyId, exptId)

);

CREATE TABLE Sources

(

exptId varchar(10) NOT NULL,

source varchar(100),

PRIMARY KEY (exptId)

);

CREATE TABLE Targets

(

affyId varchar(20) NOT NULL,

description varchar(100) NOT NULL,

PRIMARY KEY (affyId)

);

# 6

LOAD DATA LOCAL INFILE "Targets.txt" INTO TABLE Targets;

LOAD DATA LOCAL INFILE "Sources.txt" INTO TABLE Sources;

LOAD DATA LOCAL INFILE "Data_leukemia.txt" INTO TABLE Data;

LOAD DATA LOCAL INFILE "Data_ovarian.txt" INTO TABLE Data;

# 8a

SELECT affyId, level

FROM Data

WHERE exptId = "ovar-1"

ORDER BY level DESC

LIMIT 10;

# 8b

SELECT Data.affyId,Targets.description,Data.level

FROM Data, Targets

WHERE Data.affyId = Targets.affyId

ORDER BY Targets.description

LIMIT 10;

# 12

UPDATE Data

SET level = 2

WHERE level < 2;

# 13

SELECT Data.affyId,Targets.description,Data.level AS Ovarian_cancer,LOG(Data.level)/LOG(2) AS Log_ovarian_cancer,DataCopy.level AS Leukemia,LOG(DataCopy.level)/LOG(2) AS Log_Leukemia,LOG(Data.level) /LOG(DataCopy.level) AS Ratio

FROM Data, Data DataCopy, Targets

WHERE Data.affyId= DataCopy.affyId

AND Data.exptId = "ovar-1"

AND DataCopy.exptId = "leuk-1"

AND Data.affyId = Targets.affyId

ORDER BY Ratio DESC;