If a pair of genes is positively correlated, what is the possibility of it being composed of two genes both with over 10 chromosomes?
Positively correlated means Expression_Corr > 0; Possibility = count(the pairs of genes with both chromosomes over 20) / count(pairs of genes that are positively correlated)
SELECT CAST(SUM(IIF(T1.Chromosome > 10 AND T3.Chromosome > 10, 1, 0)) AS REAL) * 100 / COUNT(T1.GeneID) FROM Genes AS T1 INNER JOIN Interactions AS T2 ON T1.GeneID = T2.GeneID1 INNER JOIN Genes AS T3 ON T3.GeneID = T2.GeneID2 WHERE T2.Expression_Corr > 0
CREATE TABLE Classification
(
    GeneID       TEXT   not null
        primary key,
    Localization TEXT not null
);
CREATE TABLE Genes
(
    GeneID       TEXT   not null,
    Essential    TEXT not null,
    Class        TEXT not null,
    Complex      TEXT null,
    Phenotype    TEXT not null,
    Motif        TEXT not null,
    Chromosome   INTEGER          not null,
    Function     TEXT not null,
    Localization TEXT not null,
    foreign key (GeneID) references Classification (GeneID)
            on update cascade on delete cascade
);
CREATE TABLE Interactions
(
    GeneID1         TEXT     not null,
    GeneID2         TEXT      not null,
    Type            TEXT    not null,
    Expression_Corr REAL not null,
    primary key (GeneID1, GeneID2),
    foreign key (GeneID1) references Classification (GeneID)
            on update cascade on delete cascade,
    foreign key (GeneID2) references Classification (GeneID)
            on update cascade on delete cascade
);
