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

Table 6.1 Selected Amines and Acids from nci.structure Tables

Amines Acids

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O c1cc(oc1)C(=O)C(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(CCC(=O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCC1C2CCC3=CC(=O)CCC3C2CCC1(C)C(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O C1CC(OC1)CCC(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(C)(C(=O)O)NC(=O)N(CCCl)N=O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCNC(CC(=O)N)C(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCc1ccc(cc1)NC(=O)C=CC(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O C(=O)(C(=O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O C=COC=C.C(=CC(=O)O)C(=O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCCCCCC(C(=O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O c1cc(cc(c1)Cl)C(C(=O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(C(=O)O)NC(=O)C(F)(F)F

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(C)CC(C(=O)O)N.C(CC(C(=O)O)N)CN

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(CCC(C(=O)O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O COc1ccc(cc1CC(=O)O)F

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCOC(=O)NNCC(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O c1ccc(cc1)COC2CCC(CC2)(CC(=O)O)C(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCCC(C(=O)NCC(=O)O)NC(=O)OCc1ccccc1

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O c1ccc2c(c1)CN3C(=O)CCC3(C2=O)CCC(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O C(C(C(C(=O)O)N)O)O

Table 6.2 shows the first few rows selected by this SQL statement. This
is the result that was desired: a total of 96 compounds consisting of a com-
bination of 8 amines and 12 acids. These separate select statements are
typically called subselect clauses of an SQL statement. They are enclosed
in parentheses and named uniquely. They function as if they were tables
themselves, but are actually subsets, or subselects of a table. There can be
any number of subselects in an SQL statement and the subselect clause
itself can be more complex than shown above. For example, one might also
restrict the selected amines by molecular weight, vendor, or other criteria.

6.8 Views
As discussed in Chapter 3, a view is a subset of a table defined by a select
statement. This is quite similar to the subselect statement discussed above.
Such subselect statements are sometimes also called in-line views . Here we
discuss the use of views as a persistent way to store subselect statements
for use in SQL statements.
For example, a training _ set and a testing _ set could be
selected at random from a single table of values as follows: