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