Select Statement  «Prev  Next»

Lesson 4 SQL-92
Objective Introduction to SQL-92?

SQL-92

With all the power of the Structured Query Language to retrieve information, several key software vendors figured out that it would be smart to use this approach for queries. It quickly became apparent that a standards committee was needed to determine what the standard is and how to apply it. The ANSI standards group is charged with standardizing the language elements that make up SQL.
The current accepted revision is SQL-92, which is what you will typically find implemented in different mainstream products.

What is important to understand about the standard is that there is one.
The language steps and statements that you learn that relate to SQL, by and large, will be applicable across mainstream database applications.
One of the leverage points for SQL is the fact that you can use what you know on more than one platform.
This is not to say that there are not any differences between platforms. Vendors typically take the standard and extend it to provide enhanced data services that they believe are necessary to serve their customer base. Things that will change may include minor variances in how you refer to information in your query and how the information is returned to you when the results have been determined.
It gives them a light to steer by, a direction to follow. The extensions developed are just that. They are extensions to the underlying database capabilities. The standard language elements and approach remain intact.
In the next lesson, we will take a look at dialect variations.

Significant new features of SQL 1992:

  1. New data types defined: DATE, TIME, TIMESTAMP, INTERVAL, BIT string, VARCHAR strings, and NATIONAL CHARACTER strings.
  2. Support for additional character sets beyond the base requirement for representing SQL statements.
  3. New scalar operations such as string concatenation and substring extraction, date and time mathematics, and conditional statements.
  4. New set operations such as UNION JOIN, NATURAL JOIN, set differences, and set intersections.
  5. Conditional expressions with CASE. For an example, see Case (SQL).
  6. Support for alterations of schema definitions via ALTER and DROP.
  7. Bindings for C and Ada.
  8. New features for user privileges.
  9. New integrity-checking functionality such as within a CHECK constraint.
  10. A new information schema, read-only views about database metadata like what tables it contains, etc. For example,
    SELECT * FROM  DATABASE_CREATION_ARCHITECTURE;
    
  11. Dynamic execution of queries (as opposed to prepared).
  12. Better support for remote database access.
  13. Temporary tables; CREATE TEMP TABLE etc.
  14. Transaction isolation levels.
  15. New operations for changing data types on the fly via CAST (expr AS type).
  16. Scrolled cursors.
  17. Compatibility flagging for backwards and forwards compatibility with other SQL standards.

Understanding SQL Standards

As with databases, IBM did a lot of the original SQL work. However, a lot of other vendors took the IBM standard and developed their own versions of it. Having differing dialects of SQL poses a challenge for the developer, and in 1986 it was adopted by the standards body the American National Standards Institute (ANSI) and in 1987 by the International Standards Organization (ISO), who created a standard for SQL. Although this has helped minimize differences between the various SQL dialects, there are still differences between them. The following table gives a brief summary of the various standards and updates to those standards.
The table above describes changes from SQL 96 to SQL 2003

This course concentrates on SQL-92, SQL-99, and SQL-2003 because most of their features have been implemented by most relational database management systems. The SQL you write works on most RDBMSs with only minor modifications. There are times when the various RDBMSs do things so differently that compatible code is impossible without making a change to the SQL you have written. However, these instances are few and far between.
Although standards are important to help bring some sort of commonality among the various implementation of SQL, at the end of the day what works in practice is what really counts. Instead of endlessly debating standards, this course provides information to help you in the real world of databases.