Standard SQL

SQL was first standardized by ANSI in 1986. Updated standards are referred to as SQL92, SQL99, and SQL2003. Most RDBMS conform to the SQL92 standard, including some features from later standards. Most also provide additional features not addressed in standard SQL at all. Sometimes the differences among the RDBMS are simply minor syntax differences, but sometimes there are more fundamental differences. 

No RDBMS conforms exactly and completely to any SQL standard. For this reason, books on SQL almost always concentrate on one particular RDBMS. One notable exception documents every SQL command and details differences across different RDBMS.7 While that book discusses differences in detail, this book discusses concepts of RDBMS in general, so that any RDBMS could be used to implement the methods described here. When specific SQL examples are given however, the SQL statement uses PostgreSQL syntax. Many times this is identical to the syntax for other RDBMS, or there is only a minor difference. 

Some of the more advanced methods described in this book require a more specific use of the RDBMS. The choice made for this book is PostgreSQL. In cases where a particular feature of PostgreSQL is used, a note is added to alert the reader. For example, the array data type in SQL2003 is implemented in PostgreSQL very differently than in Oracle. The list matches function described in a later chapter of this book returns an array of integers that denote which atoms in a structure match a substructure query. The integration of this function into SQL would be handled quite differently in PostgreSQL, Oracle and MySQL. 

---

In the case of the chemistry database, the situation is more complex. Compound data cannot be queried or updated using standard SQL. The read and write operations are encapsulated in vendor-specific Oracle data cartridges. Therefore, the Data Persistence Layer has another responsibility— hide vendor specifics with a standard interface so that the Business Layer does not care about vendor variations. 

Pharmacophore profiles are defined that represent the set of all the pharmacophores found across the conformers of a series of conformers or series of molecules. Each pharmacophore added to the profile has to be unique. This profile will help in showing the spread of pharmacophores across the conformational space of a molecule or a series of molecules. No sum of the exhibited pharmacophores or normalization is done. There is no direct graphical representation of pharmacophore models. The pharmacophores can be saved to a file in CSV format that can be imported into a MySQL or Oracle database. This approach permits the use of standard SQL queries to extract common pharma-... 

The overall architecture of DiscoveryLink is common to many heterogeneous database systems. Applications connect to the DiscoveryLink server using a variety of standard database client interfaces, such as Open Database Connectivity or Java Database Connectivity, and submit queries to DiscoveryLink in standard SQL. The information required to answer the query comes from one or more data sources, which have been identified to DiscoveryLink through a process called registration. 

Once this function is created, it can be used just like any standard SQL function. This function uses PostgreSQL syntax. 

In standard SQL, a text column may contain data, or not. The null value is used to denote that no value is contained in that column of any particular row. The null value can be used in SQL statements, for example ... 

It is also possible to define other constraints on data in tables. For example, molecular weight might be stored as a numeric value. While the standard SQL numeric constraint applies, it might be desirable to also constrain molecular weight values to be positive values. This can be readily done after the column is defined using the following SQL. 

Notice that there are several ways in which SMILES could be written for the same structure, even the simplest ones. For example, hydrogen cyanide can be written as C N or N C, propene is either C=CC or CC=C. More complex structures can have three or many more SMILES that represent the same structure. If there were one standard way to write SMILES, then standard SQL text comparisons could be used to locate any particular structure. SMILES would become a uniquely spelled "name" for each unique structure. Canonical SMILES does just that. Using rules about which atoms should come before other atoms in the spelling of each SMILES, a unique name for each molecular structure can be provided.6... 

Standard SQL data types, such as integers, float, and text, are useful for storing scientific data, such as counts, measurements, and names. 

The standard SQL data type Text has been used to store SMILES. This is appropriate because every SMILES is a valid text string. But not every text string is a valid SMILES. Without additional information about SMILES, the RDBMS cannot enforce any rules about which text strings ought to be in a column intended to contain SMILES. 

The bit or bit varying data type in standard SQL will be used in the examples in this and following chapters. Oracle does not support this data type. PostgreSQL syntax will be used. 

Suppose it is decided that the valence 5, noncharge-separated representation of the nitro group is to be used throughout the database. The SMIRKS [0 2]=[N+ 1][0- 3] [0 2]=[N+0 1]=[0+0 3], when applied to any charge-separated nitro group will transform it into the proper form. This is accomplished by creating another new SQL function, xform(smiles, smarts). As with the cansmiles and matches functions, this is an extension to standard SQL. Some form of this transformation function is... 

Chapter 3 showed how SQL could be used to write a function to convert pressure data values expressed in atmospheres to kilopascals. Other functions were used in check constraints on a column containing CAS numbers. This chapter will show how new data types can be defined. This will require functions to define the method for input parsing and the method to output data values. There will also be functions to define operations on the new data types, enabling searches to be integrated easily with standard SQL syntax. 

Since the range data type is not a standard SQL data type, the standard SQL operators cannot be used with this data type. However, new operators can be defined using SQL functions. Since the range data type is so similar to the float data type, implicit conversion to float is appropriate. This automatically allows many of the standard SQL operators to work with exact(=) range values. 

For every standard SQL data type available in PostgreSQL, there is a corresponding array data type. While it is possible to define a composite data type for coordinates, consider using the array data type. For example ... 

It is possible to write PostgreSQL functions in C. Because PostgreSQL itself is written in C, any extension functions can take advantage of the internal representation of RDMBS data. In fact, examples on which C functions might be based are the very functions used by the PostgreSQL RDMBS for processing standard SQL data. This puts any C functions on an equal footing with any built-in SQL functions. For this reason, C functions are the best choice when issues of speed are important. 

Another important aspect of objects is the methods that operate on them. An ORDBMS calls these functions, but the effect is the same. As shown in this chapter, the functions defined for new data types enable them to be integrated in the SQL language and handled just like standard SQL data types. This can be by casting the data type, for example, to allow range data to be treated as float. New functions that operate exclusively on range data can be defined, such as range cmp. The ability to define operators of new data types enhances their usability and integration into SQL even more. 

Have a communication management that ensures that important information reaches decision-makers with minimal delay. Access to data within LIMS may be achieved through a wide variety of mechanisms including the industry standard SQL. Information can be communicated using all common networks... 

This is a normal SQL statement except for the fact that STRUCTURE is not a standard SQL data type. When the ADT shell command processor discovers from the data dictionary that the STRUCTURE column is a chemical structure it invokes the molecule editor and prompts the user to enter the desired structure. Following structure entry, the search is performed and any COMPOUND table row retrieved from the database is displayed, including the structure. 

The LIKE operator is used here to request a similar-structure search and the IN FILE predicate is an extension to the standard SQL IN predicate that allows files of registry numbers to be manipulated. In this case LISTl is a list of registry numbers saved from a previous search. 

For each sjmonyn, a normalised form is stored in which punctuation and blanks are removed, and lower case letters are converted to upper case. When a name search is requested, the query name is normalised and then looked up in the supporting table which is indexed on normalised name. Fast searches are thus provided when a complete name or name prefix is entered as the query. Name substring searching is handled by a slow linear search technique using the standard SQL LIKE operator. The original unnormalised name is used for display and hardcopy output. 

Tinelli, E., Cascone, A., Ruta, M., et aL I.M.P.A.K.T. an innovative semantic-based skill management system exploiting standard SQL. In ICEIS 2009—11th International Conference on Enterprise Infomiation Systems, Proceedings, pp. 224—229 (2009)... 

---