This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
The complete SQL Standard consists of five interrelated documents. Additional parts, five in number so far, describing related features will be added sometime in the future. This book describes only the first five parts, Standard SQL3, which consists of these documents.
Part 1: SQL/Framework (ISO/IEC 9075-1, approximately 100 pages) defines the fundamental concepts on which SQL is based, as well as specifying the general requirements for SQL conformance. All parts of the Standard are dependent on SQL/Framework.
Part 2: SQL/Foundation (ISO/IEC 9075-2, approximately 1,050 pages) defines the fundamental syntax and operations of SQL. All parts of the Standard, except for SQL/Framework, are dependent on SQL/Foundation.
Part 3: SQL/Call-Level Interface, or CLI (ISO/IEC 9075-3, approximately 514 pages), defines an application programming interface to SQL. No part of the Standard is dependent on SQL/CLI.
Part 4: SQL/Persistent Stored Modules, or PSM (ISO/IEC 9075-4, approximately 193 pages) defines both the control structures that define SQL Routines and the Modules that may contain SQL Routines. No part of the Standard is dependent on SQL/PSM.
Part 5: SQL/Host Language Bindings (ISO/IEC 9075-5, approximately 270 pages) defines methods for embedding SQL statements in application programs written in a standard programming language. No part of the Standard is dependent on SQL/Bindings.
The SQL3 Standard identifies two levels of SQL conformance which a DBMS may claim: Core SQL support and enhanced SQL support. (Conformance levels for SQL applications are also given; we ignore these in this book.) In order to claim conformance with the SQL Standard, a DBMS must support all of the following:
- All features defined in SQL/Framework, including an SQL Object Identifier that states the level of conformance being claimed as well as all Core SQL features defined in SQL/Foundation -- see Appendix B "SQL Taxonomy" for these requirements. A Core SQL DBMS does not have to support any of the features defined in the other parts of the Standard.
- At least one of the following two binding styles:
- The SQL-client Module binding style (defined in SQL/Foundation) for at least one host language.
- The Embedded SQL binding style (defined in SQL/Bindings) for at least one host language.
In order to claim conformance, a DBMS must state whether or not the SQL-client Module binding style is supported and, if so, which of the host languages (Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I) are supported. If applicable, the DBMS must also state which of these <keyword>s (ADA, C, COBOL, FORTRAN, MUMPS, PASCAL, PLI and SQL) may be specified for the LANGUAGE clause in an <external body reference>. A DBMS which supports MUMPS goes beyond Core SQL conformance.
<SQL Object Identifier>
[Obscure Rule] applies for this section.
The SQL Object Identifier identifies the characteristics of an SQL DBMS to
other entities in an open systems environment. (The same information is
available to SQL-data users in the
INFORMATION_SCHEMA.SQL_LANGUAGES View.) Listing 1.1 shows
the required syntax for the SQL Object Identifier.
Listing 1.1 Required Syntax for the SQL Object Identifier
<SQL Object Identifier> ::= <SQL provenance> <SQL variant> <SQL provenance> ::= <arc1> <arc2> <arc3> <arc1> ::= iso | 1 | iso(1) <arc2> ::= standard | 0 | standard(0) <arc3> ::= 9075 <SQL variant> ::= <SQL edition> <SQL conformance> <SQL edition> ::= <1987> | <1989> | <1992> | <199x> <1987> ::= 0 | edition1987(0) <1989> ::= <1989 base> <1989 package> <1989 base> ::= 1 | edition1989(1) <1989 package> ::= <integrity no> | <integrity yes> <integrity no> ::= 0 | IntegrityNo(0) <integrity yes> ::= 1 | IntegrityYes(1) <1992> ::= 2 | edition1992(2) <199x> ::= 3 | edition199x(3) <SQL conformance> ::= <level> | <parts> <level> ::= <low> | <intermediate> | <high> <low> ::= 0 | Low(0) <intermediate> ::= 1 | Intermediate(1) <high> ::= 2 | High(2) <parts> ::= <Part 3> <Part 4> <Part 5> <Part 6> <Part 7> <Part 8> <Part 9> <Part 10> <Part n> ::= <Part n no> | <Part n yes> <Part n no> ::= 0 | Part-nNo(0) <Part n yes> ::= !! per ISO/IEC 9075-n
The SQL Object Identifier's <SQL provenance> identifies the Standards document that governs the DBMS's SQL conformance, i.e., "iso standard 9075". The <SQL variant> identifies the version of the SQL Standard that is supported.
The SQL Object Identifier's <SQL conformance> identifies the conformance level being claimed for that version of the Standard. There are four options.
- If <SQL edition> is <1992>, the version of the Standard supported is SQL-92 and the Object Identifier must state which <level> of <SQL conformance> is claimed. A <level> of <low> means Entry SQL is supported, a <level> of <intermediate> means Intermediate SQL is supported, and a <level> of <high> means Full SQL is supported. A DBMS may claim a <high> <SQL conformance> only if SQL-92 is supported.
- If <SQL edition> is <1987>, the version of the Standard supported is SQL-86. If <SQL edition> is <1989>, the version of the Standard supported is SQL-89. In both of these cases, the Object Identifier must state which <level> of <SQL conformance> is claimed. A <level> of <low> means Level 1 SQL is supported and a <level> of <intermediate> means Level 2 SQL is supported.
- If <SQL edition> is <1989>, an additional conformance claim must be made. A <1989 package> value of <integrity yes> means that the features defined in ANSI X3.135-1989 "Database Language - SQL with Integrity Enhancement" are supported by the DBMS. A <1989 package> value of <integrity no> means that the integrity enhancement features are not supported.
- If <SQL edition> is <199x>, the version of the Standard supported is SQL3, with Core SQL conformance claimed. In this case, the Object Identifier must also state the DBMS's <SQL conformance> for each <part> of the Standard. A <Part n yes> value for any part means that the DBMS fully supports that part of the Standard. A <Part n no> value means that part of the Standard is not fully supported.
In order to claim enhanced conformance with the SQL Standard, a DBMS must also (a) fully support one or more of parts 3 and up of the Standard and/or one or more additional "packages" of SQL features (identified below by their "Feature ID" values from Appendix B, "SQL Taxonomy") and (b) provide an SQL Flagger.
[Obscure Rule] applies for the rest of this section.
The SQL Standard specifies seven SQL packages which may be supported by a DBMS claiming enhanced SQL conformance. They are as follows:
- Enhanced datetime facilities package -- To claim conformance with the
"enhanced datetime facilities" SQL package, a DBMS must also support:
- Feature ID F052 Interval data type.
- Feature ID F411 Time zone specification.
- Feature ID F551 Full datetime.
- Feature ID T161 Optional interval qualifier.
- Enhanced integrity management package -- To claim conformance with the
"enhanced integrity management" SQL package, a DBMS must also support:
- Feature ID F521 Assertions.
- Feature ID E142 Referential delete actions.
- Feature ID F701 Referential update actions.
- Feature ID F491 Constraint management.
- Feature ID F671 Subqueries in CHECK constraints.
- Feature ID T211 Triggers.
- Feature ID T212 FOR EACH STATEMENT triggers.
- Feature ID T191 Referential action RESTRICT.
- OLAP facilities package -- To claim conformance with the "OLAP facilities"
SQL package, a DBMS must also support:
- Feature ID T431 CUBE and ROLLUP.
- Feature ID F302 INTERSECT table operator.
- Feature ID F641 Row and table constructors.
- Feature ID F401 FULL OUTER JOIN.
- Feature ID F471 Scalar subquery values.
- PSM package -- To claim conformance with the "PSM" SQL package, a DBMS must
- Feature ID P01 Stored Modules (<SQL-server Module definition>).
- Feature ID P02 Computational completeness.
- Feature ID P03 INFORMATION_SCHEMA views.
- CLI package -- To claim conformance with the "CLI" SQL package, a DBMS must
- Feature ID C01 SQL/CLI.
- Basic object support package -- To claim conformance with the "basic object
support" SQL package, a DBMS must also support:
- Feature ID T322 Overloading of SQL-invoked functions and SQL-invoked procedures.
- Feature ID O021 Basic user-defined types, including single inheritance.
- Feature ID O041 Reference types.
- Feature ID O051 CREATE TABLE of type.
- Feature ID O091 Basic array support.
- Feature ID O092 Arrays of UDTs.
- Feature ID O094 Arrays of reference types.
- Feature ID O121 Dereference operation.
- Feature ID O131 Reference operation.
- Feature ID O141 Attribute and field reference.
- Feature ID O171 Array expressions.
- Feature ID O191 Basic SQL routines on user-defined types, including dynamic dispatch.
- Feature ID O201 SQL routine on arrays.
- Feature ID O232 Array locators.
- Enhanced object support package -- To claim conformance with the "enhanced
object support" SQL package, a DBMS must also support:
- Feature ID O022 Enhanced user-defined types, including constructor option, attribute defaults, multiple inheritance and ordering clause.
- Feature ID O061 ALTER TABLE, ADD named row type.
- Feature ID O071 SQL-paths in function and type name resolution.
- Feature ID O081 Subtables.
- Feature ID O111 ONLY in query expressions (to restrict subtable search).
- Feature ID O151 Type predicate.
- Feature ID O161 <subtype treatment>.
- Feature ID O192 SQL routines on user-defined types, including identity functions and generalized expressions.
- Feature ID O211 User-defined cast functions.
- Feature ID O231 UDT locators.
An SQL Flagger is a facility that identifies SQL language extensions or processing alternatives. It must be provided by a DBMS claiming enhanced SQL conformance. The Flagger's purpose is to help you produce portable SQL code. This is necessary because the SQL Standard allows conforming DBMSs to provide options for processing operations that the Standard doesn't address (e.g., a CREATE INDEX statement). It also allows DBMSs to provide options for processing Standard-defined SQL in a non-conforming manner, provided that the non-conforming results are returned only when you explicitly request them. Your DBMS's Flagger must identify all the non-standard syntax, features and options supported, but it only has to do a static check of SQL syntax; the Standard doesn't require a Flagger to detect extensions that cannot be determined until runtime.
An SQL Flagger has to provide one or more of these "level of flagging" options to identify SQL language that violates a given subset of SQL:
- Core SQL Flagging -- flags non-conforming Core SQL features.
- Part SQL Flagging -- flags non-conforming enhanced SQL features.
- Package SQL Flagging -- flags non-conforming package SQL features.
A flagger also has to provide one or more of these "extent of checking" options:
- Syntax Only -- only the SQL language presented is analyzed; the Flagger checks for syntax violations without accessing INFORMATION_SCHEMA so it doesn't necessarily detect violations that depend on the <data type> of syntactic elements.
- Catalog Lookup -- the SQL language and the metadata is analyzed; the Flagger checks for both syntax and access violations (except for access violations that deal with Privileges).
In order to claim conformance with the SQL Standard, a DBMS must state four things:
- Level of conformance supported for a given version of the Standard.
- Binding style(s) supported.
- Host language(s) supported.
- The DBMS's definitions are for all "elements and actions" the Standard specifies are implementation-defined. (Decisions made for the Standard's implementation-dependent features don't have to be documented.)
Portions of the text in this entry are Copyright © 1999 by Ocelot Computer Services Incorporated. Used by permission.