|
|
| Line 1: |
Line 1: |
| − | [http://www.wormbase.org/wiki/index.php/Caltech_documentation ''back'']
| |
| | | | |
| − | Query Variation-Phenotype Postgres curation tables from this site:
| |
| − |
| |
| − | [http://tazendra.caltech.edu/~postgres/cgi-bin/referenceform.cgi Postgres query page]
| |
| − |
| |
| − | Some Queries from Juancarlos:
| |
| − |
| |
| − | 1) Find number of distinct genes associated with a phenotype via allele (I think it's just allele):
| |
| − |
| |
| − | paste :
| |
| − |
| |
| − | SELECT COUNT (DISTINCT app_wbgene) FROM app_wbgene;
| |
| − |
| |
| − | and click ``Pg !
| |
| − |
| |
| − | 2) If you want to see them, paste :
| |
| − |
| |
| − | SELECT DISTINCT app_wbgene FROM app_wbgene;
| |
| − |
| |
| − | 3) Find objects where the allele status is populated with a space
| |
| − |
| |
| − | SELECT * FROM app_tempname WHERE joinkey IN (SELECT joinkey FROM app_allele_status
| |
| − |
| |
| − | WHERE app_allele_status IS NOT NULL AND app_allele_status != '');''
| |
| − |
| |
| − | 4) Find objects that have an allele status value
| |
| − |
| |
| − | SELECT * FROM app_tempname WHERE joinkey IN (SELECT joinkey FROM app_allele_status);
| |
| − |
| |
| − | 5) Find lines where anatomy term field started with a space
| |
| − |
| |
| − | SELECT * FROM app_anat_term WHERE app_anat_term ~ ' ';
| |
| − |
| |
| − | 6) Find lines that have object names with no phenotypes and display the paper info
| |
| − |
| |
| − | SELECT * FROM app_paper WHERE joinkey NOT IN (SELECT joinkey FROM app_term);
| |
| − |
| |
| − | 7) List papers by date they were entered (timestamp), in descending order
| |
| − |
| |
| − | SELECT * FROM app_paper ORDER BY app_timestamp DESC;
| |
| − |
| |
| − | 8) List papers by date they were entered (timestamp), for specific date period
| |
| − |
| |
| − | SELECT * FROM app_paper WHERE app_timestamp > '2008-06-06' ORDER BY app_timestamp DESC;
| |
| − |
| |
| − | 9) Here's a query for characters that exist and have no phenotype entry at all (i.e. never had any kind of phenotype record) :
| |
| − |
| |
| − | SELECT * FROM app_tempname WHERE joinkey NOT IN (SELECT joinkey FROM app_term);
| |
| − |
| |
| − | 10) Here's a query for characters that exist and had a term at some point and it was overwritten with a blank or is also wrong :
| |
| − |
| |
| − | SELECT * FROM app_term WHERE app_term !~ '[0-9]';
| |
| − |
| |
| − | 11) Also, there are some entries from 2:13pm that have just ``WBPhenotype:''without numbers, which I hadn't seen before, and possibly is a new problem with the switchover without having changed something or other. (IDs 12809 12811-5 12817-22 &c. To see the whole list:''
| |
| − |
| |
| − | SELECT * FROM app_term WHERE app_term !~ '[0-9]' AND app_term ~ 'WBPhenotype';
| |
| − |
| |
| − | There are 96 results to
| |
| − |
| |
| − | SELECT app_paper.joinkey, app_paper.app_paper, app_tempname.app_tempname FROM app_tempname, app_paper WHERE app_paper.joinkey = app_tempname.joinkey AND app_paper.joinkey NOT IN (SELECT joinkey FROM app_term) ORDER BY app_paper.app_paper;
| |
| − |
| |
| − | There are 225 results to
| |
| − |
| |
| − | SELECT app_paper.joinkey, app_paper.app_paper, app_tempname.app_tempname FROM app_term, app_tempname, app_paper WHERE app_paper.joinkey = app_tempname.joinkey AND app_term.joinkey = app_paper.joinkey AND app_term.app_term = 'WBPhenotype' ORDER BY app_paper.app_paper;
| |
| − |
| |
| − | <br> To not show lines with no data (blank) use:
| |
| − |
| |
| − | SELECT app_paper.joinkey, app_paper.app_paper, app_tempname.app_tempname FROM app_tempname, app_paper WHERE app_paper.joinkey = app_tempname.joinkey AND app_paper.joinkey NOT IN (SELECT joinkey FROM app_term) AND app_tempname.app_tempname != ''ORDER BY app_paper.app_paper;''
| |
| − |
| |
| − | 12) finding multiallele objects that are called allele
| |
| − |
| |
| − | SELECT * FROM app_tempname WHERE app_tempname ~ '[0-9][a-z]';
| |
| − |
| |
| − | or multiallele
| |
| − |
| |
| − | SELECT * FROM app_tempname WHERE joinkey = '3933';
| |
| − |
| |
| − |
| |
| − | [[Category:Curation]]
| |
