SQL Queries

From DictyWiki

(Difference between revisions)
Jump to: navigation, search
Revision as of 21:08, 11 February 2011 (edit)
YuliaBushmanova (Talk | contribs)
(Convert gene names to DDB_G IDs)
← Previous diff
Revision as of 21:08, 11 February 2011 (edit)
YuliaBushmanova (Talk | contribs)
(Convert gene names to DDB_G IDs)
Next diff →
Line 292: Line 292:
SELECT g.name AS GENE_NAME, dx.accession AS DDB_G_ID SELECT g.name AS GENE_NAME, dx.accession AS DDB_G_ID
FROM cgm_chado.v_gene_features g FROM cgm_chado.v_gene_features g
-INNER JOIN cgm_chado.dbxref dx ON g.dbxref_id = dx.dbxref_id+INNER JOIN cgm_chado.dbxref dx ON g.dbxref_id = dx.dbxref_id
-INNER JOIN cgm_chado.organism o ON o.organism_id = g.organism_id+INNER JOIN cgm_chado.organism o ON o.organism_id = g.organism_id
WHERE o.common_name = 'dicty' WHERE o.common_name = 'dicty'
AND g.name IN ('rab14', 'rab11A' , 'rab21' ) AND g.name IN ('rab14', 'rab11A' , 'rab21' )

Revision as of 21:08, 11 February 2011

Contents



Total GO

Canonical query (excluding obsolete annotations)
SELECT COUNT(fcvt.feature_cvterm_id) annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN cgm_chado.organism ON organism.organism_id = gene.organism_id
WHERE  type.name = 'gene'
AND gene.is_deleted = 0
AND 
cv.name IN('molecular_function',   'biological_process',   'cellular_component')
AND GO.is_obsolete = 0
AND organism.common_name = 'dicty'
Including obsolete annotations
SELECT COUNT(fcvt.feature_cvterm_id) annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN cgm_chado.organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND cv.name IN('molecular_function',   'biological_process',   'cellular_component')
AND organism.common_name = 'dicty'
Only obsolete annotations
SELECT COUNT(fcvt.feature_cvterm_id) obsolete_annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.organism ON organism.organism_id = gene.organism_id
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND 
cv.name IN('molecular_function',   'biological_process',   'cellular_component')
AND
GO.is_obsolete = 1
AND organism.common_name = 'dicty'

Total GO by aspect

biological_process
SELECT COUNT(fcvt.feature_cvterm_id) process_annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND 
cv.name = 'biological_process'
AND GO.is_obsolete = 0
AND organism.common_name = 'dicty'
molecular_function
SELECT COUNT(fcvt.feature_cvterm_id) function_annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND 
cv.name = 'molecular_function'
AND GO.is_obsolete = 0
AND organism.common_name = 'dicty'
cellular_component
SELECT COUNT(fcvt.feature_cvterm_id) component_annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND 
cv.name = 'cellular_component'
AND GO.is_obsolete = 0
AND organism.common_name = 'dicty'

GO non IEA by aspect

Canonical query(all aspects)
SELECT cv.name aspect,count(fcvt.feature_cvterm_id) annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN cgm_chado.feature_cvtermprop fcvt_prop ON fcvt_prop.feature_cvterm_id=fcvt.feature_cvterm_id
JOIN cgm_chado.cvterm evterm ON evterm.cvterm_id=fcvt_prop.type_id
JOIN cgm_chado.cv  ev ON ev.cv_id=evterm.cv_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE  type.name = 'gene'
ANd gene.is_deleted = 0
AND cv.name IN('molecular_function',   'biological_process',   'cellular_component')
AND GO.is_obsolete = 0
AND ev.name like 'evidence_code%'
AND evterm.name != 'inferred from electronic annotation'
AND organism.common_name = 'dicty'
GROUP BY cv.name
ORDER BY annotations DESC

GO by evidence code

Canonical query(all aspects)
SELECT evsyn.synonym_ evidence_code,count(fcvt.feature_cvterm_id) annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN cgm_chado.feature_cvtermprop fcvt_prop ON fcvt_prop.feature_cvterm_id=fcvt.feature_cvterm_id
JOIN cgm_chado.cvterm evterm ON evterm.cvterm_id=fcvt_prop.type_id
JOIN cgm_chado.cv  ev ON ev.cv_id=evterm.cv_id
JOIN cgm_chado.cvtermsynonym evsyn on evterm.cvterm_id=evsyn.cvterm_id
JOIN cgm_chado.cvterm synterm on synterm.cvterm_id=evsyn.type_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND organism.common_name = 'dicty'
AND GO.is_obsolete = 0
AND ev.name like 'evidence_code%'
AND synterm.name IN ('EXACT','RELATED')
AND LENGTH(evsyn.synonym_) = 3
AND 
cv.name IN('molecular_function',   'biological_process',   'cellular_component')
GROUP BY evsyn.synonym_
ORDER BY annotations DESC


biological process only
SELECT evsyn.synonym_ evidence_code,count(fcvt.feature_cvterm_id) annotations
FROM cgm_chado.feature_cvterm fcvt 
JOIN cgm_chado.feature gene ON gene.feature_id = fcvt.feature_id 
JOIN cgm_chado.cvterm type ON type.cvterm_id = gene.TYPE_ID 
JOIN cgm_chado.cvterm GO ON GO.cvterm_id = fcvt.cvterm_id 
JOIN cgm_chado.cv ON cv.cv_id = GO.cv_id
JOIN cgm_chado.feature_cvtermprop fcvt_prop ON fcvt_prop.feature_cvterm_id=fcvt.feature_cvterm_id
JOIN cgm_chado.cvterm evterm ON evterm.cvterm_id=fcvt_prop.type_id
JOIN cgm_chado.cv  ev ON ev.cv_id=evterm.cv_id
JOIN cgm_chado.cvtermsynonym evsyn on evterm.cvterm_id=evsyn.cvterm_id
JOIN cgm_chado.cvterm synterm on synterm.cvterm_id=evsyn.type_id
JOIN organism ON organism.organism_id = gene.organism_id
WHERE type.name = 'gene'
AND gene.is_deleted = 0
AND GO.is_obsolete = 0
AND ev.name like 'evidence_code%'
AND synterm.name IN ('EXACT','RELATED')
AND LENGTH(evsyn.synonym_) = 3
AND organism.common_name = 'dicty'
AND 
cv.name = 'biological_process'
GROUP BY evsyn.synonym_
ORDER BY annotations DESC
For other aspects replace biological_process (in cv.name) with either of molecular_function or cellular_component



New literatures by date

SELECT * from cgm_chado.pub
WHERE 
created_at >= to_date('12/28/2010','MM/DD/YYYY')
AND 
created_at <= to_date('01/04/2011','MM/DD/YYYY')
AND  pubplace = 'PUBMED' 
ORDER BY pub_id
To retrieve only pubmed id
SELECT pub.uniquename pubmed from cgm_chado.pub 
WHERE 
created_at >= to_date('12/28/2010','MM/DD/YYYY')
AND 
created_at <= to_date('01/04/2011','MM/DD/YYYY')
AND  pubplace = 'PUBMED' 
ORDER BY pub_id desc

Literature, all Annotations, Curator, from Distinct Reference

By reference no
  • Remember to replace 10060 with any other number
SELECT feat.name locus, fpub.pub_id reference_no,pubterm.name literature_topic,
curator.name who_did_it,cpubprop.timecreated when
FROM cgm_chado.feature_pub fpub
JOIN cgm_chado.feature_pubprop fpubprop ON fpubprop.feature_pub_id=fpub.feature_pub_id
JOIN cgm_chado.cvterm pubterm ON pubterm.cvterm_id=fpubprop.type_id
JOIN cgm_chado.cv pubcv ON pubcv.cv_id=pubterm.cv_id
JOIN 
cgm_chado.curator_feature_pubprop cpubprop ON cpubprop.feature_pubprop_id=fpubprop.feature_pubprop_id
JOIN cgm_chado.curator ON curator.curator_id=cpubprop.curator_id
JOIN cgm_chado.feature feat ON feat.feature_id = fpub.feature_id
JOIN cgm_chado.organism ON organism.organism_id = feat.organism_id
WHERE pubcv.name = 'dictyBase_literature_topic'
AND organism.common_name = 'dicty'
AND reference_no = 10060
By curator
SELECT DISTINCT c.name who_did_it
FROM cgm_chado.pub p
INNER JOIN cgm_chado.feature_pub fp               ON p.pub_id = fp.pub_id
INNER JOIN cgm_chado.feature_pubprop fpp          ON fpp.feature_pub_id = fp.feature_pub_id
INNER JOIN cgm_chado.curator_feature_pubprop cfpp ON cfpp.feature_pubprop_id = fpp.feature_pubprop_id
INNER JOIN cgm_chado.curator c                    ON c.curator_id = cfpp.curator_id
WHERE p.pub_id  = 10060

Find all lit topics associated with each gene, by paper

 
SELECT dx.accession, p.uniquename, stragg( ct.name ) AS Literature_Topics
FROM cgm_chado.v_gene_features g
INNER JOIN cgm_chado.dbxref dx           ON g.dbxref_id = dx.dbxref_id
INNER JOIN cgm_chado.feature_pub fp      ON g.feature_id = fp.feature_id
INNER JOIN cgm_chado.pub p               ON fp.pub_id = p.pub_id
INNER JOIN cgm_chado.feature_pubprop fpp ON fp.feature_pub_id = fpp.feature_pub_id
INNER JOIN cgm_chado.cvterm ct           ON fpp.type_id = ct.cvterm_id
INNER JOIN cgm_chado.cv                  ON ct.cv_id = cv.cv_id
INNER JOIN cgm_chado.organism o          ON o.organism_id = g.organism_id
WHERE o.common_name = 'dicty'
AND p.pubplace = 'PUBMED'
AND cv.name ='dictyBase_literature_topic'  
GROUP BY dx.accession, p.uniquename
ORDER BY to_number(p.uniquename) 

Notes for Genes containing word string

Replace 'public note' with 'private note for Private Notes. In this example notes containing UTR are searched.

SELECT g.name, fp.value
FROM cgm_chado.featureprop fp
INNER JOIN cgm_chado.v_gene_features g   ON g.feature_id = fp.feature_id
INNER JOIN cgm_chado.cvterm ct           ON fp.type_id = ct.cvterm_id
INNER JOIN cgm_chado.organism o          ON o.organism_id    = g.organism_id
WHERE o.common_name = 'dicty'
AND ct.name = 'public note'
AND value LIKE '%UTR%'

Convert gene names to DDB_G IDs

SELECT g.name  AS GENE_NAME, dx.accession AS DDB_G_ID
FROM cgm_chado.v_gene_features g
INNER JOIN cgm_chado.dbxref dx          ON g.dbxref_id = dx.dbxref_id
INNER JOIN cgm_chado.organism o         ON o.organism_id    = g.organism_id
WHERE o.common_name = 'dicty'
AND g.name IN ('rab14', 'rab11A' , 'rab21' )

Genes, Primary IDs from Genes with specific Gene Product

Substitute any gene product for 'TRANSCRIPT%'; the % can be omitted when gene product is distinct.

      SELECT G.GENE_NAME, G.PRIMARY_FEATURE_DICTYBASEID
        FROM CGM_DDB.GENE_PRODUCT GP
  INNER JOIN CGM_DDB.LOCUS_GP LGP
          ON LGP.GENE_PRODUCT_NO = GP.GENE_PRODUCT_NO
  INNER JOIN CGM_CHADO.V_GENE_DICTYBASEID G
          ON LGP.LOCUS_NO = G.GENE_FEATURE_ID
       WHERE UPPER(GP.GENE_PRODUCT) LIKE UPPER('TRANSCRIPT%')
    GROUP BY G.GENE_NAME, G.PRIMARY_FEATURE_DICTYBASEID;

Genes, Primary IDs from Genes with automated Gene Product

 
      SELECT G.GENE_NAME, G.PRIMARY_FEATURE_DICTYBASEID, GP.GENE_PRODUCT
        FROM CGM_DDB.GENE_PRODUCT GP
  INNER JOIN CGM_DDB.LOCUS_GP LGP
          ON LGP.GENE_PRODUCT_NO = GP.GENE_PRODUCT_NO
  INNER JOIN CGM_CHADO.V_GENE_DICTYBASEID G
          ON LGP.LOCUS_NO = G.GENE_FEATURE_ID
       WHERE GP.IS_AUTOMATED ='1'
    order by GP.GENE_PRODUCT

Pseudogene IDs

SELECT feature.uniquename
FROM CGM_CHADO.feature
INNER JOIN CGM_CHADO.cvterm ON cvterm.cvterm_id = feature.TYPE_ID
WHERE cvterm.name = 'pseudogene'
and feature.is_deleted = 0

To get also the Gene Name

SELECT psuedofeat.uniquename pseudogene,genefeat.name gene
FROM CGM_CHADO.v_notdeleted_feature pseudofeat
inner join CGM_CHADO.feature_relationship frel on frel.subject_id=pseudofeat.feature_id
inner join CGM_CHADO.v_notdeleted_feature genefeat on frel.object_id=genefeat.feature_id
inner join CGM_CHADO.cvterm on cvterm.cvterm_id=pseudofeat.type_id
WHERE cvterm.name = 'pseudogene'

Genes with conflicting evidence

This query can also be used to find genes with support notes, such as 'genomic context'.

SELECT v.name,
       p.value 
  FROM cgm_chado.featureprop p inner 
  JOIN cgm_chado.V_VERIFIED_GENE_FEATURES v on p.feature_id=v.feature_id 
 WHERE p.value like 'Conflicting evidence'

Genes with 'complete support'

 
SELECT v.name, p.value
     
  FROM cgm_chado.featureprop p inner 
  JOIN cgm_chado.V_VERIFIED_GENE_FEATURES v on p.feature_id=v.feature_id 
where  p.value like 'ESTs' or p.value like 'sequence similarity'

AND not v.name in
(SELECT v.name
  FROM cgm_chado.featureprop p inner 
  JOIN cgm_chado.V_VERIFIED_GENE_FEATURES v on p.feature_id=v.feature_id 
 WHERE p.value like 'Conflicting evidence' or p.value like 'Incomplete support') 

Modified gene models by curator and date

Replace 'CGM_DDB_BOBD' with CGM_DDB_PFEY or CGM_DDB_PASC

SELECT v.name , v.timelastmodified
 FROM cgm_chado.V_VERIFIED_GENE_FEATURES v
join cgm_chado.featureprop p on p.feature_id=v.feature_id
WHERE v.created_by ='CGM_DDB_BOBD' and p.value like 'curator inference'
ORDER BY v.timelastmodified

Genes with community annotations

SELECT f.name 
  FROM cgm_chado.feature f 
  JOIN cgm_chado.FEATUREPROP fp on f.feature_id = fp.feature_id 
 WHERE fp.value like 'Has Wiki Page'
 ORDER BY f.name

Genes with community annotations on Gene Page (description)


SELECT f.name,
       p.value as descriptions 
  FROM cgm_chado.featureprop p inner 
  JOIN cgm_chado.v_gene_features l on l.feature_id=p.feature_id 
  JOIN cgm_chado.feature f on f.feature_id = p.feature_id 
 WHERE p.type_id=972 
   AND p.feature_id in ( SELECT feature_id 
	  FROM cgm_chado.FEATUREPROP 
	 WHERE value like 'Has Wiki Page'  )

Map all primary DDB to GenBank Protein Accession

 select d.accession, f.name, db.name
              from CGM_CHADO.v_primary_feature f
        inner join CGM_CHADO.feature_dbxref fd
                on fd.feature_id = f.feature_id
        inner join CGM_CHADO.dbxref d
                on d.dbxref_id = fd.dbxref_id
        inner join CGM_CHADO.db
                on d.db_id = db.db_id
               and db.name = 'DB:Protein Accession Number';

Genes, DDB IDs with Specific GO terms, created_by

Replace GO ID with any other; delete the 'and created_by..' if not restricted to curator; add 'order by..' after parenthesis to sort by gene_name etc.

SELECT * from cgm_chado.v_gene_dictybaseid 
WHERE gene_feature_id in
	(SELECT distinct locus_no from go_locus_goev WHERE goid = 6118 and created_by='CGM_DDB_PFEY')


Summaries

select fp.FEATURE_ID, p.paragraph_text from cgm_chado.featureprop fp  
JOIN cgm_ddb.paragraph p on TO_NCHAR(fp.value)= p.paragraph_no
WHERE fp.type_id=984 and written_by ='CGM_DDB_KPIL' and not paragraph_text  like '<summary%><curation_status>%' 
 
and exists
(select * from cgm_chado.featureprop where type_id=984 and value like paragraph_no) order by date_created

Summary by Text string including Curation Status

SELECT p.created_by, FEATURE_ID as n
FROM cgm_chado.featureprop fp
JOIN cgm_ddb.paragraph p on TO_NCHAR(fp.value)= p.paragraph_no
WHERE fp.type_id=984
AND paragraph_text like '%Basic%'

Number of PM references with GO and PHenotype annotations

SELECT TAB_NAME, PRIMARY_KEY, reference_no FROM reflink WHERE reference_no in ( SELECT reference_no FROM reference WHERE pubmed is not null )

Table must be cleaned up: In excel do "convert data to columns" and distangle the PRIMARY_KEY fields. Then put PMIDs in one column and sort, then delete duplicate PMID refs.

Strain Names, Systematic Names, Strain Description where strain name starts with a specific string (example 'JS')

SELECT strain_name, systematic_name, strain_description FROM stock_center WHERE strain_name like 'JS%' or 
systematic_name like 'JS%'

Can also search for specific strains by using 'equals'

SELECT strain_name, systematic_name, strain_description FROM cgm_ddb.stock_center WHERE strain_name = 'JS65' or 
systematic_name = 'JS65'

Strain Names, Synonyms, Systematic Names, Created_by, Strain Description

SELECT strain_name, stragg( name) as synonyms, systematic_name, created_by, strain_description FROM cgm_ddb.stock_center  sc
JOIN cgm_ddb.STRAIN_SYNONYM ss on ss.strain_id = sc.id
JOIN cgm_chado.synonym_ cs on ss.synonym_id= cs.synonym_id 
GROUP BY strain_name, created_by, strain_description, systematic_name
ORDER BY created_by, strain_name

Can also restrict the curator by adding

where created_by ='CGM_DDB_PASC' 

before the GROUP BY statement

Strain Names, Created_by, Strain Description, Strain Characteristics

  SELECT strain_name,   
         systematic_name,
         strain_description,
         stragg( name) as Strain_characteristics
     FROM cgm_ddb.stock_center s 
     join cgm_ddb.STRAIN_CHAR_CVTERM sc on s.id = sc.strain_id
     join cgm_chado.CVTERM cv on sc.CVTERM_ID = cv.CVTERM_ID
    where strain_description like '%null%'
    group by strain_name,  systematic_name, strain_description

Strain Names, Created_by, by a specific plasmid


SELECT strain_name,
       plasmid,
       mutagenesis_method,
       created_by 
  FROM cgm_ddb.stock_center 
 WHERE plasmid like '%pEXP%' or plasmid like '%pExp%'
 ORDER BY created_by

Strain Names, Systematic Name, Strain Description, by a specific Strain Characteristic

SELECT strain_name,
       systematic_name,
       name,
       strain_description 
  FROM stock_center s 
  JOIN STRAIN_CHAR_CVTERM sc on s.id = sc.strain_id 
  JOIN cgm_chado.CVTERM cv on sc.CVTERM_ID = cv.CVTERM_ID 
 WHERE name ='multiple null mutant'
 ORDER BY strain_name

Strains annotated with a specific environment condition

  SELECT strain_name, systematic_name, name as envt     
  FROM cgm_ddb.stock_center s    
  JOIN cgm_chado.phenstatement ps  ON s.genotype_id = ps.genotype_id 
  JOIN cgm_chado.cvterm cv ON cv.CVTERM_ID = ps.ENVIRONMENT_ID
  WHERE name ='in suspension'

Strains annotated with a specific assay

 SELECT strain_name, systematic_name, name as assay  
  FROM stock_center s    
JOIN cgm_chado.phenstatement ph on s.genotype_id = ph.genotype_id 
JOIN cgm_chado.phenotype p ON ph.phenotype_id = p.phenotype_id
  JOIN cgm_chado.cvterm cv ON cv.CVTERM_ID = p.assay_ID
  WHERE name ='electron microscopy'

Stock Center Orders by name

Substitute any name or use 'last_name' and order by anything, here by order ID.

SELECT * from cgm_ddb.stock_center_view WHERE first_name = 'Baskar' ORDER BY Order_ID


Order rows in a resultset

select name,uniquename,seqlen,residues,created_by from cgm_chado.feature order by timelastmodified asc|desc

asc => should sort the result by ascending order
desc => should sort the result by descending order

No of Colleagues

select count(COLLEAGUE_NO) ccount from cgm_ddb.COLLEAGUE;

Number of curated models

select count(*) as c from cgm_chado.V_VERIFIED_GENE_FEATURES v

Select feature types by dictyBase ID

select ncrna.uniquename FROM cgm_chado.feature ncrna join
cgm_chado.cvterm nctype on nctype.cvterm_id=ncrna.type_id
where nctype.name like '%snoRNA%' OR
nctype.name = 'ncRNA'
or nctype.name like 'class%RNA' or nctype.name = 'rRNA' or
nctype.name = 'snRNA' OR nctype.name LIKE 'RNase%'
OR nctype.name LIKE 'SRP%'

Select Feature types by dictyBase ID with gene name, gene products (bundled into one field by comma).

select gene.name, stragg (gp.gene_product) as gene_product, ncrna.uniquename FROM cgm_chado.feature ncrna join
cgm_chado.cvterm nctype on nctype.cvterm_id=ncrna.type_id
INNER JOIN cgm_chado.feature_relationship fr
ON ncrna.feature_id = fr.subject_id
INNER JOIN cgm_chado.cvterm rcvt
ON rcvt.cvterm_id = fr.type_id
INNER JOIN cgm_chado.feature gene
ON gene.feature_id = fr.object_id
INNER JOIN cgm_ddb.locus_gp lg
ON lg.locus_no = gene.feature_id
INNER JOIN cgm_ddb.gene_product gp
ON lg.gene_product_no = gp.gene_product_no
where nctype.name like '%snoRNA%' OR
nctype.name = 'ncRNA'
or nctype.name like 'class%RNA' or nctype.name = 'rRNA' or
nctype.name = 'snRNA' OR nctype.name LIKE 'RNase%'
OR nctype.name LIKE 'SRP%' group by gene.name, ncrna.uniquename

Return to [Curation To Do]

Personal tools