Structured Query Language (SQL) - Design and Use of Relational Databases in Chemistry - page 24

Table 3.1 A Simple Structure Table

Smiles id mw Added

CC(=O)OC 1001 74.09 2007-06-20 13:35:32

NCC(=O)OC 1002 89.11 2007-06-20 13:38:05

C(N)CC(=O)OC 1003 2007-06-20 13:38:21

ways of getting large amounts of data into tables, using the SQL Copy
command or bulk-loading programs. These are not discussed here, but
examples of using the Copy command are shown in Chapter 11 and the
Appendix.
A sample table made using the above SQL Create and Insert com-
mands is shown in Table 3.1. Note that more rows have been added to this
table, not just the single row added with the Insert command above.

3 . 5 S e l e c t
Once data is inserted into a table, chosen rows and columns can be
selected. For example, the following SQL command:

Select smiles,mw From achemcompany.structure Where mw < 100;

selects smiles and molecular weight from all rows that have molecular
weight below 100. The Where clause of the SQL command can be quite
complex, involving many comparisons of many columns from many
tables.
An essential use of the Select command is to select data from dif-
ferent tables using the joining capabilities of RDBMS. Suppose there is
another table of assay data defined using

Create Table hiv1.prot_inh (
id Integer,
ic50 Float,
ki Float,
tested Timestamp(0));

When data is inserted into this table, the proper id from the structure
table is associated with the experimental data. In order to select structures
with chosen inhibition constant Ki i the hiv1.prot_inh table is joined with
the achemcompany.structure table. For example:

Select smiles,ki
From achemcompany.structure Join hiv1.prot_inh
On hiv1.prot_inh.id = achemcompany.structure.id
Where ki < 0.5;