Some Queries For Data Retrieval From StARlite

It is a public holiday in the UK today (we call these bank holidays, for reasons that seem obscure nowadays). The weather is traditionally bad on such days, and today is no exception, at least the remainder of the week, when we return to work, will be fine and sunny.

We will run a further StARlite schema and query walkthrough webinar shortly, but in the meantime here are some skeleton sql queries, that perform a set of related queries retrieving compounds/bioactivities for a given target. In this case the target is human PDE4A (for which the tid is 3), and human PDE5A (for which the tid is 276). We will walk through getting these unique target identifiers (or tids) on another occasion, but suffice it to say, that this is easy, especially programmatically, using blastp.

Firstly, retrieving a set of potent inhibitors of human PDE4A or PDE5A. There are a number of parameters one needs to set to actually do this (the end-point, the affinity cutoff, etc. Specifically here we have selected high confidence assay to target assignments (the a2t.confidence=7 bit), and where the potency is better than 1000nM for an IC50 measurement. This is a pretty generic query, and piping in the target tid to this covers a surprisingly frequent use the the data.

select  act.molregno, act.activity_type, act.relation as operator, act.standard_value, act.standard_units, 
   td.pref_name, td.organism, 
   a.description as assay_description, 
   docs.journal, docs.year, docs.volume, docs.first_page, docs.pubmed_id, cr.compound_key
from  target_dictionary td, 
   assay2target a2t,    
   assays a, 
   activities act, 
   docs, 
   compound_records cr
where  td.tid in (3,276)
and  td.tid = a2t.tid
and  a2t.confidence = 7
and  a2t.assay_id = a.assay_id
and  a2t.assay_id = act.assay_id
and  act.doc_id = docs.doc_id
and  act.record_id = cr.record_id
and  act.activity_type = 'IC50'
and  act.relation in( '=', '<')
and  act.standard_units = 'nM'
and  act.standard_value <=1000
and  a.assay_type = 'B';

Here is a modified form to retrieve just the compound identifiers (molregno)

select  distinct act.molregno
from  target_dictionary td, 
  assay2target a2t,    
  assays a,  
  activities act
where  td.tid in (3,276)
and  td.tid = a2t.tid
and  a2t.confidence = 7
and  a2t.assay_id = a.assay_id
and  a2t.assay_id = act.assay_id
and  act.activity_type = 'IC50'
and  act.relation in( '=', '<')
and  act.standard_units = 'nM'
and  act.standard_value <=1000
and  a.assay_type = 'B';

Also a common requirement is to get the associated molecule structures from the database - here the syntax is for an sdf format output and the query does not rely on any fancy chemical cartridge manipulation (since we store the molfiles in a clob called molfile in the COMPOUNDS table). The query here simply retrieves the structures, and not the associated bioactivity data. The goofy looking concatenations (||) and newlines (chr(10)) just make sure that a validly formatted sdf file emerges at the end.

select  c.molfile || chr(10) || '> ' ||chr(10)|| c.molregno||chr(10)||chr(10)||'$$$$'||chr(10)
from  compounds c, 
  (select distinct act.molregno
  from  target_dictionary td, 
    assay2target a2t,    
    assays a, 
   activities act
 where  td.tid in (3,276)
 and  td.tid = a2t.tid
 and  a2t.confidence = 7
 and  a2t.assay_id = a.assay_id
 and  a2t.assay_id = act.assay_id
 and  act.activity_type = 'IC50'
 and  act.relation in( '=', '<')
 and  act.standard_units = 'nM'
 and  act.standard_value <=1000
 and  a.assay_type = 'B') t1
where  t1.molregno = c.molregno;