SQL Queries

From DictyWiki

Revision as of 21:05, 11 February 2011 (edit) YuliaBushmanova (Talk | contribs) (→Convert gene names to DDB_G IDs) ← Previous diff		Revision as of 21:07, 11 February 2011 (edit) YuliaBushmanova (Talk | contribs) (→Notes for Genes containing word string) Next diff →
Line 280:		Line 280:
	SELECT g.name, fp.value		SELECT g.name, fp.value
	FROM cgm_chado.featureprop fp		FROM cgm_chado.featureprop fp
-	INNER JOIN cgm_chado.v_gene_features g ON g.feature_id = fp.feature_id	+	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.cvterm ct ON fp.type_id = ct.cvterm_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 ct.name = 'public note'		AND ct.name = 'public note'

---

Revision as of 21:07, 11 February 2011

Contents 1 Total GO 2 Total GO by aspect 3 GO non IEA by aspect 4 GO by evidence code 5 New literatures by date 6 Literature, all Annotations, Curator, from Distinct Reference 7 Find all lit topics associated with each gene, by paper 8 Notes for Genes containing word string 9 Convert gene names to DDB_G IDs 10 Genes, Primary IDs from Genes with specific Gene Product 11 Genes, Primary IDs from Genes with automated Gene Product 12 Pseudogene IDs 13 Genes with conflicting evidence 14 Genes with 'complete support' 15 Modified gene models by curator and date 16 Genes with community annotations 17 Genes with community annotations on Gene Page (description) 18 Map all primary DDB to GenBank Protein Accession 19 Genes, DDB IDs with Specific GO terms, created_by 20 Summaries 21 Summary by Text string including Curation Status 22 Number of PM references with GO and PHenotype annotations 23 Strain Names, Systematic Names, Strain Description where strain name starts with a specific string (example 'JS') 24 Strain Names, Synonyms, Systematic Names, Created_by, Strain Description 25 Strain Names, Created_by, Strain Description, Strain Characteristics 26 Strain Names, Created_by, by a specific plasmid 27 Strain Names, Systematic Name, Strain Description, by a specific Strain Characteristic 28 Strains annotated with a specific environment condition 29 Strains annotated with a specific assay 30 Stock Center Orders by name 31 Order rows in a resultset 32 No of Colleagues 33 Number of curated models 34 Select feature types by dictyBase ID 35 Select Feature types by dictyBase ID with gene name, gene products (bundled into one field by comma).

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]