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

structure
smiles
id
mw
added

TEXT
INTEGER
NUMERIC
TIMESTAMP

Figure 3.1 Simple entity relationship diagram for structure table.

timestamp into the added column. There are ways to improve the integ-
rity of the data in this table, using uniqueness constraints, sequences,
and user-defined functions to ensure proper SMILES syntax. These are
discussed in later chapters. A simple entity-relationship diagram for this
table is shown in Figure 3.1.
SQL is case-insensitive. Table names structure , STRUCTURE , or
Structure each refer to the same table. Likewise, the command Create
is the same as the command CREATE or create . If you wish to use case-
sensitive names for table, schema, or column names, use double quotes,
for example:

Create Table "aChemCompany".structure (
smiles Text,
"ID" Integer,
"MW" Numeric(6,2),
added Timestamp(0));

The data in the table is stored exactly as entered, preserving upper and
lower case. SQL command keywords, such as Create and Integer , are
arbitrarily shown in this topic with an initial capital letter when SQL com-
mands are shown.

3 . 4 I n s e r t
Data is inserted into a table using the SQL insert command. For example:

Insert Into achemcompany.structure (id, smiles, mw, added)
Values (1001, 'CC(=O)OC', 74.09, current_timestamp);

Notice that single quotes are required for text values and that the built-
in SQL function current _ timestamp can be used to supply a valid
timestamp. The order of the columns need not be the same as the order
used in the create command. However, the order of the data in the
Values clause of the Insert command must correspond exactly with
the order of the columns as named in the command. There is a short-cut
syntax of the Insert command that does not require column names. It
should be avoided in favor of the syntax example above. There are other