This repository was archived by the owner on Aug 1, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 2
Useful SQL Queries
Tim Read edited this page Jul 17, 2015
·
8 revisions
Some go-bys on accessing information in the Staphopia tables.
SELECT id,strain from samples_sample WHERE sample_tag LIKE 'SRX004728';
id | strain
-------+---------------------------------------------
10912 | Staphylococcus aureus subsp. aureus 65-1119
SELECT * from analysis_variant WHERE sample_id = 10912;
id | sample_id | version_id
-------+-----------+------------
10911 | 10912 | 29
SELECT * from analysis_varianttosnp WHERE variant_id = 10911 LIMIT 20;
id | quality | comment_id | filters_id | snp_id | variant_id | AC | AD | AF | DP | GQ | GT | MQ | PL | QD
-----------+----------+------------+------------+--------+------------+-----+---------+-------+----+----+-----+--------+----------------+--------
261953270 | 824.770 | 3 | 5 | 184 | 10911 | [2] | [0, 36] | 1.000 | 36 | 81 | 1/1 | 34.690 | [853, 81, 0] | 22.910
261953288 | 1024.770 | 3 | 5 | 196 | 10911 | [2] | [0, 36] | 1.000 | 36 | 93 | 1/1 | 34.690 | [1053, 93, 0] | 28.470
261953291 | 794.770 | 3 | 5 | 454 | 10911 | [2] | [0, 26] | 1.000 | 27 | 72 | 1/1 | 35.480 | [823, 72, 0] | 29.440
261953294 | 649.770 | 3 | 5 | 494 | 10911 | [2] | [0, 23] | 1.000 | 23 | 60 | 1/1 | 33.320 | [678, 60, 0] | 28.250
261953297 | 247.770 | 3 | 5 | 596 | 10911 | [2] | [0, 10] | 1.000 | 10 | 30 | 1/1 | 25.000 | [276, 30, 0] | 24.780
261953300 | 114.900 | 3 | 5 | 614 | 10911 | [2] | [0, 8] | 1.000 | 8 | 15 | 1/1 | 25.000 | [143, 15, 0] | 14.360
261953301 | 59.280 | 3 | 5 | 630 | 10911 | [2] | [0, 6] | 1.000 | 6 | 9 | 1/1 | 29.550 | [87, 9, 0] | 9.880
261953305 | 599.770 | 3 | 5 | 1401 | 10911 | [2] | [0, 18] | 1.000 | 18 | 51 | 1/1 | 37.000 | [628, 51, 0] | 33.320
261953307 | 764.770 | 3 | 5 | 1862 | 10911 | [2] | [0, 31] | 1.000 | 31 | 69 | 1/1 | 36.670 | [793, 69, 0] | 24.670
261953309 | 1675.770 | 3 | 5 | 2203 | 10911 | [2] | [0, 48] | 1.000 | 48 | 99 | 1/1 | 36.580 | [1704, 132, 0] | 34.910
261953312 | 1877.770 | 3 | 5 | 2414 | 10911 | [2] | [2, 65] | 1.000 | 67 | 99 | 1/1 | 36.680 | [1906, 107, 0] | 28.030
261953315 | 681.770 | 3 | 5 | 2744 | 10911 | [2] | [0, 25] | 1.000 | 25 | 69 | 1/1 | 29.380 | [710, 69, 0] | 27.270
261953319 | 800.770 | 3 | 5 | 2780 | 10911 | [2] | [0, 27] | 1.000 | 27 | 78 | 1/1 | 30.010 | [829, 78, 0] | 29.660
261953321 | 669.770 | 3 | 5 | 2816 | 10911 | [2] | [0, 25] | 1.000 | 25 | 63 | 1/1 | 29.880 | [698, 63, 0] | 26.790
261953324 | 1242.770 | 3 | 5 | 2961 | 10911 | [2] | [0, 46] | 1.000 | 46 | 99 | 1/1 | 36.560 | [1271, 108, 0] | 27.020
261953325 | 52.280 | 3 | 5 | 3842 | 10911 | [2] | [0, 6] | 1.000 | 6 | 9 | 1/1 | 35.280 | [80, 9, 0] | 8.710
261953328 | 163.840 | 3 | 5 | 3924 | 10911 | [2] | [0, 11] | 1.000 | 11 | 18 | 1/1 | 34.150 | [192, 18, 0] | 14.890
261953333 | 615.770 | 3 | 5 | 3959 | 10911 | [2] | [0, 20] | 1.000 | 20 | 54 | 1/1 | 35.460 | [644, 54, 0] | 30.790
261953335 | 1281.770 | 3 | 5 | 4921 | 10911 | [2] | [0, 43] | 1.000 | 43 | 99 | 1/1 | 37.000 | [1310, 114, 0] | 29.810
261953338 | 1287.770 | 3 | 5 | 5065 | 10911 | [2] | [0, 46] | 1.000 | 46 | 99 | 1/1 | 36.340 | [1316, 114, 0] | 27.990
# 'ILIKE' --> case insensitive
SELECT id, sample_tag, strain FROM samples_sample WHERE comments ILIKE '%NARSA%';
id | sample_tag | strain
-------+--------------+--------
13934 | tread_000001 | NRS218
13935 | tread_000002 | NRS178
13936 | tread_000003 | NRS073
13937 | tread_000004 | NRS211
13938 | tread_000005 | NRS239
13939 | tread_000006 | NRS174
...
###Sample ID from SRA study accession
(PRJNA239001 is the accession of the Alam 2014 Vancomycin sequence paper)
SELECT experiment_accession,sample_accession,study_accession,scientific_name from ena_experiment WHERE study_accession LIKE '%PRJNA239001%' LIMIT 5;
experiment_accession | sample_accession | study_accession | scientific_name
----------------------+------------------+-----------------+-----------------------
SRX476966 | SAMN02649749 | PRJNA239001 | Staphylococcus aureus
SRX476967 | SAMN02649750 | PRJNA239001 | Staphylococcus aureus
SRX476964 | SAMN02649747 | PRJNA239001 | Staphylococcus aureus
SRX476965 | SAMN02649748 | PRJNA239001 | Staphylococcus aureus
SRX476962 | SAMN02649745 | PRJNA239001 | Staphylococcus aureus