GMOD

FlyBase Field Mapping Tables

Contents

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/FBgn0259750.html

General information
Symbol 
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';
Species 
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;
Name 
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';
Annotation symbol 
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';
Feature type 
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';
FlyBase ID 
SELECT f.uniquename
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND cvt.name = 'gene' AND
    f.name = 'ab';
Created/Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f
  WHERE uniquename = 'FBgn0000011';
Gene Model Status 
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';
Genetic Status 
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_genetic_status' AND f.uniquename = 'FBgn0000011';
GENOMIC LOCATION
Chromosome arm 
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';
Recombination map 
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';
Cytogenetic map 
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';
Sequence location 
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';
DETAILED MAPPING DATA
FlyBase computed cytological location
Cytogenetic map 
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';
Evidence for location 
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';
Experimentally determined cytological location
Cytogenetic map 
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';
Notes 
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';
Reference 
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';
Experimentally determined recombination data
Location 
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';
Left of (cM) 
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';
Right of (cM) 
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';
Notes 
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';
Reference See above
Molecular map data
Gene Order
(in direction of increasing cytology)		 
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';
Reference See above
Gene Order
(overall orientation not stated)		 
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';
Reference See above
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
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';
Name Synonym 
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';
SECONDARY FLYBASE IDs 
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';
GENE PRODUCTS & EXPRESSION
GENE MODEL & FEATURES
COMMENTS ON GENE MODEL 
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';
SEQUENCES SUPPORTING THE GENE MODEL
DNA sequence 
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';
Protein sequence See above
Name
UniProtKB/Swiss-Prot 
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';
UniProtKB/TrEMBL 
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';
Maps to 
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';
Does NOT map to 
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';
Identified with 
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';
MAPPED FEATURES AND MUTATIONS
Type 
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';
Symbol & Location 
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';
Additional Notes 
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';
References See above
EXTERNAL DATA
DEDB (LinkOut) 
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';
EPD 
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';
TRANSCRIPT DATA
ANNOTATED TRANSCRIPTS
Name 
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';
FlyBase ID See above
Length (nt) See above
Associated CDS (aa) 
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';
ADDITIONAL TRANSCRIPT DATA AND COMMENTS
Reported
transcript sizes		 
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';
Comments 
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';
EXTERNAL DATA
MIR 
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';
Rfam 
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';
POLYPEPTIDE DATA
ANNOTATED POLYPEPTIDES
Name 
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';
FlyBase ID See above
Predicted MW (kD) See above
Length (aa) See above
Theoretical pI See above
Genbank protein 
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';
ADDITIONAL POLYPEPTIDE DATA AND COMMENTS
Reported
protein sizes		 See first polypeptide query above
Comments See first polypeptide query above
EXTERNAL DATA
GCR 
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';
InterPro domains 
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';
MEROPS 
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';
MITODROME 
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';
NRL_3D 
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';
PANTHER (LinkOut) 
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';
PDB 
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';
TransFac 
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';
EXPRESSION DATA
BDGP in situ (LinkOut) 
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';
Yale Dev. Expression (LinkOut) 
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';
RELATED COMMENTS
  
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';
ALLELES
CLASSICAL ALLELES
Allele of (gene name) 
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';
Class 
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';
Mutagen 
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';
Stocks 
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';
Known lesion 
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';
ALLELES CARRIED ON TRANSGENIC CONSTRUCTS
Allele of (gene name) 
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';
Class 
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';
Mutagen 
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';
Stocks 
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';
Known lesion 
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';
SUMMARY OF ALLELE PHENOTYPES
Lethality 
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';
Allele See above
Sterility 
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';
Allele See above
Other Phenotypes 
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';
Allele See above
Phenotype manifest in 
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';
Allele See above
ANEUPLOID ABERRATIONS
(Useful Duplication) 
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';
(Useful Deficiency) See above
(Disrupted in) 
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';
(Partially disrupted in) 
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';
(Not Disrupted in) 
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';
(Duplicated in) 
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';
(Partially duplicated in) 
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';
(Not duplicated in) 
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';
TRANSGENIC CONSTRUCTS AND INSERTIONS
Transgenic constructs
Type of construct
heat-shock construct 
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';
UAS construct 
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';
characterization construct 
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';
vital-reporter construct 
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';
reporter construct 
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';
Insertions
insertion of mobile activating element 
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';
insertion_of_enhancer_trap 
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';
insertion_of_enhancer_trap_binary_system 
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';
miscellaneous insertions 
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';
RELATED COMMENTS
  
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';
SEQUENCE ONTOLOGY: Class of gene
  
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';
GENE ONTOLOGY: Function, Process, and Cellular Component
MOLECULAR FUNCTION
Terms based on experimental evidence
CV term
(Includes qualifier)		 
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';
Evidence code
(Includes identifier)		 See above
Reference See above
Terms based on predictions
CV term
(Includes qualifier)		 See above
Evidence code
(Includes identifier)		 See above
Reference See above
BIOLOGICAL PROCESS
Terms based on experimental evidence
CV term
(Includes qualifier)		 
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';
Evidence code
(Includes identifier)		 See above
Reference See above
Terms based on predictions
CV term
(Includes qualifier)		 See above
Evidence code
(Includes identifier)		 See above
Reference See above
CELLULAR COMPONENT
Terms based on experimental evidence
CV term
(Includes qualifier)		 
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';
Evidence code
(Includes identifier)		 See above
Reference See above
Terms based on predictions
CV term
(Includes qualifier)		 See above
Evidence code
(Includes identifier)		 See above
Reference See above
RELATED COMMENTS
  
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';
INTERACTIONS AND PATHWAYS
SUMMARY OF GENETIC INTERACTIONS
Interacting Gene 
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';
Allele of this gene See above
Reference See above
EXTERNAL DATA
BioGRID (LinkOut) 
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';
Dros. PIMRider (LinkOut) 
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';
ORTHOLOGS
Genome-wide drosophilid orthologs 
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';
Curated drosophilid orthologs 
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';
InParanoid orthologs (LinkOut) 
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';
FUNCTIONAL COMPLEMENTATION BETWEEN SPECIES
Functionally complements 
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';
Partially functionally complements See above
Does NOT functionally complement See above
Functionally complemented by 
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';
Partially functionally complemented by See above
NOT functionally complemented by See above
INTER-SPECIES MISEXPRESSION DATA
Produces phenotype in 
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';
Produces NO phenotype in 
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';
STOCKS AND REAGENTS
STOCKS LISTED IN FLYBASE
Bloomington 
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';
Kyoto See above
Szeged See above
Tucson See above
Carpenter See above
Garcia-Bellido See above
Harvard See above
Nusslein-Volhard See above
Saxton See above
Notes on availability 
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';
GENOMIC CLONES
  
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';
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) 
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';
Heidelberg RNAi (LinkOut) 
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';
DRSC (LinkOut) 
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';
ANTIBODY INFORMATION
  
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 = 'reported_antibod_gen' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = p.pub_id AND f.uniquename = 'FBgn0000011';
OTHER INFORMATION
DISCOVERER
  
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';
ETYMOLOGY
  
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';
IDENTIFICATION
  
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';
POSITION EFFECT VARIEGATION DATA
No PEV in 
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';
Dominant PEV in See above
Recessive PEV in See above
RELATIONSHIP TO OTHER GENES
Source for database identity of 
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';
Source for database merge of 
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';
(Member gene of) 
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';
(Component gene(s)) 
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';
(Encoded by) 
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';
(Tags) 
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';
Additional Comments 
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';
OTHER COMMENTS
  
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';
(FOREIGN GENE DATA)
  
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';
EXTERNAL CROSSREFERENCES & LINKOUTS
Sequence Crossreferences
DNA sequence 
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';
Protein sequence 
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';
UniProt/Swiss-Prot 
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';
UniProt/TrEMBL 
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';
Other Crossreferences
EPD See above
GCR See above
InterPro domains See above
MEROPS See above
MIR See above
MITODROME See above
NRL_3D See above
PDB See above
Rfam See above
TransFac See above
LinkOuts
BioGRID See above
BDGP in situ See above
DEDB Exons See above
Dros. PIMRider See above
DRSC See above
FLIGHT 
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';
FlyMine 
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';
Heidelberg RNAi See above
InParanoid See above
Interactive Fly 
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';
NCBI GEO See above
PANTHER See above
Yale Dev. Expression See above
REFERENCES
Research paper 
SELECT f.uniquename, f.name, cvt.name AS pub_type, p.uniquename AS FBrf_id, miniref AS citation
  FROM feature f, feature_pub fp, pub p, cvterm cvt
  WHERE f.feature_id = fp.feature_id AND fp.pub_id = p.pub_id AND
    p.type_id = cvt.cvterm_id AND f.uniquename = 'FBgn0000011' ORDER BY cvt.name;
 ;Supplementary material See above
Review See above
Abstract See above
Other See above

FlyBase Allele Report

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

GENERAL INFORMATION
Symbol 
SELECT DISTINCT(s.name)
  FROM feature f, feature_synonym fs, synonym s, cvterm cvt, cvterm cvt2,
     feature_relationship fr, cvterm cvt3
  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.feature_id = subject_id AND
    fr.type_id = cvt3.cvterm_id AND cvt3.name = 'alleleof' AND
    f.uniquename = 'FBal0000067';
Species 
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 = 'FBal0000067' AND
    f.organism_id = o.organism_id;
Name 
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 = 'FBal0000046';
FlyBase ID 
SELECT f.uniquename
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND cvt.name = 'gene' AND
    f.name = 'ab[1];
Feature type N/A
Created/ Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f
  WHERE uniquename = 'FBal0000046';
Associated gene (reported to Flybase as potentially erroneous) 
SELECT g.uniquename, g.name
  FROM feature a, feature g, feature_relationship fr, 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
    a.uniquename = 'FBal0000046';
Allele class 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'promoted_allele_class';
Mutagen 
SELECT f.feature_id, f.uniquename, f.name, cvt.cvterm_id, cvt.name, cvtp.type_id, cvtp.VALUE, p.uniquename
  FROM cvterm cvt, cvtermprop cvtp, feature f, feature_cvterm fcv, pub p
  WHERE f.feature_id = cv.feature_id AND fcv.cvterm_id = cvt.cvterm_id AND
    cvt.cvterm_id = cvtp.cvterm_id AND fcv.pub_id = p.pub_id AND
    cvtp.VALUE = 'origin_of_mutation' AND f.uniquename = 'FBal0000049' AND
    p.uniquename = 'unattributed';
NATURE OF THE ALLELE
Allele class 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_allele_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Mutagen 
SELECT f.feature_id, f.uniquename, f.name, cvt.cvterm_id, cvt.name, cvtp.type_id, cvtp.VALUE, p.uniquename
  FROM cvterm cvt, cvtermprop cvtp, feature f, feature_cvterm fcv, pub p
  WHERE f.feature_id = cv.feature_id AND fcv.cvterm_id = cvt.cvterm_id AND
    cvt.cvterm_id = cvtp.cvterm_id AND fcv.pub_id = p.pub_id AND
    cvtp.VALUE = 'origin_of_mutation' AND f.uniquename = 'FBal0000049' AND
    p.uniquename != 'unattributed';
Mapped features and mutations
Type
Symbol & Location
Additional Notes
References
Associated sequence data
DNA sequence 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_supporting_accessions' AND a.uniquename LIKE 'FBal%';
Protein sequence See above
Name
UniProt/Swiss-Prot 
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 'FBal%' 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';
UniProt/TrEMBL 
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 'FBal%' 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';
Progenitor genotype 
SELECT pr.uniquename, pr.name p.uniquename
  FROM feature f, feature_relationship fr, feature pr, cvterm cvt, feature_relationship_pub frp,
     pub p
  WHERE f.feature_id = fr.object_id AND fr.subject_id = pr.feature_id AND
    fr.type_id = cvt.cvterm_id AND cvt.name = 'progenitor' AND
    fr.feature_relationship_id = frp.feature_relationship_id AND frp.pub_id = p.pub_id AND
    f.uniquename = 'FBal0000080';
Nature of the lesion 
SELECT a.uniquename, a.name, cvt.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name IN ('molecular_info','aminoacid_rep',
      'nucleotide_sub') AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Mode of assay 
SELECT f.uniquename, f.name, cvtp.VALUE, cvt.name, p.uniquename
  FROM feature f, feature_genotype fg, phendesc ph, environment e, environment_cvterm ec,
     cvterm cvt, cvtermprop cvtp, pub p, cvterm gtp
  WHERE f.feature_id = fg.feature_id AND fg.genotype_id = ph.genotype_id AND
    ph.environment_id = e.environment_id AND ph.pub_id = p.pub_id AND
    e.environment_id = ec.environment_id AND ec.cvterm_id = cvt.cvterm_id AND
    cvt.cvterm_id = cvtp.cvterm_id AND cvtp.VALUE = 'mode_of_assay' AND
    f.is_obsolete = 'f' AND f.is_analysis = 'f' AND
    f.type_id = gtp.cvterm_id AND gtp.name = 'gene' AND
    f.uniquename LIKE 'FBal%';
(Caused by insertion) 
SELECT i.uniquename, i.name, p.uniquename
  FROM feature a, feature_relationship fr, feature i, cvterm cvt, cvterm cvt2,
     feature_relationship_pub frp, pub p
  WHERE a.feature_id = subject_id AND object_id = i.feature_id AND
    fr.feature_relationship_id = frp.feature_relationship_id AND frp.pub_id = p.pub_id AND
    i.type_id = cvt.cvterm_id AND fr.type_id = cvt2.cvterm_id AND
    cvt2.name = 'associated_with' AND cvt.name = 'transposable_element_insertion_site' AND
    a.uniquename LIKE 'FBal%';
(Carried in construct) 
SELECT a.uniquename, a.name, i.uniquename, i.name, p.uniquename
  FROM feature a, feature_relationship fr, feature i, cvterm itp, cvterm frtp,
     feature_relationship_pub frp, pub p
  WHERE a.feature_id = subject_id AND object_id = i.feature_id AND
    fr.type_id = frtp.cvterm_id AND frtp.name = 'associated_with' AND
    fr.feature_relationship_id = frp.feature_relationship_id AND frp.pub_id = p.pub_id AND
    i.type_id = itp.cvterm_id AND itp.name = 'transgenic_transposon' AND
    a.uniquename LIKE 'FBal%';
(Tags) 
SELECT a.uniquename, a.name, t.uniquename, t.name
  FROM feature a, feature_relationship fr, feature t, cvterm cvt
  WHERE a.feature_id = fr.subject_id AND fr.object_id = t.feature_id AND
    fr.type_id = cvt.cvterm_id AND cvt.name = 'included_in' AND
    a.uniquename LIKE 'FBal%';
(Tagged with)
(Caused by aberration) 
SELECT a.uniquename, a.name, i.uniquename, i.name, p.uniquename
  FROM feature a, feature_relationship fr, feature i, cvterm cvt, feature_relationship_pub frp,
     pub p, cvterm cvt2
  WHERE a.feature_id = object_id AND subject_id = i.feature_id AND
    fr.feature_relationship_id = frp.feature_relationship_id AND fr.type_id = cvt2.cvterm_id AND
    cvt2.name = 'associated_with' AND frp.pub_id = p.pub_id AND
    i.type_id = cvt.cvterm_id AND cvt.name = 'chromosome_structure_variation' AND
    a.uniquename LIKE 'FBal%';
(Carried on aberration) 
SELECT a.uniquename, a.name, i.uniquename, i.name, p.uniquename
  FROM feature a, feature_relationship fr, feature i, cvterm cvt, feature_relationship_pub frp,
     pub p, cvterm cvt2
  WHERE a.feature_id = subject_id AND object_id = i.feature_id AND
    fr.feature_relationship_id = frp.feature_relationship_id AND fr.type_id = cvt2.cvterm_id AND
    cvt2.name = 'carried_on' AND frp.pub_id = p.pub_id AND
    i.type_id = cvt.cvterm_id AND cvt.name = 'chromosome_structure_variation' AND
    a.uniquename LIKE 'FBal%';
Cytology 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'cyto_change_comment' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
PHENOTYPIC DATA
Phenotypic class
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_pheno_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Phenotype manifest in
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_pheno_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Detailed description 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_allele_pheno_description' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
GENETIC INTERACTIONS
Phenotypic class
Enhanced by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_enhanceable_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Enhanced by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-enhanceable_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Suppressed by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_suppressible_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Suppressed by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-suppressible_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Enhancer of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_enhancer_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Enhancer of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-enhancer_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Suppressor of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_suppressor_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Suppressor of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-suppressor_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Other
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_other_class' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Phenotype manifest in
Enhanced by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_enhanceable_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Enhanced by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-enhanceable_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Suppressed by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_suppressible_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Suppressed by
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-suppressible_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Enhancer of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_enhancer_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Enhancer of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-enhancer_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Suppressor of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_suppressor_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
NOT Suppressor of
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non-suppressor_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Other
  
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_other_manifest' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Additional comments (Genetic interactions) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_allele_interaction_comment' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Additional comments (Xenogenetic interactions) 


SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_allele_xeno_interaction_comment' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
COMPLEMENTATION & RESCUE DATA
(Complements) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_complements' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Partially complements) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_part_complements' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Fails to complement) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non_complements' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Rescued by) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_rescued_by' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Partially rescued by) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_part_rescued_by' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Not rescued by) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non_rescued_by' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Rescues) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_rescues' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Partially rescues) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_part_rescues' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
(Fails to rescue) 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_non_rescues' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
Comments 
SELECT a.uniquename, a.name, frp.VALUE, p.uniquename
  FROM feature a, featureprop frp, featureprop_pub frpp, pub p, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_allele_complementation_comment' AND frp.featureprop_id = frpp.featureprop_id AND
    frpp.pub_id = p.pub_id;
STOCKS
Bloomington 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Bloomington';
Kyoto 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Kyoto';
Szeged 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Szeged';
Tucson 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Tucson';
Carpenter 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Carpenter';
Garcia-Bellido 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Garcia-Bellido';
Harvard 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Harvard';
Nusslein-Volhard 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Nusslein-Volhard';
Saxton 
SELECT a.uniquename, a.name, frp.VALUE
  FROM feature a, featureprop frp, cvterm cvt
  WHERE a.feature_id = frp.feature_id AND frp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_stock_Saxton';
Notes on availability
NOTES ON ORIGIN
Discoverer
 
COMMENTS
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym
Name Synonym
SECONDARY FLYBASE IDs
REFERENCES
Research paper
Supplementary
material
Review
Abstract
Other

FlyBase Gene Expression Report

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

GENERAL INFORMATION [unattributed]
Symbol 
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';
Species 
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;
Name 
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';
Annotation symbol 
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';
Feature type 
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';
FlyBase ID 
SELECT f.uniquename
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND cvt.name = 'gene' AND
    f.name = 'ab';
Created/ Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f
  WHERE uniquename = 'FBgn0000011';
TRANSCRIPT EXPRESSION
EXPRESSION DATA
Stage 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_bodypart_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490'; NOTE that this will RETURN a structured string that contains stage information IN the bracketed 't' portion OF the statement.;
Tissue/Position 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_bodypart_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490'; NOTE that this will RETURN a structured string that contains tissue/POSITION information IN the bracketed 'a' (AND 'p') portion OF the statement.;
Reference 
NOTE: the pub uniquename associated WITH a statement IS returned USING the queries above. TO directly GET a specific pub association you need either the featureprop_id OR the feature_id/rank/featureprop.type_id UNIQUE KEY VALUES WITH which you can:
SELECT uniquename
  FROM featureprop fp, featureprop_pub, pub
  WHERE fp.featureprop_id = fpp.featureprop_id AND fpp.pub_id = pub.pub_id AND
    fp.featureprop_id = ?;
ADDITIONAL DESCRIPTIVE DATA
  
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_bodypart_expression_text' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
Assay mode 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_mode_of_assay' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
Marker for 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_bodypart_expression_marker' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
SUBCELLULAR LOCALIZATION
Subcellular localization 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_subcellular_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
Reference 
NOTE: the pub uniquename associated WITH a statement IS returned USING the queries above. TO directly GET a specific pub association you need either the featureprop_id OR the feature_id/rank/featureprop.type_id UNIQUE KEY VALUES WITH which you can:
SELECT uniquename
  FROM featureprop fp, featureprop_pub, pub
  WHERE fp.featureprop_id = fpp.featureprop_id AND fpp.pub_id = pub.pub_id AND
    fp.featureprop_id = ?;
Notes 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_transcript_subcellular_expression_text' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
POLYPEPTIDE EXPRESSION REPORT
EXPRESSION DATA
Stage 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_bodypart_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490'; NOTE that this will RETURN a structured string that contains stage information IN the bracketed 't' portion OF the statement.;
Tissue/Position 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_bodypart_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490'; NOTE that this will RETURN a structured string that contains stage information IN the bracketed 'a' (AND 'p') portion OF the statement.;
Reference 
NOTE: the pub uniquename associated WITH a statement IS returned USING the queries above. TO directly GET a specific pub association you need either the featureprop_id OR the feature_id/rank/featureprop.type_id UNIQUE KEY VALUES WITH which you can:
SELECT uniquename
  FROM featureprop fp, featureprop_pub, pub
  WHERE fp.featureprop_id = fpp.featureprop_id AND fpp.pub_id = pub.pub_id AND
    fp.featureprop_id = ?;
ADDITIONAL DESCRIPTIVE DATA
  
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_bodypart_expression_text' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
Assay mode 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_bodypart_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490'; NOTE: assay IS found IN the 'as' bracketed tag IN the statement;
Marker for 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_bodypart_expression_marker' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
SUBCELLULAR LOCALIZATION
Subcellular localization 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_subcellular_expression_cv' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
Reference 
NOTE: the pub uniquename associated WITH a statement IS returned USING the queries above. TO directly GET a specific pub association you need either the featureprop_id OR the feature_id/rank/featureprop.type_id UNIQUE KEY VALUES WITH which you can:
SELECT uniquename
  FROM featureprop fp, featureprop_pub, pub
  WHERE fp.featureprop_id = fpp.featureprop_id AND fpp.pub_id = pub.pub_id AND
    fp.featureprop_id = ?;
Notes 
SELECT VALUE, pub.uniquename
  FROM feature f, featureprop fp, cvterm cvt, featureprop_pub fpp, pub
  WHERE f.feature_id = fp.feature_id AND fp.type_id = cvt.cvterm_id AND
    cvt.name = 'derived_polypeptide_subcellular_expression_text' AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = pub.pub_id AND f.uniquename = 'FBgn0000490';
REFERENCES
Research paper
Supplementary
material
Review
Abstract
Other

FlyBase Transcript Report

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

GENERAL INFORMATION
Symbol 
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 = 'pseudogene' OR
    cvt.name LIKE '%RNA') 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 = 'FBtr0087703' GROUP BY s.synonym_sgml;
Species 
SELECT o.genus, o.species
  FROM feature f, cvterm cvt, organism o
  WHERE f.type_id = cvt.cvterm_id AND (cvt.name = 'pseudogene' OR
    cvt.name LIKE '%RNA') AND f.is_obsolete = 'f' AND
    f.uniquename = 'FBtr0087703' AND f.organism_id = o.organism_id;
Annotation symbol 
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 = 'FBtr0087703';
FlyBase ID 
SELECT f.uniquename
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND (cvt.name = 'pseudogene' OR
    cvt.name LIKE '%RNA') AND f.name = 'cnn-RA' AND
    f.is_analysis=FALSE AND f.is_obsolete=FALSE;
Feature type 
SELECT cvt.name
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND f.uniquename = 'FBtr0087703' AND
    f.is_analysis=FALSE AND f.is_obsolete=FALSE;
Associated gene 
SELECT DISTINCT(g.uniquename),g.name
  FROM feature tr, feature g, feature_relationship fr, cvterm fr_type, cvterm tr_type,
     cvterm g_type
  WHERE tr.uniquename='FBtr0087703' AND tr.is_obsolete=FALSE AND
    tr.is_analysis=FALSE AND fr_type.name='partof' AND
    g_type.name='gene' AND tr.feature_id=fr.subject_id AND
    fr.object_id=g.feature_id;
Created/ Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND (cvt.name = 'pseudogene' OR
    cvt.name LIKE '%RNA') AND f.uniquename = 'FBtr0087703' AND
    f.is_analysis=FALSE AND f.is_obsolete=FALSE;
Evidence score 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtr0087703' AND fp_type.name='score' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Evidence rank 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtr0087703' AND fp_type.name='score_text' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Length (nt) 
SELECT seqlen
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND (cvt.name = 'pseudogene' OR
    cvt.name LIKE '%RNA') AND f.uniquename = 'FBtr0087703' AND
    f.is_analysis=FALSE AND f.is_obsolete=FALSE;
Map (GBrowse)
SUPPORTING cDNA CLONES
cDNA clones, fully sequenced
Exact Match 
SELECT clone.name, clone.uniquename
  FROM feature tr, feature cDNA, feature clone, feature_relationship fr1, feature_relationshipprop fr1_prop,
     feature_relationship fr2, cvterm fr1_type, cvterm cDNA_type, cvterm clone_type
  WHERE tr.uniquename = 'FBtr0070000' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND fr1_type.name='supports' AND
    fr1_prop.VALUE='exact' AND cDNA_type.name='cDNA' AND
    clone_type.name='cDNA_clone' AND tr.feature_id=fr1.object_id AND
    fr1.feature_relationship_id=fr1_prop.feature_relationship_id AND fr1.subject_id=cDNA.feature_id AND
    cDNA.type_id=cDNA_type.cvterm_id AND cDNA.feature_id=fr2.subject_id AND
    fr2.object_id=clone.feature_id AND clone.type_id=clone_type.cvterm_id;
Contained within the
annotated transcript,
internally consistent		 
SELECT clone.name, clone.uniquename
  FROM feature tr, feature cDNA, feature clone, feature_relationship fr1, feature_relationshipprop fr1_prop,
     feature_relationship fr2, cvterm fr1_type, cvterm cDNA_type, cvterm clone_type
  WHERE tr.uniquename = 'FBtr0005088' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND fr1_type.name='supports' AND
    fr1_prop.VALUE='full' AND cDNA_type.name='cDNA' AND
    clone_type.name='cDNA_clone' AND tr.feature_id=fr1.object_id AND
    fr1.feature_relationship_id=fr1_prop.feature_relationship_id AND fr1.subject_id=cDNA.feature_id AND
    cDNA.type_id=cDNA_type.cvterm_id AND cDNA.feature_id=fr2.subject_id AND
    fr2.object_id=clone.feature_id AND clone.type_id=clone_type.cvterm_id;
End(s) extend beyond
the annotated transcript,
internally consistent		 
SELECT clone.name, clone.uniquename
  FROM feature tr, feature cDNA, feature clone, feature_relationship fr1, feature_relationshipprop fr1_prop,
     feature_relationship fr2, cvterm fr1_type, cvterm cDNA_type, cvterm clone_type
  WHERE tr.uniquename = 'FBtr0005009' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND fr1_type.name='supports' AND
    fr1_prop.VALUE IN ('not_five','not_three',
      'not_ends') AND cDNA_type.name='cDNA' AND
    clone_type.name='cDNA_clone' AND tr.feature_id=fr1.object_id AND
    fr1.feature_relationship_id=fr1_prop.feature_relationship_id AND fr1.subject_id=cDNA.feature_id AND
    cDNA.type_id=cDNA_type.cvterm_id AND cDNA.feature_id=fr2.subject_id AND
    fr2.object_id=clone.feature_id AND clone.type_id=clone_type.cvterm_id;
cDNA clones, end sequence only (ESTs)
Contained within the
annotated transcript,
internally consistent		 
SELECT clone.name, clone.uniquename
  FROM feature tr, feature EST, feature clone, feature_relationship fr1, feature_relationshipprop fr1_prop,
     feature_relationship fr2, cvterm fr1_type, cvterm EST_type, cvterm clone_type
  WHERE tr.uniquename = 'FBtr0005009' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND fr1_type.name='supports' AND
    fr1_prop.VALUE='full' AND EST_type.name='EST' AND
    clone_type.name='cDNA_clone' AND tr.feature_id=fr1.object_id AND
    fr1.feature_relationship_id=fr1_prop.feature_relationship_id AND fr1.subject_id=EST.feature_id AND
    EST.type_id=EST_type.cvterm_id AND EST.feature_id=fr2.subject_id AND
    fr2.object_id=clone.feature_id AND clone.type_id=clone_type.cvterm_id;
End(s) extend beyond
the annotated transcript,
internally consistent		 
SELECT clone.name, clone.uniquename
  FROM feature tr, feature EST, feature clone, feature_relationship fr1, feature_relationshipprop fr1_prop,
     feature_relationship fr2, cvterm fr1_type, cvterm EST_type, cvterm clone_type
  WHERE tr.uniquename = 'FBtr0005009' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND fr1_type.name='supports' AND
    fr1_prop.VALUE IN ('not_five','not_three',
      'not_ends') AND EST_type.name='EST' AND
    clone_type.name='cDNA_clone' AND tr.feature_id=fr1.object_id AND
    fr1.feature_relationship_id=fr1_prop.feature_relationship_id AND fr1.subject_id=EST.feature_id AND
    EST.type_id=EST_type.cvterm_id AND EST.feature_id=fr2.subject_id AND
    fr2.object_id=clone.feature_id AND clone.type_id=clone_type.cvterm_id;
EXONS
Exon
Sequence Location
SEQUENCE
  
SELECT residues
  FROM feature f, cvterm cvt
  WHERE f.type_id = cvt.cvterm_id AND (cvt.name = 'pseudogene' OR
    cvt.name LIKE '%RNA') AND f.uniquename = 'FBtr0005009' AND
    f.is_analysis=FALSE AND f.is_obsolete=FALSE;
OTHER PRODUCTS OF THIS GENE
Other Transcripts
Name 
SELECT s.synonym_sgml
  FROM feature tr, feature g, feature otr, cvterm tr_type, cvterm g_type,
     cvterm otr_type, feature_relationship fr1, feature_relationship fr2, cvterm fr1_type, cvterm fr2_type,
     featureloc fl, feature_synonym fs, synonym s, cvterm s_type
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND otr.uniquename ~ '^FBtr[0-9]+$' AND
    g.uniquename ~ '^FBgn[0-9]+$' AND (tr_type.name = 'pseudogene' OR
    tr_type.name LIKE '%RNA') AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    fr1_type.name='partof' AND fr2_type.name='partof' AND
    otr.uniquename != tr.uniquename AND fs.is_current=TRUE AND
    fs.is_internal=FALSE AND s_type.name='symbol' AND
    tr.feature_id=fr1.subject_id AND fr1.object_id=g.feature_id AND
    g.feature_id=fr2.object_id AND fr2.subject_id=otr.feature_id AND
    fr1.type_id=fr1_type.cvterm_id AND fr2.type_id=fr2_type.cvterm_id AND
    tr.type_id=tr_type.cvterm_id AND g.type_id=g_type.cvterm_id AND
    otr.type_id=otr_type.cvterm_id AND otr.feature_id=fl.feature_id AND
    otr.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;
FlyBase ID 
SELECT otr.uniquename
  FROM feature tr, feature g, feature otr, cvterm tr_type, cvterm g_type,
     cvterm otr_type, feature_relationship fr1, feature_relationship fr2, cvterm fr1_type, cvterm fr2_type,
     featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND otr.uniquename ~ '^FBtr[0-9]+$' AND
    g.uniquename ~ '^FBgn[0-9]+$' AND (tr_type.name = 'pseudogene' OR
    tr_type.name LIKE '%RNA') AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    fr1_type.name='partof' AND fr2_type.name='partof' AND
    otr.uniquename != tr.uniquename AND tr.feature_id=fr1.subject_id AND
    fr1.object_id=g.feature_id AND g.feature_id=fr2.object_id AND
    fr2.subject_id=otr.feature_id AND fr1.type_id=fr1_type.cvterm_id AND
    fr2.type_id=fr2_type.cvterm_id AND tr.type_id=tr_type.cvterm_id AND
    g.type_id=g_type.cvterm_id AND otr.type_id=otr_type.cvterm_id AND
    otr.feature_id=fl.feature_id;
Length (nt) 
SELECT otr.seqlen
  FROM feature tr, feature g, feature otr, cvterm tr_type, cvterm g_type,
     cvterm otr_type, feature_relationship fr1, feature_relationship fr2, cvterm fr1_type, cvterm fr2_type,
     featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND otr.uniquename ~ '^FBtr[0-9]+$' AND
    g.uniquename ~ '^FBgn[0-9]+$' AND (tr_type.name = 'pseudogene' OR
    tr_type.name LIKE '%RNA') AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    fr1_type.name='partof' AND fr2_type.name='partof' AND
    otr.uniquename != tr.uniquename AND tr.feature_id=fr1.subject_id AND
    fr1.object_id=g.feature_id AND g.feature_id=fr2.object_id AND
    fr2.subject_id=otr.feature_id AND fr1.type_id=fr1_type.cvterm_id AND
    fr2.type_id=fr2_type.cvterm_id AND tr.type_id=tr_type.cvterm_id AND
    g.type_id=g_type.cvterm_id AND otr.type_id=otr_type.cvterm_id AND
    otr.feature_id=fl.feature_id;
Polypeptides derived from this transcript
Name 
SELECT s.synonym_sgml
  FROM feature tr, feature p, cvterm tr_type, cvterm p_type, feature_relationship fr1,
     cvterm fr1_type, featureloc fl, feature_synonym fs, synonym s, cvterm s_type
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND p.uniquename ~ '^FBpp[0-9]+$' AND
    (tr_type.NAME = 'pseudogene' OR tr_type.NAME LIKE '%RNA') AND
    p_type.NAME='protein' AND fr1_type.NAME='producedby' AND
    fs.is_current=TRUE AND fs.is_internal=FALSE AND
    s_type.NAME='symbol' AND tr.feature_id=fr1.object_id AND
    fr1.subject_id=p.feature_id AND fr1.type_id=fr1_type.cvterm_id AND
    tr.type_id=tr_type.cvterm_id AND p.feature_id=fl.feature_id AND
    p.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;
FlyBase ID 
SELECT p.uniquename
  FROM feature tr, feature p, cvterm tr_type, cvterm p_type, feature_relationship fr1,
     cvterm fr1_type, featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND p.uniquename ~ '^FBpp[0-9]+$' AND
    (tr_type.NAME = 'pseudogene' OR tr_type.NAME LIKE '%RNA') AND
    p_type.NAME='protein' AND fr1_type.NAME='producedby' AND
    tr.feature_id=fr1.object_id AND fr1.subject_id=p.feature_id AND
    fr1.type_id=fr1_type.cvterm_id AND tr.type_id=tr_type.cvterm_id AND
    p.feature_id=fl.feature_id;
Length (nt) 
SELECT p.seqlen
  FROM feature tr, feature p, cvterm tr_type, cvterm p_type, feature_relationship fr1,
     cvterm fr1_type, featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND p.uniquename ~ '^FBpp[0-9]+$' AND
    (tr_type.NAME = 'pseudogene' OR tr_type.NAME LIKE '%RNA') AND
    p_type.NAME='protein' AND fr1_type.NAME='producedby' AND
    tr.feature_id=fr1.object_id AND fr1.subject_id=p.feature_id AND
    fr1.type_id=fr1_type.cvterm_id AND tr.type_id=tr_type.cvterm_id AND
    p.feature_id=fl.feature_id;
Polypeptides derived from other transcripts of this gene
Name 
SELECT s.synonym_sgml
  FROM feature tr, feature g, feature otr, feature op, cvterm g_type,
     cvterm otr_type, cvterm op_type, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3,
     cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, featureloc fl, feature_synonym fs,
     synonym s, cvterm s_type
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    op_type.name='protein' AND fr1_type.name='partof' AND
    fr2_type.name='partof' AND fr3_type.name='producedby' AND
    otr.uniquename != tr.uniquename AND fs.is_current=TRUE AND
    fs.is_internal=FALSE AND s_type.name='symbol' AND
    tr.feature_id=fr1.subject_id AND fr1.object_id=g.feature_id AND
    g.feature_id=fr2.object_id AND fr2.subject_id=otr.feature_id AND
    otr.feature_id=fr3.object_id AND fr3.subject_id=op.feature_id AND
    fr1.type_id=fr1_type.cvterm_id AND fr2.type_id=fr2_type.cvterm_id AND
    fr3.type_id=fr3_type.cvterm_id AND g.type_id=g_type.cvterm_id AND
    otr.type_id=otr_type.cvterm_id AND op.type_id=op_type.cvterm_id AND
    op.feature_id=fl.feature_id AND op.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;
FlyBase ID 
SELECT op.uniquename
  FROM feature tr, feature g, feature otr, feature op, cvterm g_type,
     cvterm otr_type, cvterm op_type, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3,
     cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    op_type.name='protein' AND fr1_type.name='partof' AND
    fr2_type.name='partof' AND fr3_type.name='producedby' AND
    otr.uniquename != tr.uniquename AND tr.feature_id=fr1.subject_id AND
    fr1.object_id=g.feature_id AND g.feature_id=fr2.object_id AND
    fr2.subject_id=otr.feature_id AND otr.feature_id=fr3.object_id AND
    fr3.subject_id=op.feature_id AND fr1.type_id=fr1_type.cvterm_id AND
    fr2.type_id=fr2_type.cvterm_id AND fr3.type_id=fr3_type.cvterm_id AND
    g.type_id=g_type.cvterm_id AND otr.type_id=otr_type.cvterm_id AND
    op.type_id=op_type.cvterm_id AND op.feature_id=fl.feature_id;
Length (nt) 
SELECT op.seqlen
  FROM feature tr, feature g, feature otr, feature op, cvterm g_type,
     cvterm otr_type, cvterm op_type, feature_relationship fr1, feature_relationship fr2, feature_relationship fr3,
     cvterm fr1_type, cvterm fr2_type, cvterm fr3_type, featureloc fl
  WHERE tr.uniquename='FBtr0087703' AND tr.is_analysis=FALSE AND
    tr.is_obsolete=FALSE AND (otr_type.name = 'pseudogene' OR
    otr_type.name LIKE '%RNA') AND g_type.name='gene' AND
    op_type.name='protein' AND fr1_type.name='partof' AND
    fr2_type.name='partof' AND fr3_type.name='producedby' AND
    otr.uniquename != tr.uniquename AND tr.feature_id=fr1.subject_id AND
    fr1.object_id=g.feature_id AND g.feature_id=fr2.object_id AND
    fr2.subject_id=otr.feature_id AND otr.feature_id=fr3.object_id AND
    fr3.subject_id=op.feature_id AND fr1.type_id=fr1_type.cvterm_id AND
    fr2.type_id=fr2_type.cvterm_id AND fr3.type_id=fr3_type.cvterm_id AND
    g.type_id=g_type.cvterm_id AND otr.type_id=otr_type.cvterm_id AND
    op.type_id=op_type.cvterm_id AND op.feature_id=fl.feature_id;
COMMENTS
  
SELECT fp.VALUE
  FROM feature tr, featureprop fp, cvterm fp_type
  WHERE tr.uniquename='FBtr0000021' AND fp_type.name='comment' AND
    tr.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
EXTERNAL CROSSREFERENCES
DNA sequence
Name
SYNONYMS
  
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 = 'FBtr0087703';
REFERENCES
Primary 
SELECT p.uniquename, p.miniref, p.title
  FROM feature f, pub p, feature_pub fp
  WHERE f.uniquename = 'FBtr0087703' AND f.feature_id=fp.feature_id AND
    fp.pub_id=p.pub_id;

FlyBase Polypeptide Report

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

GENERAL INFORMATION
Symbol 
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;
Species 
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;
Annotation symbol 
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';
FlyBase ID 
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;
Associated gene 
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;
Created/ Updated 
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;
Length (aa) 
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;
Theoretical pI 
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';
Predicted MW (kD) 
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';
Map (GBrowse)
SEQUENCE
  
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;
OTHER PRODUCTS OF THIS GENE
Transcripts Corresponding to THIS polypeptide
Name 
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;
FlyBase ID 
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;
Length (nt) 
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;
Transcripts Corresponding to OTHER polypeptides
Name 
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;
FlyBase ID 
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;
Length (nt) 
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;
Other Polypeptides
Name 
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;
FlyBase ID 
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;
Length (nt) 
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;
EXTERNAL CROSSREFERENCES
Protein accessions 
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;
SYNONYMS
  
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';
REFERENCES
Primary 
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;

FlyBase Insertion Report

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

GENERAL INFORMATION [unattributed]
Symbol 
SELECT DISTINCT(s.synonym_sgml)
  FROM feature f, feature_synonym fs, synonym s, cvterm cvt
  WHERE 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 = cvt.cvterm_id AND cvt.name = 'symbol' AND
    f.is_obsolete = 'f' AND f.uniquename = 'FBti0000001';
Species 
SELECT o.genus, o.species
  FROM feature f, organism o
  WHERE f.is_obsolete = 'f' AND f.uniquename ='FBti0000001' AND
    f.organism_id = o.organism_id;
Name
FlyBase ID
Feature type 
SELECT cvterm.name
  FROM feature f, cvterm
  WHERE f.type_id=cvterm.cvterm_id AND f.uniquename = 'FBti0000001';
Created\ Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f
  WHERE uniquename = 'FBti0000001' AND is_obsolete='f';
DESCRIPTION [unattributed]
Inserted element 
SELECT fp.name
  FROM feature f, feature fp, feature_relationship fr
  WHERE fr.subject_id=f.feature_id AND fr.object_id=fp.feature_id AND
    fr.type_id=27 AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f';
Affected gene(s) 
SELECT fg.name
  FROM feature fg, feature f, feature_relationship fr, cvterm cvt
  WHERE fr.subject_id=fg.feature_id AND f.feature_id=fr.object_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fg.type_id=219 AND fg.uniquename LIKE 'FBgn%' AND
    fg.organism_id=f.organism_id AND f.uniquename='FBti0000001';
Causes allele(s) 
SELECT fg.name
  FROM feature fg, feature f, feature_relationship fr, cvterm cvt
  WHERE fr.subject_id=fg.feature_id AND f.feature_id=fr.object_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fg.type_id=219 AND fg.uniquename LIKE 'FBal%' AND
    fg.organism_id=f.organism_id AND f.uniquename='FBti0000001';
LINE ID 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE cvt.name='originating_line' AND cvt.cvterm_id=fp.type_id AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001' AND
    fp.feature_id=f.feature_id;
Localized function
Expression data 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_expression_data';
Viability/fertility 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='curated_phenotype';
Stock availability
GENOMIC LOCATION [unattributed]
Chromosomal location [part 1: arm] 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_chromosome_location';
Chromosomal location [part 2: cytol] 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_genomic_cyto_location';
Sequence location 
SELECT src.uniquename fl.fmin, fl.fmax
  FROM feature f, featureloc fl, feature src
  WHERE fl.feature_id=f.feature_id AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001' AND fl.srcfeature_id=src.feature_id;
Map (GBrowse)
DETAILED MAPPING DATA
Chromosome (arm) 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, cvterm cvt, pub, featureprop_pub frp
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    frp.pub_id=pub.pub_id AND frp.featureprop_id=fp.featureprop_id AND
    cvt.name='curated_chromosomal_location';
Sequence location 
SELECT src.uniquename fl.fmin, fl.fmax, pub.uniquename
  FROM feature f, featureloc fl,featureloc_pub, flp, pub,
     feature src
  WHERE fl.feature_id=f.feature_id AND flp.pub_id=pub.pub_id AND
    flp.featureloc_id=fl.featureloc_id AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001' AND fl.srcfeature_id=src.feature_id;
Orientation 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, featureprop_pub, fpp, pub, feature f,
     cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fpp.featureprop_id=fp.featureprop_id AND
    pub.pub_id=fpp.pub_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='chromosomal_orientation';
Cytological location (computed by FlyBase) 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_comput_cyto_location';
Cytological location (reported) 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, cvterm cvt, pub, featureprop_pub frp
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    frp.pub_id=pub.pub_id AND frp.featureprop_id=fp.featureprop_id AND
    cvt.name='curated_cytological_location';
Comments concerning location 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, cvterm cvt, pub, featureprop_pub frp
  WHERE fp.feature_id=f.feature_id AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f' AND fp.type_id=cvt.cvterm_id AND
    frp.pub_id=pub.pub_id AND frp.featureprop_id=fp.featureprop_id AND
    cvt.name='gen_loc_comment';
Chromosome in situ
SEQUENCE DATA
Flanking sequence 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transposable_element_flanking_region' AND
    f.uniquename='FBti0000001';
INSERTED ELEMENT
Construct 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='producedby' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name!='natural_transposon' AND
    f.uniquename='FBti0000001';
Location-dependent role
Size 
SELECT fp.VALUE
  FROM feature f, feature_relationship fr, feature ft, featureprop fp, cvterm cvt,
     cvterm cvt2
  WHERE f.feature_id=fr.subject_id AND fr.object_id=ft.feature_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='producedby' AND
    ft.feature_id=fp.feature_id AND fp.type_id=cvt2.cvterm_id AND
    (cvt2.name='compiled_kb_length' OR cvt2.name='kb_length') AND
    f1.is_obsolete='f' AND f1.uniquename='FBti0000001';
Associated alleles 
SELECT fp.name
  FROM feature f, feature_relationship fr1, feature_relationship fr2, feature fp, cvterm c1,
     cvterm c2
  WHERE fr1.type_id=c1.cvterm_id AND c1.name='producedby' AND
    f.feature_id=fr1.subject_id AND fr1.object_id=fr2.object_id AND
    fr2.object_id=fp.feature_id AND fr2.type_id=c2.cvterm_id AND
    c2.name='derived_tp_assoc_alleles' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Molecular map
Transposon class
Element type
AFFECTED GENES
Insertion within gene
Insertion may affect gene 
SELECT fo.uniquename, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, feature_relationship_pub frp, pub,
     cvterm cvt
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' frp.feature_relationship_id=fr.feature_relationship_id AND
    frp.pub_id=pub.pub_id AND fo.uniquename LIKE 'FBgn%' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Orientation (relative to gene) 
SELECT fo.name, frp.VALUE, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, feature_relationshipprop frp, feature_relationshipprop_pub frpp,
     pub, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.uniquename LIKE 'FBgn%' AND frp.feature_relationshipprop_id=frpp.feature_relationshipprop_id AND
    frpp.pub_id=pub.pub_id AND fr.feature_relationship_id=frp.feature_relationship_id AND
    frp.type_id= cvt2.cvterm_id AND cvt2.name='relative_orientation' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
ALLELES AND PHENOTYPES
Causes alleles 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.uniquename LIKE 'FBal%' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Lethality (table)
Sterility (table)
Phenotype manifest in
Detailed description
EXPRESSION DATA
[Transcript]
Reporter expression
Stage
Tissue/Position
Reference
Additional Information
Statement
Reference
Assay mode
Marker for
Reflects expression of
EXTERNAL IMAGES
DATA ON GENETIC LINE
Line ID 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, featureprop_pub frp, pub, feature f, cvterm cvt
  WHERE cvt.name='originating_line' AND cvt.cvterm_id=fp.type_id AND
    frp.featureprop_id=fp.featureprop_id AND frp.pub_id=pub.pub_id AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001' AND
    fp.feature_id=f.feature_id;
Origin as multiple insertion line 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp,featureprop_pub frp, pub, feature f, cvterm cvt
  WHERE cvt.name='is_multiple_insertion_line' AND frp.featureprop_id=fp.featureprop_id AND
    frp.pub_id=pub.pub_id AND cvt.cvterm_id=fp.type_id AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001' AND
    fp.feature_id=f.feature_id;
PROGENITOR(S) WITHIN GENOME
 
Transposed descendant of 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='transposed_descendant_of' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transgenic_transposon_insertion_site' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Recombinant descendant of (donor) 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='recombinant_descendant_of' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transgenic_transposon_insertion_site' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Replacement descendant of (target) 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='replacement_descendant_of' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transgenic_transposon_insertion_site' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Modified descendant of 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='modified_descendant_of' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transgenic_transposon_insertion_site' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
RELATED ABERRATION OR BALANCER
Aberration 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='chromosome_structure_variation' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Balancer 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='single balancer' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
STOCKS
Bloomington 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Bloomington' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Kyoto 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Kyoto' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Szeged 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Szeged' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Tucson 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Tucson' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Carpenter 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Carpenter' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Garcia-Bellido 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Garcia-Bellido' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Harvard 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Harvard' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Nusslein-Volhard 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Nusslein-Volhard' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Saxton 
SELECT fp.VALUE
  FROM featureprop fp, feature f, cvterm cvt
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='derived_stock_Saxton' AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
Notes on availability 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='availablility' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
LINK OUTS
DrosDel
FlyView
COMMENTS
  
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='comment' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBti0000001';
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
SELECT s.synonym_sgml, pub.uniquename
  FROM feature_synonym fs, feature f, synonym s, pub
  WHERE fs.feature_id=f.feature_id AND fs.synonym_id=s.synonym_id AND
    fs.is_internal='f' AND fs.pub_id=pub.pub_id AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
SECONDARY FLYBASE IDs
  
SELECT dbxref.accession
  FROM dbxref, feature_dbxref fd, feature f, db
  WHERE f.feature_id=fd.feature_id AND dbxref.dbxref_id=fd.dbxref_id AND
    fd.is_current='f' AND dbxref.db_id=db.db_id AND
    db.name='FlyBase' AND f.uniquename='FBti0000001' AND
    f.is_obsolete='f';
REFERENCES
Research paper 
SELECT f.uniquename, f.name, cvt.name AS pub_type, p.uniquename AS FBrf_id, miniref AS citation
  FROM feature f, feature_pub fp, pub p, cvterm cvt
  WHERE f.feature_id = fp.feature_id AND fp.pub_id = p.pub_id AND
    p.type_id = cvt.cvterm_id AND f.uniquename = 'FBti0000001' ORDER BY cvt.name;
High-throughput resource research paper
Supplementary
material
FlyBase analysis
Computer file
Review
Abstract
Other

FlyBase Clone Report

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

General information
Symbol 
SELECT name
  FROM feature
  WHERE uniquename = 'FBcl0000001' AND is_obsolete=FALSE;
Species 
SELECT o.genus, o.species
  FROM feature f, organism o
  WHERE f.is_obsolete = 'f' AND f.uniquename = 'FBcl0000001' AND
    f.organism_id = o.organism_id;
Name 
SELECT name
  FROM feature
  WHERE uniquename = 'FBcl0000001' AND is_obsolete=FALSE;
FlyBase ID 
SELECT uniquename
  FROM feature
  WHERE uniquename = 'FBcl0000001' AND is_obsolete=FALSE;
Feature type 
SELECT cvt.NAME
  FROM feature f, cvterm cvt
  WHERE f.uniquename = 'FBcl0000001' AND f.is_obsolete=FALSE AND
    f.type_id=cvt.cvterm_id;
Created/Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature
  WHERE uniquename = 'FBcl0000001' AND is_obsolete=FALSE;
(Computed) gene 
SELECT DISTINCT g.name
  FROM feature c, feature evidence, feature tr, feature g, feature_relationship fr1,
     feature_relationship fr2, feature_relationship fr3, cvterm evidence_type, cvterm fr2_type, cvterm g_type
  WHERE c.uniquename='FBcl0000001' AND evidence_type.name IN ('cDNA','EST') AND
    fr2_type.name='supports' AND g_type.name = 'gene' AND
    c.feature_id=fr1.object_id AND fr1.subject_id=evidence.feature_id AND
    evidence.type_id=evidence_type.cvterm_id AND evidence.feature_id=fr2.subject_id AND
    fr2.type_id=fr2_type.cvterm_id AND fr2.object_id=tr.feature_id AND
    tr.feature_id=fr3.subject_id AND fr3.object_id=g.feature_id AND
    g.type_id=g_type.cvterm_id;
Collection Status 
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 = 'status' AND f.uniquename = 'FBcl0000003';
Known Problems 
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 = 'problem' AND f.uniquename = 'FBcl0000248';
Library 
SELECT l.name
  FROM feature f, library_feature lf, library l
  WHERE f.uniquename='FBcl0000001' AND f.feature_id=lf.feature_id AND
    lf.library_id=l.library_id;
Strain 
SELECT lp.VALUE
  FROM feature f, library_feature lf, library l, libraryprop lp, cvterm lp_type
  WHERE f.uniquename='FBcl0394020' AND lp_type.name='strain' AND
    f.feature_id=lf.feature_id AND lf.library_id=l.library_id AND
    l.library_id=lp.library_id AND lp.type_id=lp_type.cvterm_id;
Stage 
SELECT lbp.VALUE FROM feature f, library_feature lf, library l, libraryprop lbp, cvterm cvt WHERE f.uniquename='FBcl0000001' AND
  cvt.name='stage' AND f.feature_id=lf.feature_id AND  lf.library_id=l.library_id AND
  l.library_id=lbp.library_id AND lbp.type_id=cvt.cvterm_id;
Tissue Source 
SELECT DISTINCT cvt.name FROM feature f, library_feature lf, library l, library_cvterm lcvt, cvterm cvt, cv
  WHERE f.uniquename='FBcl0000001' AND cv.name IN ('FlyBase anatomy CV','cellular_component') AND
  f.feature_id=lf.feature_id AND lf.library_id=l.library_id AND l.library_id=lcvt.library_id AND lcvt.cvterm_id=cvt.cvterm_id;
Vector 
SELECT DISTINCT s.synonym_sgml
  FROM feature cl, feature vector, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm vector_type
  WHERE cl.uniquename = 'FBcl0000001' AND fs.is_current = TRUE AND
    fs.is_internal = FALSE AND fs_type.NAME = 'symbol' AND
    vector_type.name='engineered_construct' AND cl.feature_id=fr.object_id AND
    fr.subject_id=vector.feature_id AND vector.type_id=vector_type.cvterm_id AND
    vector.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id;
INSERT SEQUENCE DATA
Full length sequence
Total bases 
SELECT fls.seqlen
  FROM feature cl, feature fls, feature_relationship fr, cvterm fls_type
  WHERE cl.uniquename='FBcl0000003' AND fls_type.name IN ('cDNA','BAC_cloned_genomic_insert') AND
    cl.feature_id=fr.object_id AND fr.subject_id=fls.feature_id AND
    fls.type_id=fls_type.cvterm_id;
Submission date
Genbank 
SELECT dbx.accession
  FROM feature cl, feature fls, feature_relationship fr, cvterm fls_type, feature_dbxref fdbx,
     dbxref dbx, db
  WHERE cl.uniquename='FBcl0000003' AND fls_type.name IN ('cDNA','BAC_cloned_genomic_insert') AND
    db.name='GB' AND fdbx.is_current=TRUE AND
    cl.feature_id=fr.object_id AND fr.subject_id=fls.feature_id AND
    fls.type_id=fls_type.cvterm_id AND fls.feature_id=fdbx.feature_id AND
    fdbx.dbxref_id=dbx.dbxref_id AND dbx.db_id=db.db_id;
sequence data 
SELECT fls.residues
  FROM feature cl, feature fls, feature_relationship fr, cvterm fls_type
  WHERE cl.uniquename='FBcl0000003' AND fls_type.name IN ('cDNA','BAC_cloned_genomic_insert') AND
    cl.feature_id=fr.object_id AND fr.subject_id=fls.feature_id AND
    fls.type_id=fls_type.cvterm_id;
5prime sequence
Total bases 
SELECT fiveps.seqlen
  FROM feature cl, feature fiveps, feature_relationship fr, cvterm fiveps_type
  WHERE cl.uniquename='FBcl0000001' AND fiveps_type.name='EST' AND
    fiveps.is_obsolete=FALSE AND (fiveps.name LIKE '%5prime' OR
    fiveps.name LIKE '%contig1') AND cl.feature_id=fr.object_id AND
    fr.subject_id=fiveps.feature_id AND fiveps.type_id=fiveps_type.cvterm_id;
High quality bases
Submission date
dbEST
Genbank 
SELECT DISTINCT dbx.accession
  FROM feature cl, feature fiveps, feature_relationship fr, cvterm fiveps_type, feature_dbxref fdbx,
     dbxref dbx, db
  WHERE cl.uniquename='FBcl0000001' AND fiveps_type.name='EST' AND
    db.name='GB' AND fdbx.is_current=TRUE AND
    fiveps.is_obsolete=FALSE AND (fiveps.name LIKE '%5prime' OR
    fiveps.name LIKE '%contig1') AND cl.feature_id=fr.object_id AND
    fr.subject_id=fiveps.feature_id AND fiveps.type_id=fiveps_type.cvterm_id AND
    fiveps.feature_id=fdbx.feature_id AND fdbx.dbxref_id=dbx.dbxref_id AND
    dbx.db_id=db.db_id;
5prime
sequence data		 
SELECT fiveps.residues
  FROM feature cl, feature fiveps, feature_relationship fr, cvterm fiveps_type
  WHERE cl.uniquename='FBcl0000001' AND fiveps_type.name='EST' AND
    fiveps.is_obsolete=FALSE AND (fiveps.name LIKE '%5prime' OR
    fiveps.name LIKE '%contig1') AND cl.feature_id=fr.object_id AND
    fr.subject_id=fiveps.feature_id AND fiveps.type_id=fiveps_type.cvterm_id;
3prime sequence
Total bases 
SELECT threeps.seqlen
  FROM feature cl, feature threeps, feature_relationship fr, cvterm threeps_type
  WHERE cl.uniquename='FBcl0000002' AND threeps_type.name='EST' AND
    threeps.is_obsolete=FALSE AND (threeps.name LIKE '%3prime' OR
    threeps.name LIKE '%contig2') AND cl.feature_id=fr.object_id AND
    fr.subject_id=threeps.feature_id AND threeps.type_id=threeps_type.cvterm_id;
High quality bases
Submission date
dbEST
Genbank 
SELECT DISTINCT dbx.accession
  FROM feature cl, feature threeps, feature_relationship fr, cvterm threeps_type, feature_dbxref fdbx,
     dbxref dbx, db
  WHERE cl.uniquename='FBcl0000002' AND threeps_type.name='EST' AND
    db.name='GB' AND fdbx.is_current=TRUE AND
    threeps.is_obsolete=FALSE AND (threeps.name LIKE '%3prime' OR
    threeps.name LIKE '%contig2') AND cl.feature_id=fr.object_id AND
    fr.subject_id=threeps.feature_id AND threeps.type_id=threeps_type.cvterm_id AND
    threeps.feature_id=fdbx.feature_id AND fdbx.dbxref_id=dbx.dbxref_id AND
    dbx.db_id=db.db_id;
3prime
sequence data		 
SELECT threeps.residues
  FROM feature cl, feature threeps, feature_relationship fr, cvterm threeps_type
  WHERE cl.uniquename='FBcl0000002' AND threeps_type.name='EST' AND
    threeps.is_obsolete=FALSE AND (threeps.name LIKE '%3prime' OR
    threeps.name LIKE '%contig2') AND cl.feature_id=fr.object_id AND
    fr.subject_id=threeps.feature_id AND threeps.type_id=threeps_type.cvterm_id;
Partial sequence
Total bases 
SELECT partial.seqlen
  FROM feature cl, feature partial, feature_relationship fr, cvterm partial_type
  WHERE cl.uniquename='FBcl0000111' AND partial_type.name='EST' AND
    partial.is_obsolete=FALSE AND partial.name !~ '(5prime|contig1|3prime|contig2)$' AND
    cl.feature_id=fr.object_id AND fr.subject_id=partial.feature_id AND
    partial.type_id=partial_type.cvterm_id;
High quality bases
Submission date
dbEST
Genbank 
SELECT DISTINCT dbx.accession
  FROM feature cl, feature partial, feature_relationship fr, cvterm partial_type, feature_dbxref fdbx,
     dbxref dbx, db
  WHERE cl.uniquename='FBcl0000111' AND partial_type.name='EST' AND
    db.name='GB' AND fdbx.is_current=TRUE AND
    partial.is_obsolete=FALSE AND partial.name !~ '(5prime|contig1|3prime|contig2)$' AND
    cl.feature_id=fr.object_id AND fr.subject_id=partial.feature_id AND
    partial.type_id=partial_type.cvterm_id AND partial.feature_id=fdbx.feature_id AND
    fdbx.dbxref_id=dbx.dbxref_id AND dbx.db_id=db.db_id;
Partial
sequence data		 
SELECT partial.residues
  FROM feature cl, feature partial, feature_relationship fr, cvterm partial_type
  WHERE cl.uniquename='FBcl0000111' AND partial_type.name='EST' AND
    partial.is_obsolete=FALSE AND partial.name !~ '(5prime|contig1|3prime|contig2)$' AND
    cl.feature_id=fr.object_id AND fr.subject_id=partial.feature_id AND
    partial.type_id=partial_type.cvterm_id;
SEQUENCING PRIMERS
Forward
Reverse
VECTOR INFORMATION
Vector map
& Polylinker
COMMENTS
 
VECTOR SEQUENCE
 
VECTOR SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym
SECONDARY FLYBASE IDs
 
VECTOR REFERENCES
Research paper
Supplementary
material
Review
Abstract
Other
LIBRARY INFORMATION
Comments 
SELECT lp.VALUE
  FROM feature f, library_feature lf, library l, libraryprop lp, cvterm lp_type
  WHERE f.uniquename='FBcl0000111' AND lp_type.name='comment' AND
    f.feature_id=lf.feature_id AND lf.library_id=l.library_id AND
    l.library_id=lp.library_id AND lp.type_id=lp_type.cvterm_id;
LIBRARY SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
SELECT DISTINCT s.synonym_sgml
  FROM feature f, library_feature lf, library l, library_synonym ls, synonym s
  WHERE f.uniquename='FBcl0000111' AND f.feature_id=lf.feature_id AND
    lf.library_id=l.library_id AND l.library_id=ls.library_id AND
    ls.synonym_id=s.synonym_id;
SECONDARY FLYBASE IDs
 
LIBRARY REFERENCES
Research paper 
SELECT p.uniquename
  FROM feature f, library_feature lf, library l, library_pub lp, pub p
  WHERE f.uniquename='FBcl0000111' AND f.feature_id=lf.feature_id AND
    lf.library_id=l.library_id AND l.library_id=lp.library_id AND
    lp.pub_id=p.pub_id;
Supplementary
material		 same as above use pub type to distinguish
Review same as above use pub type to distinguish
Abstract same as above use pub type to distinguish
Other same as above use pub type to distinguish
COMMENTS
  
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 = 'comment' AND f.uniquename = 'FBcl0000001';
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
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 = 'FBcl0000001' AND
    cvt.name='symbol';
Name Synonym 
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 = 'FBcl0000001' AND
    cvt.name='fullname';
SECONDARY FLYBASE IDs
  
SELECT dbxref.accession
  FROM dbxref, feature_dbxref fd, feature f, db
  WHERE f.feature_id=fd.feature_id AND dbxref.dbxref_id=fd.dbxref_id AND
    fd.is_current='f' AND dbxref.db_id=db.db_id AND
    db.name='FlyBase' AND f.uniquename='FBcl0006217' AND
    f.is_obsolete='f';
REFERENCES
Research paper 
SELECT p.uniquename, p.miniref, p.title
  FROM feature f, pub p, feature_pub fp
  WHERE f.uniquename = 'FBcl0006217' AND f.feature_id=fp.feature_id AND
    fp.pub_id=p.pub_id;
Supplementary
material		 same as above use pub type to distinguish
Review same as above use pub type to distinguish
Abstract same as above use pub type to distinguish
Other same as above use pub type to distinguish

FlyBase Stock Report

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

GENERAL INFORMATION
Collection 
SELECT sc.uniquename
  FROM stock s, stockcollection_stock scs, stockcollection sc
  WHERE s.uniquename='FBst0000001' AND s.stock_id=scs.stock_id AND
    scs.stockcollection_id=sc.stockcollection_id;
Collection Type 
SELECT sc_type.name
  FROM stock s, stockcollection_stock scs, stockcollection sc, cvterm sc_type
  WHERE s.uniquename='FBst0000001' AND s.stock_id=scs.stock_id AND
    scs.stockcollection_id=sc.stockcollection_id AND sc.type_id=sc_type.cvterm_id;
Stock Number 
SELECT name
  FROM stock s
  WHERE s.uniquename='FBst0000001';
Species 
SELECT genus,species
  FROM stock s, organism o
  WHERE s.uniquename='FBst0000001' AND s.organism_id=o.organism_id;
FlyBase ID 
SELECT uniquename
  FROM stock s
  WHERE s.uniquename='FBst0000001';
Created\ Updated
Stock List Description 
SELECT description
  FROM stock s
  WHERE s.uniquename='FBst0000001';
FlyBase Genotype 
SELECT genotype.name
  FROM stock s, stock_genotype sg, genotype
  WHERE s.uniquename='FBst0000001' AND s.stock_id=sg.stock_id AND
    sg.genotype_id=genotype.genotype_id;
State of Stock 
SELECT s_type.name
  FROM stock s, cvterm s_type
  WHERE s.uniquename='FBst0000001' AND s.type_id=s_type.cvterm_id;
Classification 
SELECT cvt.name
  FROM stock s, stock_cvterm scvt, cvterm cvt
  WHERE s.uniquename='FBst0000001' AND s.stock_id=scvt.stock_id AND
    scvt.cvterm_id=cvt.cvterm_id;
Images
Expression
To Request Stock 
SELECT scp.VALUE
  FROM stock s, stockcollection_stock scs, stockcollection sc, stockcollectionprop scp, cvterm scp_type
  WHERE s.uniquename='FBst0000001' AND scp_type.name IN ('request_text', 'homepage_url',
       'order_url', 'request_text',
       'email_order_contact_name') AND
    s.stock_id=scs.stock_id AND scs.stockcollection_id=sc.stockcollection_id AND
    sc.stockcollection_id=scp.stockcollection_id AND scp.type_id=scp_type.cvterm_id;

FlyBase Aberration Report

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

GENERAL INFORMATION [unattributed]
Symbol 
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 = 'chromosome_structure_variation' 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 = 'FBab0000001' GROUP BY s.synonym_sgml;
Species 
SELECT o.genus, o.species
  FROM feature f, organism o
  WHERE f.uniquename='FBab0000001' AND f.organism_id=o.organism_id;
Name
FlyBase ID 
SELECT uniquename
  FROM feature f
  WHERE f.uniquename='FBab0000001';
Feature type 
SELECT c.name
  FROM feature f, cvterm c, feature_cvterm fc, feature_cvtermprop cp, cvterm c1,
     cvterm c2, pub p
  WHERE f.type_id = c1.cvterm_id AND c1.name = 'chromosome_structure_variation' AND
    f.feature_id = fc.feature_id AND fc.cvterm_id = c.cvterm_id AND
    cp.feature_cvterm_id = fc.feature_cvterm_id AND cp.type_id = c2.cvterm_id AND
    c2.name = 'wt_class' AND fc.pub_id = p.pub_id AND
    p.uniquename LIKE 'FBrf%' AND f.uniquename='FBab0000001';
Created/ Updated 
SELECT timeaccessioned,timelastmodified
  FROM feature f
  WHERE f.uniquename='FBab0000001';
[additional unattributed data]
Formalized
genetic data		 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBab0000001' AND fp_type.name = 'computed_map' AND
    f.feature_id = fp.feature_id AND fp.type_id = fp_type.cvterm_id;
Sequence coordinates 
SELECT s.uniquename, fmin, fmax, miniref AS citation
  FROM feature f, feature b, feature_relationship fr, cvterm cvt1, cvterm cvt2,
     featureloc fl, feature s, featureloc_pub flp, pub p
  WHERE f.uniquename = 'FBab0030010' AND f.feature_id = fr.object_id AND
    fr.type_id = cvt1.cvterm_id AND cvt1.name = 'break_of' AND
    fr.subject_id = b.feature_id AND b.type_id = cvt2.cvterm_id AND
    cvt2.name = 'breakpoint' AND b.feature_id = fl.feature_id AND
    fl.srcfeature_id = s.feature_id AND fl.featureloc_id = flp.featureloc_id AND
    flp.pub_id = p.pub_id;
Deleted segment 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBab0000001' AND fp_type.name = 'deleted_segment' AND
    f.feature_id = fp.feature_id AND fp.type_id = fp_type.cvterm_id;
Duplicated segment
Computed
Breakpoints include		 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename LIKE 'FBab%' AND f.organism_id = 1 AND
    fp_type.name = 'derived_computed_breakpoint' AND f.feature_id = fp.feature_id AND
    fp.type_id = fp_type.cvterm_id UNION
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename LIKE 'FBab%' AND f.organism_id != 1 AND
    fp_type.name = 'non_Dmel_location' AND f.feature_id = fp.feature_id AND
    fp.type_id = fp_type.cvterm_id;
Breakpoints Inherited
NATURE OF THE ABERRATION
Cytological Order 
SELECT fp.VALUE , miniref AS citation
  FROM feature f, featureprop fp, cvterm fp_type, featureprop_pub fpp, pub p
  WHERE f.uniquename='FBab0044894' AND fp_type.name = 'new_order' AND
    f.feature_id = fp.feature_id AND fp.type_id = fp_type.cvterm_id AND
    fp.featureprop_id = fpp.featureprop_id AND fpp.pub_id = p.pub_id;
Progenitor 
SELECT s.synonym_sgml
  FROM feature ab, feature prog, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ab.uniquename = 'FBab0030010' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='progenitor' AND ab.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=prog.feature_id AND
    prog.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Mutagen 
SELECT c.name
  FROM feature f, cvterm c, feature_cvterm fc, cvtermprop cp, cvterm c1,
     pub p
  WHERE f.type_id = c1.cvterm_id AND c1.name = 'chromosome_structure_variation' AND
    f.feature_id = fc.feature_id AND fc.cvterm_id = c.cvterm_id AND
    c.cvterm_id = cp.cvterm_id AND cp.VALUE = 'origin_of_mutation' AND
    fc.pub_id = p.pub_id AND p.uniquename LIKE 'FBrf%' AND
    f.uniquename='FBab0030010';
Class of aberration
(relative to progenitor)		 
SELECT c.name
  FROM feature f, cvterm c, feature_cvterm fc, feature_cvtermprop cp, cvterm c1,
     cvterm c2, pub p
  WHERE f.type_id = c1.cvterm_id AND c1.name = 'chromosome_structure_variation' AND
    f.feature_id = fc.feature_id AND fc.cvterm_id = c.cvterm_id AND
    cp.feature_cvterm_id = fc.feature_cvterm_id AND cp.type_id = c2.cvterm_id AND
    c2.name = 'aberr_class' AND fc.pub_id = p.pub_id AND
    p.uniquename LIKE 'FBrf%' AND f.uniquename='FBab0030010';
Breakpoints 
SELECT fp.VALUE, miniref AS citation
  FROM feature f, featureprop fp, cvterm fp_type , featureprop_pun fpp, pub p
  WHERE f.uniquename LIKE 'FBab%' AND f.organism_id = 1 AND
    fp_type.name = 'derived_attributed_breakpoint' AND f.feature_id = fp.feature_id AND
    fp.type_id = fp_type.cvterm_id AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = p.pub_id AND p.uniquename lik 'FBrf%' UNION
SELECT fp.VALUE, miniref AS citation
  FROM feature f, featureprop fp, cvterm fp_type , featureprop_pun fpp, pub p
  WHERE f.uniquename LIKE 'FBab%' AND f.organism_id != 1 AND
    fp_type.name = 'non_Dmel_location' AND f.feature_id = fp.feature_id AND
    fp.type_id = fp_type.cvterm_id AND fp.featureprop_id = fpp.featureprop_id AND
    fpp.pub_id = p.pub_id AND p.uniquename lik 'FBrf%';
Causes alleles
Carries alleles
Transposon Insertions
Genetic mapping information
Comments
COMMENTS ON CYTOLOGY
 
MOLECULARLY MAPPED BREAKPOINTS
Symbol
Location
Additional Notes
References
SEQUENCE CROSSREFERENCES
DNA sequence
Protein sequence
Name
GENE DELETION & DUPLICATION DATA
GENES DELETED/DISRUPTED
COMPLEMENTATION DATA
Completely deleted/ disrupted
Partially deleted/ disrupted
MOLECULAR DATA
Completely deleted
Partially deleted
GENES NOT DELETED/DISRUPTED
COMPLEMENTATION DATA
 
MOLECULAR DATA
 
GENES DUPLICATED
COMPLEMENTATION DATA
Completely duplicated
Partially duplicated
MOLECULAR DATA
Completely duplicated
Partially duplicated
GENES NOT DUPLICATED
COMPLEMENTATION DATA
 
MOLECULAR DATA
 
RELATED COMMENTS
 
PHENOTYPIC DATA
In combination with
other aberrations
NOT in combination with
other aberrations
POSITION EFFECT VARIEGATION DATA
Genes
subject to PEV
Genes showing
dominant PEV
Genes NOT
subject to PEV
STOCKS
Bloomington
Kyoto
Szeged
Tucson
Carpenter
Garcia-Bellido
Harvard
Nusslein-Volhard
Saxton
Notes on availability
NOTES ON ORIGIN
Discoverer
 
BALANCER/ GENOTYPE VARIANTS OF THE ABERRATION
 
SEPARABLE COMPONENTS
 
OTHER COMMENTS
 
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym
Name Synonym
SECONDARY FLYBASE IDs
 
REFERENCES
Research paper
Supplementary
material
Review
Abstract
Other

FlyBase Balancer Report

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

GENERAL INFORMATION [unattributed]
Symbol 
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 = 'single balancer' 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 = 'FBba0000005' GROUP BY s.synonym_sgml;
Species 
SELECT o.genus, o.species
  FROM feature f, organism o
  WHERE f.uniquename='FBba0000005' AND f.organism_id=o.organism_id;
FlyBase ID 
SELECT uniquename
  FROM feature f
  WHERE f.uniquename='FBba0000005';
Created/ Updated 
SELECT timeaccessioned,timelastmodified
  FROM feature f
  WHERE f.uniquename='FBba0000005';
Short genotype 
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 = 'single balancer' 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 = 'nickname' AND
    f.is_obsolete = 'f' AND f.uniquename = 'FBba0000005' GROUP BY s.synonym_sgml;
Parent aberration 
SELECT s.synonym_sgml
  FROM feature ba, feature pab, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000005' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='variant_of' AND ba.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=pab.feature_id AND
    pab.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Parent Cytology 
SELECT fp.VALUE
  FROM feature ba, feature pab, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE ba.uniquename = 'FBba0000005' AND fp_type.NAME='new_order' AND
    fr_type.NAME='variant_of' AND ba.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=pab.feature_id AND
    pab.feature_id = fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Progenitor 
SELECT s.synonym_sgml
  FROM feature ba, feature prog, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000005' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='progenitor' AND ba.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=prog.feature_id AND
    prog.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
(Described as Balancer) 
SELECT fp.VALUE, p.miniref
  FROM feature ba, featureprop fp, featureprop_pub fpp, pub p, cvterm fp_type
  WHERE ba.uniquename='FBba0000005' AND fp_type.NAME='balancer_status' AND
    p.uniquename ~ '^FBrf[0-9]+$' AND ba.feature_id=fp.feature_id AND
    fp.type_id=fp_type.cvterm_id AND fp.featureprop_id=fpp.featureprop_id AND
    fpp.pub_id=p.pub_id;
Alleles 
SELECT s.synonym_sgml
  FROM feature ba, feature al, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000005' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='carried_on' AND ba.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Transposon Insertions 
SELECT s.synonym_sgml
  FROM feature ba, feature ti, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000473' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='associated_with' AND ba.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=ti.feature_id AND
    ti.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Discoverer 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000006' AND fp_type.NAME='discoverer' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
STOCKS
Bloomington 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Bloomington' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Kyoto 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000002' AND fp_type.NAME='derived_stock_Bloomington' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Szeged 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000003' AND fp_type.NAME='derived_stock_Szeged' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Tucson 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Tucson' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Carpenter 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Carpenter' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Garcia-Bellido 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Garcia-Bellido' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Harvard 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Harvard' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Nusslein-Volhard 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Nusslein-Volhard' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Saxton 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='derived_stock_Saxton' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Notes on availability 
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000001' AND fp_type.NAME='availability' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
GENETIC DATA
Genes subject to position-effect variegation 
SELECT s.synonym_sgml
  FROM feature ba, feature g, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000116' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='rec_position_effect' AND ba.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=g.feature_id AND
    g.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Genes showing dominant position-effect variegation 
SELECT s.synonym_sgml
  FROM feature ba, feature g, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000119' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='dom_position_effect' AND ba.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=g.feature_id AND
    g.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
Genes NOT subject to position-effect variegation 
SELECT s.synonym_sgml
  FROM feature ba, feature g, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE ba.uniquename = 'FBba0000119' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='no_position_effect' AND ba.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=g.feature_id AND
    g.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id GROUP BY s.synonym_sgml;
OTHER INFORMATION
  
SELECT fp.VALUE
  FROM feature ba, featureprop fp, cvterm fp_type
  WHERE ba.uniquename ='FBba0000002' AND fp_type.NAME='misc' AND
    ba.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
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 = 'FBba0000116' AND
    cvt.name='symbol';
Name Synonym 
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 = 'FBba0000116' AND
    cvt.name='fullname';
SECONDARY FLYBASE IDs
  
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 = 'FBba0000005';
REFERENCES
Research paper 
SELECT p.uniquename, p.miniref, p.title
  FROM feature f, pub p, feature_pub fp
  WHERE f.uniquename = 'FBba0000005' AND f.feature_id=fp.feature_id AND
    fp.pub_id=p.pub_id;
Supplementary
material		 Same as above use pub type to distinguish
Review Same as above use pub type to distinguish
Abstract Same as above use pub type to distinguish
Other Same as above use pub type to distinguish

FlyBase Reference Report

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

REFERENCE
Citation 
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';
FlyBase ID 
SELECT uniquename
  FROM pub
  WHERE uniquename='FBrf0126983';
Type of publication
Offprint 
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;
EXTERNAL CROSSREFERENCES
PubMed ID 
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;
PubMed Abstract 
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;
(Conference Abstract) 
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;
Biosis 
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;
Zoological record 
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;
ASSOCIATED INFORMATION
Comments 
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;
Text of personal
communication		 
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;
Associated files 
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;
RELATED PUBLICATIONS
Research paper 
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;
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 
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;
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 
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;
Language of publication 
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;
Additional Language(s)
of abstract		 
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;
ISBN 
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;
Place of Publication 
SELECT pubplace
  FROM pub
  WHERE uniquename ='FBrf0075564';
PUBLISHED IN
Abbreviation 
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;
Title 
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;
Authors 
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;
Volume Range 
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;
Year Range 
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;
Page Range 
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;
Publisher 
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;
Place of publication 
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;
Language of publication 
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;
ISBN or ISSN 
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;
CODEN 
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;
Associated files 
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;
DATA FROM REFERENCE
Genes
  
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;
Transcripts
  
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;
Polypeptides
  
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;
Alleles
  
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;
Constructs
  
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;
Insertions
  
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;
Aberations
  
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;
Balancers
  
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;
Images
 
Clones
  
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;
Natural Transposons
  
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;

FlyBase Transgenic Construct Report

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

GENERAL INFORMATION [unattributed]
Symbol 
SELECT DISTINCT s.synonym_sgml
  FROM feature f, feature_synonym fs, synonym s, cvterm cvt
  WHERE 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 = cvt.cvterm_id AND cvt.name = 'symbol' AND
    f.is_obsolete = 'f' AND f.uniquename = 'FBtp0000001';
FlyBase ID 
SELECT f.uniquename
  FROM feature f
  WHERE f.uniquename = 'FBtp0000001';
Feature type 
SELECT cvt.name
  FROM feature f, cvterm cvt
  WHERE f.uniquename = 'FBtp0000001' AND f.type_id=cvt.cvterm_id;
Created / Updated 
SELECT f.timeaccessioned,f.timelastmodified
  FROM feature f
  WHERE f.uniquename = 'FBtp0000001';
[additional unattributed data]
Size 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0018202' AND (fp_type.name='kb_length' OR
    fp_type.name='compiled_kb_length') AND f.feature_id=fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Expression data 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0036669' AND fp_type.name='derived_expression_data' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Localized function
Associated insertions 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature ins, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE tp.uniquename='FBtp0036624' AND fs.is_current='t' AND
    fs.is_internal='f' AND fs_type.NAME='symbol' AND
    fr_type.NAME='producedby' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=ins.feature_id AND
    ins.feature_id=fs.feature_id AND fs.synonym_id=s.synonym_id AND
    s.type_id=fs_type.cvterm_id;
Molecular map
DESCRIPTION & USES
Description
Species 
SELECT o.genus,o.species
  FROM feature f, organism o
  WHERE f.uniquename='FBtp0000001' AND f.organism_id=o.organism_id;
CV term 
SELECT cvt.name
  FROM feature f, feature_cvterm fcvt, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_description' AND
    f.feature_id=fcvt.feature_id AND fcvt.cvterm_id=cvt.cvterm_id AND
    cvt.cv_id=cv.cv_id;
Qualifiers and info 
SELECT fcvtp.VALUE
  FROM feature f, feature_cvterm fcvt, feature_cvtermprop fcvtp, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_description' AND
    fcvt.feature_cvterm_id=fcvtp.feature_cvterm_id AND f.feature_id=fcvt.feature_id AND
    fcvt.cvterm_id=cvt.cvterm_id AND cvt.cv_id=cv.cv_id;
Reference 
SELECT p.uniquename
  FROM feature f, feature_cvterm fcvt, pub p, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_description' AND
    fcvt.pub_id=p.pub_id AND f.feature_id=fcvt.feature_id AND
    fcvt.cvterm_id=cvt.cvterm_id AND cvt.cv_id=cv.cv_id;
Uses
CV term 
SELECT cvt.name
  FROM feature f, feature_cvterm fcvt, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_uses' AND
    f.feature_id=fcvt.feature_id AND fcvt.cvterm_id=cvt.cvterm_id AND
    cvt.cv_id=cv.cv_id;
Qualifier & info 
SELECT fcvtp.VALUE
  FROM feature f, feature_cvterm fcvt, feature_cvtermprop fcvtp, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_uses' AND
    fcvt.feature_cvterm_id=fcvtp.feature_cvterm_id AND f.feature_id=fcvt.feature_id AND
    fcvt.cvterm_id=cvt.cvterm_id AND cvt.cv_id=cv.cv_id;
Reference 
SELECT p.uniquename
  FROM feature f, feature_cvterm fcvt, pub p, cvterm cvt, cv
  WHERE f.uniquename='FBtp0022777' AND cv.name='transgene_uses' AND
    fcvt.pub_id=p.pub_id AND f.feature_id=fcvt.feature_id AND
    fcvt.cvterm_id=cvt.cvterm_id AND cvt.cv_id=cv.cv_id;
Cloning Sites
Location
Restriction sites
Reference
SEQUENCE DATA
Sequence (FB)
Sequence
Alternative sequence
ASSOCIATED SEQUENCE DATA
DDBJ /EMBL /GenBank
DNA sequence 
SELECT arm.name, fl.fmin+1, fl.fmax
  FROM feature f, featureloc fl, feature arm
  WHERE f.uniquename='FBtp0000078' AND f.feature_id=fl.feature_id AND
    fl.srcfeature_id=arm.feature_id;
Comments 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0036625' AND fp_type.name='comment' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
SEGMENTS & SIZE
Total Size 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0018202' AND (fp_type.name='kb_length' OR
    fp_type.name='compiled_kb_length') AND f.feature_id=fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Left end 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0000078' AND fp_type.name='original_left_end' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Right end 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0000078' AND fp_type.name='original_right_end' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Segments
Number 
SELECT fr.rank
  FROM feature f, feature seg, feature_relationship fr, cvterm fr_type
  WHERE f.uniquename='FBtp0000078' AND fr_type.name='partof' AND
    f.feature_id=fr.object_id AND fr.subject_id=seg.feature_id AND
    fr.type_id=fr_type.cvterm_id;
Orientation 
SELECT frp.VALUE
  FROM feature f, feature seg, feature_relationship fr, cvterm fr_type, cvterm frp_type,
     feature_relationshipprop frp
  WHERE f.uniquename='FBtp0000078' AND fr_type.name='partof' AND
    frp_type.name='relative_orientation' AND f.feature_id=fr.object_id AND
    fr.subject_id=seg.feature_id AND fr.type_id=fr_type.cvterm_id AND
    fr.feature_relationship_id=frp.feature_relationship_id AND frp.type_id=frp_type.cvterm_id;
Symbol 
SELECT DISTINCT s.synonym_sgml
  FROM feature f, feature seg, feature_relationship fr, cvterm fr_type, feature_synonym fs,
     synonym s, cvterm s_type
  WHERE f.uniquename='FBtp0000078' AND fr_type.name='partof' AND
    fs.is_current=TRUE AND s_type.name='symbol' AND
    f.feature_id=fr.object_id AND fr.subject_id=seg.feature_id AND
    fr.type_id=fr_type.cvterm_id AND seg.feature_id=fs.feature_id AND
    fs.synonym_id=s.synonym_id AND s.type_id=s_type.cvterm_id;
Reference 
SELECT p.uniquename
  FROM feature f, feature seg, feature_relationship fr, cvterm fr_type, feature_relationship_pub frp,
     pub p
  WHERE f.uniquename='FBtp0000078' AND fr_type.name='partof' AND
    f.feature_id=fr.object_id AND fr.subject_id=seg.feature_id AND
    fr.type_id=fr_type.cvterm_id AND fr.feature_relationship_id=frp.feature_relationship_id AND
    frp.pub_id=p.pub_id;
FEATURES
CV term 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0022777' AND fp_type.name='derived_transgene_features' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Qualifiers & info 
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0022777' AND fp_type.name='derived_transgene_features' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Reference 
SELECT p.uniquename
  FROM feature f, featureprop fp, featureprop_pub fpp, pub p, cvterm fp_type
  WHERE f.uniquename='FBtp0022777' AND fp_type.name='derived_transgene_features' AND
    f.feature_id=fp.feature_id AND fp.type_id=fp_type.cvterm_id AND
    fp.featureprop_id=fpp.featureprop_id AND fpp.pub_id=p.pub_id;
COMPONENT ALLELES
Allele 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature al, feature_relationship fr, feature_synonym fs, synonym s,
     cvterm fs_type, cvterm fr_type
  WHERE tp.uniquename = 'FBtp0022777' AND fs.is_current = 't' AND
    fs.is_internal = 'f' AND fs_type.NAME = 'symbol' AND
    fr_type.NAME='derived_tp_assoc_alleles' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fs.feature_id AND fs.synonym_id = s.synonym_id AND
    s.type_id = fs_type.cvterm_id;
Reference(s) 
SELECT p.uniquename
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, feature_relationship_pub frp,
     pub p
  WHERE tp.uniquename = 'FBtp0022777' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND fr.feature_relationship_id=frp.feature_relationship_id AND
    frp.pub_id=p.pub_id;
Molecular data 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0022777' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    fp_type.name='molecular_info' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Phenotypic class 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0022777' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    fp_type.name='derived_pheno_class' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Phenotype manifest in 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0022777' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    fp_type.name='derived_pheno_manifest' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fp.feature_id AND fp.type_id=fp_type.cvterm_id;
Other information 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0022777' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    fp_type.name='derived_allele_phendesc' AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=al.feature_id AND
    al.feature_id = fp.feature_id AND fp.type_id=fp_type.cvterm_id;
EXPRESSION DATA
[Transcript]
Reporter expression
Stage 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0036669' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_cv' OR fp_type.name='derived_polypeptide_bodypart_expression_cv') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Tissue/Position 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0036669' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_cv' OR fp_type.name='derived_polypeptide_bodypart_expression_cv') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Reference 
SELECT DISTINCT p.uniquename
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     featureprop_pub fpp, pub p, cvterm fp_type
  WHERE tp.uniquename = 'FBtp0036669' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_cv' OR fp_type.name='derived_polypeptide_bodypart_expression_cv') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id AND fp.featureprop_id=fpp.featureprop_id AND
    fpp.pub_id=p.pub_id;
Additional Information
Statement 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0001557' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_text' OR fp_type.name='derived_polypeptide_bodypart_expression_text') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Reference 
SELECT DISTINCT p.uniquename
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     featureprop_pub fpp, pub p, cvterm fp_type
  WHERE tp.uniquename = 'FBtp0001557' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_text' OR fp_type.name='derived_polypeptide_bodypart_expression_text') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id AND fp.featureprop_id=fpp.featureprop_id AND
    fpp.pub_id=p.pub_id;
Assay mode 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0036669' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_cv' OR fp_type.name='derived_polypeptide_bodypart_expression_cv') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Marker for 
SELECT fp.VALUE
  FROM feature tp, feature al, feature_relationship fr, cvterm fr_type, featureprop fp,
     cvterm fp_type
  WHERE tp.uniquename = 'FBtp0000365' AND fr_type.NAME='derived_tp_assoc_alleles' AND
    (fp_type.name='derived_transcript_bodypart_expression_marker' OR fp_type.name='derived_polypeptide_bodypart_expression_marker') AND
    tp.feature_id=fr.object_id AND fr.type_id=fr_type.cvterm_id AND
    fr.subject_id=al.feature_id AND al.feature_id = fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
Reflects expression of 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature al, feature tr, feature gn, 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
  WHERE tp.uniquename = 'FBtp0000362' AND fr1_type.NAME='derived_tp_assoc_alleles' AND
    fr2_type.name='partof' AND fr3_type.name='attributed_as_expression_of' AND
    fs.is_current=TRUE AND fs.is_internal=FALSE AND
    s_type.name='symbol' AND tp.feature_id=fr1.object_id AND
    fr1.type_id=fr1_type.cvterm_id AND fr1.subject_id=al.feature_id AND
    al.feature_id = fr2.object_id AND fr2.type_id=fr2_type.cvterm_id AND
    fr2.subject_id=tr.feature_id AND tr.feature_id=fr3.subject_id AND
    fr3.type_id=fr3_type.cvterm_id AND fr3.object_id=gn.feature_id AND
    gn.feature_id=fs.feature_id AND fs.synonym_id=s.synonym_id AND
    s.type_id=s_type.cvterm_id;
PROGENITORS & DESCENDANTS
Progenitor(s) 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature prog, feature_relationship fr, cvterm fr_type, feature_synonym fs,
     synonym s, cvterm s_type
  WHERE tp.uniquename='FBtp0001096' AND fr_type.name='in_vitro_descendant_of' AND
    s_type.name='symbol' AND fs.is_current=TRUE AND
    fs.is_internal=FALSE AND tp.feature_id=fr.subject_id AND
    fr.type_id=fr_type.cvterm_id AND fr.object_id=prog.feature_id AND
    prog.feature_id=fs.feature_id AND fs.synonym_id=s.synonym_id AND
    s.type_id=s_type.cvterm_id;
Descendant(s) 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature descn, feature_relationship fr, cvterm fr_type, feature_synonym fs,
     synonym s, cvterm s_type
  WHERE tp.uniquename='FBmc0001085' AND fr_type.name='in_vitro_descendant_of' AND
    s_type.name='symbol' AND fs.is_current=TRUE AND
    fs.is_internal=FALSE AND tp.feature_id=fr.object_id AND
    fr.type_id=fr_type.cvterm_id AND fr.subject_id=descn.feature_id AND
    descn.feature_id=fs.feature_id AND fs.synonym_id=s.synonym_id AND
    s.type_id=s_type.cvterm_id;
COMMENTS
  
SELECT fp.VALUE
  FROM feature f, featureprop fp, cvterm fp_type
  WHERE f.uniquename='FBtp0036625' AND (fp_type.name='comment' OR
    fp_type.name='restriction_sites') AND f.feature_id=fp.feature_id AND
    fp.type_id=fp_type.cvterm_id;
SYNONYMS AND SECONDARY IDs
REPORTED AS
Symbol Synonym 
SELECT DISTINCT s.synonym_sgml
  FROM feature tp, feature_synonym fs, synonym s, cvterm s_type
  WHERE tp.uniquename='FBtp0036625' AND tp.feature_id=fs.feature_id AND
    fs.is_internal=FALSE AND fs.synonym_id=s.synonym_id AND
    s.type_id=s_type.cvterm_id AND s_type.name='symbol';
SECONDARY FLYBASE IDs
  
SELECT dbx.accession
  FROM feature tp, feature_dbxref fdbx, dbxref dbx, db
  WHERE tp.uniquename='FBtp0023480' AND tp.feature_id=fdbx.feature_id AND
    fdbx.is_current=FALSE AND fdbx.dbxref_id=dbx.dbxref_id AND
    dbx.db_id=db.db_id AND db.name='FlyBase';
REFERENCES
Research paper 
SELECT p.uniquename, p.miniref, p.title
  FROM feature f, pub p, feature_pub fp
  WHERE f.uniquename = 'FBtp0023480' AND f.feature_id=fp.feature_id AND
    fp.pub_id=p.pub_id;
Supplementary
material		 same as above use pub type to distinguish
Review same as above use pub type to distinguish
Abstract same as above use pub type to distinguish
Other same as above use pub type to distinguish

FlyBase Natural Transposon Report

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

General information
Symbol 
SELECT DISTINCT(s.synonym_sgml)
  FROM feature f, feature_synonym fs, synonym s, cvterm cvt2
  WHERE 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 = 'FBte0000001';
Species 
SELECT o.genus, o.species
  FROM feature f, organism o
  WHERE f.is_obsolete = 'f' AND f.uniquename ='FBte0000001' AND
    f.organism_id = o.organism_id;
Name 
SELECT DISTINCT(s.synonym_sgml)
  FROM feature f, feature_synonym fs, synonym s, cvterm cvt2
  WHERE 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 = 'FBte0000001';
FlyBase ID
Created/ Updated 
SELECT timeaccessioned, timelastmodified
  FROM feature f
  WHERE uniquename = 'FBte0000001' AND is_obsolete='f';
SEQUENCES & COMPONENTS
Complete element (bp) 
SELECT fp.VALUE , pub.uniquename
  FROM featureprop fp, feature f, cvterm cvt, featureprop_pub fpp, pub
  WHERE cvt.name='TE_total_length' AND cvt.cvterm_id=fp.type_id AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001' AND
    fp.featureprop_id=fpp.featureprop_id AND fpp.pub_id=pub.pub_id AND
    fp.feature_id=f.feature_id;
Terminal repeat (bp) 
SELECT fp.VALUE , pub.uniquename
  FROM featureprop fp, feature f, cvterm cvt, featureprop_pub fpp, pub
  WHERE cvt.name='TE_repeat_length' AND cvt.cvterm_id=fp.type_id AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001' AND
    fp.featureprop_id=fpp.featureprop_id AND fpp.pub_id=pub.pub_id AND
    fp.feature_id=f.feature_id;
Reference sequence
Component genes 
SELECT fp.name
  FROM feature f, feature fp, feature_relationship fr, cvterm cvt
  WHERE fr.subject_id=f.feature_id AND fr.object_id=fp.feature_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='has_component_gene' AND
    f.uniquename='FBte0000001' AND f.is_obsolete='f';
SEQUENCE ACCESSIONS
 
Transposon sequence 
SELECT dbxref.accession
  FROM dbxref, feature_dbxref fd, feature f, db
  WHERE f.feature_id=fd.feature_id AND dbxref.dbxref_id=fd.dbxref_id AND
    fd.is_current='f' AND dbxref.db_id=db.db_id AND
    db.name LIKE 'GB%' AND f.uniquename='FBte0000001' AND
    f.is_obsolete='f';
Flanking sequence 
SELECT fo.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, cvterm cvt2
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND cvt.name='associated_with' AND
    fo.type_id= cvt2.cvterm_id AND cvt2.name='transposable_element_flanking_region' AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
SEQUENCE ONTOLOGY (SO)
Transposon type 
SELECT c.name
  FROM cvterm c, feature f, feature_cvterm fc, cv
  WHERE fc.feature_id=f.feature_id AND c.cvterm_id=fc.cvterm_id AND
    c.cv_id=cv.cv_id AND cv.name='SO' AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
INSERTIONS & COPY NUMBER
Copy number
and comments		 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='TE_copies_in_genome' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
Search for 
SELECT s.name
  FROM feature f, feature_relationship fr, cvterm c, feature s
  WHERE f.feature_id=fr.object_id AND fr.type_id=c.cvterm_id AND
    c.name='producedby' AND fr.subject_id=s.feature_id AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
Sequenced genome 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='TE_copies_in_sequenced_genome' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
Copies in
sequenced genome		 Same as above
Other genomes
Copies in
other genomes
TARGET SITE DUPLICATION
Size 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='TE_duplication_length' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
consensus sequence 
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='TE_target_sequence' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
DERIVATIVES & RELATED CONSTRUCTS
Named isolates 
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.object_id AND f.feature_id=fr.subject_id AND
    fr.type_id=cvt.cvterm_id AND frp.feature_relationship_id=fr.feature_relationship_id AND
    pub.pub_id=frp.pub_id AND cvt.name='isolate_of' AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
Characterisation constructs 
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.subject_id AND fo.is_obsolete='f' AND
    f.feature_id=fr.object_id AND fr.type_id=cvt.cvterm_id AND
    frp.feature_relationship_id=fr.feature_relationship_id AND pub.pub_id=frp.pub_id AND
    cvt.name='in_vitro_descendant_of' AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
Derived constructs 
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.subject_id AND fo.is_obsolete='f' AND
    f.feature_id=fr.object_id AND fr.type_id=cvt.cvterm_id AND
    frp.feature_relationship_id=fr.feature_relationship_id AND pub.pub_id=frp.pub_id AND
    (cvt.name='in_vitro_descendant_of' OR cvt.name='belongs_to') AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
Other variants 
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.subject_id AND fo.is_obsolete='f' AND
    f.feature_id=fr.object_id AND fr.type_id=cvt.cvterm_id AND
    frp.feature_relationship_id=fr.feature_relationship_id AND pub.pub_id=frp.pub_id AND
    cvt.name='alleleof' AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
ORTHOLOGS
Curated drosophilid orthologs 
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.subject_id AND fo.is_obsolete='f' AND
    f.feature_id=fr.object_id AND fr.type_id=cvt.cvterm_id AND
    frp.feature_relationship_id=fr.feature_relationship_id AND pub.pub_id=frp.pub_id AND
    cvt.name='homologue' AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001' UNION
SELECT fo.name, pub.uniquename
  FROM feature fo, feature f, feature_relationship fr, cvterm cvt, feature_relationship_pub frp,
     pub
  WHERE fo.feature_id=fr.object_id AND fo.is_obsolete='f' AND
    f.feature_id=fr.subject_id AND fr.type_id=cvt.cvterm_id AND
    frp.feature_relationship_id=fr.feature_relationship_id AND pub.pub_id=frp.pub_id AND
    cvt.name='homologue' AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
COMMENTS
  
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='comment' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
OTHER INFORMATION
ETYMOLOGY
  
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='etymology' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
PHYLOGENETIC RANGE
  
SELECT fp.VALUE, pub.uniquename
  FROM featureprop fp, feature f, featureprop_pub frp, cvterm cvt, pub
  WHERE fp.feature_id=f.feature_id AND fp.type_id=cvt.cvterm_id AND
    cvt.name='phylogentic_range' AND pub.pub_id=frp.pub_id AND
    frp.featureprop_id=fp.featureprop_id AND f.is_obsolete='f' AND
    f.uniquename='FBte0000001';
EXTERNAL CROSSREFERENCES & LINKOUTS
SEQUENCE CROSSREFERENCES
  
SELECT dbxref.accession
  FROM dbxref, feature_dbxref fd, feature f, db
  WHERE f.feature_id=fd.feature_id AND dbxref.dbxref_id=fd.dbxref_id AND
    fd.is_current='f' AND dbxref.db_id=db.db_id AND
    (db.name LIKE 'GB%' OR db.name ='UNIPROT/TREMBL' )AND f.uniquename='FBte0000001' AND
    f.is_obsolete='f' AND fd.is_current='t';
OTHER CROSSREFERENCES
 
LINKOUTS
  
SELECT f.uniquename, f.name, db.name, accession
  FROM feature f, feature_dbxref fd, dbxref dx, dbxrefprop dxp, cvterm cvt
  WHERE f.feature_id = fd.feature_id AND fd.dbxref_id = dx.dbxref_id AND
    dx.dbxref_id = dxp.dbxref_id AND dxp.type_id = cvt.cvterm_id AND
    cvt.name = 'linkout' AND f.is_obsolete='f' AND
    f.uniquename = 'FBsf0000000001';
SYNONYMS & SECONDARY IDs
REPORTED AS
Symbol Synonym 
SELECT s.synonym_sgml, pub.uniquename
  FROM feature_synonym fs, feature f, synonym s, cvterm cvt, pub
  WHERE fs.feature_id=f.feature_id AND fs.synonym_id=s.synonym_id AND
    fs.is_internal='f' AND fs.pub_id=pub.pub_id AND
    cvt.cvterm_id=s.type_id AND cvt.name='symbol' AND
    f.is_obsolete='f' AND f.uniquename='FBti0000001';
Name Synonym 
SELECT s.synonym_sgml, pub.uniquename
  FROM feature_synonym fs, feature f, synonym s, cvterm cvt, pub
  WHERE fs.feature_id=f.feature_id AND fs.synonym_id=s.synonym_id AND
    fs.is_internal='f' AND fs.pub_id=pub.pub_id AND
    cvt.cvterm_id=s.type_id AND cvt.name='fullname' AND
    f.is_obsolete='f' AND f.uniquename='FBte0000001';
SECONDARY FLYBASE IDs
  
SELECT dbxref.accession
  FROM dbxref, feature_dbxref fd, feature f, db
  WHERE f.feature_id=fd.feature_id AND dbxref.dbxref_id=fd.dbxref_id AND
    fd.is_current='f' AND dbxref.db_id=db.db_id AND
    db.name='FlyBase' AND f.uniquename='FBte0000001' AND
    f.is_obsolete='f';
REFERENCES
Research paper 
SELECT f.uniquename, f.name, cvt.name AS pub_type, p.uniquename AS FBrf_id, miniref AS citation
  FROM feature f, feature_pub fp, pub p, cvterm cvt
  WHERE f.feature_id = fp.feature_id AND fp.pub_id = p.pub_id AND
    p.type_id = cvt.cvterm_id AND f.uniquename = 'FBte0000001' ORDER BY cvt.name;
Supplementary material
Review
Abstract
Other

Category:

Documentation

Community

Tools