Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 64

Rather than individual program statement or lines, SQL combines clauses
into one statement to produce a result. A simple select statement might
look like this:
Select id,smiles,mw From atable Where mw < 500;
This would return the id , smiles and molecular weight for the com-
pounds of interest. One added complexity might require the use of paren-
theses to specify the correct match. For example:
Select id,smiles,mw,logp From atable Where
(logp > 0 And mw < 500) Or
(logp < 0 And mw < 580);
This kind of complexity is straightforward and will not be considered
further.
When the desired data is in two different tables, a join is required.
It is often helpful to begin to develop complex SQL statements by consid-
ering one table at a time. For example, data from the nci _ h23 table of
pubchem schema was considered earlier in this chapter. The experimen-
tal data to be selected from that table was the substance id (called sid in
the table), activity _ outcome , log_gi50_M and n.log_gi50_
ugml . This is accomplished by the simple SQL
Select
n.sid, n.activity_outcome, n."log_gi50_M", n.log_gi50_ugml
From
nci_h23 n
Where activity_outcome = 2
Order By sid;
Notice that a table alias n is used to refer to the table nci _ h23 . This
shorthand notation will make it easier to express complex SQL when more
and more table joins are used. This statement is spread over several lines
with indentation in order to locate and modify SQL clauses more easily.
Along with these experimental results, information about the sub-
stance is desired. This information is in the substance table, indexed by
substance _ id . The SQL statement discussed previously is modified
as follows.
Select
n.sid, n.activity_outcome, n."log_gi50_M", n.log_gi50_ugml,
s.ext_datasource_name, s.ext_datasource_regid
From
nci_h23 n
Join substance s On s.substance_id = n.sid
Where activity_outcome = 2
Order By sid;