Difference between revisions of "Postgres Query"
m |
m |
||
Line 32: | Line 32: | ||
SELECT * FROM app_anat_term WHERE app_anat_term ~ ' '; | 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'; |
Revision as of 18:37, 23 June 2008
Query Variation-Phenotype Postgres curation tables from this site:
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';