Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


SQL92


Source: ISO/IEC 9075:1992(E) Information technology - Database languages - SQL.

The SQL92 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, SQL92 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 SQL92, along with a transitional level (defined by FIPS) covering some features of intermediate level.

Entry level contains the set of features that define basic SQL92 compliance.

Note: When vendors claim they are SQL92 compliant it is important to know at which level.
Note: Mimer SQL complies fully with entry level and transitional level SQL92 (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 SQL92

Transitional level SQL92 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 SQL92 (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 SQL92

Intermediate level SQL92 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 SQL92

Full SQL92 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.


Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Reference Manual TOC PREV NEXT INDEX