Of SQLs

In this program the oratabs libref allows all of the tables found in that Oracle data instance to be treated like SAS data sets. This is a simple and fast way of accessing relational databases, and it requires no knowledge of SQL to implement. 

The contribution of this paper is the definition of four different kinds of components for distributed event collection, according to the way they interact with the database and with the outside world. It also generically defines three types of information that are handled by the application. This structure is very important in terms of security, because it allows a strong separation of privileges at the table and column level (for databases that support it), between insertions, modifications, access and deletion. We are also able to effectively leverage the consistency mechanisms of SQL (foreign keys and references) to ensure that event information remains consistent over time. 

Based on this, to implement the chase by means of SQL statements, as a preliminary step we replace existential variables in the conclusion by means of Skolem terms. More specifically, for each tgd m y. for each variable yi e J, and take as argument all universal variables that appear in the conclusion. 

OQL (object query language) A SQL-like query language with special features dealing with complex objects, values, and methods. OQL is an object-based version of SQL, and the key differences between OQL and traditional SQL are (1) OQL has the ability to support object referencing within tables and allows objects to be nested within objects, (2) not all SQL keywords are supported within OQL such as irrelevant keywords removed from the syntax, and (3) OQL has the ability to perform mathematical computations from within OQL statements. 

SQL-X An extension of SQL that provides a set of operators to compose queries against a relational database, producing complex XML documents with a style reminiscent of report generation languages. 

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. 

One type of SQL function is simply a collection of SQL statements. The input data type must be defined along with the data type of the result of... 

Another use of an aggregate function depends on the use of the Group by clause of SQL. The following SQL will return multiple rows. 

There are other features of SQL that are useful for chemical relational databases. Domains, triggers, and views are objects that belong to a schema just as tables and functions. These are also discussed in later chapters that focus on practical uses. 

For routine database maintenance such as backup, each RDBMS provider typically supplies other command line programs. For example, PostgreSQL provides the pg dump program that outputs a file of SQL commands containing the definition and data contained in each table, schema, etc., in the database. This file becomes a backup of the database, which can be restored using the psql command to execute the SQL commands in the file. 

Figure 5.1 shows a sample Web page from phpPgAdmin. The left frame is interactive, allowing the user to view and select various database, schemas, tables, functions, etc. The right frame typically shows table data, results of SQL commands, or interactive Web forms allowing operations on the database. 

The substances in PubChem are available as a set of sdf files. The data in these files can be read by a wide variety of programs.5 The one most directly useful here produces a file of SQL commands to create a table and copy data into it. This sdf2sql program is available online.6 Using the PubChem file Substance 00000001 00025000.sdf.gz, the output of sdf2sql produces the following ... 

Many other uses of the xform function are possible. Because the function is an extension of SQL, it can be easily used with all the other features of the SQL language and capabilities of an RDBMS. 

The < and > operators of SQL can also be used with range data. However, since the conversion from range to float returns null for range data containing < and >, the following SQL only selects exact range values. 

Consider extending SQL with new functions. This might be considered the fundamental suggestion in this book. There are many useful functions built into SQL, but sometimes a simple extension function can allow an SQL operation to run completely on the database server without having to pass data to the client. For example, to sort selected rows by and value in a column requires only simple SQL. If the data needed to sort the rows is not part of data being selected, consider writing a function that will provide the value to be sorted. For example, if it were necessary to sort by the number of atoms in a molecule, a natoms (smiles) function could be used in the order clause of SQL. 

A SMILES files is readily imported into the database using the following perl script smiloader. The output of this command is a set of SQL commands interspersed with lines in the input SMILES file. The file is minimally processed. The script expects the name of a schema in which the tables will be created. The entire perl script is shown in the Appendix. It is used at the linux command line as follows. The schema name here is drugs, the first argument to smiloader. 

Sq6 is at 630 nm and is red-shifted from that of Sql by 2.4 nm. The red-shift, although small, nevertheless indicates that the F substituent increases the CT character of the chromphore and results in a spectral red-shift. Very similar small red-shifts are also obtained for fluorinated V-benzyl squaraines Sq20 and Sq21 and fluorinated V-pyrrolidino-substituted squaraine Sq23. 

Figure 1 Corrected fluorescence excitation and emission spectrum of Sql in CH2CI2 (cone. 3 X 10" M).

The ( )p of Sql to Sq5 are in the range of 0.65 to 0.74. After correction for the y-emission, the quantum yields for the (a -f P) band, ( ) 3, range from 0.58-0.66. As outlined in Scheme 2, the major radiationless decay process for the excited states of squaraine is by rotation of the C-C bond between the phenyl ring and the four membered ring, the small increase in < )p may be attributable to the decrease in rate of rotation of the iV,iV-dialkylanilino group as its size increases. 

---