Various Implementations

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour  24.  Extensions to Standard SQL


Various Implementations

There are numerous SQL implementations that are released by various vendors. All the relational database vendors could not possibly be mentioned; a few of the leading implementations, however, are discussed. The implementations discussed here are Sybase, dBASE, Microsoft SQL Server, and Oracle. Other popular vendors providing database products other than those mentioned previously include Borland, IBM, Informix, Progress, CA-Ingres, and many more.

Differences Between Implementations

Although the implementations listed here are relational database products, there are specific differences between each. These differences stem from the design of the product and the way data is handled by the database engine; however, this book concentrates on the SQL aspect of the differences. All implementations use SQL as the language for communicating with the database, as directed by ANSI. Many have some sort of extension to SQL that is unique to that particular implementation.

graphics/note_icon.gif

Differences in SQL have been adopted by various vendors to enhance ANSI SQL for performance considerations and ease of use. Vendors also strive to make enhancements that provide them with advantages over other vendors, making their implementation more attractive to the customer.


Now that you know SQL, you should have little problem adjusting to the differences in SQL among the various vendors. In other words, if you can write SQL in a Sybase implementation, you should be able to write SQL in Oracle. Besides, knowing SQL for various vendors accomplishes nothing less than improving your rsum.

The following sections compare the SELECT statement's syntax from a few major vendors to the ANSI standard.

The following is the ANSI standard:

 graphics/syntax_icon.gif SELECT [DISTINCT ] [*  COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE SEARCH_ CONDITION ] GROUP BY [ TABLE_ALIAS  COLUMN1 [, COLUMN2 ] [ HAVING SEARCH_CONDITION ]] [{UNION  INTERSECT  EXCEPT}][ ALL ] [ CORRESPONDING [ BY (COLUMN1 [, COLUMN2 ]) ] QUERY_SPEC  SELECT * FROM TABLE  TABLE_CONSTRUCTOR ] [ORDER BY SORT_LIST ] 

The following is the syntax for SQLBase:

 graphics/syntax_icon.gif SELECT  [ ALL  DISTINCT ] COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE SEARCH_CONDITION ] [ GROUP BY COLUMN1 [, COLUMN2 ] [ HAVING SEARCH_CONDITION ]] [ UNION [ ALL ]] [ ORDER BY SORT_LIST ] [ FOR UPDATE OF COLUMN1 [, COLUMN2 ]] 

The following is the syntax for Oracle:

 graphics/syntax_icon.gif SELECT [ ALL  DISTINCT ] COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE SEARCH_CONDITION ] [[ START WITH SEARCH_CONDITION ] CONNECT BY SEARCH_CONDITION ] [ GROUP BY COLUMN1 [, COLUMN2 ] [ HAVING SEARCH_CONDITION ]] [{UNION [ ALL ]  INTERSECT  MINUS} QUERY_SPEC ] [ ORDER BY COLUMN1 [, COLUMN2 ]] [ NOWAIT ] 

The following is the syntax for Informix:

 graphics/syntax_icon.gif SELECT [ ALL   DISTINCT  UNIQUE ] COLUMN1 [, COLUMN2 ] FROM TABLE1 [, TABLE2 ] [ WHERE SEARCH_CONDITION ] [ GROUP BY {COLUMN1 [, COLUMN2 ]  INTEGER} [ HAVING SEARCH_CONDITION ]] [ UNION QUERY_SPEC ] [ ORDER BY COLUMN1 [, COLUMN2 ] [ INTO TEMP TABLE [ WITH NO LOG ]] 

As you can see by comparing the syntax examples, the basics are there. All have the SELECT, FROM, WHERE, GROUP BY, HAVING, UNION, and ORDER BY clauses. Each of these clauses works conceptually the same, but some have additional options that may not be found in other implementations. These options are called enhancements.

Compliance with ANSI SQL

Vendors do strive to comply with ANSI SQL; however, none are 100 percent ANSI SQL-standard. Some vendors have added commands or functions to ANSI SQL, and many of these new commands or functions have been adopted by ANSI SQL. It is beneficial for a vendor to comply with the standard for many reasons. One obvious benefit to standard compliance is that the vendor's implementation will be easy to learn, and the SQL code used is portable to other implementations. Portability is definitely a factor when a database is being migrated from one implementation to another. Why would a company spend uncountable dollars on a conversion to another implementation that was not compliant to the standard? It probably wouldn't if too many changes would have to be made to the application and the new implementation was difficult to learn. Therefore, ANSI SQL compliance is not a problem in most cases.

Extensions to SQL

Practically all the major vendors have an extension to SQL. A SQL extension is unique to a particular implementation and is generally not portable between implementations. However, popular standard extensions are reviewed by ANSI and are sometimes implemented as a part of the new standard.

PL/SQL, which is a product of Oracle Corporation, and Transact-SQL, which is used by both Sybase and Microsoft SQL Server, are two examples of standard SQL extensions. Both extensions are discussed in relative detail for the examples during this hour.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net