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

structure hiv_prot

FK id * smiles TEXT INTEGER

PK id * INTEGER ic50 NUMERIC

mw
added

NUMERIC
TIMESTAMP

ki NUMERIC

tested TIMESTAMP

Figure 3.2 Entity relationship diagram for structure and hiv_prot tables.

Both tables are named in the SQL command, the chosen Ki i limit is given
and the Join condition On hiv1.prot _ inh.id = achemcompany.
structure.id ensures that the proper rows of each table are joined. Note
that the hiv1.prot _ inh table resides in a schema different from the
achemcompany.structure table. Of course, this is not a requirement
but is done for convenience and clarity during the design of the database.
It is possible to leave out the schema name in the On clause because there
is no ambiguity of table names. It is also possible to include the schema
and table name along with the columns named smiles and ki . It is sim-
ply a matter of programming style. However, the schema name must be
used for the table names in the From clause. Figure 3.2 illustrates the two
tables and the relationship between them using the column named id.
There is an alternative form of SQL that is commonly used to Join
tables. This alternative form is so common, especially in older SQL, that
it must be mentioned here. The following SQL accomplishes exactly the
same join described above, but uses a different syntax.

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

Notice that the Join keyword is not used, but rather just a comma sepa-
rates the two tables being joined. The Join condition becomes a part of
the where clause. The former syntax using the Join keyword will be used
in examples throughout this topic.

3.6 Update and Delete
Once data has been inserted into a table, it may become necessary to
update it. Suppose, for example, that a compound molecular weight had
been entered incorrectly, or not at all for some particular compound. The
following SQL command would update one row in the structure table.

Update achemcompany.structure Set mw=103.14 where id=1003;

Notice the use of the Where clause, similar to its use in the Select state-
ment. It is important to use Where with Update. Otherwise every row of