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