-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathSQL-SRA-QRY-url.txt
405 lines (392 loc) · 18.6 KB
/
SQL-SRA-QRY-url.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
-- By Jamie Al-Nasir, 01/12/2013
-- Dept. of Computer Science, Royal Holloway university
--
--
-- SQL Queries for SQLite that list TOTAL_RECORDS and counts of all fields
-- containing URL/WEB LINK
-- for the key SUBMISSION, STUDY, SAMPLE, EXPERIMENT, RUN, SRA tables
--
-- Data used was extracted from SRA (DATE BELOW according to METADATA table date entry)
-- creation timestamp|2013-11-15 02:10:25
-- SUBMISSION
select 'table: submission';
select * from
(
select 'TOTAL_RECORDS', count(*) from submission
union all
select 'submission_alias', count(*) as [Count] from submission where submission_alias like "%http://%"
union all
select 'submission_comment', count(*) as [Count] from submission where submission_comment like "%http://%"
union all
select 'files', count(*) as [Count] from submission where files like "%http://%"
union all
select 'broker_name', count(*) as [Count] from submission where broker_name like "%http://%"
union all
select 'center_name', count(*) as [Count] from submission where center_name like "%http://%"
union all
select 'lab_name', count(*) as [Count] from submission where lab_name like "%http://%"
union all
select 'submission_date', count(*) as [Count] from submission where submission_date like "%http://%"
union all
select 'sra_link', count(*) as [Count] from submission where sra_link like "%http://%"
union all
select 'submission_url_link', count(*) as [Count] from submission where submission_url_link like "%http://%"
union all
select 'xref_link', count(*) as [Count] from submission where xref_link like "%http://%"
union all
select 'submission_entrez_link', count(*) as [Count] from submission where submission_entrez_link like "%http://%"
union all
select 'ddbj_link', count(*) as [Count] from submission where ddbj_link like "%http://%"
union all
select 'ena_link', count(*) as [Count] from submission where ena_link like "%http://%"
union all
select 'submission_attribute', count(*) as [Count] from submission where submission_attribute like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from submission where sradb_updated like "%http://%"
);
-- STUDY
select 'table: study';
select * from
(
select 'TOTAL_RECORDS', count(*) from study
union all
select 'study_alias', count(*) as [Count] from study where study_alias like "%http://%"
union all
select 'study_title', count(*) as [Count] from study where study_title like "%http://%"
union all
select 'study_type', count(*) as [Count] from study where study_type like "%http://%"
union all
select 'study_abstract', count(*) as [Count] from study where study_abstract like "%http://%"
union all
select 'broker_name', count(*) as [Count] from study where broker_name like "%http://%"
union all
select 'center_name', count(*) as [Count] from study where center_name like "%http://%"
union all
select 'center_project_name', count(*) as [Count] from study where center_project_name like "%http://%"
union all
select 'study_description', count(*) as [Count] from study where study_description like "%http://%"
union all
select 'related_studies', count(*) as [Count] from study where related_studies like "%http://%"
union all
select 'primary_study', count(*) as [Count] from study where primary_study like "%http://%"
union all
select 'sra_link', count(*) as [Count] from study where sra_link like "%http://%"
union all
select 'study_url_link', count(*) as [Count] from study where study_url_link like "%http://%"
union all
select 'xref_link', count(*) as [Count] from study where xref_link like "%http://%"
union all
select 'study_entrez_link', count(*) as [Count] from study where study_entrez_link like "%http://%"
union all
select 'ddbj_link', count(*) as [Count] from study where ddbj_link like "%http://%"
union all
select 'ena_link', count(*) as [Count] from study where ena_link like "%http://%"
union all
select 'study_attribute', count(*) as [Count] from study where study_attribute like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from study where sradb_updated like "%http://%"
);
-- SAMPLE
select 'table: sample';
select * from
(
select 'TOTAL_RECORDS', count(*) from sample
union all
select 'sample_alias', count(*) as [Count] from sample where sample_alias like "%http://%"
union all
select 'broker_name', count(*) as [Count] from sample where broker_name like "%http://%"
union all
select 'center_name', count(*) as [Count] from sample where center_name like "%http://%"
union all
select 'taxon_id', count(*) as [Count] from sample where taxon_id like "%http://%"
union all
select 'scientific_name', count(*) as [Count] from sample where scientific_name like "%http://%"
union all
select 'common_name', count(*) as [Count] from sample where common_name like "%http://%"
union all
select 'anonymized_name', count(*) as [Count] from sample where anonymized_name like "%http://%"
union all
select 'individual_name', count(*) as [Count] from sample where individual_name like "%http://%"
union all
select 'description', count(*) as [Count] from sample where description like "%http://%"
union all
select 'sra_link', count(*) as [Count] from sample where sra_link like "%http://%"
union all
select 'sample_url_link', count(*) as [Count] from sample where sample_url_link like "%http://%"
union all
select 'xref_link', count(*) as [Count] from sample where xref_link like "%http://%"
union all
select 'sample_entrez_link', count(*) as [Count] from sample where sample_entrez_link like "%http://%"
union all
select 'ddbj_link', count(*) as [Count] from sample where ddbj_link like "%http://%"
union all
select 'ena_link', count(*) as [Count] from sample where ena_link like "%http://%"
union all
select 'sample_attribute', count(*) as [Count] from sample where sample_attribute like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from sample where sradb_updated like "%http://%"
);
-- EXPERIMENT
select 'table: experiment';
select * from
(
select 'TOTAL_RECORDS', count(*) from experiment
union all
select 'bamFile', count(*) as [Count] from experiment where bamFile like "%http://%"
union all
select 'fastqFTP', count(*) as [Count] from experiment where fastqFTP like "%http://%"
union all
select 'experiment_alias', count(*) as [Count] from experiment where experiment_alias like "%http://%"
union all
select 'broker_name', count(*) as [Count] from experiment where broker_name like "%http://%"
union all
select 'center_name', count(*) as [Count] from experiment where center_name like "%http://%"
union all
select 'title', count(*) as [Count] from experiment where title like "%http://%"
union all
select 'study_name', count(*) as [Count] from experiment where study_name like "%http://%"
union all
select 'design_description', count(*) as [Count] from experiment where design_description like "%http://%"
union all
select 'sample_name', count(*) as [Count] from experiment where sample_name like "%http://%"
union all
select 'sample_member', count(*) as [Count] from experiment where sample_member like "%http://%"
union all
select 'library_name', count(*) as [Count] from experiment where library_name like "%http://%"
union all
select 'library_strategy', count(*) as [Count] from experiment where library_strategy like "%http://%"
union all
select 'library_source', count(*) as [Count] from experiment where library_source like "%http://%"
union all
select 'library_selection', count(*) as [Count] from experiment where library_selection like "%http://%"
union all
select 'library_layout', count(*) as [Count] from experiment where library_layout like "%http://%"
union all
select 'targeted_loci', count(*) as [Count] from experiment where targeted_loci like "%http://%"
union all
select 'library_construction_protocol', count(*) as [Count] from experiment where library_construction_protocol like "%http://%"
union all
select 'spot_length', count(*) as [Count] from experiment where spot_length like "%http://%"
union all
select 'adapter_spec', count(*) as [Count] from experiment where adapter_spec like "%http://%"
union all
select 'read_spec', count(*) as [Count] from experiment where read_spec like "%http://%"
union all
select 'platform', count(*) as [Count] from experiment where platform like "%http://%"
union all
select 'instrument_model', count(*) as [Count] from experiment where instrument_model like "%http://%"
union all
select 'platform_parameters', count(*) as [Count] from experiment where platform_parameters like "%http://%"
union all
select 'sequence_space', count(*) as [Count] from experiment where sequence_space like "%http://%"
union all
select 'base_caller', count(*) as [Count] from experiment where base_caller like "%http://%"
union all
select 'quality_scorer', count(*) as [Count] from experiment where quality_scorer like "%http://%"
union all
select 'number_of_levels', count(*) as [Count] from experiment where number_of_levels like "%http://%"
union all
select 'multiplier', count(*) as [Count] from experiment where multiplier like "%http://%"
union all
select 'qtype', count(*) as [Count] from experiment where qtype like "%http://%"
union all
select 'sra_link', count(*) as [Count] from experiment where sra_link like "%http://%"
union all
select 'experiment_url_link', count(*) as [Count] from experiment where experiment_url_link like "%http://%"
union all
select 'xref_link', count(*) as [Count] from experiment where xref_link like "%http://%"
union all
select 'experiment_entrez_link', count(*) as [Count] from experiment where experiment_entrez_link like "%http://%"
union all
select 'ddbj_link', count(*) as [Count] from experiment where ddbj_link like "%http://%"
union all
select 'ena_link', count(*) as [Count] from experiment where ena_link like "%http://%"
union all
select 'experiment_attribute', count(*) as [Count] from experiment where experiment_attribute like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from experiment where sradb_updated like "%http://%"
);
-- RUN
select 'table: run';
select * from
(
select 'TOTAL_RECORDS', count(*) from run
union all
select 'bamFile', count(*) as [Count] from run where bamFile like "%http://%"
union all
select 'run_alias', count(*) as [Count] from run where run_alias like "%http://%"
union all
select 'broker_name', count(*) as [Count] from run where broker_name like "%http://%"
union all
select 'instrument_name', count(*) as [Count] from run where instrument_name like "%http://%"
union all
select 'run_date', count(*) as [Count] from run where run_date like "%http://%"
union all
select 'run_file', count(*) as [Count] from run where run_file like "%http://%"
union all
select 'run_center', count(*) as [Count] from run where run_center like "%http://%"
union all
select 'total_data_blocks', count(*) as [Count] from run where total_data_blocks like "%http://%"
union all
select 'experiment_name', count(*) as [Count] from run where experiment_name like "%http://%"
union all
select 'sra_link', count(*) as [Count] from run where sra_link like "%http://%"
union all
select 'run_url_link', count(*) as [Count] from run where run_url_link like "%http://%"
union all
select 'xref_link', count(*) as [Count] from run where xref_link like "%http://%"
union all
select 'run_entrez_link', count(*) as [Count] from run where run_entrez_link like "%http://%"
union all
select 'ddbj_link', count(*) as [Count] from run where ddbj_link like "%http://%"
union all
select 'ena_link', count(*) as [Count] from run where ena_link like "%http://%"
union all
select 'run_attribute', count(*) as [Count] from run where run_attribute like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from run where sradb_updated like "%http://%"
);
-- SRA
select 'table: sra';
select * from
(
select 'TOTAL_RECORDS', count(*) from sra
union all
select 'SRR_bamFile', count(*) as [Count] from sra where SRR_bamFile like "%http://%"
union all
select 'SRX_bamFile', count(*) as [Count] from sra where SRX_bamFile like "%http://%"
union all
select 'SRX_fastqFTP', count(*) as [Count] from sra where SRX_fastqFTP like "%http://%"
union all
select 'run_ID', count(*) as [Count] from sra where run_ID like "%http://%"
union all
select 'run_alias', count(*) as [Count] from sra where run_alias like "%http://%"
union all
select 'run_date', count(*) as [Count] from sra where run_date like "%http://%"
union all
select 'updated_date', count(*) as [Count] from sra where updated_date like "%http://%"
union all
select 'spots', count(*) as [Count] from sra where spots like "%http://%"
union all
select 'bases', count(*) as [Count] from sra where bases like "%http://%"
union all
select 'run_center', count(*) as [Count] from sra where run_center like "%http://%"
union all
select 'experiment_name', count(*) as [Count] from sra where experiment_name like "%http://%"
union all
select 'run_url_link', count(*) as [Count] from sra where run_url_link like "%http://%"
union all
select 'run_entrez_link', count(*) as [Count] from sra where run_entrez_link like "%http://%"
union all
select 'run_attribute', count(*) as [Count] from sra where run_attribute like "%http://%"
union all
select 'experiment_ID', count(*) as [Count] from sra where experiment_ID like "%http://%"
union all
select 'experiment_alias', count(*) as [Count] from sra where experiment_alias like "%http://%"
union all
select 'experiment_title', count(*) as [Count] from sra where experiment_title like "%http://%"
union all
select 'study_name', count(*) as [Count] from sra where study_name like "%http://%"
union all
select 'sample_name', count(*) as [Count] from sra where sample_name like "%http://%"
union all
select 'design_description', count(*) as [Count] from sra where design_description like "%http://%"
union all
select 'library_name', count(*) as [Count] from sra where library_name like "%http://%"
union all
select 'library_strategy', count(*) as [Count] from sra where library_strategy like "%http://%"
union all
select 'library_source', count(*) as [Count] from sra where library_source like "%http://%"
union all
select 'library_selection', count(*) as [Count] from sra where library_selection like "%http://%"
union all
select 'library_layout', count(*) as [Count] from sra where library_layout like "%http://%"
union all
select 'library_construction_protocol', count(*) as [Count] from sra where library_construction_protocol like "%http://%"
union all
select 'adapter_spec', count(*) as [Count] from sra where adapter_spec like "%http://%"
union all
select 'read_spec', count(*) as [Count] from sra where read_spec like "%http://%"
union all
select 'platform', count(*) as [Count] from sra where platform like "%http://%"
union all
select 'instrument_model', count(*) as [Count] from sra where instrument_model like "%http://%"
union all
select 'instrument_name', count(*) as [Count] from sra where instrument_name like "%http://%"
union all
select 'platform_parameters', count(*) as [Count] from sra where platform_parameters like "%http://%"
union all
select 'sequence_space', count(*) as [Count] from sra where sequence_space like "%http://%"
union all
select 'base_caller', count(*) as [Count] from sra where base_caller like "%http://%"
union all
select 'quality_scorer', count(*) as [Count] from sra where quality_scorer like "%http://%"
union all
select 'number_of_levels', count(*) as [Count] from sra where number_of_levels like "%http://%"
union all
select 'multiplier', count(*) as [Count] from sra where multiplier like "%http://%"
union all
select 'qtype', count(*) as [Count] from sra where qtype like "%http://%"
union all
select 'experiment_url_link', count(*) as [Count] from sra where experiment_url_link like "%http://%"
union all
select 'experiment_entrez_link', count(*) as [Count] from sra where experiment_entrez_link like "%http://%"
union all
select 'experiment_attribute', count(*) as [Count] from sra where experiment_attribute like "%http://%"
union all
select 'sample_ID', count(*) as [Count] from sra where sample_ID like "%http://%"
union all
select 'sample_alias', count(*) as [Count] from sra where sample_alias like "%http://%"
union all
select 'taxon_id', count(*) as [Count] from sra where taxon_id like "%http://%"
union all
select 'common_name', count(*) as [Count] from sra where common_name like "%http://%"
union all
select 'anonymized_name', count(*) as [Count] from sra where anonymized_name like "%http://%"
union all
select 'individual_name', count(*) as [Count] from sra where individual_name like "%http://%"
union all
select 'description', count(*) as [Count] from sra where description like "%http://%"
union all
select 'sample_url_link', count(*) as [Count] from sra where sample_url_link like "%http://%"
union all
select 'sample_entrez_link', count(*) as [Count] from sra where sample_entrez_link like "%http://%"
union all
select 'sample_attribute', count(*) as [Count] from sra where sample_attribute like "%http://%"
union all
select 'study_ID', count(*) as [Count] from sra where study_ID like "%http://%"
union all
select 'study_alias', count(*) as [Count] from sra where study_alias like "%http://%"
union all
select 'study_title', count(*) as [Count] from sra where study_title like "%http://%"
union all
select 'study_type', count(*) as [Count] from sra where study_type like "%http://%"
union all
select 'study_abstract', count(*) as [Count] from sra where study_abstract like "%http://%"
union all
select 'center_project_name', count(*) as [Count] from sra where center_project_name like "%http://%"
union all
select 'study_description', count(*) as [Count] from sra where study_description like "%http://%"
union all
select 'study_url_link', count(*) as [Count] from sra where study_url_link like "%http://%"
union all
select 'study_entrez_link', count(*) as [Count] from sra where study_entrez_link like "%http://%"
union all
select 'study_attribute', count(*) as [Count] from sra where study_attribute like "%http://%"
union all
select 'related_studies', count(*) as [Count] from sra where related_studies like "%http://%"
union all
select 'primary_study', count(*) as [Count] from sra where primary_study like "%http://%"
union all
select 'submission_ID', count(*) as [Count] from sra where submission_ID like "%http://%"
union all
select 'submission_comment', count(*) as [Count] from sra where submission_comment like "%http://%"
union all
select 'submission_center', count(*) as [Count] from sra where submission_center like "%http://%"
union all
select 'submission_lab', count(*) as [Count] from sra where submission_lab like "%http://%"
union all
select 'submission_date', count(*) as [Count] from sra where submission_date like "%http://%"
union all
select 'sradb_updated', count(*) as [Count] from sra where sradb_updated like "%http://%"
);