Select Statement  «Prev  Next»

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

SQL-92 Standards

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.
changes from SQL 96 to SQL 2003
The table above describes changes from SQL 96 to SQL 2003

Significant Changes which occurred after SQL 2003

After the release of SQL 2003, there have been several significant changes to the SQL language. Here are some of the most notable changes:
  1. SQL 2008: The SQL 2008 standard introduced several new features, including support for spatial data types, improved support for XML data, and new data types for storing dates and times.
  2. SQL 2011: The SQL 2011 standard introduced support for temporal data, which allows for the tracking of changes to data over time.
  3. SQL 2016: The SQL 2016 standard introduced support for JSON data, making it easier to work with semi-structured data in SQL.
  4. SQL 2019: The SQL 2019 standard introduced several new features, including support for graph data, improved support for big data and machine learning, and enhanced security features.
  5. NoSQL databases: In recent years, there has been a trend towards NoSQL databases, which offer a different approach to data storage and retrieval than traditional SQL databases. NoSQL databases are designed to handle large volumes of unstructured data and are often used in big data and real-time applications.

In addition to these changes, there have also been several vendor-specific extensions to the SQL language, such as Microsoft's T-SQL and Oracle's PL/SQL, which add additional functionality to the language. Overall, the SQL language has continued to evolve and adapt to new data storage and retrieval needs, and is still widely used today in a variety of applications and industries.

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.

Fundamentals of Database Systems