Database Language SQL

Federal Information Processing Standards Publications (FIPS PUBS) are issued by the National Institute of Standards and Technology after approval by the Secretary of Commerce pursuant to Section 111(d) of the Federal Property and Administrative Services Act of 1949, as amended by the Computer Security Act of 1987, Public Law 100-235.

)chema definition, to declare the structures, integrity constraints, and access privileges of a database.

)chema manipulation, to alter a schema definition.


• Data manipulation, to populate a database and access SQL-data.
  
  
• Transaction management, to define and manage SQL-transactions.
  
  
• Connection management, to establish and manage SQL-connections.
  
  )ession management, to set the attributes of an SQL-session.
  
  
• Dynamic SQL, to provide facilities for dynamic construction and execution of SQL statements.
  
  
• Diagnostics management, to communicate constraint violations and warnings to applications.
  
  
• Information schema tables, to provide an SQL description of schema definitions.
  
  
• Programming language bindings, to declare database procedures that may be called from various programming languages.
  
  
• Embedded SQL, to define how SQL statements may be syntactically embedded into one of the following programming languages: Ada, C, COBOL, FORTRAN, MUMPS, Pascal, or PL/I. Embedded SQL was formerly defined in ANSI X3.168-1989.

a. American National Standard Database Language SQL, ANSI X3.135-1992 (revision of ANSI X3.135-1989 and replacement of ANSI X3.168-1989).

b. ISO/IEC 9075:1992, Database Language SQL (revision of ISO/IEC 9075:1989).

Note: Except for a different Foreword, Introduction, and Normative references, ANSI X3.135-1992 and ISO/IEC 9075:1992 are identical documents.

a. Federal Information Resources Management Regulations (FIRMR) subpart 201.20.303, Standards, and subpart 201.39.1002, Federal Standards, April 1992.

b. Federal ADP and Telecommunication Standards Index, U.S. General Services Administration, Information Resources Management Service, October 1992 (updated periodically).

c. NIST, Validated Products List: Programming Languages, Database Language SQL, Graphics, GOSIP, POSIX, Security; Judy B. Kailey, Editor, NISTIR 5103, issue No. 1, January 1993 (republished quarterly). Available by subscription from the National Technical Information Service (NTIS).

d. FIPS PUB 21-3, Programming Language COBOL, 1990.

e. FIPS PUB 69-1, Programming Language FORTRAN, 1985.

f. FIPS PUB 109, Programming Language Pascal, 1985.

g. FIPS PUB 119, Programming Language Ada, 1985.

h. FIPS PUB 125, Programming Language MUMPS, 1986 (Revision expected in 1993).

i. FIPS PUB 160, Programming Language C, 1991.

j. FIPS PUB 146, Government Open Systems Interconnection Profile (GOSIP). A revision to FIPS PUB 146-1, including Remote Database Access (RDA) specifications, is planned for mid-1993. To be issued in conjunction with IGOSS.

k. IGOSS, Industry/Government Open Systems Specification, publication planned mid-1993. This specification will reference "stable agreements" from the NIST OSI Implementor's Workshop as of December 1992.

l. NIST SP 500-206, Stable Implementation Agreements for Open Systems Interconnection Protocols, Version 6, Edition 1, NIST Workshop for Implementors of Open Systems Interconnection, December 1992.

m. ISO/IEC DIS 9579-1, Information Technology - Open Systems Interconnection - Remote Database Access - Part 1: Generic model, service, and protocol, document ISO/IEC JTC1/SC21 N6375, August 1991.

n. ISO/IEC DIS 9579-2, Information Technology - Open Systems Interconnection - Remote Database Access - Part 2: SQL specialization, document ISO/IEC JTC1/SC21 N6376, August 1991.

o. ISO/IEC 10026, Information Technology - Open Systems Interconnection - Distributed Transaction Processing - Part 1: OSI TP Model, Part 2: OSI TP Service, Part 3: OSI TP Protocol Specification, International Standard, December 1992.

p. SQL Information Bulletin, Number 1, SQLIB-1, Interpretations of ANSI X3.135-1989, available from Global Engineering Documents, April 1991.

q. FIPS PUB 29-2, Interpretation Procedures for FIPS Software, 14 September 1987.

r. ISO 646, Information Processing - ISO 7-bit coded character set for information interchange, 2nd edition, Third Edition, December 1991.

s. ISO 4873, Information Processing - ISO 8-bit code for information interchange - Structure and rules for implementation, Third Edition, 1991. Replaces ANSI X3.134.1, 8-bit ASCII.

t. ISO 8859-1, Information processing - 8-bit single-byte coded graphic character sets - Part 1: Latin alphabet No. 1, February 1987. Replaces ANSI X3.134.2.

u. ISO/IEC CD 11404, Information Technology - Programming Languages - Language Independent Data Types (CLID), document JTC1/SC22/WG11 N345, December 1992.

• to encourage more effective utilization and management of database application programmers by ensuring that skills acquired on one project are transportable to other projects, thereby reducing the cost of database programmer retraining.
  
  
• to reduce overall software costs by making it easier and less expensive to maintain database definitions and database application programs and to transfer those definitions and programs among different computers and database management systems, including replacement database management systems.
  
  
• to promote communication and interoperability among data installations conforming to FIPS SQL and related GOSIP communications standards.
  
  
• to reduce the cost of software development by achieving increased database application programmer productivity through the understanding and use of database methods employing standard structures and operations, standard data types, standard constraints, and standard interfaces to programming languages.
  
  
• to protect the software assets of the Federal government by ensuring to the maximal feasible extent that Federal database management system standards are technically sound and that subsequent revisions are compatible with the installed base.

• It is anticipated that the life of the database application will be longer than the life of the presently utilized equipment or database management system, if any.
  
  
• The database application is under constant review for updating of the specifications, and changes may result frequently.
  
  
• The database application is being designed and developed centrally for a decentralized system that employs computers of different makes and models or database software acquired from a different vendor.
  
  
• The database application will or might be run under a database management system other than that for which the database application is initially written.
  
  
• The database application is to be understood and maintained by programmers other than the original ones.
  
  
• The database application is one part of a distributed application that requires exchange of data or interoperation of the various parts.
  
  
• The database application is or is likely to be used by organizations outside the Federal government (e.g., Federal government contractors, State and local governments, and others).

• a. FIPS SQL requires conformance to Entry SQL. Conformance to Transitional SQL, Intermediate SQL, or Full SQL are options that may be specified explicitly in SQL procurements (see Section 14).
  
  
• b. FIPS SQL does not include PL/I language bindings, since PL/I is not a FIPS programming language.
  
  
• c. FIPS SQL does not recognize conformance solely by "direct invocation and processing of SQL language" as specified in Subclause 23.2 of ANSI X3.135-1992, because direct invocation does not mandate all of the facilities desired in a FIPS SQL conforming product. Conformance to FIPS SQL requires a Module or Embedded SQL binding style to one or more FIPS programming languages.
  
  
• d. FIPS SQL requires that the "SQL Flagger" be implemented in Entry SQL in addition to Intermediate SQL and Full SQL. This is because FIPS SQL has always included a flagger requirement, even from its first specification in 1987. For conformance to Entry SQL, FIPS SQL requires "Entry SQL Flagging" with the "Syntax Only" extent of checking option as defined in Subclause 4.33 of ANSI X3.135-1992. The SQL Flagger is required for each language binding style, including "Interactive Direct SQL" (see Section 16.5).
  
  
• e. For conformance to Intermediate SQL or to Full SQL, FIPS SQL requires implementation of the following named character sets: SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, ASCII_FULL, and SQL_TEXT. The form-of-use and default collation requirements for these character sets are defined in Section 16.7 of this standard.
  
  
• f. For conformance to Intermediate SQL or to Full SQL, FIPS SQL requires implementation of the FIPS_DOCUMENTATION schema, as specified in Section 15 of this standard.

Under certain exceptional circumstances, the heads of Federal departments and agencies may approve waivers to Federal Information Processing Standards (FIPS). The head of such agency may redelegate such authority only to a senior official designated pursuant to section 3506(b) of Title 44, U.S. Code. Waivers shall be granted only when:

• a. Compliance with a standard would adversely affect the accomplishment of the mission of an operator of a Federal computer system, or
  
  
• b. Cause a major adverse financial impact on the operator which is not offset by Government-wide savings.

1. Integrity enhancement. The Integrity Enhancement Feature was an option in FIPS PUB 127-1. Many implementations of the existing FIPS SQL support this feature and both ISO and ANSI standardization groups have made it required for all conforming Entry SQL implementations. This feature is now required in FIPS SQL. It includes any explicit or implicit (unique constraint definition), (referential constraint definition), (check constraint definition), or the (default clause), each with specified restrictions for Entry SQL.

2. SQLSTATE status codes. This mechanism for returning exceptions to SQL statements augments the SQLCODE status codes originally specified. SQLCODE is now a deprecated feature. SQLSTATE specifies more than seventy-five (75) exception, completion, and warning codes, whereas SQLCODE specifies only three. SQLSTATE and SQLCODE are defined in Clause 22, "Status codes", of ANSI X3.135-1992.

3. Delimited identifiers. In the previous ANSI SQL specification, it was not possible for an application to specify identifiers with spaces or other special symbols. Also, it was not possible to protect against future assaults on the name space for (identifier) by additions to the (reserved word) list. The new facility for (delimited identifier) allows a user to enclose all identifiers in double-quotation marks, thereby ensuring that the name defined or referenced may contain spaces or other special symbols and will not be impacted by future additions to the (reserved word) list.

4. Renaming columns. In the SQL language it is possible to reference (sort key) columns in a (cursor specification) by position number instead of by column name. This is because it was not possible in the previous standard to name derived columns resulting from (value expression). Reference by position number is now a deprecated feature; as an alternative, it is required to be able to name, or rename, any (derived column) with an (as clause).

5. Commas in parameter lists. In the previous ANSI SQL specification, items in a (parameter declaration list) were separated by spaces or other token separators. It is now possible to also separate such items by commas. This makes parameter lists compatible in style with all other lists in the SQL language.

6. SQL Errata. Several errors in the previous ANSI SQL specification have been corrected via announcement in the SQL Information Bulletin, SQLIB-1, including: addition of a leading colon for (parameter name), corrections to the specification of WITH CHECK OPTION in a (view definition), clarification of the argument mode for Pascal parameters, and clarification of statement termination requirements for Embedded SQL in Pascal programs. Other errors in ANSI X3.135-1989 have also been corrected in ANSI X3.135-1992, including: additional overflow exceptions on data assignment, exceptions generated during evaluation of a (numeric value expression), correction of the data type for the SQLCODE variable in an (embedded SQL Ada program), removal of an ambiguity in the definition of WITH CHECK OPTION in nested view definitions, addition of a rule to prevent defining two (unique constraint definition) with identical unique column lists in the same table, additional requirements to enforce SELECT privileges, restrictions on circular view definitions, correction to the syntax of a (host label identifier) in an (embedded exception declaration), corrections for data type declarations in (parameter declaration) for COBOL and PL/I, correction to the specification of (COBOL integer type), and correction to the specification of PL/I support for SQLCODE. Most of these corrections are listed in Annex E, "Incompatibilities", and Annex F, "Maintenance and interpretation of SQL" of ANSI X3.135-1992. All such corrections that pertain to Entry SQL are now required elements of FIPS SQL.

Note: The term "deprecated", as used in ANSI SQL, means that a feature so labeled may not be supported in some future version of the standard, but it is still a fully supported and required feature of the existing standard.

1. Dynamic SQL. All provisions of Clause 17, "Dynamic SQL", with restrictions identified in the Leveling Rules for Intermediate SQL; removal of all Entry SQL Leveling Rules in Clause 17; removal of the Entry SQL restriction requiring that a (module contents) not be a (dynamic declare cursor), as specified in Leveling Rule 2b of Subclause 12.1, "(module)"; removal of Leveling Rule 2a of Subclause 6.2 that prohibits reference to a dynamic parameter in a (general value specification). A (preparable statement) shall include all (preparable SQL data statement) that are otherwise supported for Entry SQL, as well as any other (preparable statement) for which non-preparable support is claimed by that implementation.

2. Basic information schema. Requires existence of an accessible INFORMATION_SCHEMA consisting of the following views defined in Subclause 21.2, "Information Schema": TABLES, VIEWS, and COLUMNS, all with any restrictions identified in the Leveling Rules for Intermediate SQL.

3. Basic schema manipulation. Support for the following schema definition and schema manipulation statements as (SQL statement) in an explicit or implicit (procedure): Subclauses 11.3 through 11.9, "(table definition)"; Subclause 11.18, "(drop table statement)"; Subclause 11.19, "(view definition)"; Subclause 11.20, "(drop view statement)"; Subclause 11.10, "(alter table statement)", containing Subclause 11.11, "(add column definition)"; Subclause 11.10, "(alter table statement)", containing Subclause 11.15, "(drop column definition)"; Subclause 11.36, "(grant statement)"; and Subclause 11.37, "(revoke statement)"; all with any other restrictions identified in the Leveling Rules for Entry SQL, and all with any enhancements derived from other features claimed to be supported by the implementation. Removal of Leveling Rules 2a in Subclauses 11.11, 11.15, 11.18, 11.20, and 11.37.

4. Joined table. All provisions for NATURAL JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, (join condition), and (named columns join) from Subclause 7.5, "(joined table)", with restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Entry SQL Leveling Rule 2a of Subclause 6.3, "(table reference)", that prohibits (joined table) in a table reference. Removal of Entry SQL Leveling Rule 2c of Subclause 7.10, "(query expression)", that prohibits (joined table) in a (query expression). This feature does not include support for CROSS JOIN, UNION JOIN, or FULL OUTER JOIN.

5. DATETIME data types. Support for DATE, TIME, TIMESTAMP, and INTERVAL data types as defined in Subclause 6.1, "(data type)", with the exception of support for time zones and time zone management; support for (datetime literal) and (interval literal) as defined in Subclause 5.3, (literal); support for the following datetime operations: (datetime field) in an (extract expression) as defined in Subclause 6.6, (numeric value function), (datetime value function) defined in Subclause 6.8, (datetime value expression) defined in Subclause 6.14, (interval value expression) defined in Subclause 6.15, and (overlaps predicate) defined in Subclause 8.11 (Note: With support for (row value constructor) here); support for datetime comparison defined in Subclause 8.2, (comparison predicate); support for (interval qualifier) as specified in Subclause 10.1; support for (datetime value function) in a (default clause) as specified in Subclause 11.5, (default clause); all with restrictions identified in the Leveling Rules for Intermediate SQL. The Syntax Rules require, by default from Syntax Rule 26 of Subclause 6.1, (data type), a (timestamp precision) greater than or equal to 6 decimal digits. This feature does not include support for time zones, including: (time zone interval) (time zone field) (time zone) (time zone specifier) and (set local time zone statement) (see feature #41). Removal of the Entry SQL restriction on datetime data types as specified in Leveling Rule 2b of Subclause 6.1, (data type), and on datetime functions as specified in Leveling Rule 2a of Subclause 6.8, (datetime value function). Removal of the Entry SQL restriction on datetime literals as specified in Leveling Rule 2b of Subclause 5.3, on datetime value expressions as specified in Leveling Rules 2a and 2b of Subclause 6.11, Leveling Rule 2a of Subclause 6.14, and Leveling Rule 2a of Subclause 6.15, on the overlaps predicate as specified in Leveling Rules 2a of Subclause 8.1 and Subclause 8.11, and for specifying the field precision of an INTERVAL data type or literal as specified in Leveling Rule 2a of Subclause 10.1, (interval qualifier), all with the exception of retaining the restrictions on time zone support.

6. VARCHAR data type. Support for CHARACTER VARYING, and its syntactic shorthands VARCHAR and CHAR VARYING, as defined in Subclause 6.1, (data type); support for the following character operations: (length expression) defined in Subclause 6.6, (numeric value function), (character substring function) defined in Subclause 6.7, (string value function), (concatenation> and (character value expression> defined in Subclause 6.13, (string value expression); support for comparison of fixed and variable length character strings as defined in Subclause 8.2, (comparison predicate); support for CHARACTER VARYING in any (embedded SQL host program) supported by the implementation; all with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of the Entry SQL requirement that at least one (character representation) be present in a (character string literal) as specified in Leveling Rule 2c of Subclause 5.3, (literal). Removal of Leveling Rule 2a of Subclause 6.1, (data type). Removal of the Entry SQL requirement that a (numeric value function) not be a (length expression) as specified in Leveling Rule 2a of Subclause 6.6, (numeric value function). Removal of the Entry SQL restriction against using the SUBSTRING function, as specified in Leveling Rule 2a of Subclause 6.7, ()tring value function). Removal of Leveling Rule 2a of Subclause 6.13, (string value expression), that prohibits concatenation in Entry SQL. Removal of Leveling Rule 2a of Subclause 12.3, (procedure), that prohibits specification of CHARACTER VARYING in an Entry SQL (procedure). Removal of Leveling Rule 2a of Subclause 19.4, (embedded SQL C program), and Leveling Rule 2a of Subclause 19.9, (embedded SQL PL/I program).

7. TRIM function. Removal of the Entry SQL restriction against specifying a (trim function) as an alternative in a (character value function) as specified in Leveling Rule 2b of Subclause 6.7, (string value function).

8. UNION in views. Removal of the Entry SQL restriction against specification of UNION in a (view definition) as specified by the Entry SQL Leveling Rule 2a of Subclause 11.19, (view definition). Support (query expression> in a (view definition) provided that the (query expression) abides by the other restrictions for Entry SQL, as specified in Leveling Rule 2 of Subclause 7.10, (query expression).

9. Implicit numeric casting. Removal of all Entry SQL restrictions for operations involving the assignment of approximate numeric values to exact numeric types, including: Leveling Rule 2c of Subclause 13.3, (fetch statement); Leveling Rule 2a of Subclause 13.5, (select statement: single row); Leveling Rule 2b of Subclause 13.8, (insert statement); Leveling Rule 2a of Subclause 13.9, (update statement: positioned); Leveling Rule 2a of Subclause 13.10, (update statement: searched).

10. Implicit character casting. Removal of all Entry SQL restrictions for operations involving the assignment of character string values to character string types, including: Leveling Rule 2c of Subclause 13.8, (insert statement); Leveling Rule 2b of Subclause 13.9, (update statement: positioned); Leveling Rule 2b of Subclause 13.10, (update statement: searched).

11. Transaction isolation. All provisions, except DIAGNOSTICS SIZE, of Subclause 14.1, (set transaction statement), in particular: READ ONLY, READ WRITE, and ISOLATION LEVEL, and support for the (set transaction statement> as an (SQL statement) in an explicit or implicit (procedure) with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rule 2a of Subclause 14.1. Removal of the Entry SQL limitation on updatable tables, as specified by Leveling Rule 2a of Subclause 7.9, (query specification). Removal of the Entry SQL restriction against inclusion of an (updatability clause) in a (declare cursor) as specified by Leveling Rule 2b of Subclause 13.1, (declare cursor).

12. Get diagnostics. Support for the (get diagnostics statement) specified in Subclause 18.1, as an (SQL statement) in an explicit or implicit (procedure) with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rule 2a of Subclause 18.1. If feature #11, "Transaction isolation" is supported, then support for specification of (diagnostics size) in the (set transaction statement).

13. Grouped operations. Removal of all Entry SQL restrictions for operations involving grouped views and other grouping operations, including: Leveling Rule 2a of Subclause 7.3, (table expression); Leveling Rule 2a of Subclause 7.4, (from clause); Leveling Rule 2c of Subclause 7.9, (query specification); Leveling Rule 2a of Subclause 7.11, (calar subquery) (row subquery) and (table subquery), and Leveling Rule 2b of Subclause 13.5, (select statement: single row).

14. Qualified * in select list. Removal of the Entry SQL restriction for specifying (qualifier>.* in a (select sublist) as specified in Leveling Rule 2b of Subclause 7.9, (query specification).

15. Lowercase identifiers. Removal of the Entry SQL restriction requiring that identifiers not contain any lowercase letters, as specified in Leveling Rule 2b of Subclause 5.2, (token> and (separator); with restrictions identified in the Leveling Rules for Intermediate SQL.

16. PRIMARY KEY enhancement. Removal of the Entry SQL restriction requiring that NOT NULL always be declared with any UNIQUE or PRIMARY KEY, as specified in Leveling Rule 2a of Subclause 11.7, (unique constraint definition).

17. Multiple schemas per user. Support for separation of (schema name) and (authorization identifier) in a (schema definition). The (schema definition) itself need be processed only as required for Entry SQL (i.e. it need not be supported as an SQL statement in an explicit or implicit (procedure)), and a (schema element) need only be as required by Entry SQL. See below feature #31, "Schema definition statement", for more restrictive syntactic requirements. If feature #2, "Basic information schema", is supported, then implementation of Subclause 21.2.4, "SCHEMATA view", in the INFORMATION_SCHEMA. If the Module language binding style is supported, as specified in Subclause 12.1, (module), and Subclause 23.2, "Claims of conformance", then removal of Leveling Rule 2c of Subclause 12.1, (module), that prohibits reference to a (schema name) in a (module) definition.

18. Multiple module support. Removal of the Entry SQL restriction that a (module) be associated with an SQL-agent during its execution, and that an SQL-agent be associated with at most one (module) as specified in Leveling Rule 2a of Subclause 12.1, (module). With removal of this restriction, it will be possible to compile (module) or (embedded SQL host program) separately and rely on the implementation to link them together properly at execution time. To ensure universal portability, applications should adhere to the following self-imposed restrictions: 1) avoid linking modules having cursors with the same (cursor name) 2) avoid linking modules that prepare statements with the same <)SQL statement name) 3) avoid linking modules that allocate descriptors with the same (descriptor name) 4) the scope of an (embedded exception declaration) is a single compilation unit, 5) an (embedded variable name) can be referenced only in the same compilation unit in which it is declared.

19. Referential delete actions. Remove Leveling Rule 2a of Subclause 11.8, (referential constraint definition) thereby providing support for a (referential triggered action) that contains a (delete rule) with restrictions identified in the Leveling Rules for Intermediate SQL. Remove Leveling Rule 2b of Subclause 11.4, (column definition), thereby allowing an ON DELETE trigger.

20. CAST functions. All provisions of Subclause 6.10, (cast specification), with restrictions identified in the Leveling Rules for Intermediate SQL. The resulting data type of the (cast operand) and of the (cast target) of a (cast specification) shall include all data types required for Entry SQL, as well as any other SQL standard data type whose support is claimed by the implementation. In particular, if feature #5, "Datetime data type", is supported, then (cast specification) for casting DATE, TIME, TIMESTAMP, and INTERVAL to and from character strings. Removal of the Entry SQL restrictions against use of CAST, as specified in Leveling Rule 2a of Subclause 6.10, (cast specification), and Leveling Rule 2d of Subclause 6.11, (value expression).

21. INSERT expressions. Removal of the Entry SQL restriction against specifying a (value expression> in an (insert statement) as specified in the second part of Leveling Rule 2a of Subclause 13.8, (insert statement).

22. Explicit defaults. Removal of the Entry SQL restriction against use of DEFAULT VALUES in an (insert statement) as specified in Leveling Rule 2d of Subclause 13.8, (insert statement). Removal of the Entry SQL restriction against use of DEFAULT in a (row value constructor) as specified in Leveling Rule 2a of Subclause 7.1, (row value constructor). Removal of the Entry SQL restriction against use of datetime and certain USER defaults, as specified in Leveling Rule 2a of Subclause 11.5, (default clause). Removal of the Entry SQL restriction against use of DEFAULT in an (update source) of an (update statement: positioned) or an (update statement: searched) as specified in Leveling Rule 2c of Subclause 13.9, (update statement: positioned).

23. Privilege tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": TABLE_PRIVILEGES,COLUMN_PRIVILEGES, and USAGE_PRIVILEGES, all with any restrictions identified in the Leveling Rules for Intermediate SQL.

24. Keyword relaxations. Removal of the Entry SQL restriction against using AS before a (correlation name) as specified in Leveling Rule 2b of Subclause 6.3, (table reference). Removal of the Entry SQL restriction against using the optional keyword TABLE in a GRANT statement, as specified in Leveling Rule 2a of Subclause 11.36, (grant statement). Removal of the Entry SQL restriction for specifying FROM in a FETCH statement, as specified in Leveling Rule 2b of Subclause 13.3, (fetch statement). Removal of the Entry SQL requirement to include the keyword WORK in COMMIT and ROLLBACK statements, as specified in Leveling Rules 2a of Subclause 14.3, (commit statement) and Subclause 14.4, (rollback statement).

25. Domain definition. Support for Subclause 11.21, (domain definition), and Subclause 11.27, (drop domain statement); support for VALUE in (general value specification> as specified in Syntax Rule 5 of Subclause 6.2, (value specification); reference to (domain name> in a (column definition> as specified in Subclause 11.4, (column definition); reference to (domain name> in GRANT and REVOKE statements as specified in Subclause 11.36, (grant statement), and Subclause 11.37, (revoke statement); if feature #2, "Basic information schema", is supported, then implementation of Subclause 21.2.5, "DOMAINS view", in the INFORMATION_SCHEMA; if feature #3, "Basic schema manipulation", is supported, then support for (domain definition) and (drop domain statement> as (SQL statement) in an explicit or implicit (procedure>; if feature #20, "CAST functions", is supported, then support for (domain name) as a (cast target); if feature #33, "Constraint tables", is supported then implementation of Subclause 21.2.6, "DOMAIN_CONSTRAINTS view"; all with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 2a of Subclause 11.1, (schema definition), and Subclause 11.21, (domain definition), that prohibit domain definitions in Entry SQL; removal of Leveling Rule 2b of Subclause 6.2 that prohibits use of VALUE in a domain check constraint; removal of Leveling Rule 2a of Subclause 11.27, (drop domain statement), that prohibits dropping a domain from a schema;removal of Leveling Rule 2b of Subclause 11.36, (grant statement), that prohibits privilege definition on domains; removal of Leveling Rule 2a of Subclause 11.4, (column definition), that prohibits column data type references to a domain.

26. CASE expression. All provisions of Subclause 6.9, (case expression), with restrictions identified in the Leveling Rules for Intermediate SQL. Removal of all Entry SQL restrictions against use of CASE expressions, including Leveling Rule 2a of Subclause 6.9, (case expression), and Leveling Rule 2c of Subclause 6.11, (value expression).

27. Compound character literals. Removal of the Entry SQL restriction against specifying multiple repetitions of (character representation) strings in a (character string literal) as specified in Leveling Rule 2d of Subclause 5.3, (literal). This feature allows very long character strings to be subdivided into components prior to concatenation into a result string, thereby avoiding problems with line length or line termination in some programming languages.

28. LIKE enhancements. Removal of all Entry SQL restrictions in the LIKE predicate, including: Leveling Rules 2a, 2b, and 2c of Subclause 8.5, (like predicate), thereby allowing the (match value) to be a general (character value expression) instead of just a (column reference) and allowing the (pattern> and (escape character) to be general (character value expression) instead of just simple (value specification).

29. UNIQUE predicate. Support for the UNIQUE predicate, as specified in Subclause 8.9, (unique predicate); with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of the Entry SQL restrictions against use of the (unique predicate) as specified by Leveling Rule 2b of Subclause 8.1 and Leveling Rule 2a of Subclause 8.9.

30. Table operations. Removal of Entry SQL restrictions in Subclause 7.10, (query expression), pertaining to the UNION, EXCEPT and INTERSECT operations, and the CORRESPONDING option, including Leveling Rules 2a, 2b, 2d, and 2e; removal of the Entry SQL restriction requiring that a (query expression) in an (insert statement) not include any table operations, as specified in part 1 of Leveling Rule 2a of Subclause 13.8, (insert statement); removal of the Entry SQL restriction against using a (derived column list) in a (table reference) as specified in Leveling Rule 2c of Subclause 6.3, (table reference); removal of the Entry SQL restriction requiring that a (query expression> in a ()ubquery) not include any table operations, as specified in Leveling Rule 2b of Subclause 7.11, (subquery); removal of the Entry SQL restriction requiring that a (query expression> in a (view definition> not include any table operations, as specified in Leveling Rule 2a of Subclause 11.19, (view definition); all with any other restrictions identified in the Leveling Rules for Intermediate SQL.

31. Schema definition statement. Support for (schema definition) Subclause 11.1, as an (SQL statement) in an explicit or implicit(procedure) by removal of Leveling Rule 2a of Subclause 12.5, (SQL procedure statement), with any restrictions identified in the Leveling Rules for Intermediate SQL. A (schema element) shall be an element required byEntry SQL, or an element defined in another feature whose support is claimed by the implementation. Support for feature #17, "Multiple schemas per user", defined above and removal of the Entry SQL restriction that prohibits definition of a (schema name) in a (schema definition) as specified in Leveling Rule 2b of Subclause 11.1. A (schema definition) may contain any circular references that are permitted for Intermediate SQL; in particular, (referential constraint definition) in two different (table definition) that reference columns in the other table.

32. User authorization. All provisions of Subclause 16.4, (set session authorization identifier statement), with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rule 2a of Subclause 16.4. Support for CURRENT_USER, SESSION_USER, and SYSTEM_USER in (general value specification) by removal of Leveling Rule 2c of Subclause 6.2, (value specification).

33. Constraint tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": TABLE_CONSTRAINTS,REFERENTIAL_CONSTRAINTS, and CHECK_CONSTRAINTS, all with any restrictions identified in the Leveling Rules for Intermediate SQL.

34. Usage tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": KEY_COLUMN_USAGE, VIEW_TABLE_USAGE, VIEW_COLUMN_USAGE, CONSTRAINT_TABLE_USAGE, CONSTRAINT_COLUMN_USAGE, and COLUMN_DOMAIN_USAGE, all with any restrictions identified in the Leveling Rules for Intermediate SQL.

35. Intermediate information schema. All provisions of Subclause 21.2, "Information schema", with restrictions identified in the Leveling Rules for Intermediate SQL. This feature includes feature #2, "Basic information schema", feature #23, "Privilege tables", feature #33, "Constraint tables", and feature #34, "Usage tables", defined above, the SCHEMATA view from feature #17, the DOMAINS and DOMAIN_CONSTRAINTS views from feature #25, and the CHARACTER_SETS view from feature #45, as well as all remaining tables required for Intermediate SQL, including: Subclause 21.2.2, "INFORMATION_SCHEMA_CATALOG_NAME base table", Subclause 21.2.17, "ASSERTIONS view", and Subclause 21.2.26, "SQL_LANGUAGES view". Removal of all Leveling Rules 2a in Subclause 21.2, "Information Schema". In Entry SQL or Intermediate SQL, some of these tables may have empty or trivial contents, but an implementation supporting this feature is required to process statements that properly reference any of these tables. These tables are all self-describing in the sense that they appear in the Definition Schema as PUBLIC tables and thus are visible in the INFORMATION_SCHEMA for every user.

36. Subprogram support. Removal of the Entry SQL restriction that prohibits full use of local variable name scoping in subprograms, as specified in Leveling Rule 2b of Subclause 19.1, (embedded SQL host program). With removal of this restriction, compilation units may include subprograms with SQL statements that reference local variables defined in the subprogram even if some other identically named variable, having different scope, happens to be defined in an SQL (host variable definition) in the main program or in a different subprogram of the same compilation unit. This feature applies only to the Embedded SQL language interface specified in Clause 19, "Embedded SQL"; it does not apply to any Module or Direct Invocation interfaces.

37. Intermediate SQL Flagging. Support for both "Entry SQL Flagging" and "Intermediate SQL Flagging" with the "Syntax Only" extent of checking option as specified for conforming Intermediate SQL implementations in Subclause 23.4, "Flagger requirements", of ANSI X3.135-1992. This facility would allow an application to distinguish between vendor extensions beyond Entry SQL that are supported in Intermediate SQL and those that are beyond Intermediate SQL or are non-standard.

38. Schema manipulation. Support for feature #3, "Basic schema manipulation". In addition, support for the following schema definition and schema manipulation statements as (SQL statement) in an explicit or implicit (procedure): Subclause 11.2, (drop schema statement); Subclauses 11.10 through 11.17, (alter table statement); all with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 2a in Subclause 11.2 and Subclauses 11.10 through 11.17. If Direct invocation and processing of SQL language is supported, as specified in Subclause 23.2, "Claims of conformance", then removal of Leveling Rule 2a of Subclause 20.1, (direct SQL statement).

39. Long identifiers. Support for (regular identifier) or (delimiter identifier body) that have lengths of up to 128 characters; that is, remove the restrictions of Leveling Rule 2a of Subclause 5.2, (token> and (separator).

40. Full outer join. Support for feature #4, "Joined table". In addition, support for FULL OUTER JOIN by removal of Leveling Rule 2a from Subclause 7.5, (joined table).

41. Time zone specification. Support for feature #5, "DATETIME data types". In addition, full implementation of time zones and time zone management, including: support for (time zone interval) specified in Subclause 5.3, (literal), (time zone field) specified in Subclause 6.6, (numeric value function), (time zone) and (time zone specifier) specified in Subclause 6.14, (datetime value expression), and (set local time zone statement) specified in Subclause 16.5, (set local time zone statement). Removal of the Entry SQL restriction on (timezone field) in an (extract expression) as specified in Leveling Rule 2b of Subclause 6.6, (numeric value function). Removal of Leveling Rule 2a in Subclause 16.5, (set local time zone statement).

42. National character. Support for NATIONAL CHARACTER, and its syntactic shorthands NATIONAL CHAR and NCHAR, as defined in Subclause 6.1, (data type); support for (national character string literal) as defined in Subclause 5.3, (literal); removal of the Entry SQL restriction requiring that a (delimiter token) shall not be a (national character string literal) as specified in Leveling Rule 2a of Subclause 5.3, (literal). Removal of (national character string type) from Leveling Rule 2c of Subclause 6.1, (data type). If feature #6, "VARCHAR data type" is supported, then support for NATIONAL CHARACTER VARYING, and its syntactic shorthands NATIONAL CHAR VARYING and NCHAR VARYING, as specified in Subclause 6.1, (data type).

43. Scrolled cursors. Support for SCROLL in a cursor declaration and for (fetch orientation) in a FETCH statement, by removal of the Entry SQL restrictions against declaration and use of scrolled cursors as specified in Leveling Rule 2a of Subclause 13.1, (declare cursor), and Leveling Rule 2a of Subclause 13.3, (fetch statement).

44. Intermediate set function. Removal of Entry SQL restrictions against certain set function operations, including: removal of Leveling Rule 2a concerning COUNT ALL, Leveling Rule 2b concerning (column reference), Leveling Rule 2c concerning (value expression) and Leveling Rule 2d concerning column references, all of Subclause 6.5, (set function specification); removal of Leveling Rule 2a concerning reference to a column generally containing a set function, as specified in Subclause 7.6, (where clause).

45. Character set definition. Support for Subclause 11.28, (character set definition), and Subclause 11.29, (drop character set statement), as (SQL statement) in an explicit or implicit (procedure) Support for granting and revoking USAGE privileges on any defined character sets, as specified in Subclause 10.3, (privileges), Subclause 11.36, (grant statement), and Subclause 11.37, (revoke statement). If feature #2, "Basic information schema", is supported, then implementation of Subclause 21.2.18, "CHARACTER_SETS view" in the INFORMATION_SCHEMA. Removal of Leveling Rules 2a in Subclause 11.28 and Subclause 11.29. Removal of the Entry SQL restriction against using a (character set specification) in Embedded SQL, as specified in the Leveling Rules of each (embedded SQL host program) Support for all other references to (character set specification) including removal of Leveling Rule 2a of Subclause 10.4, (character set specification), Leveling Rules 2c and 2d of Subclause 11.1, (schema definition), and Leveling Rule 2a of Subclause 12.2, (module name clause).

46. Named character sets. Support for the named character sets: SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, ASCII_FULL, and SQL_TEXT. If feature #2, "Basic information schema" is supported, then implementation of the CHARACTER_SETS view in the INFORMATION_SCHEMA. Support for SQL_TEXT is required by ANSI SQL Syntax Rule 3 of Subclause 10.4, (character set specification); the other character sets are defined in Section 16.7 of this standard. Removal of Leveling Rule 2e of Subclause 5.3, (literal). Removal of Leveling Rule 2b of Subclause 5.4, "Names and identifiers". Removal of CHARACTER SET from Leveling Rule 2c of Subclause 6.1, (data type). Removal of the Entry SQL restriction against referencing these character sets in Embedded SQL, as specified by Leveling Rule 2a of Subclause 19.1, (embedded SQL host program), and by the Leveling Rule of each (embedded SQL host program) that prohibits use of a (character set specification) Support for all other references to these named character sets in any (character set specification) in any SQL statement supported in Entry SQL, and in any other SQL statement whose support is claimed by the implementation.

47. Scalar subquery values. Support for the use of a (scalar subquery) in any (value expression) by removal of Leveling Rule 2e of Subclause 6.11, (value expression).

48. Expanded null predicate. Support for referencing values other than a (column reference> in a (null predicate) by removal of Leveling Rule 2a of Subclause 8.6, (null predicate). This includes support for testing any Entry SQL (value expression) or any supported Intermediate SQL (value expression) to see if it is null.

49. Constraint management. Support for user-defined constraint names, including use of an explicit (constraint name definition> in any table constraint, by removal of: Leveling Rule 2a of Subclause 10.6, (constraint name definition), Leveling Rule 2c of Subclause 11.4, (column definition), and Leveling Rule 2a of Subclause 11.6, (table constraint definition). Support for feature #33, "Constraint tables". If feature #25, "Domain definition", is supported, then removal of all restrictions against explicit (constraint name definition) in Subclause 11.21, (domain definition). If feature #12, "Get diagnostics", is supported, then support for reference to user-defined, explicit (constraint name) in the (get diagnostics statement).

50. Documentation schema. Implementation of the documentation schema tables, SQL_FEATURES and SQL_SIZING, as specified in Section 15 of this standard.

51. BIT data type. Support for BIT and BIT VARYING data types, as defined in Subclause 6.1, (data type); support for (bit string literal) and (hex string literal) as defined in Subclause 5.3, (literal); support for the following bit string operations: (length expression) defined in Subclause 6.6, (numeric value function), (bit substring function) defined in Subclause 6.7, (string value function), (concatenation) and (bit value expression) defined in Subclause 6.13, (string value expression); support for comparison of fixed and variable length bit strings as defined in Subclause 8.2, (comparison predicate); support for BIT VARYING in any (embedded SQL host program) supported by the implementation. If feature #20, "CAST functions", is supported, then (cast specification) for casting bit strings to and from character strings. Removal of Leveling Rule 1b of Subclause 5.3, (literal). Removal of Leveling Rule 1b of Subclause 6.1, (data type). Removal of the Intermediate SQL requirement that a (numeric value function) not be a (length expression) as specified in Leveling Rule 1b of Subclause 6.6, (numeric value function). Removal of the Intermediate SQL restriction against using a (bit value function) as specified in Leveling Rule 1d of Subclause 6.7, (string value function). Removal of Leveling Rule 1b of Subclause 6.13, (string value expression), that prohibits use of a (bit value expression) in Intermediate SQL. Removal of Leveling Rule 1a of Subclause 12.3, (procedure), that prohibits specification of BIT or BIT VARYING in an Intermediate SQL (procedure) Removal of Leveling Rule 1a of Subclause 19.3, (embedded SQL Ada program), Leveling Rule 1a of Subclause 19.4, (embedded SQL C program), Leveling Rule 1a of Subclause 19.5, (embedded SQL COBOL program), Leveling Rule 1a of Subclause 19.6, (embedded SQL Fortran program), Leveling Rule 1a of Subclause 19.8, (embedded SQL Pascal program), and Leveling Rule 1a of Subclause 19.9, (embedded SQL PL/I program).

52. Assertion constraints. Support for (assertion definition) and (drop assertion statement) as (SQL statement) in an explicit or implicit (procedure) as specified in Subclauses 11.34 and 11.35, respectively. If feature #2, "Basic information schema" is supported, then implementation of Subclause 21.2.17, "ASSERTIONS view", in the INFORMATION_SCHEMA. Removal of Leveling Rules 1a in Subclause 11.1, Subclause 11.34, and Subclause 11.35.

53. Temporary tables. Support for GLOBAL TEMPORARY or LOCAL TEMPORARY table definitions and ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS options, as specified in Subclause 11.3, (table definition); all provisions of Subclause 13.11, (temporary table declaration); all with any other restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 1a from Subclause 11.3, Subclause 12.1, and Subclause13.11.

54. Full dynamic SQL. All provisions of Clause 17, "Dynamic SQL", including: host variables for descriptor names, statement names, and cursor names; DEALLOCATE PREPARE; DESCRIBE INPUT; dynamic single row select; and preparable positioned dynamic update/delete. Removal of the Intermediate SQL restriction against reference to system-supplied names, as specified in Leveling Rule 1a of Subclause 5.4, "Names and identifiers". Removal of Leveling Rules 1a of Subclauses 17.2, 17.3, 17.4, and 17.9 that restrict (occurrences> and (descriptor name) to be (literal). Support for Subclause 17.7, (deallocate prepared statement), Subclause 17.8, (describe statement), Subclause 17.13, (allocate cursor statement), Subclause 17.19, (preparable dynamic delete statement: positioned), and Subclause 17.20, (preparable dynamic update statement: positioned), by removal Intermediate SQL Leveling Rule 1a from each of these clauses. Support for a (result using clause) in an EXECUTE statement, by removal of Leveling Rule 1a of Subclause 17.10, (execute statement).

55. Full DATETIME. Support for feature #5, "DATETIME data type", and feature #41, "Time zone specification". In addition, removal of the Intermediate SQL restrictions against specification of precision in TIME and TIMESTAMP data types, as specified in Leveling Rule 1a of Subclause 6.1, (data type), Leveling Rule 1a of Subclause 5.3, (literal), and Leveling Rule 1a of Subclause 6.8, (datetime value function).

56. Full value expressions. Support for references to (value expression) in all places where they are restricted in Entry SQL or Intermediate SQL, including: removal of the Intermediate SQL restriction against using a (value expression) in a (general set function) with DISTINCT, as specified in Leveling Rule 1a of Subclause 6.5, (set function specification); removal of the Intermediate SQL restriction against using a (value expression) in an (in predicate) as specified in Leveling Rule 1a of Subclause 8.4, (in predicate).

57. Truth value tests. Support for (truth value) tests of TRUE, FALSE, or UNKNOWN, or their negations, applied to a (boolean primary) as specified in Subclause 8.12, (search condition). Removal of Leveling Rule 1a of Subclause 8.12.

58. Full character functions. Removal of the Intermediate SQL restriction against use of a POSITION expression, as specified in Leveling Rule 1a of Subclause 6.6, (numeric value function). Removal of the Intermediate SQL restrictions against use of UPPER and LOWER functions, as specified in Leveling Rule 1a of Subclause 6.7, (string value function).

59. Derived tables in FROM. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 6.3, (table reference), that prohibits a (table reference) from being a (derived table). The effect is that a (derived table) possibly with a (derived column list) may be specified in a FROM clause.

60. Trailing underscore. Removal of the Intermediate SQL restriction against using an (underscore) as the last character of an identifier, as specified in Leveling Rule 1a of Subclause 5.2, (token) and (separator).

61. Indicator data types. Removal of the Intermediate SQL restrictions on the data types of indicator parameters and variables, as specified in Leveling Rule 1a of Subclause 6.2, (value specification) and (target specification).

62. Referential name order. Removal of the Intermediate SQL restriction on the order of column names in a referential constraint definition, as specified in Leveling Rule 1c of Subclause 11.8, (referential constraint definition).

63. Full SQL Flagging. Support for "Entry SQL Flagging", "Intermediate SQL Flagging", and "Full SQL Flagging", each with the "Syntax Only" extent of checking option as defined in Subclause 4.33 of ANSI X3.135-1992. This facility would allow an application to distinguish among vendor extensions beyond Entry SQL that are supported in Intermediate SQL, those beyond Intermediate SQL that are supported in Full SQL, and those that are non- standard. This feature does not include support for the "Catalog Lookup" option of the SQL Flagger (see feature #81).

64. Row and table constructors. All provisions of (row value constructor) and (table value constructor) as specified in Subclause 7.1, (row value constructor), and Subclause 7.2, (table value constructor), thereby providing support for multiple column row and table constructors. Removal of all Intermediate SQL Leveling Rules in these two clauses, allowing full use of (row value constructor) and (table value constructor) in Clause 8, "Predicates", and in Subclause 13.8, (insert statement).

65. Catalog name qualifiers. Removal of the Intermediate SQL restriction against reference to catalog names, as specified in part of Leveling Rule 1b of Subclause 5.4, "Names and identifiers".

66. Simple tables. Support for (simple table) and removal of the Intermediate SQL restriction against simple or explicit table references in a (query expression) as specified in Leveling Rules 1a and 1b of Subclause 7.10, (query expression).

67. Subqueries in CHECK. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 11.9, (check constraint definition), that prohibits specification of a (subquery) in a CHECK constraint, but retaining any other restrictions on (subquery) as required for Intermediate SQL. Support for granting and revoking REFERENCES privileges on all tables, including views, as specified in Subclause 10.3, (privileges), so that the (subquery) can reference base tables and views external to the table containing the check constraint. Removal of Intermediate SQL Leveling Rule 1b of Subclause 11.9, (check constraint definition), that allows implicit assumption of REFERENCES privileges on tables in Intermediate SQL.

68. Union and Cross join. Support for feature #4, "Joined table". In addition, support for UNION JOIN and CROSS JOIN by removal of Leveling Rule 1a and Leveling Rule 1b of Subclause 7.5, (joined table).

69. Collation and translation. Support for Subclause 11.30, (collation definition), Subclause 11.31, (drop collation statement), Subclause 11.32, (translation definition), and Subclause 11.33, (drop translation statement), as (SQL statement) in an explicit or implicit (procedure) Removal of Leveling Rules 1a in Subclauses 11.30 through 11.33. Support for granting and revoking USAGE privileges on any defined collations or translations, as specified in Subclause 10.3, (privileges), by removal of Leveling Rule 1a in Subclause 11.36, (grant statement). If feature #2, "Basic information schema" is supported, then implementation of Subclause 21.2.19, "COLLATIONS view", and Subclause 21.2.20, "TRANSLATIONS view", in the INFORMATION_SCHEMA. Removal of the Intermediate SQL restriction against reference to collation, translation, or conversion names, as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers", in Leveling Rule 1a of Subclause 6.13, (string value expression), and in Leveling Rules 1b and 1c of Subclause 11.1, (schema definition). Removal of the Intermediate SQL restriction against use of the (collate clause) as specified in Leveling Rule 1a of Subclause 10.5, (collate clause), Leveling Rule 1a of Subclause 11.4, (column definition), Leveling Rule 1a of Subclause 7.7, (group by clause), Leveling Rule 1a of Subclause 11.21, (domain definition), and Leveling Rule 1a of Subclause 11.28, (character set definition). Removal of the Intermediate SQL restrictions against use of character translations or form-of-use conversions, as specified in Leveling Rules 1b and 1c of Subclause 6.7, (string value function).

70. Referential update actions. Support for a (referential triggered action) that contains an (update rule) as defined in Subclause 11.8, (referential constraint definition). Removal of Leveling Rule 1b of Subclause 11.8, (referential constraint definition), that prohibits specification of an (update rule) in a (referential triggered action) thereby allowing an ON UPDATE trigger.

71. ALTER domain. Support for (alter domain statement) (set domain default clause) (drop domain default clause) (add domain constraint definition) and (drop domain constraint definition) all specified in Subclauses 11.22 through 11.26, as (SQL statement) in an explicit or implicit (procedure) Remove all Intermediate SQL Leveling Rules in Subclauses 11.22 through 11.26.

72. Deferrable constraints. Support for the (set constraints mode statement) specified in Subclause 14.2, as an (SQL statement> in an explicit or implicit (procedure) Removal of Leveling Rule 1a of Subclause 14.2. Removal of Intermediate SQL Leveling Rule 1a of Subclause 10.6, (constraint name definition) and (constraint attributes), that prohibits user specification of DEFERRABLE constraints and (constraint check time) as DEFERRED or IMMEDIATE.

73. INSERT column privileges. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 10.3, (privileges), that prohibits the granting of INSERT privileges on individual columns.

74. Referential MATCH types. Support for MATCH FULL and MATCH PARTIAL, as defined in Subclause 11.8, (referential constraint definition), by removal of Intermediate SQL Leveling Rule 1a. Support for the MATCH predicate, as specified in Subclause 8.10, (match predicate), by removal of Intermediate SQL Leveling Rules 1a of Subclause 8.1, (predicate), and Subclause 8.10, (match predicate).

75. View CHECK enhancements. Support for CASCADED and LOCAL options in the WITH CHECK OPTION clause of a (view definition) Removal of the Intermediate SQL restrictions against explicit declaration of these options, as specified in Leveling Rule 1a of Subclause 11.19, (view definition).

76. Session management. Support for "Session management", as specified in Subclause 16.1, (set catalog statement) Subclause 16.2, (set schema statement), and Subclause 16.3, (set names statement). Removal of all Intermediate SQL Leveling Rules in Subclauses 16.1, 16.2, and 16.3.

77. Connection management. Support for all provisions of Clause 15, "Connection management", including: CONNECT, SET CONNECTION, and DISCONNECT. Those (simple value specification) that are valid (connection target) and (user name) are implementation-defined, so long as Syntax Rule 1 of Subclause 15.1, (connect statement), is satisfied. The communication protocols used to implement the connection management statements are implementation-defined. Removal of all Intermediate SQL Leveling Rules in Subclauses 15.1, 15.2, and 15.3. Removal of the Intermediate SQL restriction against reference to (connection name) as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers".

78. Self-referencing operations. Removal of the Intermediate SQL restrictions against self-referencing DELETE, INSERT, and UPDATE statements, as specified in Leveling Rules 1a of Subclause 13.7, (delete statement: searched), Subclause 13.8, (insert statement), and Subclause 13.10, (update statement: searched).

79. Insensitive cursors. Support for the INSENSITIVE option on a cursor declaration, by removal of the Intermediate SQL Leveling Rule 1a of Subclause 13.1, (declare cursor). If feature #54, "Full dynamic SQL", is supported, then removal of Leveling Rule 1a in Subclause 17.12, (dynamic declare cursor).

80. Full set function. Support for feature #44, "Intermediate set function". In addition, removal of the Intermediate SQL restrictions against use of DISTINCT in a (general set function) as specified in Leveling Rule 1a of Subclause 6.5, (set function specification), and removal of the Intermediate SQL restriction against multiple use of DISTINCT in a (query specification) as specified in Leveling Rule 1a of Subclause 7.9, (query specification).

81. Catalog flagging. Support for both the "Syntax Only" and "Catalog Lookup" extent of checking options of the SQL Flagger feature, as defined in Subclause 4.34 of ANSI X3.135-1992. This facility would allow the SQL Flagger to catch a syntactic extension that violates a Syntax Rule dependent upon information stored in the INFORMATION_SCHEMA.

82. Local table references. Support for qualified local table references of the form MODULE.T, by removal of the Intermediate SQL restriction against reference to a (qualified local table name) as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers".

83. Full cursor update. Support for the updatability of SCROLL or ORDER BY cursors, by removal of Leveling Rule 1b of Subclause 13.1, (declare cursor) and Leveling Rule 1a of Subclause 13.9, (update statement: positioned). If feature #54, "Full dynamic SQL", is supported, then removal of Leveling Rule 1b in Subclause 17.12, (dynamic declare cursor).

84. RDA/SQL-Client. Conformance to the Remote Database Access (RDA) component of FIPS PUB 146, "Government Open Systems Interconnection Profile (GOSIP)", by satisfying all of the requirements for the "Immediate Execution" profile as an "RDA Client" and abiding by the NIST OSI Implementor's Workshop implementation agreements. The Immediate Execution profile requires the following RDA functional units: Dialogue Initialization, Dialogue Termination, Transaction Management (Basic Application Context), Resource Handling, and Immediate Execution DBL. Conformance to FIPS PUB 127-2, "FIPS SQL", at the Entry SQL level or above, and support for FIPS feature #1, "Dynamic SQL", and FIPS feature #2, "Basic information schema". Support for the FIPS feature #77, "Connection management", defined above, with the following additional requirements: the CONNECT statement triggers Dialogue Initialization (R-Initialize Service) and Resource Handling (R-Open Service); the DISCONNECT statement triggers Resource Handling (R-Close Service) and Dialogue Termination (R-Terminate Service); the SET CONNECTION statement re-establishes active Resource Handling and, if necessary may trigger Dialogue Termination (R-Close Service) to make a current SQL- connection dormant; Transaction Management supports SQL-COMMIT and SQL-ROLLBACK, and satisfies the timing and semantics of an SQL-Transaction; other SQL-Statements, in the host language binding style supported by the SQL-implementation, are mapped to Immediate Execution DBL (R-ExecuteDBL Service) protocols.

85. RDA/SQL-Server. Conformance to Remote Database Access (RDA) component of FIPS PUB 146, "Government Open Systems Interconnection Profile (GOSIP)", by satisfying all of the requirements for the "Immediate Execution" profile as an "RDA Server" and abiding by the NIST OSI Implementor's Workshop implementation agreements. The Immediate Execution profile includes: Dialogue Initialization, Dialogue Termination, Transaction Management (Basic Application Context), Resource Handling, and Immediate Execution DBL. Conformance to FIPS PUB 127-2, "FIPS SQL", at the Entry SQL level or above, and support for FIPS feature #1, "Dynamic SQL", and FIPS feature #2, "Basic information schema".

86. RDA Stored Execution. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Stored Execution Functional Unit as specified in ISO/IEC 9579- 2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop.

87. RDA Cancel. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Cancel Functional Unit as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop.

88. RDA Status. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Status Functional Unit as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop.

89. RDA TP Application Context. Conformance to feature #84, "RDA/SQL- Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA SQL TP Application Context as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and dependent upon ISO/IEC DIS 10026 (Distributed Transaction Processing), and with Distributed Transaction Processing implementor agreements specified by the NIST OSIImplementor's Workshop.

     CREATE SCHEMA FIPS_DOCUMENTATION 
         AUTHORIZATION "_SYSTEM"
         DEFAULT CHARACTER SET SQL_CHARACTER

     CREATE TABLE SQL_FEATURES
         (
         FEATURE_ID   SMALLINT   PRIMARY KEY   CHECK
          (FEATURE_ID > 0),
         FEATURE_NAME   CHARACTER (50)   NOT NULL,
         CLASSIFICATION   CHARACTER (12)   NOT NULL  CHECK
          (CLASSIFICATION IN
         ('TRANSITIONAL', 'INTERMEDIATE', 'FULL', 'RDA')),
         IS_SUPPORTED  CHARACTER (3) NOT NULL CHECK
(IS_SUPPORTED IN ('YES',
         'NO')),
         IS_VERIFIED   CHARACTER (3)   NOT NULL   CHECK
          (IS_VERIFIED IN ('YES',
         'NO')),
         FEATURE_COMMENTS   VARCHAR (500)   CHARACTER SET
          SQL_TEXT,
              CHECK (IS_SUPPORTED='YES' OR IS_VERIFIED='NO')
         )

     CREATE TABLE SQL_SIZING
         (
         SIZING_ID   SMALLINT   PRIMARY KEY   CHECK (SIZING_ID >
          0), 
         DESCRIPTION   CHARACTER (50)   NOT NULL,
         ENTRY_VALUE   INTEGER,
         INTERMEDIATE_VALUE   INTEGER,
         VALUE_SUPPORTED   INTEGER,
         SIZING_COMMENTS   VARCHAR (500)   CHARACTER SET
          SQL_TEXT
         )

     GRANT SELECT, REFERENCES ON SQL_FEATURES TO PUBLIC
          WITH GRANT OPTION
     GRANT SELECT, REFERENCES ON SQL_SIZING TO PUBLIC WITH
          GRANT OPTION

   Sizing                                                 Entry    Interm.
     Id        Description                                Value    Value

     1.  Length of an identifier                            18      128
     2   CHARACTER max length                              240     1000
     3.  CHARACTER VARYING max length                      254     1000
     4.  BIT max length in bits                              *     8000
     5.  BIT VARYING max length in bits                      *     8000
     6.  NATIONAL CHARACTER max length                       *      500
     7.  NATIONAL CHAR VARYING max length                    *      500
     8.  NUMERIC decimal precision                          15       15
     9.  DECIMAL decimal precision                          15       15
     10. INTEGER decimal precision                           9        *
     11. INTEGER binary precision                            *       31
     12. SMALLINT decimal precision                          4        *
     13. SMALLINT binary precision                           *       15
     14. FLOAT binary mantissa precision                    20       47
     15. FLOAT binary exponent precision                     *        9
     16. REAL binary mantissa precision                     20       23
     17. REAL binary exponent precision                      *        7
     18. DOUBLE PRECISION binary mantissa precision         30       47
     19. DOUBLE PRECISION binary exponent precision          *        9
     20. TIME decimal fractional second precision            *        0
     21. TIMESTAMP decimal fractional second precision       *        6
     22. INTERVAL decimal fractional second precision        *        6
     23. INTERVAL decimal leading field precision            *        7
     24. Columns in a table                                100      250
     25. Values in an INSERT statement                     100      250
     26. Set clauses in UPDATE statement                    20      250
     27. Length of a row (see Note 1)                     2000     8000
     28. Columns in UNIQUE constraint                        6       15
     29. Length of UNIQUE columns (Note 1)                 120      750
     30. Columns in GROUP BY column list                     6       15
     31. Length of GROUP BY column list (Note 1)           120      750
     32. Sort items in ORDER BY clause                       6       15
     33. Length of ORDER BY column list (Note 1)           120      750
     34. Referencing columns in FOREIGN KEY                  6       15
     35. Length of FOREIGN KEY column list (Note 1)        120      750
     36. Table references in an SQL statement (Note 3)      15       50
     37. Cursors simultaneously open                        10      100
     38. WHEN clauses in a CASE expression                   *       50
     39. Columns in a named columns JOIN                     *       15
     40. Length of JOIN column list (Note 1)                 *      750
     41. Items in a SELECT list                            100      250
     42. Length of SQL  (Note 2)                             *    30000
     43. Length of  (Note 2)                                 *     4000
     44. Length of  (Note 2)                                 *    
4000
     45. Occurrences in an ALLOCATE DESCRIPTOR               *      100
     46. Default occurrences in ALLOCATE DESCRIPTOR          *      100