1 DROP TABLE affy_annotation;
2 DROP TABLE gcrma_expression;
4 DROP TABLE gcrma_samples;
6 CREATE TABLE gcrma_samples (
7 id SERIAL NOT NULL, -- PRIMARY KEY,
11 CREATE TABLE affy_probe (
12 id SERIAL NOT NULL, -- PRIMARY KEY,
16 CREATE TABLE affy_annotation (
17 id SERIAL NOT NULL, -- PRIMARY KEY,
18 probe INT NOT NULL,-- REFERENCES affy_probe,
25 refseq_transcript TEXT
28 CREATE TABLE gcrma_expression (
29 id SERIAL NOT NULL, -- PRIMARY KEY,
30 probe INT NOT NULL,-- REFERENCES affy_probe,
31 sample INT NOT NULL,-- REFERENCES gcrma_samples,
32 expression FLOAT NOT NULL
39 ge.expression AS expression,
40 aa.gene_symbol AS symbol
41 FROM gcrma_expression ge
42 JOIN affy_probe ap ON ge.probe=ap.id
43 JOIN gcrma_samples gs ON ge.sample=gs.id
44 LEFT OUTER JOIN affy_annotation aa ON ap.id=aa.probe;