SQL-SRA-QRY-reagent-kits.txt

select 'Reagent kit probing...';

-- Inspecting the primary annotation field "library_construction_protocol"


select 'table: experiment (total records)';

select
  count(*)
from
  experiment;


select 'table: experiment records containing words reagent or kit';
  
select
  count(library_construction_protocol)
from
  experiment
where
  library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %';



select 'table: experiment records containing words reagent or kit and URL';
 
select
  count(library_construction_protocol)
from
  experiment
where
  (library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %')
  AND library_construction_protocol like '%http://%';

select 'experiments contain the word "kit" or "reagent" AND are annotated for ALL three protocol steps';


select
  count(library_construction_protocol)
from
  experiment
where
  library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %'  
  AND
	(
	   library_construction_protocol like "% shear%"
	or library_construction_protocol like "% restriction%"
	or library_construction_protocol like "% digest%"
	or library_construction_protocol like "% fragment%"
	or library_construction_protocol like "% breaks %"
	or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
	or library_construction_protocol like "% acoustic%"
	or library_construction_protocol like "% sonic%"
	)
	and
	(
	   library_construction_protocol like "% adapter%"
	or library_construction_protocol like "% ligat%"
	or library_construction_protocol like "% blunt%"
	or library_construction_protocol like "% phosphorylat%"
	or library_construction_protocol like "% overhang"
	or library_construction_protocol like "% t4-PNK%"
	or library_construction_protocol like "% t4%"
	or library_construction_protocol like "% pnk%"
	or library_construction_protocol like "% kinase%"
	or library_construction_protocol like "% a-tail%"
	)
	and
	(
	   library_construction_protocol like "% clone%"
	or library_construction_protocol like "% clonin%"
	or library_construction_protocol like "% pcr %"
	or library_construction_protocol like "% amplif%"
	or library_construction_protocol like "% polymerase%"
	or library_construction_protocol like "% taq%"
	or library_construction_protocol like "% phusion%"
	or library_construction_protocol like "% temperat%"
	or library_construction_protocol like "% thermal%"
	or library_construction_protocol like "% anneal%"
	or library_construction_protocol like "% denature%"
	);


select 'Experiments containing the word "kit" or "reagent", and include a URL AND are annotated for ALL three protocol steps';  
  
select
  count(library_construction_protocol)
from
  experiment
where
  (
    library_construction_protocol like '% kit %'
    OR library_construction_protocol like '% reagent %'
  )
  AND library_construction_protocol like '%http://%'
  AND
	(
	   library_construction_protocol like "% shear%"
	or library_construction_protocol like "% restriction%"
	or library_construction_protocol like "% digest%"
	or library_construction_protocol like "% fragment%"
	or library_construction_protocol like "% breaks %"
	or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
	or library_construction_protocol like "% acoustic%"
	or library_construction_protocol like "% sonic%"
	)
	and
	(
	   library_construction_protocol like "% adapter%"
	or library_construction_protocol like "% ligat%"
	or library_construction_protocol like "% blunt%"
	or library_construction_protocol like "% phosphorylat%"
	or library_construction_protocol like "% overhang"
	or library_construction_protocol like "% t4-PNK%"
	or library_construction_protocol like "% t4%"
	or library_construction_protocol like "% pnk%"
	or library_construction_protocol like "% kinase%"
	or library_construction_protocol like "% a-tail%"
	)
	and
	(
	   library_construction_protocol like "% clone%"
	or library_construction_protocol like "% clonin%"
	or library_construction_protocol like "% pcr %"
	or library_construction_protocol like "% amplif%"
	or library_construction_protocol like "% polymerase%"
	or library_construction_protocol like "% taq%"
	or library_construction_protocol like "% phusion%"
	or library_construction_protocol like "% temperat%"
	or library_construction_protocol like "% thermal%"
	or library_construction_protocol like "% anneal%"
	or library_construction_protocol like "% denature%"
	);


--=========================================================================================

select 'table: study-experiment joined (total)';

select '(study-exp join)experiment:library_construction_protocol record count', count(*)
from
(
select
   stu.study_accession id, count(library_construction_protocol) from study as stu
join
  experiment as exp
	on exp.study_accession = stu.study_accession
	group by id
);

select 'studies mention Reagent or kit';

select '(study-exp join)experiment:library_construction_protocol record count', count(*)
from
(
select
   stu.study_accession id, count(library_construction_protocol) from study as stu
join
  experiment as exp
	on exp.study_accession = stu.study_accession
where
  library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %'
group by id
);

select 'studies mention a Reagent kit and provide a URL';

select '(study-exp join)experiment:library_construction_protocol record count', count(*)
from
(
select
   stu.study_accession id, count(library_construction_protocol) from study as stu
join
  experiment as exp
	on exp.study_accession = stu.study_accession
where
  (library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %')
  AND library_construction_protocol like '%http://%'
group by id
);

select 'studies mention reagent or kit AND are annotated for ALL three protocol steps';

select '(study-exp join)experiment:library_construction_protocol record count', count(*)
from
(
select
   stu.study_accession id, count(library_construction_protocol) from study as stu
join
  experiment as exp
	on exp.study_accession = stu.study_accession
where
  library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %'
  AND
	(
	   library_construction_protocol like "% shear%"
	or library_construction_protocol like "% restriction%"
	or library_construction_protocol like "% digest%"
	or library_construction_protocol like "% fragment%"
	or library_construction_protocol like "% breaks %"
	or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
	or library_construction_protocol like "% acoustic%"
	or library_construction_protocol like "% sonic%"
	)
	and
	(
	   library_construction_protocol like "% adapter%"
	or library_construction_protocol like "% ligat%"
	or library_construction_protocol like "% blunt%"
	or library_construction_protocol like "% phosphorylat%"
	or library_construction_protocol like "% overhang"
	or library_construction_protocol like "% t4-PNK%"
	or library_construction_protocol like "% t4%"
	or library_construction_protocol like "% pnk%"
	or library_construction_protocol like "% kinase%"
	or library_construction_protocol like "% a-tail%"
	)
	and
	(
	   library_construction_protocol like "% clone%"
	or library_construction_protocol like "% clonin%"
	or library_construction_protocol like "% pcr %"
	or library_construction_protocol like "% amplif%"
	or library_construction_protocol like "% polymerase%"
	or library_construction_protocol like "% taq%"
	or library_construction_protocol like "% phusion%"
	or library_construction_protocol like "% temperat%"
	or library_construction_protocol like "% thermal%"
	or library_construction_protocol like "% anneal%"
	or library_construction_protocol like "% denature%"
	)
 group by id
 );
 

select 'studies mention reagent or kit and provide a URL AND are annotated for ALL three protocol steps';
 
 
select '(study-exp join)experiment:library_construction_protocol record count', count(*)
from
(
select
   stu.study_accession id, count(library_construction_protocol) from study as stu
join
  experiment as exp
	on exp.study_accession = stu.study_accession
where
  (library_construction_protocol like '% kit %'
  OR library_construction_protocol like '% reagent %')
  AND library_construction_protocol like '%http://%'
  AND
	(
	   library_construction_protocol like "% shear%"
	or library_construction_protocol like "% restriction%"
	or library_construction_protocol like "% digest%"
	or library_construction_protocol like "% fragment%"
	or library_construction_protocol like "% breaks %"
	or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
	or library_construction_protocol like "% acoustic%"
	or library_construction_protocol like "% sonic%"
	)
	and
	(
	   library_construction_protocol like "% adapter%"
	or library_construction_protocol like "% ligat%"
	or library_construction_protocol like "% blunt%"
	or library_construction_protocol like "% phosphorylat%"
	or library_construction_protocol like "% overhang"
	or library_construction_protocol like "% t4-PNK%"
	or library_construction_protocol like "% t4%"
	or library_construction_protocol like "% pnk%"
	or library_construction_protocol like "% kinase%"
	or library_construction_protocol like "% a-tail%"
	)
	and
	(
	   library_construction_protocol like "% clone%"
	or library_construction_protocol like "% clonin%"
	or library_construction_protocol like "% pcr %"
	or library_construction_protocol like "% amplif%"
	or library_construction_protocol like "% polymerase%"
	or library_construction_protocol like "% taq%"
	or library_construction_protocol like "% phusion%"
	or library_construction_protocol like "% temperat%"
	or library_construction_protocol like "% thermal%"
	or library_construction_protocol like "% anneal%"
	or library_construction_protocol like "% denature%"
	)
 group by id
 );