SQL-92
Source: ISO/IEC 9075:1992(E) Information technology - Database languages - SQL.
The SQL-92 standard was accepted in 1992. The standard is different from the other three standards in that it contains much more functionality. At the time of writing, SQL-92 is not fully implemented by any vendor, and serves the important function of defining a standard for implementation of new features.
The standard is divided into the following levels: entry, intermediate and full SQL-92, along with a transitional level (defined by FIPS) covering some features of intermediate level.
Entry level contains the set of features that define basic SQL-92 compliance.
Note: When vendors claim they are SQL-92 compliant, it is important to know at which level.
Note: Mimer SQL complies fully with entry level and transitional level SQL-92 (verified by using the NIST test suite).
Notation
The standard features listed below in this section are marked with different bullets to clarify exactly which features are implemented in Mimer SQL and which are not.
The bullet marks should be interpreted as follows:
+ Denotes a feature in the SQL standard that is implemented in Mimer SQL
- Denotes a feature in the SQL standard that is not yet implemented in Mimer SQL.
Transitional Level SQL-92
Transitional level SQL-92 includes the following additions:
+ support for dynamic SQL
+ requirement for an accessible
INFORMATION_SCHEMA supporting the
TABLES,
VIEWS,
COLUMNS,
TABLE_PRIVILEGES,
COLUMN_PRIVILEGES and
USAGE_PRIVILEGES views
+ support for schema definition and manipulation statements
+ support for various join features including all provisions for
NATURAL JOIN,
INNER JOIN,
LEFT OUTER JOIN,
RIGHT OUTER JOIN
+ support for data types
DATE,
TIME,
TIMESTAMP and
INTERVAL, including various datetime and interval features (excluding time zones)
+ support for
CHARACTER VARYING,
CHAR VARYING and
VARCHAR data types and various character data operations and functions
+ the
TRIM function as an alternative for a character value function in string value function
+ support for specifying
UNION in a view definition
+ support for implicit numeric casting when an approximate numeric value is assigned to an exact numeric type
+ support for implicit character casting when character string values are assigned to character string types
+ support for general use of the
SET TRANSACTION options for
ISOLATION LEVEL, READ_ONLY,
READ_WRITE and
DIAGNOSTIC SIZE
+ support for
GET DIAGNOSTICS
+ relaxation of restrictions concerning grouped operations
+ support for the
qualifier.* construction in select lists
+ use of lowercase letters allowed in identifiers
+ table columns involved in a
UNIQUE or
PRIMARY KEY constraint are no longer required to be explicitly declared as
NOT NULL
+ support for separation of schema name and authorization name in a schema definition, allowing multiple schemas per user
+ support for multiple, separately compiled, modules
+ support for delete actions in referential constraints
+ provision for the use of the
CAST function with all supported data types
+ support for value expressions in
INSERT statements
+ support for the specification of explicit default values in
INSERT and
UPDATE statements and in a row value constructor
+ relaxation of certain keyword restrictions defined in Entry level SQL-92 (AS permitted before a correlation name in a table reference,
TABLE permitted in a
GRANT statement,
FROM permitted in a
FETCH statement,
WORK becomes optional in
COMMIT and
ROLLBACK statements).
Intermediate Level SQL-92
Intermediate level SQL-92 includes the following additions:
+ support for the definition and use of domains
+ support for
CASE expressions
+ support for compound character literals
+ support of the specification of a general character value expression for the match value in
LIKE predicates
- support for the
UNIQUE predicate
- support for table operations in query expressions
+ support for the schema definition statement, including circular references in schema elements
+ support for
CURRENT_USER and
SESSION_USER
- support for
SYSTEM_USER
+ inclusion of the following
INFORMATION_SCHEMA views:
TABLE_CONSTRAINTS,
REFERENTIAL_CONSTRAINTS,
DOMAINS,
DOMAIN_CONSTRAINTS,
CHECK_CONSTRAINTS,
ASSERTIONS,
KEY_COLUMN_USAGE,
VIEW_TABLE_USAGE,
SCHEMATA,
VIEW_COLUMN_USAGE,
CONSTRAINT_TABLE_USAGE,
CONSTRAINT_COLUMN_USAGE,
COLUMN_DOMAIN_USAGE,
CHARACTER_SETS and
SQL_LANGUAGES
- support for the accessible base table
INFORMATION_SCHEMA_CATALOG_NAME
+ support for subprograms
+ support for Entry and Intermediate SQL flagging
+ support for schema manipulation including
DROP SCHEMA and
ALTER TABLE operations
+ support for long identifiers (up to 128 characters)
- support for
FULL OUTER JOIN
- support for time zones and timezone management
- support for
NATIONAL CHARACTER
+ support for the declaration of scrolled cursors and for fetch orientation in a
FETCH statement
+ removal of various restrictions concerning certain set function operations
- support for the definition and use of character sets
- support for the named character sets
SQL_CHARACTER,
ASCII_GRAPHIC,
LATIN1,
ASCII_FULL and
SQL_TEXT
- support for the use of a scalar subquery in any value expression
+ extending the
NULL predicate to allow values other than a column reference
+ support for user-defined names for constraints
+ support for the documentation schema tables
SQL_FEATURES and
SQL_SIZING.
Full SQL-92
Full SQL-92 includes the following additions:
+ support for full dynamic SQL
- support for the
BIT data type
- support for
ASSERTION constraints
- support for temporary tables
+ support for specification of precision in
TIME and
TIMESTAMP data types
+ full support for general use of value expressions
- support for truth value tests of
TRUE,
FALSE or
UNKNOWN and their negations
+ full support of the character functions
POSITION,
LOWER and
UPPER
- support for the specification of a derived table in a
FROM clause
+ trailing underscore is permitted in an identifier
+ removal of restrictions on the data types permitted for indicator parameters and variables
- removal of restrictions on the order of column names in a referential constraint definition
+ support for complete Entry, Intermediate and Full SQL flagging
- support for the use of table value constructors and row value constructors in predicate and in the
INSERT statement
- support for catalog name qualifiers
- support for simple or explicit table references in a query expression
- support for sub-queries in a
CHECK constraint
- support for
UNION JOIN and
CROSS JOIN
- support for character set collations and translations
- support for update actions in referential constraints
- support for
ALTER DOMAIN functionality
- support for deferrable constraints
+ support for granting
INSERT privilege on individual table columns
- support for
MATCH FULL and
MATCH PARTIAL in a referential constraint definition
- support for the
CASCADED and
LOCAL options in the
WITH CHECK OPTION
- support for the session management statements for setting catalog, schema and names
+ support for connection management, including
CONNECT,
SET CONNECTION and
DISCONNECT
+ support for self-referencing
DELETE,
INSERT and
UPDATE statements
- support for the
INSENSITIVE option on a cursor declaration
- support for full set function
- support for the
Syntax Only and
Catalog Lookup checking options in SQL flagging
- support for local table references qualified by module name
- support for fully updatable cursors.
