CREATE TABLE GTEX (
	id INTEGER PRIMARY KEY,
	analysis_id INTEGER NOT NULL,
	snp_rsid TEXT NOT NULL,
	snp_chromosome TEXT NOT NULL,
	snp_position INTEGER NOT NULL,
	probe_gi TEXT NOT NULL,
	probe_chromosome TEXT NOT NULL,
	probe_position INTEGER NOT NULL,
	gene TEXT,
	gene_id TEXT,
	mRNA TEXT,
	p_value FLOAT,
	r_squared FLOAT,
	description TEXT
) ;

CREATE TABLE snp138 (
    bin INT NOT NULL,
    chrom TEXT NOT NULL,
    chromStart  INT NOT NULL,
    chromEnd    INT NOT NULL,
    name    TEXT NOT NULL,
    score   INT,
    strand  TEXT NOT NULL,
    refNCBI TEXT,
    refUCSC TEXT,
    observed TEXT,
    molType TEXT,
    class   TEXT,
    valid   TEXT,
    avHet   FLOAT,
    avHetSE FLOAT,
    func    TEXT,
    locType TEXT,
    weight  INT,
    exceptions  TEXT,
    submitterCount  INT,
    submitters  TEXT,
    alleleFreqCount INT,
    alleles     TEXT,
    alleleNs     TEXT,
    alleleFreqs  TEXT,
    bitfields TEXT
) ;

.mode tabs
.import GTEX-130925-1732-19664.tab GTEX
SELECT * FROM GTEX LIMIT 10 ;
DELETE FROM GTEX WHERE id == 0 ;

EXPLAIN QUERY PLAN
SELECT COUNT(*) FROM snp138 S
INNER JOIN GTEX G
ON G.snp_rsid == S.name
AND G.p_value < 1E-6
WHERE S.func LIKE '%missense%'
;

CREATE INDEX GTEXSNPIndex ON GTEX( snp_rsid ) ;

EXPLAIN QUERY PLAN
SELECT * FROM snp138 S
INNER JOIN GTEX G
ON G.snp_rsid == S.name
AND G.p_value < 1E-6
WHERE S.func LIKE '%missense%'
;

-- Find out what functional classes eQTLs are.
SELECT func, COUNT(*)
FROM snp138 S
INNER JOIN GTEX G
ON G.snp_rsid == S.name
AND G.p_value < 1E-6
GROUP BY func ;

