insert IGNORE into SCGO16032013.tbschema (intTRMid, strTRMname,
intQtdeTermosAdjEhPai, intQtdeTermosAdjEhFilho, intDistFilhoMax, intQtdeFilhos, fltDistMediaFilhos, fltDistDesvPadFilhos, intDistPaiMax, intQtdePais, fltDistMediaPais,
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF 95
intNroSpecies, intNroGeneProduct, fltMediaProductCountSpecies, intQtdeSeqs, fltPorcAlanine, fltPorcArginine, fltPorcAsparagine, fltPorcAsparticAcid, fltPorcCysteine,
fltPorcGlutamicAcid, fltPorcGlutamine, fltPorcGlycine, fltPorcHistidine, fltPorcIsoleucine, fltPorcLeucine, fltPorcLysine, fltPorcMethionine, fltPorcPhenylalanine, fltPorcProline, fltPorcSerine, fltPorcThreonine, fltPorcTryptophan, fltPorcTyrosine, fltPorcValine, strTRMterm type)
select distinct GPH.term2 id as intTRMid, (select TRM3.name
from term TRM3
where TRM3.id = GPH.term2 id) as strTRMname, (select count(T2T2.term2 id)
from term2term T2T2
where T2T2.term1 id = GPH.term2 id) as intQtdeTermosAdjEhPai, (select count(T2T2.term1 id)
from term2term T2T2
where T2T2.term2 id = GPH.term2 id) as intQtdeTermosAdjEhFilho , (select max(GPH2.distance)
from graph path GPH2
where GPH2.term1 id = GPH.term2 id) as intDistFilhoMax , (select count(GPH2.distance)
from graph path GPH2
where GPH2.term1 id = GPH.term2 id) as intQtdeFilhos , (select avg(GPH2.distance)
from graph path GPH2
where GPH2.term1 id = GPH.term2 id) as fltDistMediaFilhos , (select std(GPH2.distance)
from graph path GPH2
where GPH2.term1 id = GPH.term2 id) as fltDistDesvPadFilhos , (select max(GPH2.distance)
from graph path GPH2
where GPH2.term2 id = GPH.term2 id) as intDistPaiMax , (select count(GPH2.distance)
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF 96
from graph path GPH2
where GPH2.term2 id = GPH.term2 id) as intQtdePais , (select avg(GPH2.distance)
from graph path GPH2
where GPH2.term2 id = GPH.term2 id) as fltDistMediaPais , (select count(GPC.species id)
from gene product count GPC
where GPC.term id = GPH.term2 id) as intNroSpecies , (select sum(GPC.product count)
from gene product count GPC
where GPC.term id = GPH.term2 id) as intNroGeneProduct, (select sum(GPC.product count)/count(GPC.species id) from gene product count GPC
where GPC.term id = GPH.term2 id) as fltMediaProductCountSpecies, (select count(SEQ.seq)
from gene product seq GPS, association ASS, seq SEQ where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as intQtdeSeqs, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’A’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcAlanine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’R’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcArginine, (select (sum((SELECT
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF 97
from gene product seq GPS, association ASS, seq SEQ where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcAsparagine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’D’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcAsparticAcid, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’C’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcCysteine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’E’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcGlutamicAcid, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’Q’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcGlutamine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’G’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF 98
(select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’H’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcHistidine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’I’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcIsoleucine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’L’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcLeucine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’K’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcLysine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’M’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcMethionine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’F’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF 99
and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcPhenylalanine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’P’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcProline, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’S’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcSerine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’T’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcThreonine, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’W’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcTryptophan, (select (sum((SELECT
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’Y’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcTyrosine , (select (sum((SELECT
A.2 Consultas SQL utilizadas na obtenc¸˜ao dos registros relacionados `as subclasses de BP, CC e MF100
LENGTH(SEQ.seq)-LENGTH(REPLACE(SEQ.seq,’V’,”))))/sum(SEQ.seq len)) from gene product seq GPS, association ASS, seq SEQ
where GPS.gene product id = ASS.gene product id and GPS.seq id = SEQ.id
and ASS.term id = GPH.term2 id) as fltPorcValine, (CONCAT(’CC-’,(select TRM3.name
from term TRM3
where TRM3.id = GPH.term1 id))) as strTRMterm type from term TRM, graph path GPH
where TRM.id = GPH.term1 id and TRM.id in (
select GPH2.term2 id
from term TRM2, graph path GPH2 where TRM2.id = GPH2.term1 id
and TRM2.term type = ’cellular component’ and TRM2.name = ’cellular component’ and GPH2.distance = 1)
and GPH.term1 id< > GPH.term2 id having fltPorcValine is not null