Developing Complex SQL

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  

This would return the id, smiles and molecular weight for the compounds of interest. One added complexity might require the use of parentheses 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 considering one table at a time. For example, data from the nci h23 table of pubchem schema was considered earlier in this chapter. The experimental 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 

---