Difference between revisions of "FlyBase Field Mapping Tables"

From GMOD
Jump to: navigation, search
(Updated SQL formatting.)
(Added reference report sql.)
Line 245: Line 245:
 
g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and  
 
g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and  
 
g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and  
 
g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and  
−
dbx.db_id = d\ b.db_id and db.name = 'UniProt/Swiss-Prot' and  
+
dbx.db_id = db.db_id and db.name = 'UniProt/Swiss-Prot' and  
 
g.uniquename = 'FBgn0000011';</sql>
 
g.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
Line 254: Line 254:
 
g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and  
 
g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and  
 
g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and  
 
g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and  
−
dbx.db_id = d\ b.db_id and db.name = 'UniProt/TrEMBL' and  
+
dbx.db_id = db.db_id and db.name = 'UniProt/TrEMBL' and  
 
g.uniquename = 'FBgn0000011';</sql>
 
g.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
Line 1,232: Line 1,232:
 
|-
 
|-
 
! BDGP DGC clones
 
! BDGP DGC clones
−
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, o.is_obsolete, fp.value
 
−
from feature_relationship fr, cvterm cvt, feature f, feature o left outer join featureprop fp on (o.feature_id = fp.feature_id)
 
−
where f.feature_id = object_id and subject_id = o.feature_id and
 
−
fr.type_id = cvt.cvterm_id and cvt.name = 'derived_assoc_cdna_clone' and
 
−
fp.value = 'BDGP' and f.uniquename = 'FBgn0000011';</sql>
 
 
|-
 
|-
 
! Other clones
 
! Other clones
−
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, o.is_obsolete, fp.value
 
−
from feature_relationship fr, cvterm cvt, feature f, feature o left outer join featureprop fp on (o.feature_id = fp.feature_id)
 
−
where f.feature_id = object_id and subject_id = o.feature_id and
 
−
fr.type_id = cvt.cvterm_id and cvt.name = 'derived_assoc_cdna_clone' and
 
−
fp.value is null and f.uniquename = 'FBgn0000011';</sql>
 
 
|-
 
|-
 
! colspan="2" |cDNA clones, end sequenced (ESTs)
 
! colspan="2" |cDNA clones, end sequenced (ESTs)
Line 1,256: Line 1,246:
 
|-
 
|-
 
! NCBI GEO (LinkOut)
 
! NCBI GEO (LinkOut)
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'geo' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! Heidelberg RNAi (LinkOut)
 
! Heidelberg RNAi (LinkOut)
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'hdri' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! DRSC (LinkOut)
 
! DRSC (LinkOut)
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'drsc' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! colspan="2" |ANTIBODY INFORMATION
 
! colspan="2" |ANTIBODY INFORMATION
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'derived_reported_antibod_gen' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! colspan="2" |OTHER INFORMATION
 
! colspan="2" |OTHER INFORMATION
Line 1,270: Line 1,286:
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'discoverer' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! colspan="2" |ETYMOLOGY
 
! colspan="2" |ETYMOLOGY
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'etymology' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000953';</sql>
 
|-
 
|-
 
! colspan="2" |IDENTIFICATION
 
! colspan="2" |IDENTIFICATION
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'identified_by' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0011737';</sql>
 
|-
 
|-
 
! colspan="2" |POSITION EFFECT VARIEGATION DATA
 
! colspan="2" |POSITION EFFECT VARIEGATION DATA
 
|-
 
|-
 
! No PEV in
 
! No PEV in
 +
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename
 +
from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp,
 +
pub p
 +
where f.feature_id = subject_id and object_id = o.feature_id and
 +
fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name in ('dom_position_effect', 'no_position_effect',
 +
'rec_position_effect') and
 +
f.uniquename = 'FBgn0000012';</sql>
 
|-
 
|-
 
! Dominant PEV in
 
! Dominant PEV in
Line 1,290: Line 1,329:
 
|-
 
|-
 
! Source for database identity of
 
! Source for database identity of
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'identity_source' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! Source for database merge of
 
! Source for database merge of
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'merge_source' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000008';</sql>
 
|-
 
|-
 
! (Member gene of)
 
! (Member gene of)
 +
| <sql>SELECT o.uniquename, o.name, cvt.name, f.uniquename, f.name, p.uniquename
 +
from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp,
 +
pub p
 +
where f.feature_id = object_id and subject_id = o.feature_id and
 +
fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name = 'member_gene_of' and
 +
o.uniquename = 'FBgn0053354';</sql>
 
|-
 
|-
 
! (Component gene(s))
 
! (Component gene(s))
 +
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename
 +
from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp,
 +
pub p
 +
where f.feature_id = object_id and subject_id = o.feature_id and
 +
fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name = 'member_gene_of' and
 +
f.uniquename = 'FBgn0000002';</sql>
 
|-
 
|-
 
! (Encoded by)
 
! (Encoded by)
 +
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name
 +
from feature f, feature_relationship fr, cvterm cvt, feature o
 +
where f.feature_id = subject_id and object_id = o.feature_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name in ('encoded_by','has_component_gene') and
 +
f.uniquename = 'FBgn0061475';</sql>
 
|-
 
|-
 
! (Tags)
 
! (Tags)
 +
| <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name
 +
from feature f, feature_relationship fr, cvterm cvt, feature o
 +
where f.feature_id = subject_id and object_id = o.feature_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name = 'included_in' and
 +
f.uniquename = 'FBgn0015015';</sql>
 
|-
 
|-
 
! Additional Comments
 
! Additional Comments
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'gene_relationships' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000042';</sql>
 
|-
 
|-
 
! colspan="2" |OTHER COMMENTS
 
! colspan="2" |OTHER COMMENTS
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value, p.uniquename
 +
from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'misc' and fp.featureprop_id = fpp.featureprop_id and
 +
fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! colspan="2" |(FOREIGN GENE DATA)
 
! colspan="2" |(FOREIGN GENE DATA)
 
|-
 
|-
 
! &nbsp;
 
! &nbsp;
 +
| <sql>SELECT f.uniquename, fp.value
 +
from feature f, featureprop fp, cvterm cvt
 +
where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and
 +
cvt.name = 'foreign_seq_data' and f.uniquename = 'FBgn0014442';</sql>
 
|-
 
|-
 
! colspan="2" |EXTERNAL CROSSREFERENCES & LINKOUTS
 
! colspan="2" |EXTERNAL CROSSREFERENCES & LINKOUTS
Line 1,316: Line 1,403:
 
|-
 
|-
 
! DNA sequence
 
! DNA sequence
 +
| <sql>SELECT p.uniquename, p.name, p.seqlen, db.name, dx.accession
 +
from feature f, feature p, feature_relationship fr, cvterm cvt, cvterm cvt2,
 +
feature_dbxref fd, dbxref dx, db
 +
where f.feature_id = object_id and subject_id = p.feature_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and
 +
p.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA',
 +
'ncRNA', 'snRNA',
 +
'tRNA','rRNA',
 +
'miRNA', 'pseudogene') and
 +
p.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and db.name = 'REFSEQ' and
 +
f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! Protein sequence
 
! Protein sequence
 +
| <sql>SELECT p.uniquename, p.name, p.seqlen, db.name, dx.accession
 +
from feature g, feature_relationship fr, cvterm cvt, feature t, cvterm cvt2,
 +
feature p, feature_relationship fr2, cvterm cvt3, cvterm cvt4, fea\ ture_dbxref fd,
 +
dbxref dx, db
 +
where g.feature_id = fr.object_id and fr.subject_id = t.feature_id and
 +
fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and
 +
t.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA',
 +
'ncRNA', 'snRNA',
 +
'tRNA','rRNA',
 +
'miRNA', 'pseudogene') and
 +
t.feature_id = fr2.object_id and fr2.subject_id = p.feature_id and
 +
fr2.type_id = cvt3.cvterm_id and cvt3.name = 'producedby' and
 +
p.type_id = cvt4.cvterm_id and cvt4.name = 'protein' and
 +
p.seqlen is not null and
 +
p.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and db.name = 'REFSEQ' and
 +
g.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
|-
 
|-
 
! UniProt/Swiss-Prot
 
! UniProt/Swiss-Prot
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and db.name = 'UniProt/Swiss-Prot' and
 +
f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! UniProt/TrEMBL
 
! UniProt/TrEMBL
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and db.name = 'UniProt/TrEMBL' and
 +
f.uniquename = 'FBgn0000015';</sql>
 
|-
 
|-
 
! colspan="2" |Other Crossreferences
 
! colspan="2" |Other Crossreferences
Line 1,359: Line 1,485:
 
|-
 
|-
 
! FLIGHT
 
! FLIGHT
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'flight' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! FlyMine
 
! FlyMine
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'flight' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! Heidelberg RNAi
 
! Heidelberg RNAi
Line 1,367: Line 1,507:
 
|-
 
|-
 
! Interactive Fly
 
! Interactive Fly
 +
| <sql>SELECT f.uniquename, f.name, db.name, accession
 +
from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp,
 +
cvterm cvt
 +
where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and
 +
dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and
 +
dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and
 +
db.name = 'if' and f.uniquename = 'FBgn0000011';</sql>
 
|-
 
|-
 
! NCBI GEO
 
! NCBI GEO
Line 1,635: Line 1,782:
 
where f.uniquename = 'FBpp0086822' and f.feature_id=fp.feature_id and  
 
where f.uniquename = 'FBpp0086822' and f.feature_id=fp.feature_id and  
 
fp.pub_id=p.pub_id;</sql>
 
fp.pub_id=p.pub_id;</sql>
 +
|}
 +
==FlyBase Reference Report==
 +
'''Example report:''' http://flybase.org/reports/FBrf0126983.html
 +
{| class="wikitable"
 +
|-
 +
|-
 +
! colspan="2" |REFERENCE
 +
|-
 +
! Citation
 +
| <sql>select p.uniquename, p.pyear, p.title, array_to_string( array(
 +
select pa.surname || ', ' || pa.givennames
 +
from pubauthor pa
 +
where pa.pub_id=p.pub_id), ', ' ) as authors, (select pp.value
 +
from pubprop pp, cvterm pp_type
 +
where pp_type.name='pubmed_fulltext_url' and pp.pub_id=p.pub_id and
 +
pp.type_id=pp_type.cvterm_id) as fulltext_url, (select pub_in.miniref
 +
from pub pub_in, pub_relationship pr, cvterm pr_type
 +
where pr_type.name='published_in' and p.pub_id=pr.subject_id and
 +
pr.object_id=pub_in.pub_id and pr.type_id=pr_type.cvterm_id) as journal, p.volume, p.issue, p.pages
 +
from pub p
 +
where p.uniquename='FBrf0126983';</sql>
 +
|-
 +
! FlyBase ID
 +
| <sql>select uniquename
 +
from pub
 +
where uniquename='FBrf0126983';</sql>
 +
|-
 +
! Type of publication
 +
|-
 +
! Offprint
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0126983' and cvt.name='cam_offprint' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! colspan="2" |EXTERNAL CROSSREFERENCES
 +
|-
 +
! PubMed ID
 +
| <sql>select dbx.accession
 +
from pub p, pub_dbxref pdbx, dbxref dbx, db
 +
where p.uniquename='FBrf0126983' and db.name='pubmed' and
 +
pdbx.is_current=true and p.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! PubMed Abstract
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0126983' and cvt.name='pubmed_abstract' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! (Conference Abstract)
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0188865' and cvt.name='conf_abs_text' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! Biosis
 +
| <sql>select dbx.accession
 +
from pub p, pub_dbxref pdbx, dbxref dbx, db
 +
where p.uniquename='FBrf0126983' and db.name='biosis' and
 +
pdbx.is_current=true and p.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! Zoological record
 +
| <sql>select dbx.accession
 +
from pub p, pub_dbxref pdbx, dbxref dbx, db
 +
where p.uniquename='FBrf0025508' and db.name='zoorec_id' and
 +
pdbx.is_current=true and p.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! colspan="2" |ASSOCIATED INFORMATION
 +
|-
 +
! Comments
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0148886' and cvt.name='associated_text' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! Text of personal<br />communication
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0188739' and cvt.name='perscommtext' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! Associated files
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0191798' and cvt.name='deposited_files' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! colspan="2" |RELATED PUBLICATIONS
 +
|-
 +
! Research paper
 +
| <sql>select related_pub.uniquename, related_pub.miniref, pub_type.name
 +
from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type
 +
where p.uniquename='FBrf0000634' and rel_type.name='related_to' and
 +
p.pub_id=pr.object_id and pr.subject_id=related_pub.pub_id and
 +
pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id UNION
 +
select related_pub.uniquename, related_pub.miniref, pub_type.name
 +
from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type
 +
where p.uniquename='FBrf0000634' and rel_type.name='related_to' and
 +
p.pub_id=pr.subject_id and pr.object_id=related_pub.pub_id and
 +
pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id;</sql>
 +
|-
 +
! Supplementary<br />material
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Review
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Erratum
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Retraction
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Personal<br />communication
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Abstract
 +
|same as above, use pub type to distinguish
 +
|-
 +
! FlyBase analysis
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Other
 +
|same as above, use pub type to distinguish
 +
|-
 +
! colspan="2" |ALSO PUBLISHED AS
 +
|-
 +
! Research paper
 +
| <sql>select related_pub.uniquename, related_pub.miniref, pub_type.name
 +
from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type
 +
where p.uniquename='FBrf0000424' and rel_type.name='also_in' and
 +
p.pub_id=pr.object_id and pr.subject_id=related_pub.pub_id and
 +
pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id UNION
 +
select related_pub.uniquename, related_pub.miniref, pub_type.name
 +
from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type
 +
where p.uniquename='FBrf0000424' and rel_type.name='also_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=related_pub.pub_id and
 +
pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id;</sql>
 +
|-
 +
! Supplementary<br />material
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Review
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Erratum
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Retraction
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Personal<br />communication
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Abstract
 +
|same as above, use pub type to distinguish
 +
|-
 +
! FlyBase analysis
 +
|same as above, use pub type to distinguish
 +
|-
 +
! Other
 +
|same as above, use pub type to distinguish
 +
|-
 +
! colspan="2" |OTHER REFERENCE INFORMATION
 +
|-
 +
! Secondary IDs
 +
| <sql>select dbx.accession
 +
from pub p, pub_dbxref pdbx, dbxref dbx, db
 +
where p.uniquename='FBrf0000810' and db.name='FlyBase' and
 +
pdbx.is_current=false and p.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! Language of publication
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0000002' and cvt.name='languages' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! Additional Language(s)<br />of abstract
 +
| <sql>select pp.value
 +
from pub p, pubprop pp, cvterm cvt
 +
where p.uniquename='FBrf0064412' and cvt.name='abstract_languages' and
 +
p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>
 +
|-
 +
! ISBN
 +
| <sql>select dbx.accession
 +
from pub p, pub_dbxref pdbx, dbxref dbx, db
 +
where p.uniquename='FBrf0019088' and db.name='isbn' and
 +
pdbx.is_current=true and p.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! Place of Publication
 +
| <sql>select pubplace
 +
from pub
 +
where uniquename ='FBrf0075564';</sql>
 +
|-
 +
! colspan="2" |PUBLISHED IN
 +
|-
 +
! Abbreviation
 +
| <sql>select pub_in.miniref
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Title
 +
| <sql>select pub_in.title
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Authors
 +
| <sql>select array_to_string( array(
 +
select pa.surname || ', ' || pa.givennames
 +
from pubauthor pa
 +
where pa.pub_id=pub_in.pub_id), ', ' ) as authors
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0191612' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Volume Range
 +
| <sql>select pub_in.volume
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Year Range
 +
| <sql>select pub_in.pyear
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Page Range
 +
| <sql>select pub_in.pages
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Publisher
 +
| <sql>select pub_in.publisher
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Place of publication
 +
| <sql>select pub_in.pubplace
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type
 +
where p.uniquename='FBrf0126983' and rel_type.name='published_in' and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id;</sql>
 +
|-
 +
! Language of publication
 +
| <sql>select pp.value
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pubprop pp,
 +
cvterm pp_type
 +
where p.uniquename='FBrf0002356' and rel_type.name='published_in' and
 +
pp_type.name='languages' and p.pub_id=pr.subject_id and
 +
pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and
 +
pp.type_id=pp_type.cvterm_id and pub_in.pub_id=pp.pub_id;</sql>
 +
|-
 +
! ISBN or ISSN
 +
| <sql>select dbx.accession
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pub_dbxref pdbx,
 +
dbxref dbx, db
 +
where p.uniquename='FBrf0000051' and rel_type.name='published_in' and
 +
db.name in ('isbn','issn') and pdbx.is_current=true and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id and pub_in.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id=dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! CODEN
 +
| <sql>select dbx.accession
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pub_dbxref pdbx,
 +
dbxref dbx, db
 +
where p.uniquename='FBrf0000015' and rel_type.name='published_in' and
 +
db.name='coden' and pdbx.is_current=true and
 +
p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and
 +
pr.type_id=rel_type.cvterm_id and pub_in.pub_id=pdbx.pub_id and
 +
pdbx.dbxref_id=dbx.dbxref_id and dbx.db_id=db.db_id;</sql>
 +
|-
 +
! Associated files
 +
| <sql>select pp.value
 +
from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pubprop pp,
 +
cvterm pp_type
 +
where p.uniquename='FBrf0002356' and rel_type.name='published_in' and
 +
pp_type.name='deposted_files' and p.pub_id=pr.subject_id and
 +
pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and
 +
pp.type_id=pp_type.cvterm_id and pub_in.pub_id=pp.pub_id;</sql>
 +
|-
 +
! colspan="2" |DATA FROM REFERENCE
 +
|-
 +
! colspan="2" |Genes
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBgn%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Transcripts
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBtr%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Polypeptides
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBpp%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Alleles
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBal%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Constructs
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename ~ 'FB(tp|ms|mc)[0-9]+$' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Insertions
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBti%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Aberations
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBab%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Balancers
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBba%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Images
 +
|-
 +
! &nbsp;
 +
|-
 +
! colspan="2" |Clones
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBcl%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 +
|-
 +
! colspan="2" |Natural Transposons
 +
|-
 +
! &nbsp;
 +
| <sql>select s.synonym_sgml
 +
from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s,
 +
cvterm cvt
 +
where p.uniquename='FBrf0126983' and f.uniquename like 'FBte%' and
 +
cvt.name='symbol' and fs.is_current=true and
 +
p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and
 +
f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and
 +
s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>
 
|}
 
|}
  
 
[[Category:Chado FlyBase]]
 
[[Category:Chado FlyBase]]

Revision as of 21:37, 9 June 2008

Introduction

The FlyBase field mapping tables map fields in the FlyBase data class reports (genes, alleles, insertions, etc...) to locations in Chado. They contain simple tables with the first column containing the field name and the second column containing the SQL required to find data for that field.

FlyBase Gene Report

Example report: http://flybase.org/reports/FBgn0000011.html

General information
Symbol <sql>SELECT distinct(s.name)

from feature f, feature_synonym fs, synonym s, cvterm cvt, cvterm cvt2 where f.type_id = cvt.cvterm_id and cvt.name = 'gene' and f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.is_current = 't' and fs.is_internal = 'f' and s.type_id = cvt2.cvterm_id and cvt2.name = 'symbol' and f.is_obsolete = 'f' and f.uniquename = 'FBgn0000011';</sql>

Species <sql>SELECT f.uniquename, f.name, o.genus, o.species

from feature f, cvterm cvt, organism o where f.type_id = cvt.cvterm_id and cvt.name = 'gene' and f.is_obsolete = 'f' and f.uniquename like 'FBgn%' and f.organism_id = o.organism_id;</sql>

Name <sql>SELECT distinct(s.name)

from feature f, feature_synonym fs, synonym s, cvterm cvt, cvterm cvt2 where f.type_id = cvt.cvterm_id and cvt.name = 'gene' and f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.is_current = 't' and fs.is_internal = 'f' and s.type_id = cvt2.cvterm_id and cvt2.name = 'fullname' and f.is_obsolete = 'f' and f.uniquename = 'FBgn0000011';</sql>

Annotation symbol <sql>SELECT accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'FlyBase Annotation IDs' and f.uniquename = 'FBgn0000011';</sql>

Feature type <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'promoted_gene_type' and f.uniquename = 'FBgn0000011';</sql>

FlyBase ID <sql>SELECT f.uniquename

from feature f, cvterm cvt where f.type_id = cvt.cvterm_id and cvt.name = 'gene' and f.name = 'ab';</sql>

Created/Updated <sql>SELECT timeaccessioned, timelastmodified

from feature f where uniquename = 'FBgn0000011';</sql>

Gene Model Status <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_gene_model_status' and f.uniquename = 'FBgn0000011';</sql>

Genetic Status
GENOMIC LOCATION
Chromosome arm <sql>SELECT a.uniquename

from feature f, featureloc fl, feature a where f.feature_id = fl.feature_id and fl.srcfeature_id = a.feature_id and f.uniquename = 'FBgn0000011';</sql>

Recombination map <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'promoted_genetic_location' and f.uniquename = 'FBgn0000011';</sql>

Cytogenetic map <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_computed_cyto' and f.uniquename = 'FBgn0000011';</sql>

Sequence location <sql>SELECT s.uniquename, fmin, fmax, strand

from feature f, featureloc fl, feature s where f.feature_id = fl.feature_id and fl.srcfeature_id = s.feature_id and f.uniquename = 'FBgn0000011';</sql>

DETAILED MAPPING DATA
FlyBase computed cytological location
Cytogenetic map <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_computed_cyto' and f.uniquename = 'FBgn0000011';</sql>

Evidence for location <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_computed_cyto' and f.uniquename = 'FBgn0000011';</sql>

Experimentally determined cytological location
Cytogenetic map <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_experimental_cyto' and f.uniquename = 'FBgn0000011';</sql>

Notes <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'cyto_loc_comment' and f.uniquename = 'FBgn0000011';</sql>

Reference <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'cyto_loc_comment' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000060';</sql>

Experimentally determined recombination data
Location <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'genetic_location' and f.uniquename = 'FBgn0000011';</sql>

Left of (cM) <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = subject_id and object_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name = 'recom_right_end' and f.uniquename = 'FBgn0000051';</sql>

Right of (cM) <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = subject_id and object_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name = 'recom_left_end' and f.uniquename = 'FBgn0000051';</sql>

Notes <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'cyto_loc_comment' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000060';</sql>

Reference
Molecular map data
Gene Order
(in direction of increasing cytology) 		<sql>SELECT fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and cvt.name = 'gene_order' and fp.value like 'Gene order: In direction%' and f.uniquename = 'FBgn0000011';</sql>

Reference
Gene Order
(overall orientation not stated) 		<sql>SELECT fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and cvt.name = 'gene_order' and fp.value like 'Gene order: In direction%' and f.uniquename = 'FBgn0000053';</sql>

Reference
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym <sql>SELECT f.uniquename, f.name, s.name as synonym, synonym_sgml, cvt.name, p.uniquename

FROM cvterm cvt, feature f, feature_synonym fs, synonym s, pub p WHERE f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.pub_id = p.pub_id and s.type_id = cvt.cvterm_id and cvt.name = 'symbol' and fs.is_current = 'f' and f.uniquename = 'FBgn0000011';</sql>

Name Synonym <sql>SELECT f.uniquename, f.name, s.name as synonym, synonym_sgml, cvt.name, p.uniquename

FROM cvterm cvt, feature f, feature_synonym fs, synonym s, pub p WHERE f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.pub_id = p.pub_id and s.type_id = cvt.cvterm_id and cvt.name = 'fullname' and fs.is_current = 'f' and f.uniquename = 'FBgn0000011';</sql>

SECONDARY FLYBASE IDs <sql>SELECT f.uniquename, f.name, accession, db.name

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 'f' and d.db_id = db.db_id and db.name = 'FlyBase' and f.uniquename = 'FBgn0000011';</sql>

GENE PRODUCTS & EXPRESSION
GENE MODEL & FEATURES
COMMENTS ON GENE MODEL <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p, cv where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and cvt.cv_id = cv.cv_id and cv.name = 'annotation property type' and cvt.name = 'comment' and f.uniquename = 'FBgn0000011';</sql>

SEQUENCES SUPPORTING THE GENE MODEL
DNA sequence <sql>SELECT f.uniquename, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_supporting_accessions' and f.uniquename = 'FBgn0000011';</sql>

Protein sequence
Name
UniProtKB/Swiss-Prot <sql>SELECT g.uniquename, g.name, fd.is_current, db.name, dbx.accession

from feature g, feature_dbxref fd, dbxref dbx, db, cvterm gt where g.type_id = gt.cvterm_id and gt.name = 'gene' and g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and dbx.db_id = db.db_id and db.name = 'UniProt/Swiss-Prot' and g.uniquename = 'FBgn0000011';</sql>

UniProtKB/TrEMBL <sql>SELECT g.uniquename, g.name, fd.is_current, db.name, dbx.accession

from feature g, feature_dbxref fd, dbxref dbx, db, cvterm gt where g.type_id = gt.cvterm_id and gt.name = 'gene' and g.uniquename like 'FBgn%' and g.is_obsolete = 'f' and g.feature_id = fd.feature_id and fd.dbxref_id = dbx.dbxref_id and dbx.db_id = db.db_id and db.name = 'UniProt/TrEMBL' and g.uniquename = 'FBgn0000011';</sql>

Maps to <sql>SELECT f.uniquename, f.name, c.uniquename, c.name

from feature f, feature_relationship fr, cvterm cvt, feature c where f.feature_id = subject_id and object_id = c.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'maps_to_clone' and f.uniquename = 'FBgn0000011';</sql>

Does NOT map to <sql>SELECT f.uniquename, f.name, c.uniquename, c.name

from feature f, feature_relationship fr, cvterm cvt, feature c where f.feature_id = subject_id and object_id = c.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'nomaps_to_clone' and f.uniquename = 'FBgn0003308';</sql>

Identified with <sql>SELECT f.uniquename, f.name, c.uniquename, c.name

from feature f, feature_relationship fr, cvterm cvt, feature c where f.feature_id = subject_id and object_id = c.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'identified_with' and f.uniquename = 'FBgn0000011';</sql>

MAPPED FEATURES AND MUTATIONS
Type <sql>SELECT cvt.name

from feature f, feature_relationship fr, feature m, cvterm cvt where f.uniquename like 'FBgn%' and f.feature_id = object_id and subject_id = m.feature_id and m.type_id = cvt.cvterm_id and cvt.name in ('aberration_junction','complex_substitution', 'deletion','enhancer', 'insertion_site','point_mutation', 'protein_binding_site','regulatory_region', 'rescue_fragment','sequence_variant', 'transposable_element_target_site_duplication','uncharacterized_change_in_nucleotide_sequence') and f.uniquename = 'FBgn0000011';</sql>

Symbol & Location <sql>SELECT m.uniquename as mutation, s.uniquename as arm, strand, fmin, fmax

from featureloc fl, feature f, feature_relationship fr, feature m, cvterm cvt, feature s where f.uniquename like 'FBgn%' and f.feature_id = object_id and subject_id = m.feature_id and m.type_id = cvt.cvterm_id and cvt.name in ('aberration_junction','complex_substitution', 'deletion','enhancer', 'insertion_site','point_mutation', 'protein_binding_site','regulatory_region', 'rescue_fragment','sequence_variant', 'transposable_element_target_site_duplication','uncharacterized_change_in_nucleotide_sequence') and m.feature_id = fl.feature_id and fl.srcfeature_id = s.feature_id and f.uniquename = 'FBgn0000011';</sql>

Additional Notes <sql>SELECT m.uniquename, p.uniquename, cvt2.name, fp.value

from feature f, feature_relationship fr, feature m, feature_pub mp, pub p, cvterm cvt, cvterm cvt2, featureprop fp where f.uniquename like 'FBgn%' and f.feature_id = object_id and su\ bject_id = m.feature_id and m.type_id = cvt.cvterm_id and cvt.name in ('aberration_junction','complex_substitution', 'deletion','enhancer', 'insertion_site','point_mutation', 'protein_binding_site','regulatory_region', 'rescue_fragment','sequence_variant', 'transposable_element_target_site_duplication','uncharacterized_change_in_nucleotide_sequence') and m.feature_id = mp.feature_id and mp.pub_id = p.pub_id and m.feature_id = fp.feature_id and fp.type_id = cvt2.cvterm_id and f.uniquename = 'FBgn0000011';</sql>

References
EXTERNAL DATA
DEDB (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'dedb' and f.uniquename = 'FBgn0000011';</sql>

EPD <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'EPD' and f.uniquename = 'FBgn0000042';</sql>

TRANSCRIPT DATA
ANNOTATED TRANSCRIPTS
Name <sql>SELECT p.uniquename, p.name, p.seqlen, fmin, fmax, strand, a.uniquename as arm

from feature f, feature p, feature_relationship fr, cvterm cvt, featureloc fl, feature a, cvterm cvt2 where f.feature_id = object_id and subject_id = p.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and p.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA','rRNA', 'miRNA', 'pseudogene') and p.feature_id = fl.feature_id and fl.srcfeature_id = a.feature_id and f.uniquename = 'FBgn0000011';</sql>

FlyBase ID
Length (nt)
Associated CDS (aa) <sql>SELECT t.uniquename, t.name, p.uniquename, p.name, p.seqlen

from feature g, feature_relationship fr, cvterm cvt, feature t, cvterm cvt2, feature p, feature_relationship fr2, cvterm cvt3, cvterm cvt4 where g.feature_id = fr.object_id and fr.subject_id = t.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and t.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA','rRNA', 'miRNA', 'pseudogene') and t.feature_id = fr2.object_id and fr2.subject_id = p.feature_id and fr2.type_id = cvt3.cvterm_id and cvt3.name = 'producedby' and p.type_id = cvt4.cvterm_id and cvt4.name = 'protein' and p.seqlen is not null and g.uniquename = 'FBgn0000011';</sql>

ADDITIONAL TRANSCRIPT DATA AND COMMENTS
Reported
transcript sizes 		<sql>SELECT f.uniquename, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_transcript_reported_sizes' and f.uniquename = 'FBgn0000011';</sql>

Comments <sql>SELECT f.uniquename, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_transcript_comment' and f.uniquename = 'FBgn0000038';</sql>

EXTERNAL DATA
MIR <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'MIR' and f.uniquename = 'FBgn0064191';</sql>

Rfam <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'Rfam' and f.uniquename = 'FBgn0000810';</sql>

POLYPEPTIDE DATA
ANNOTATED POLYPEPTIDES
Name <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, p.uniquename, p.name, p.seqlen, fmin, fmax, strand, a.uniquename, cvt5.name, fp.value

from feature g, feature_relationship fr, cvterm cvt, feature t, cvterm cvt2, feature p, feature_relationship fr2, cvterm cvt3, cvterm cvt4, featureloc fl, feature a, featureprop fp, cvterm cvt5 where g.feature_id = fr.object_id and fr.subject_id = t.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and t.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA', 'rRNA', 'miRNA', 'pseudogene') and t.feature_id = fr2.object_id and fr2.subject_id = p.feature_id and fr2.type_id = cvt3.cvterm_id and cvt3.name = 'producedby' and p.type_id = cvt4.cvterm_id and cvt4.name = 'protein' and p.seqlen is not null and p.feature_id = fl.feature_id and fl.srcfeature_id = a.feature_id and p.feature_id = fp.feature_id and fp.type_id = cvt5.cvterm_id and g.uniquename = 'FBgn0000011';</sql>

FlyBase ID
Predicted MW (kD)
Length (aa)
Theoretical pI
Genbank protein <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, p.uniquename, p.name, p.seqlen, fmin, fmax, strand, a.uniquename, d.accession

from feature g, feature_relationship fr, cvterm cvt, feature t, cvterm cvt2, feature p, feature_relationship fr2, cvterm cvt3, cvterm cvt4, featureloc fl, feature a, feature_dbxref fd, dbxref d, db where g.feature_id = fr.object_id and fr.subject_id = t.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and t.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA', 'rRNA', 'miRNA', 'pseudogene') and t.feature_id = fr2.object_id and fr2.subject_id = p.feature_id and fr2.type_id = cvt3.cvterm_id and cvt3.name = 'producedby' and p.type_id = cvt4.cvterm_id and cvt4.name = 'protein' and p.seqlen is not null and p.feature_id = fl.feature_id and fl.srcfeature_id = a.feature_id and p.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and d.db_id = db.db_id and db.name = 'GB_protein' and g.uniquename = 'FBgn0051371';</sql>

ADDITIONAL POLYPEPTIDE DATA AND COMMENTS
Reported
protein sizes
Comments
EXTERNAL DATA
GCR <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'GCR' and f.uniquename = 'FBgn0004168';</sql>

InterPro domains <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'INTERPRO' and f.uniquename = 'FBgn0015570';</sql>

MEROPS <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'MEROPS' and f.uniquename = 'FBgn0004648';</sql>

MITODROME <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'MITODROME' and f.uniquename = 'FBgn0027085';</sql>

NRL_3D <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'NRL_3D' and f.uniquename = 'FBgn0003470';</sql>

PANTHER (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'panther' and f.uniquename = 'FBgn0000011';</sql>

PDB <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'PDB' and f.uniquename = 'FBgn0003659';</sql>

TransFac <sql>SELECT f.uniquename, accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'TF' and f.uniquename = 'FBgn0000014';</sql>

EXPRESSION DATA
BDGP in situ (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'bdgpinsituexpr' and f.uniquename = 'FBgn0000011';</sql>

Yale Dev. Expression (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'kwexpression' and f.uniquename = 'FBgn0000014';</sql>

RELATED COMMENTS
  <sql>SELECT f.uniquename, f.name, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'molecular_info' and f.uniquename = 'FBgn0000014';</sql>

ALLELES
CLASSICAL ALLELES
Allele of (gene name) <sql>SELECT g.uniquename, g.name, a.uniquename, a.name

from feature g, feature_relationship fr, feature a, cvterm cvt where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and not exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and g.uniquename = 'FBgn0000011';</sql>

Class <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and not exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name = 'promoted_allele_class' and g.uniquename = 'FBgn0000011';</sql>

Mutagen <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, cvtermprop cvtp, feature_cvterm fcv where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and not exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fcv.feature_id and fcv.cvterm_id = cvt4.cvterm_id and cvt4.cvterm_id = cvtp.cvterm_id and cvtp.value = 'origin_of_mutation' and g.uniquename = 'FBgn0000011';</sql>

Stocks <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt3.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and not exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name like 'derived_stock_%' and g.uniquename = 'FBgn0000011';</sql>

Known lesion <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt3.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and not exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name in ('molecular_info','aminoacid_rep', 'nucleotide_rep') and g.uniquename = 'FBgn0000011';</sql>

ALLELES CARRIED ON TRANSGENIC CONSTRUCTS
Allele of (gene name) <sql>SELECT g.uniquename, g.name, a.uniquename, a.name

from feature g, feature_relationship fr, feature a, cvterm cvt where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and g.uniquename = 'FBgn0000011';</sql>

Class <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name = 'promoted_allele_class' and g.uniquename = 'FBgn0000011';</sql>

Mutagen <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, cvtermprop cvtp, feature_cvterm fcv where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fcv.feature_id and fcv.cvterm_id = cvt4.cvterm_id and cvt4.cvterm_id = cvtp.cvterm_id and cvtp.value = 'origin_of_mutation' and g.uniquename = 'FBgn0000011';</sql>

Stocks <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt3.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name like 'derived_stock_%' and g.uniquename = 'FBgn0000011';</sql>

Known lesion <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt3.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, featureprop fp, cvterm cvt3 where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and exists (select * from feature_relationship fr2, feature t, cvterm cvt2, cvterm cvt3 where fr2.subject_id = a.feature_id and fr2.type_id = cvt2.cvterm_id and cvt2.name = 'associated_with' and fr2.object_id = t.feature_id and t.type_id = cvt3.cvterm_id and cvt3.name = 'transgenic_transposon') and a.feature_id = fp.feature_id and fp.type_id = cvt3.cvterm_id and cvt3.name in ('molecular_info','aminoacid_rep', 'nucleotide_rep') and g.uniquename = 'FBgn0000011';</sql>

SUMMARY OF ALLELE PHENOTYPES
Lethality <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class'and fp.value like '%lethal%' and g.uniquename = 'FBgn0000011' UNION SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class' and fp.value like '%viable%' and g.uniquename = 'FBgn0000011';</sql>

Allele
Sterility <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class'and fp.value like '%fertile%' and g.uniquename = 'FBgn0000011' UNION SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class' and fp.value like '%sterile%' and g.uniquename = 'FBgn0000011';</sql>

Allele
Other Phenotypes <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class'and fp.value not like '%lethal%' and g.uniquename = 'FBgn0000011' INTERSECT SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class' and fp.value not like '%viable%' and g.uniquename = 'FBgn0000011' INTERSECT SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class'and fp.value not like '%fertile%' and g.uniquename = 'FBgn0000011' INTERSECT SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_class' and fp.value not like '%sterile%' and g.uniquename = 'FBgn0000011';</sql>

Allele
Phenotype manifest in <sql>SELECT g.uniquename, g.name, a.uniquename, a.name, cvt4.name, fp.value

from feature g, feature_relationship fr, feature a, cvterm cvt, cvterm cvt4, featureprop fp where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'alleleof' and a.feature_id = fp.feature_id and fp.type_id = cvt4.cvterm_id and cvt4.name = 'derived_pheno_manifest' and g.uniquename = 'FBgn0000011';</sql>

Allele
ANEUPLOID ABERRATIONS
(Useful Duplication) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('useful_Dp_direct', 'useful_Dp_from_cyto') and g.uniquename = 'FBgn0000022';</sql>

(Useful Deficiency)
(Disrupted in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('deletes', 'molec_deletes') and g.uniquename = 'FBgn0000011';</sql>

(Partially disrupted in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('part_deletes', 'molec_partdeletes') and g.uniquename = 'FBgn0000014';</sql>

(Not Disrupted in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('nondeletes', 'molec_nondeletes') and g.uniquename = 'FBgn0000014';</sql>

(Duplicated in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('duplicates', 'molec_dups') and g.uniquename = 'FBgn0000014';</sql>

(Partially duplicated in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('part_duplicates', 'molec_partdups') and g.uniquename = 'FBgn0000014';</sql>

(Not duplicated in) <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name

from feature g, feature_relationship fr, cvterm cvt, feature a where g.feature_id = object_id and subject_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('nonduplicates', 'molec_nondups') and g.uniquename = 'FBgn0000022';</sql>

TRANSGENIC CONSTRUCTS AND INSERTIONS
Transgenic constructs
Type of construct
heat-shock construct <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, frtp.name, frp.value

from feature g, feature t, cvterm frtp, cvterm gtp, cvterm ttp, cvterm frpt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where subject_id = g.feature_id and g.type_id = gtp.cvterm_id and gtp.name = 'gene' and g.is_analysis = 'f' and g.is_obsolete = 'f' and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_heat_shock_construct' and object_id = t.feature_id and t.type_id = ttp.cvterm_id and ttp.name = 'transgenic_transposon' and t.is_analysis = 'f' and t.is_obsolete = 'f' and frp.type_id = frpt.cvterm_id and frpt.name = 'has_expression_data' and g.uniquename = 'FBgn0000015';</sql>

UAS construct <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, frtp.name, frp.value

from feature g, feature t, cvterm frtp, cvterm gtp, cvterm ttp, cvterm frpt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where subject_id = g.feature_id and g.type_id = gtp.cvterm_id and gtp.name = 'gene' and g.is_analysis = 'f' and g.is_obsolete = 'f' and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_UAS_construct' and object_id = t.feature_id and t.type_id = ttp.cvterm_id and ttp.name = 'transgenic_transposon' and t.is_analysis = 'f' and t.is_obsolete = 'f' and frp.type_id = frpt.cvterm_id and frpt.name = 'has_expression_data' and g.uniquename = 'FBgn0000011';</sql>

characterization construct <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, frtp.name, frp.value

from feature g, feature t, cvterm frtp, cvterm gtp, cvterm ttp, cvterm frpt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where subject_id = g.feature_id and g.type_id = gtp.cvterm_id and gtp.name = 'gene' and g.is_analysis = 'f' and g.is_obsolete = 'f' and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_characterization_construct' and object_id = t.feature_id and t.type_id = ttp.cvterm_id and ttp.name = 'transgenic_transposon' and t.is_analysis = 'f' and t.is_obsolete = 'f' and frp.type_id = frpt.cvterm_id and frpt.name = 'has_expression_data' and g.uniquename = 'FBgn0000009';</sql>

vital-reporter construct <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, frtp.name, frp.value

from feature g, feature t, cvterm frtp, cvterm gtp, cvterm ttp, cvterm frpt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where subject_id = g.feature_id and g.type_id = gtp.cvterm_id and gtp.name = 'gene' and g.is_analysis = 'f' and g.is_obsolete = 'f' and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_vital_reporter_construct' and object_id = t.feature_id and t.type_id = ttp.cvterm_id and ttp.name = 'transgenic_transposon' and t.is_analysis = 'f' and t.is_obsolete = 'f' and frp.type_id = frpt.cvterm_id and frpt.name = 'has_expression_data' and g.uniquename = 'FBgn0000042';</sql>

reporter construct <sql>SELECT g.uniquename, g.name, t.uniquename, t.name, frtp.name, frp.value

from feature g, feature t, cvterm frtp, cvterm gtp, cvterm ttp, cvterm frpt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where subject_id = g.feature_id and g.type_id = gtp.cvterm_id and gtp.name = 'gene' and g.is_analysis = 'f' and g.is_obsolete = 'f' and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_reporter_construct' and object_id = t.feature_id and t.type_id = ttp.cvterm_id and ttp.name = 'transgenic_transposon' and t.is_analysis = 'f' and t.is_obsolete = 'f' and frp.type_id = frpt.cvterm_id and frpt.name = 'has_expression_data' and g.uniquename = 'FBgn0000014';</sql>

Insertions
insertion of mobile activating element <sql>SELECT stp.name, s.uniquename, s.name, frtp.name, otp.name, o.uniquename, o.name, frcvt.name, frp.value

from feature s, feature o, cvterm frtp, cvterm otp, cvterm stp, cvterm frcvt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where s.feature_id = subject_id and object_id = o.feature_id and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_insertion_of_mobile_activating_element' and o.type_id = otp.cvterm_id and stp.name = 'gene' and s.type_id = stp.cvterm_id and otp.name = 'transposable_element_insertion_site' and s.is_obsolete = 'f' and s.is_analysis = 'f' and o.is_obsolete = 'f' and o.is_analysis = 'f' and frp.type_id = frcvt.cvterm_id and frcvt.name = 'has_expression_data' and s.uniquename = 'FBgn0000011';</sql>

insertion_of_enhancer_trap <sql>SELECT stp.name, s.uniquename, s.name, frtp.name, otp.name, o.uniquename, o.name, frcvt.name, frp.value

from feature s, feature o, cvterm frtp, cvterm otp, cvterm stp, cvterm frcvt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where s.feature_id = subject_id and object_id = o.feature_id and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_insertion_of_enhancer_trap' and o.type_id = otp.cvterm_id and stp.name = 'gene' and s.type_id = stp.cvterm_id and otp.name = 'transposable_element_insertion_site' and s.is_obsolete = 'f' and s.is_analysis = 'f' and o.is_obsolete = 'f' and o.is_analysis = 'f' and frp.type_id = frcvt.cvterm_id and frcvt.name = 'has_expression_data' and s.uniquename = 'FBgn0000011';</sql>

insertion_of_enhancer_trap_binary_system <sql>SELECT stp.name, s.uniquename, s.name, frtp.name, otp.name, o.uniquename, o.name, frcvt.name, frp.value

from feature s, feature o, cvterm frtp, cvterm otp, cvterm stp, cvterm frcvt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where s.feature_id = subject_id and object_id = o.feature_id and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_insertion_of_enhancer_trap_binary_system' and o.type_id = otp.cvterm_id and stp.name = 'gene' and s.type_id = stp.cvterm_id and otp.name = 'transposable_element_insertion_site' and s.is_obsolete = 'f' and s.is_analysis = 'f' and o.is_obsolete = 'f' and o.is_analysis = 'f' and frp.type_id = frcvt.cvterm_id and frcvt.name = 'has_expression_data' and s.uniquename = 'FBgn0000157';</sql>

miscellaneous insertions <sql>SELECT stp.name, s.uniquename, s.name, frtp.name, otp.name, o.uniquename, o.name, frcvt.name, frp.value

from feature s, feature o, cvterm frtp, cvterm otp, cvterm stp, cvterm frcvt, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where s.feature_id = subject_id and object_id = o.feature_id and fr.type_id = frtp.cvterm_id and frtp.name = 'derived_assoc_misc_insertion' and o.type_id = otp.cvterm_id and stp.name = 'gene' and s.type_id = stp.cvterm_id and otp.name = 'transposable_element_insertion_site' and s.is_obsolete = 'f' and s.is_analysis = 'f' and o.is_obsolete = 'f' and o.is_analysis = 'f' and frp.type_id = frcvt.cvterm_id and frcvt.name = 'has_expression_data' and s.uniquename = 'FBgn0000011';</sql>

RELATED COMMENTS
  <sql>SELECT f.uniquename, f.name, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'gene_phenotypes' and f.uniquename = 'FBgn0000011';</sql>

SEQUENCE ONTOLOGY: Class of gene
  <sql>SELECT f.uniquename, f.name, cv.name accession as SO_accession, cvt.name, p.uniquename

from feature f, feature_cvterm fc, cvterm cvt, cv, dbxref d, db, pub p, cvterm gft where f.feature_id = fc.feature_id and fc.cvterm_id = cvt.cvterm_id and cvt.cv_id = cv.cv_id and cv.name = 'SO' and cvt.dbxref_id = d.dbxref_id and d.db_id = db.db_id and fc.pub_id = p.pub_id and f.type_id = gft.cvterm_id and gft.name = 'gene' and f.uniquename = 'FBgn0000011';</sql>

GENE ONTOLOGY: Function, Process, and Cellular Component
MOLECULAR FUNCTION
Terms based on experimental evidence
CV term
(Includes qualifier) 		<sql>SELECT f.uniquename, f.name, cvt.name, fcpt.name, fcp.value, p.uniquename

from feature f, feature_cvterm fc, cvterm cvt, cv, cvterm fcpt, pub p, feature_cvtermprop fcp where f.feature_id = fc.feature_id and fc.cvterm_id = cvt.cvterm_id and fc.pub_id = p.pub_id and fc.feature_cvterm_id = fcp.feature_cvterm_id and cvt.cv_id = cv.cv_id and fcp.type_id = fcpt.cvterm_id and cv.name = 'molecular_function' and fcpt.name = 'evidence_code' and f.uniquename = 'FBgn0000011';</sql>

Evidence code
(Includes identifier)
Reference
Terms based on predictions
CV term
(Includes qualifier)
Evidence code
(Includes identifier)
Reference
BIOLOGICAL PROCESS
Terms based on experimental evidence
CV term
(Includes qualifier) 		<sql>SELECT f.uniquename, f.name, cvt.name, cvt2.name, fcp.value, p.uniquename

from feature f, feature_cvterm fc, cvterm cvt, cv, pub p, feature_cvtermprop fcp, cvterm cvt2 where f.feature_id = fc.feature_id and fc.cvterm_id = cvt.cvterm_id and fc.pub_id = p.pub_id and fc.feature_cvterm_id = fcp.feature_cvterm_id and fcp.type_id = cvt2.cvterm_id and cvt2.name = 'evidence_code' and cvt.cv_id = cv.cv_id and cv.name = 'biological_process' and f.uniquename = 'FBgn0000011';</sql>

Evidence code
(Includes identifier)
Reference
Terms based on predictions
CV term
(Includes qualifier)
Evidence code
(Includes identifier)
Reference
CELLULAR COMPONENT
Terms based on experimental evidence
CV term
(Includes qualifier) 		<sql>SELECT f.uniquename, f.name, cvt.name, cvt2.name, fcp.value, p.uniquename

from feature f, feature_cvterm fc, cvterm cvt, cv, pub p, feature_cvtermprop fcp, cvterm cvt2 where f.feature_id = fc.feature_id and fc.cvterm_id = cvt.cvterm_id and fc.pub_id = p.pub_id and fc.feature_cvterm_id = fcp.feature_cvterm_id and fcp.type_id = cvt2.cvterm_id and cvt2.name = 'evidence_code' and cvt.cv_id = cv.cv_id and cv.name = 'cellular_component' and f.uniquename = 'FBgn0000011';</sql>

Evidence code
(Includes identifier)
Reference
Terms based on predictions
CV term
(Includes qualifier)
Evidence code
(Includes identifier)
Reference
RELATED COMMENTS
  <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'wild_type_role' and f.uniquename = 'FBgn0000011';</sql>

INTERACTIONS AND PATHWAYS
SUMMARY OF GENETIC INTERACTIONS
Interacting Gene <sql>SELECT g.uniquename, g.name, cvt.name, a.uniquename, a.name, frp.value, p.uniquename

from feature g, cvterm cvt, feature a, feature_relationship_pub frpb, pub p, feature_relationship fr left outer join feature_relationshipprop frp on (fr.feature_relationship_id = frp.feature_relationship_id) where g.feature_id = subject_id and object_id = a.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'interacts_genetically' and fr.feature_relationship_id = frpb.feature_relationship_id and frpb.pub_id = p.pub_id and g.uniquename = 'FBgn0000011';</sql>

Allele of this gene
Reference
EXTERNAL DATA
BioGRID (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'flygrid' and f.uniquename = 'FBgn0000011';</sql>

Dros. PIMRider (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'hybrigenics' and f.uniquename = 'FBgn0000011';</sql>

ORTHOLOGS
Genome-wide drosophilid orthologs <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name

from feature f, feature_relationship fr, cvterm cvt, feature o where f.feature_id = object_id and subject_id = o.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'putative_ortholog_of' and f.uniquename = 'FBgn0000011';</sql>

Curated drosophilid orthologs <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = object_id and subject_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name = 'homologue' and f.uniquename = 'FBgn0000011';</sql>

InParanoid orthologs (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'orthologs' and f.uniquename = 'FBgn0000011';</sql>

FUNCTIONAL COMPLEMENTATION BETWEEN SPECIES
Functionally complements <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = subject_id and object_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name in ('fnally_comps', 'fnaly_noncomps', 'fnally_partcomps') and f.uniquename = 'FBgn0010602';</sql>

Partially functionally complements
Does NOT functionally complement
Functionally complemented by <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = object_id and subject_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name in ('fnally_comps', 'fnaly_noncomps', 'fnally_partcomps') and f.uniquename = 'FBgn0000099';</sql>

Partially functionally complemented by
NOT functionally complemented by
INTER-SPECIES MISEXPRESSION DATA
Produces phenotype in <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'gain_of_fn_species' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and fp.value like 'Gain of function effect%' and f.uniquename = 'FBgn0000490';</sql>

Produces NO phenotype in <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'gain_of_fn_species' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and fp.value like 'No gain of function effect%' and f.uniquename = 'FBgn0013263';</sql>

STOCKS AND REAGENTS
STOCKS LISTED IN FLYBASE
Bloomington <sql>SELECT f.uniquename, f.name, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name like 'derived_stock_%' and f.uniquename = 'FBgn0000011';</sql>

Kyoto
Szeged
Tucson
Carpenter
Garcia-Bellido
Harvard
Nusslein-Volhard
Saxton
Notes on availability <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'availability' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0003200';</sql>

GENOMIC CLONES
  <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name

from feature f, feature_relationship fr, cvterm cvt, feature o where f.feature_id = object_id and subject_id = o.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'derived_assoc_BAC' and f.uniquename = 'FBgn0000011';</sql>

cDNA CLONES
cDNA clones, fully sequenced
BDGP DGC clones
Other clones
cDNA clones, end sequenced (ESTs)
BDGP DGC clones
Other clones
RNAi & ARRAY INFORMATION
Affy Oligo
NCBI GEO (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'geo' and f.uniquename = 'FBgn0000011';</sql>

Heidelberg RNAi (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'hdri' and f.uniquename = 'FBgn0000011';</sql>

DRSC (LinkOut) <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'drsc' and f.uniquename = 'FBgn0000011';</sql>

ANTIBODY INFORMATION
  <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_reported_antibod_gen' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>

OTHER INFORMATION
DISCOVERER
  <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'discoverer' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>

ETYMOLOGY
  <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'etymology' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000953';</sql>

IDENTIFICATION
  <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'identified_by' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0011737';</sql>

POSITION EFFECT VARIEGATION DATA
No PEV in <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = subject_id and object_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name in ('dom_position_effect', 'no_position_effect', 'rec_position_effect') and f.uniquename = 'FBgn0000012';</sql>

Dominant PEV in
Recessive PEV in
RELATIONSHIP TO OTHER GENES
Source for database identity of <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'identity_source' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>

Source for database merge of <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'merge_source' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000008';</sql>

(Member gene of) <sql>SELECT o.uniquename, o.name, cvt.name, f.uniquename, f.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = object_id and subject_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name = 'member_gene_of' and o.uniquename = 'FBgn0053354';</sql>

(Component gene(s)) <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name, p.uniquename

from feature f, feature_relationship fr, cvterm cvt, feature o, feature_relationship_pub frp, pub p where f.feature_id = object_id and subject_id = o.feature_id and fr.feature_relationship_id = frp.feature_relationship_id and frp.pub_id = p.pub_id and fr.type_id = cvt.cvterm_id and cvt.name = 'member_gene_of' and f.uniquename = 'FBgn0000002';</sql>

(Encoded by) <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name

from feature f, feature_relationship fr, cvterm cvt, feature o where f.feature_id = subject_id and object_id = o.feature_id and fr.type_id = cvt.cvterm_id and cvt.name in ('encoded_by','has_component_gene') and f.uniquename = 'FBgn0061475';</sql>

(Tags) <sql>SELECT f.uniquename, f.name, cvt.name, o.uniquename, o.name

from feature f, feature_relationship fr, cvterm cvt, feature o where f.feature_id = subject_id and object_id = o.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'included_in' and f.uniquename = 'FBgn0015015';</sql>

Additional Comments <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'gene_relationships' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000042';</sql>

OTHER COMMENTS
  <sql>SELECT f.uniquename, fp.value, p.uniquename

from feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub p where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'misc' and fp.featureprop_id = fpp.featureprop_id and fpp.pub_id = p.pub_id and f.uniquename = 'FBgn0000011';</sql>

(FOREIGN GENE DATA)
  <sql>SELECT f.uniquename, fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'foreign_seq_data' and f.uniquename = 'FBgn0014442';</sql>

EXTERNAL CROSSREFERENCES & LINKOUTS
Sequence Crossreferences
DNA sequence <sql>SELECT p.uniquename, p.name, p.seqlen, db.name, dx.accession

from feature f, feature p, feature_relationship fr, cvterm cvt, cvterm cvt2, feature_dbxref fd, dbxref dx, db where f.feature_id = object_id and subject_id = p.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and p.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA','rRNA', 'miRNA', 'pseudogene') and p.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and db.name = 'REFSEQ' and f.uniquename = 'FBgn0000011';</sql>

Protein sequence <sql>SELECT p.uniquename, p.name, p.seqlen, db.name, dx.accession

from feature g, feature_relationship fr, cvterm cvt, feature t, cvterm cvt2, feature p, feature_relationship fr2, cvterm cvt3, cvterm cvt4, fea\ ture_dbxref fd, dbxref dx, db where g.feature_id = fr.object_id and fr.subject_id = t.feature_id and fr.type_id = cvt.cvterm_id and cvt.name = 'partof' and t.type_id = cvt2.cvterm_id and cvt2.name in ('mRNA', 'snoRNA', 'ncRNA', 'snRNA', 'tRNA','rRNA', 'miRNA', 'pseudogene') and t.feature_id = fr2.object_id and fr2.subject_id = p.feature_id and fr2.type_id = cvt3.cvterm_id and cvt3.name = 'producedby' and p.type_id = cvt4.cvterm_id and cvt4.name = 'protein' and p.seqlen is not null and p.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and db.name = 'REFSEQ' and g.uniquename = 'FBgn0000011';</sql>

UniProt/Swiss-Prot <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and db.name = 'UniProt/Swiss-Prot' and f.uniquename = 'FBgn0000011';</sql>

UniProt/TrEMBL <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and db.name = 'UniProt/TrEMBL' and f.uniquename = 'FBgn0000015';</sql>

Other Crossreferences
EPD
GCR
InterPro domains
MEROPS
MIR
MITODROME
NRL_3D
PDB
Rfam
TransFac
LinkOuts
BioGRID
BDGP in situ
DEDB Exons
Dros. PIMRider
DRSC
FLIGHT <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'flight' and f.uniquename = 'FBgn0000011';</sql>

FlyMine <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'flight' and f.uniquename = 'FBgn0000011';</sql>

Heidelberg RNAi
InParanoid
Interactive Fly <sql>SELECT f.uniquename, f.name, db.name, accession

from feature f, feature_dbxref fd, db, dbxref dx, dbxrefprop dxp, cvterm cvt where f.feature_id = fd.feature_id and fd.dbxref_id = dx.dbxref_id and dx.db_id = db.db_id and dx.dbxref_id = dxp.dbxref_id and dxp.type_id = cvt.cvterm_id and cvt.name = 'linkout' and db.name = 'if' and f.uniquename = 'FBgn0000011';</sql>

NCBI GEO
PANTHER
Yale Dev. Expression
REFERENCES
Research paper
 ;Supplementary material
Review
Abstract
Other

FlyBase Polypeptide Report

Example report: http://flybase.org/reports/FBpp0086822.html

GENERAL INFORMATION
Symbol <sql>SELECT s.synonym_sgml

from feature f, feature_synonym fs, synonym s, cvterm cvt, cvterm cvt2 where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.is_current = 't' and fs.is_internal = 'f' and s.type_id = cvt2.cvterm_id and cvt2.name = 'symbol' and f.is_obsolete = 'f' and f.uniquename = 'FBpp0086822' group by s.synonym_sgml;</sql>

Species <sql>SELECT o.genus, o.species

from feature f, cvterm cvt, organism o where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.is_obsolete = 'f' and f.uniquename = 'FBpp0086822' and f.organism_id = o.organism_id;</sql>

Annotation symbol <sql>SELECT accession

from feature f, feature_dbxref fd, dbxref d, db where f.feature_id = fd.feature_id and fd.dbxref_id = d.dbxref_id and fd.is_current = 't' and d.db_id = db.db_id and db.name = 'FlyBase Annotation IDs' and f.uniquename = 'FBpp0086822';</sql>

FlyBase ID <sql>SELECT f.uniquename

from feature f, cvterm cvt where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.name = 'cnn-PA' and f.is_analysis=false and f.is_obsolete=false;</sql>

Associated gene <sql>SELECT distinct(g.uniquename),g.name

from feature p, feature tr, feature g, feature_relationship fr1, feature_relationship fr2, cvterm fr1_type, cvterm fr2_type, cvterm p_type, cvterm g_type where p.uniquename='FBpp0086822' and p.is_obsolete=false and p.is_analysis=false and fr1_type.name='producedby' and fr2_type.name='partof' and p_type.name='protein' and g_type.name='gene' and p.feature_id=fr1.subject_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.object_id=g.feature_id;</sql>

Created/ Updated <sql>SELECT timeaccessioned, timelastmodified

from feature f, cvterm cvt where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.uniquename = 'FBpp0086822' and f.is_analysis=false and f.is_obsolete=false;</sql>

Length (aa) <sql>SELECT seqlen

from feature f, cvterm cvt where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.uniquename = 'FBpp0086822' and f.is_analysis=false and f.is_obsolete=false;</sql>

Theoretical pI <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_isoelectric_point' and f.uniquename = 'FBpp0086822';</sql>

Predicted MW (kD) <sql>SELECT fp.value

from feature f, featureprop fp, cvterm cvt where f.feature_id = fp.feature_id and fp.type_id = cvt.cvterm_id and cvt.name = 'derived_molecular_weight' and f.uniquename = 'FBpp0086822';</sql>

Map (GBrowse)
SEQUENCE
  <sql>SELECT residues

from feature f, cvterm cvt where f.type_id = cvt.cvterm_id and cvt.name = 'protein' and f.uniquename = 'FBpp0086822' and f.is_analysis=false and f.is_obsolete=false;</sql>

OTHER PRODUCTS OF THIS GENE
Transcripts Corresponding to THIS polypeptide
Name <sql>select s.synonym_sgml

from feature pp, feature_relationship fr, cvterm fr_type, feature tr, feature_synonym fs, synonym s, cvterm s_type where pp.uniquename='FBpp0086822' and fr_type.name='producedby' and s_type.name='symbol' and fs.is_current=true and pp.feature_id=fr.subject_id and fr.type_id=fr_type.cvterm_id and fr.object_id=tr.feature_id and tr.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=s_type.cvterm_id group by s.synonym_sgml;</sql>

FlyBase ID <sql>select tr.uniquename

from feature pp, feature_relationship fr, cvterm fr_type, feature tr where pp.uniquename='FBpp0086822' and fr_type.name='producedby' and pp.feature_id=fr.subject_id and fr.type_id=fr_type.cvterm_id and fr.object_id=tr.feature_id;</sql>

Length (nt) <sql>select tr.seqlen

from feature pp, feature_relationship fr, cvterm fr_type, feature tr where pp.uniquename='FBpp0086822' and fr_type.name='producedby' and pp.feature_id=fr.subject_id and fr.type_id=fr_type.cvterm_id and fr.object_id=tr.feature_id;</sql>

Transcripts Corresponding to OTHER polypeptides
Name <sql>select s.synonym_sgml

from feature pp, feature tr, feature g, feature otr, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, feature_synonym fs, synonym s, cvterm s_type, featureloc fl where pp.uniquename='FBpp0086822' and otr.is_analysis=false and otr.is_obsolete=false and otr.uniquename like 'FBtr%' and otr.uniquename != tr.uniquename and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and fs.is_current=true and fs.is_internal=false and s_type.name='symbol' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fs.feature_id and otr.feature_id=fl.feature_id and fs.synonym_id=s.synonym_id and s.type_id=s_type.cvterm_id group by s.synonym_sgml;</sql>

FlyBase ID <sql>select otr.uniquename

from feature pp, feature tr, feature g, feature otr, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, featureloc fl where pp.uniquename='FBpp0086822' and otr.is_analysis=false and otr.is_obsolete=false and otr.uniquename like 'FBtr%' and otr.uniquename != tr.uniquename and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fl.feature_id;</sql>

Length (nt) <sql>select otr.seqlen

from feature pp, feature tr, feature g, feature otr, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, featureloc fl where pp.uniquename='FBpp0086822' and otr.is_analysis=false and otr.is_obsolete=false and otr.uniquename like 'FBtr%' and otr.uniquename != tr.uniquename and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fl.feature_id;</sql>

Other Polypeptides
Name <sql>select s.synonym_sgml

from feature pp, feature tr, feature g, feature otr, feature opp, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, feature_relationship fr4, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, cvterm fr4_type, feature_synonym fs, synonym s, cvterm s_type, featureloc otrfl, featureloc oppfl where pp.uniquename='FBpp0086822' and opp.is_obsolete=false and opp.is_analysis=false and opp.uniquename like 'FBpp%' and opp.uniquename != pp.uniquename and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and fr4_type.name='producedby' and fs.is_current=true and fs.is_internal=false and s_type.name='symbol' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fr4.object_id and fr4.type_id=fr4_type.cvterm_id and fr4.subject_id=opp.feature_id and otr.feature_id=otrfl.feature_id and opp.feature_id=oppfl.feature_id and opp.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=s_type.cvterm_id group by s.synonym_sgml;</sql>

FlyBase ID <sql>select opp.uniquename

from feature pp, feature tr, feature g, feature otr, feature opp, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, feature_relationship fr4, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, cvterm fr4_type, featureloc otrfl, featureloc oppfl where pp.uniquename='FBpp0086822' and opp.uniquename like 'FBpp%' and opp.uniquename != pp.uniquename and opp.is_analysis=false and opp.is_obsolete=false and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and fr4_type.name='producedby' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fr4.object_id and fr4.type_id=fr4_type.cvterm_id and fr4.subject_id=opp.feature_id and otr.feature_id=otrfl.feature_id and opp.feature_id=oppfl.feature_id;</sql>

Length (nt) <sql>select opp.seqlen

from feature pp, feature tr, feature g, feature otr, feature opp, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3, feature_relationship fr4, cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, cvterm fr4_type, featureloc otrfl, featureloc oppfl where pp.uniquename='FBpp0086822' and opp.uniquename like 'FBpp%' and opp.uniquename != pp.uniquename and opp.is_analysis=false and opp.is_obsolete=false and fr1_type.name='producedby' and fr2_type.name='partof' and fr3_type.name='partof' and fr4_type.name='producedby' and pp.feature_id=fr1.subject_id and fr1.type_id=fr1_type.cvterm_id and fr1.object_id=tr.feature_id and tr.feature_id=fr2.subject_id and fr2.type_id=fr2_type.cvterm_id and fr2.object_id=g.feature_id and g.feature_id=fr3.object_id and fr3.type_id=fr3_type.cvterm_id and fr3.subject_id=otr.feature_id and otr.feature_id=fr4.object_id and fr4.type_id=fr4_type.cvterm_id and fr4.subject_id=opp.feature_id and otr.feature_id=otrfl.feature_id and opp.feature_id=oppfl.feature_id;</sql>

EXTERNAL CROSSREFERENCES
Protein accessions <sql>select db.name,dbx.accession

from feature f, feature_dbxref fdbx, dbxref dbx, db where f.uniquename='FBpp0086822' and fdbx.is_current=true and db.name in ('GB_protein','REFSEQ') and f.feature_id=fdbx.feature_id and fdbx.dbxref_id=dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

SYNONYMS
  <sql>SELECT f.uniquename, f.name, s.name as synonym, synonym_sgml, cvt.name, p.uniquename

FROM cvterm cvt, feature f, feature_synonym fs, synonym s, pub p WHERE f.feature_id = fs.feature_id and fs.synonym_id = s.synonym_id and fs.pub_id = p.pub_id and s.type_id = cvt.cvterm_id and fs.is_current = 'f' and f.uniquename = 'FBpp0086822';</sql>

REFERENCES
Primary <sql>SELECT p.uniquename, p.miniref, p.title

from feature f, pub p, feature_pub fp where f.uniquename = 'FBpp0086822' and f.feature_id=fp.feature_id and fp.pub_id=p.pub_id;</sql>

FlyBase Reference Report

Example report: http://flybase.org/reports/FBrf0126983.html

REFERENCE
Citation <sql>select p.uniquename, p.pyear, p.title, array_to_string( array(

select pa.surname || ', ' || pa.givennames from pubauthor pa where pa.pub_id=p.pub_id), ', ' ) as authors, (select pp.value from pubprop pp, cvterm pp_type where pp_type.name='pubmed_fulltext_url' and pp.pub_id=p.pub_id and pp.type_id=pp_type.cvterm_id) as fulltext_url, (select pub_in.miniref from pub pub_in, pub_relationship pr, cvterm pr_type where pr_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=pr_type.cvterm_id) as journal, p.volume, p.issue, p.pages from pub p where p.uniquename='FBrf0126983';</sql>

FlyBase ID <sql>select uniquename

from pub where uniquename='FBrf0126983';</sql>

Type of publication
Offprint <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0126983' and cvt.name='cam_offprint' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

EXTERNAL CROSSREFERENCES
PubMed ID <sql>select dbx.accession

from pub p, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0126983' and db.name='pubmed' and pdbx.is_current=true and p.pub_id=pdbx.pub_id and pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

PubMed Abstract <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0126983' and cvt.name='pubmed_abstract' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

(Conference Abstract) <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0188865' and cvt.name='conf_abs_text' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

Biosis <sql>select dbx.accession

from pub p, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0126983' and db.name='biosis' and pdbx.is_current=true and p.pub_id=pdbx.pub_id and pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

Zoological record <sql>select dbx.accession

from pub p, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0025508' and db.name='zoorec_id' and pdbx.is_current=true and p.pub_id=pdbx.pub_id and pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

ASSOCIATED INFORMATION
Comments <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0148886' and cvt.name='associated_text' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

Text of personal
communication 		<sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0188739' and cvt.name='perscommtext' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

Associated files <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0191798' and cvt.name='deposited_files' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

RELATED PUBLICATIONS
Research paper <sql>select related_pub.uniquename, related_pub.miniref, pub_type.name

from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type where p.uniquename='FBrf0000634' and rel_type.name='related_to' and p.pub_id=pr.object_id and pr.subject_id=related_pub.pub_id and pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id UNION select related_pub.uniquename, related_pub.miniref, pub_type.name from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type where p.uniquename='FBrf0000634' and rel_type.name='related_to' and p.pub_id=pr.subject_id and pr.object_id=related_pub.pub_id and pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id;</sql>

Supplementary
material 		same as above, use pub type to distinguish
Review same as above, use pub type to distinguish
Erratum same as above, use pub type to distinguish
Retraction same as above, use pub type to distinguish
Personal
communication 		same as above, use pub type to distinguish
Abstract same as above, use pub type to distinguish
FlyBase analysis same as above, use pub type to distinguish
Other same as above, use pub type to distinguish
ALSO PUBLISHED AS
Research paper <sql>select related_pub.uniquename, related_pub.miniref, pub_type.name

from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type where p.uniquename='FBrf0000424' and rel_type.name='also_in' and p.pub_id=pr.object_id and pr.subject_id=related_pub.pub_id and pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id UNION select related_pub.uniquename, related_pub.miniref, pub_type.name from pub p, pub_relationship pr, pub related_pub, cvterm rel_type, cvterm pub_type where p.uniquename='FBrf0000424' and rel_type.name='also_in' and p.pub_id=pr.subject_id and pr.object_id=related_pub.pub_id and pr.type_id=rel_type.cvterm_id and related_pub.type_id=pub_type.cvterm_id;</sql>

Supplementary
material 		same as above, use pub type to distinguish
Review same as above, use pub type to distinguish
Erratum same as above, use pub type to distinguish
Retraction same as above, use pub type to distinguish
Personal
communication 		same as above, use pub type to distinguish
Abstract same as above, use pub type to distinguish
FlyBase analysis same as above, use pub type to distinguish
Other same as above, use pub type to distinguish
OTHER REFERENCE INFORMATION
Secondary IDs <sql>select dbx.accession

from pub p, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0000810' and db.name='FlyBase' and pdbx.is_current=false and p.pub_id=pdbx.pub_id and pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

Language of publication <sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0000002' and cvt.name='languages' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

Additional Language(s)
of abstract 		<sql>select pp.value

from pub p, pubprop pp, cvterm cvt where p.uniquename='FBrf0064412' and cvt.name='abstract_languages' and p.pub_id=pp.pub_id and pp.type_id=cvt.cvterm_id;</sql>

ISBN <sql>select dbx.accession

from pub p, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0019088' and db.name='isbn' and pdbx.is_current=true and p.pub_id=pdbx.pub_id and pdbx.dbxref_id = dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

Place of Publication <sql>select pubplace

from pub where uniquename ='FBrf0075564';</sql>

PUBLISHED IN
Abbreviation <sql>select pub_in.miniref

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Title <sql>select pub_in.title

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Authors <sql>select array_to_string( array(

select pa.surname || ', ' || pa.givennames from pubauthor pa where pa.pub_id=pub_in.pub_id), ', ' ) as authors from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0191612' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Volume Range <sql>select pub_in.volume

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Year Range <sql>select pub_in.pyear

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Page Range <sql>select pub_in.pages

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Publisher <sql>select pub_in.publisher

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Place of publication <sql>select pub_in.pubplace

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type where p.uniquename='FBrf0126983' and rel_type.name='published_in' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id;</sql>

Language of publication <sql>select pp.value

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pubprop pp, cvterm pp_type where p.uniquename='FBrf0002356' and rel_type.name='published_in' and pp_type.name='languages' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and pp.type_id=pp_type.cvterm_id and pub_in.pub_id=pp.pub_id;</sql>

ISBN or ISSN <sql>select dbx.accession

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0000051' and rel_type.name='published_in' and db.name in ('isbn','issn') and pdbx.is_current=true and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and pub_in.pub_id=pdbx.pub_id and pdbx.dbxref_id=dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

CODEN <sql>select dbx.accession

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pub_dbxref pdbx, dbxref dbx, db where p.uniquename='FBrf0000015' and rel_type.name='published_in' and db.name='coden' and pdbx.is_current=true and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and pub_in.pub_id=pdbx.pub_id and pdbx.dbxref_id=dbx.dbxref_id and dbx.db_id=db.db_id;</sql>

Associated files <sql>select pp.value

from pub p, pub pub_in, pub_relationship pr, cvterm rel_type, pubprop pp, cvterm pp_type where p.uniquename='FBrf0002356' and rel_type.name='published_in' and pp_type.name='deposted_files' and p.pub_id=pr.subject_id and pr.object_id=pub_in.pub_id and pr.type_id=rel_type.cvterm_id and pp.type_id=pp_type.cvterm_id and pub_in.pub_id=pp.pub_id;</sql>

DATA FROM REFERENCE
Genes
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBgn%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Transcripts
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBtr%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Polypeptides
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBpp%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Alleles
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBal%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Constructs
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename ~ 'FB(tp|ms|mc)[0-9]+$' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Insertions
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBti%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Aberations
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBab%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Balancers
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBba%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Images
 
Clones
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBcl%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Natural Transposons
  <sql>select s.synonym_sgml

from pub p, feature_pub fp, feature f, feature_synonym fs, synonym s, cvterm cvt where p.uniquename='FBrf0126983' and f.uniquename like 'FBte%' and cvt.name='symbol' and fs.is_current=true and p.pub_id=fp.pub_id and fp.feature_id=f.feature_id and f.feature_id=fs.feature_id and fs.synonym_id=s.synonym_id and s.type_id=cvt.cvterm_id group by s.synonym_sgml;</sql>

Retrieved from "http://gmod.org/mediawiki/index.php?title=FlyBase_Field_Mapping_Tables&oldid=5585"