Informix Guide to SQL: Tutorial Chapter 1: Informix Databases

Home

Contents

Index

Master Index

New Book

Structured Query Language Most computer software has not yet reached a point where you can literally ask a database, "What orders have been placed by customers in New Jersey with ship dates in the second quarter?" You must still phrase questions in a restricted syntax that the software can easily parse. You can pose the same question to the demonstration database in the following terms: SELECT * FROM customer, orders WHERE customer.customer_num = orders.customer_num AND customer.state = 'NJ' AND orders.ship_date BETWEEN DATE('7/1/94') AND DATE('7/30/94') This question is a sample of Structured Query Language (SQL). It is the language that you use to direct all operations on the database. SQL is composed of statements, each of which begins with one or two keywords that specify a function. The Informix implementation of SQL includes about 76 statements, from ALLOCATE DESCRIPTOR to WHENEVER. All the SQL statements are specified in detail in the Informix Guide to SQL: Syntax. Most of the statements are used infrequently, when you set up or tune a database. Most users generally use only three or four statements to query or update databases. One statement, SELECT, is in almost constant use. SELECT is the only statement that you can use to retrieve data from the database. It is also the most complicated statement, and the next two chapters of this book explore its many uses. Standard SQL SQL and the relational model were invented and developed at IBM in the early and middle 1970s. Once IBM proved that it was possible to implement practical relational databases and that SQL was a usable language for manipulating them, other vendors began to provide similar products for non-IBM computers. For reasons of performance or competitive advantage, or to take advantage of local hardware or software features, each SQL implementation differed in small ways from the others and from the IBM version of the language. To ensure that the differences remained small, a standards committee was formed in the early 1980s. Committee X3H2, sponsored by the American National Standards Institute (ANSI), issued the SQL1 standard in 1986. This standard defines a core set of SQL features and the syntax of statements such as SELECT. Informix SQL and ANSI SQL The SQL version that Informix products support is highly compatible with standard SQL (it is also compatible with the IBM version of the language). However, it does contain extensions to the standard; that is, extra options or features for certain statements, and looser rules for others. Most of the differences occur in the statements that are not in everyday use. For example, few differences occur in the SELECT statement, which accounts for 90 percent of the SQL use for a typical person. However, the extensions do exist and create a conflict. Thousands of Informix customers have embedded Informix-style SQL in programs and stored queries. They rely on Informix to keep its language the same. Other customers require the ability to use databases in a way that conforms exactly to the ANSI standard. They rely on Informix to change its language to conform. Informix resolved the conflict with the following compromise: The Informix version of SQL, with its extensions to the standard, is available by default. You can ask any Informix SQL language processor to check your use of SQL and post a warning flag whenever you use an Informix extension. This resolution is fair but makes the SQL documentation more complicated. Wherever a difference exists between Informix and ANSI SQL, the Informix Guide to SQL: Syntax describes both versions. Because you probably intend to use only one version, simply ignore the version you do not need. ANSI-Compliant Databases Use the MODE ANSI keywords when you create a database to designate it as ANSI compliant. Within such a database, certain characteristics of the ANSI standard apply. For example, all actions that modify data automatically take place within a transaction, which means that the changes are made in their entirety or not at all. Differences in the behavior of ANSI-compliant databases are noted where appropriate in the Informix Guide to SQL: Syntax. GLS Databases GLS The Version 7.2 and later Informix database server products provide Global Language Support (GLS). In addition to U.S. ASCII English, GLS allows you to work in other locales. You can use GLS to conform to the customs of a specific locale. The locale files contain unique information such as various money and date formats and multibyte characters used in identification or data names.