Information Technology -
Database Language SQL
(Proposed revised text of DIS 9075)
July 1992
(Second Informal Review Draft) ISO/IEC 9075:1992, Database
Language SQL- July 30, 1992
Digital Equipment Corporation
Maynard, Massachusetts
Contents Page
Foreword.........................................................xi
Introduction.....................................................xiii
1 Scope ........................................................ 1
2 Normative references ......................................... 3
3 Definitions, notations, and conventions ...................... 5
3.1 Definitions ................................................ 5
3.1.1Definitions taken from ISO/IEC 10646 ....................... 5
3.1.2Definitions taken from ISO 8601 ............................ 5
3.1.3Definitions provided in this International Standard ........ 5
3.2 Notation ................................................... 7
3.3 Conventions ................................................ 9
3.3.1Informative elements ....................................... 9
3.3.2Specification of syntactic elements ........................ 9
3.3.3Specification of the Information Schema ....................10
3.3.4Use of terms ...............................................10
3.3.4Exceptions .................................................10
3.3.4Syntactic containment ......................................11
3.3.4Terms denoting rule requirements ...........................12
3.3.4Rule evaluation order ......................................12
3.3.4Conditional rules ..........................................13
3.3.4Syntactic substitution .....................................13
3.3.4Other terms ................................................14
3.3.5Descriptors ................................................14
3.3.6Index typography ...........................................15
3.4 Object identifier for Database Language SQL ................16
4 Concepts .....................................................19
4.1 Data types .................................................19
4.2 Character strings ..........................................20
4.2.1Character strings and collating sequences ..................20
4.2.2Operations involving character strings .....................22
4.2.2Operators that operate on character strings and return char-
acter strings...............................................22
4.2.2Other operators involving character strings ................23
4.2.3Rules determining collating sequence usage .................23
4.3 Bit strings ................................................26
4.3.1Bit string comparison and assignment .......................27
4.3.2Operations involving bit strings ...........................27
4.3.2Operators that operate on bit strings and return bit strings
............................................................27
4.3.2Other operators involving bit strings ......................27
ii Database Language SQL
4.4 Numbers ....................................................27
4.4.1Characteristics of numbers .................................28
4.4.2Operations involving numbers ...............................29
4.5 Datetimes and intervals ....................................29
4.5.1Datetimes ..................................................30
4.5.2Intervals ..................................................32
4.5.3Operations involving datetimes and intervals ...............34
4.6 Type conversions and mixing of data types ..................34
4.7 Domains ....................................................35
4.8 Columns ....................................................36
4.9 Tables .....................................................37
4.10 Integrity constraints ......................................40
4.10.Checking of constraints ....................................41
4.10.Table constraints ..........................................41
4.10.Domain constraints .........................................43
4.10.Assertions .................................................43
4.11 SQL-schemas ................................................44
4.12 Catalogs ...................................................45
4.13 Clusters of catalogs .......................................45
4.14 SQL-data ...................................................45
4.15 SQL-environment ............................................46
4.16 Modules ....................................................46
4.17 Procedures .................................................47
4.18 Parameters .................................................47
4.18.Status parameters ..........................................47
4.18.Data parameters ............................................48
4.18.Indicator parameters .......................................48
4.19 Diagnostics area ...........................................48
4.20 Standard programming languages .............................49
4.21 Cursors ....................................................49
4.22 SQL-statements .............................................51
4.22.Classes of SQL-statements ..................................51
4.22.SQL-statements classified by function ......................52
4.22.Embeddable SQL-statements ..................................55
4.22.Preparable and immediately executable SQL-statements .......56
4.22.Directly executable SQL-statements .........................58
4.22.SQL-statements and transaction states ......................59
4.23 Embedded syntax ............................................61
4.24 SQL dynamic statements .....................................61
4.25 Direct invocation of SQL ...................................64
4.26 Privileges .................................................64
4.27 SQL-agents .................................................66
4.28 SQL-transactions ...........................................67
4.29 SQL-connections ............................................70
4.30 SQL-sessions ...............................................72
Table of Contents iii
4.31 Client-server operation ....................................74
4.32 Information Schema .........................................75
4.33 Leveling ...................................................75
4.34 SQL Flagger ................................................76
5 Lexical elements .............................................79
5.1 <SQL terminal character> ...................................79
5.2 <token> and <separator> ....................................82
5.3 <literal> ..................................................89
5.4 Names and identifiers ......................................98
6 Scalar expressions ...........................................107
6.1 <data type> ................................................107
6.2 <value specification> and <target specification> ...........114
6.3 <table reference> ..........................................118
6.4 <column reference> .........................................121
6.5 <set function specification> ...............................124
6.6 <numeric value function> ...................................128
6.7 <string value function> ....................................132
6.8 <datetime value function> ..................................139
6.9 <case expression> ..........................................141
6.10 <cast specification> .......................................144
6.11 <value expression> .........................................155
6.12 <numeric value expression> .................................157
6.13 <string value expression> ..................................160
6.14 <datetime value expression> ................................165
6.15 <interval value expression> ................................168
7 Query expressions ............................................173
7.1 <row value constructor> ....................................173
7.2 <table value constructor> ..................................176
7.3 <table expression> .........................................177
7.4 <from clause> ..............................................178
7.5 <joined table> .............................................180
7.6 <where clause> .............................................185
7.7 <group by clause> ..........................................187
7.8 <having clause> ............................................189
7.9 <query specification> ......................................191
7.10 <query expression> .........................................196
7.11 <scalar subquery>, <row subquery>, and <table subquery> ....203
8 Predicates ...................................................205
8.1 <predicate> ................................................205
8.2 <comparison predicate> .....................................207
8.3 <between predicate> ........................................211
8.4 <in predicate> .............................................212
iv Database Language SQL
8.5 <like predicate> ...........................................214
8.6 <null predicate> ...........................................218
8.7 <quantified comparison predicate> ..........................220
8.8 <exists predicate> .........................................222
8.9 <unique predicate> .........................................223
8.10 <match predicate> ..........................................224
8.11 <overlaps predicate> .......................................227
8.12 <search condition> .........................................229
9 Data assignment rules ........................................231
9.1 Retrieval assignment .......................................231
9.2 Store assignment ...........................................234
9.3 Set operation result data types ............................237
10 Additional common elements ...................................239
10.1 <interval qualifier> .......................................239
10.2 <language clause> ..........................................243
10.3 <privileges> ...............................................245
10.4 <character set specification> ..............................248
10.5 <collate clause> ...........................................251
10.6 <constraint name definition> and <constraint attributes> ...252
11 Schema definition and manipulation ...........................255
11.1 <schema definition> ........................................255
11.2 <drop schema statement> ....................................258
11.3 <table definition> .........................................260
11.4 <column definition> ........................................262
11.5 <default clause> ...........................................266
11.6 <table constraint definition> ..............................270
11.7 <unique constraint definition> .............................272
11.8 <referential constraint definition> ........................274
11.9 <check constraint definition> ..............................281
11.10<alter table statement> ....................................283
11.11<add column definition> ....................................284
11.12<alter column definition> ..................................286
11.13<set column default clause> ................................287
11.14<drop column default clause> ...............................288
11.15<drop column definition> ...................................289
11.16<add table constraint definition> ..........................291
11.17<drop table constraint definition> .........................292
11.18<drop table statement> .....................................294
11.19<view definition> ..........................................296
11.20<drop view statement> ......................................300
11.21<domain definition> ........................................301
11.22<alter domain statement> ...................................304
11.23<set domain default clause> ................................305
Table of Contents v
11.24<drop domain default clause> ...............................306
11.25<add domain constraint definition> .........................307
11.26<drop domain constraint definition> ........................308
11.27<drop domain statement> ....................................309
11.28<character set definition> .................................311
11.29<drop character set statement> .............................313
11.30<collation definition> .....................................314
11.31<drop collation statement> .................................318
11.32<translation definition> ...................................320
11.33<drop translation statement> ...............................323
11.34<assertion definition> .....................................325
11.35<drop assertion statement> .................................328
11.36<grant statement> ..........................................329
11.37<revoke statement> .........................................333
12 Module .......................................................341
12.1 <module> ...................................................341
12.2 <module name clause> .......................................344
12.3 <procedure> ................................................346
12.4 Calls to a <procedure> .....................................352
12.5 <SQL procedure statement> ..................................368
13 Data manipulation ............................................371
13.1 <declare cursor> ...........................................371
13.2 <open statement> ...........................................375
13.3 <fetch statement> ..........................................377
13.4 <close statement> ..........................................381
13.5 <select statement: single row> .............................382
13.6 <delete statement: positioned> .............................384
13.7 <delete statement: searched> ...............................386
13.8 <insert statement> .........................................388
13.9 <update statement: positioned> .............................391
13.10<update statement: searched> ...............................394
13.11<temporary table declaration> ..............................397
14 Transaction management .......................................399
14.1 <set transaction statement> ................................399
14.2 <set constraints mode statement> ...........................401
14.3 <commit statement> .........................................403
14.4 <rollback statement> .......................................405
15 Connection management ........................................407
15.1 <connect statement> ........................................407
15.2 <set connection statement> .................................410
15.3 <disconnect statement> .....................................412
vi Database Language SQL
16 Session management ...........................................415
16.1 <set catalog statement> ....................................415
16.2 <set schema statement> .....................................417
16.3 <set names statement> ......................................419
16.4 <set session authorization identifier statement> ...........420
16.5 <set local time zone statement> ............................422
17 Dynamic SQL ..................................................425
17.1 Description of SQL item descriptor areas ...................425
17.2 <allocate descriptor statement> ............................431
17.3 <deallocate descriptor statement> ..........................433
17.4 <get descriptor statement> .................................434
17.5 <set descriptor statement> .................................438
17.6 <prepare statement> ........................................442
17.7 <deallocate prepared statement> ............................449
17.8 <describe statement> .......................................450
17.9 <using clause> .............................................451
17.10<execute statement> ........................................459
17.11<execute immediate statement> ..............................462
17.12<dynamic declare cursor> ...................................464
17.13<allocate cursor statement> ................................465
17.14<dynamic open statement> ...................................467
17.15<dynamic fetch statement> ..................................469
17.16<dynamic close statement> ..................................471
17.17<dynamic delete statement: positioned> .....................472
17.18<dynamic update statement: positioned> .....................474
17.19<preparable dynamic delete statement: positioned> ..........476
17.20<preparable dynamic update statement: positioned> ..........477
18 Diagnostics management .......................................479
18.1 <get diagnostics statement> ................................479
19 Embedded SQL .................................................489
19.1 <embedded SQL host program> ................................489
19.2 <embedded exception declaration> ...........................497
19.3 <embedded SQL Ada program> .................................500
19.4 <embedded SQL C program> ...................................504
19.5 <embedded SQL COBOL program> ...............................508
19.6 <embedded SQL Fortran program> .............................512
19.7 <embedded SQL MUMPS program> ...............................515
19.8 <embedded SQL Pascal program> ..............................517
19.9 <embedded SQL PL/I program> ................................520
20 Direct invocation of SQL .....................................525
20.1 <direct SQL statement> .....................................525
20.2 <direct select statement: multiple rows> ...................530
Table of Contents vii
21 Information Schema and Definition Schema .....................533
21.1 Introduction ...............................................533
21.2 Information Schema .........................................535
21.2.INFORMATION_SCHEMA Schema ..................................535
21.2.INFORMATION_SCHEMA_CATALOG_NAME base table .................536
21.2.INFORMATION_SCHEMA_CATALOG_NAME_CARDINALITY assertion ......537
21.2.SCHEMATA view ..............................................538
21.2.DOMAINS view ...............................................539
21.2.DOMAIN_CONSTRAINTS view ....................................541
21.2.TABLES view ................................................542
21.2.VIEWS view .................................................543
21.2.COLUMNS view ...............................................544
21.2.TABLE_PRIVILEGES view ......................................546
21.2.COLUMN_PRIVILEGES view .....................................547
21.2.USAGE_PRIVILEGES view ......................................548
21.2.TABLE_CONSTRAINTS view .....................................549
21.2.REFERENTIAL_CONSTRAINTS view ...............................550
21.2.CHECK_CONSTRAINTS view .....................................551
21.2.KEY_COLUMN_USAGE view ......................................552
21.2.ASSERTIONS view ............................................553
21.2.CHARACTER_SETS view ........................................554
21.2.COLLATIONS view ............................................555
21.2.TRANSLATIONS view ..........................................556
21.2.VIEW_TABLE_USAGE view ......................................557
21.2.VIEW_COLUMN_USAGE view .....................................558
21.2.CONSTRAINT_TABLE_USAGE view ................................559
21.2.CONSTRAINT_COLUMN_USAGE view ...............................561
21.2.COLUMN_DOMAIN_USAGE view ...................................562
21.2.SQL_LANGUAGES view .........................................563
21.2.SQL_IDENTIFIER domain ......................................564
21.2.CHARACTER_DATA domain ......................................564
21.2.CARDINAL_NUMBER domain .....................................565
21.3 Definition Schema ..........................................566
21.3.Introduction ...............................................566
21.3.DEFINITION_SCHEMA Schema ...................................567
21.3.USERS base table ...........................................568
21.3.SCHEMATA base table ........................................569
21.3.DATA_TYPE_DESCRIPTOR base table ............................570
21.3.DOMAINS base table .........................................573
21.3.DOMAIN_CONSTRAINTS base table ..............................574
21.3.TABLES base table ..........................................576
21.3.VIEWS base table ...........................................578
21.3.COLUMNS base table .........................................580
21.3.VIEW_TABLE_USAGE base table ................................583
21.3.VIEW_COLUMN_USAGE base table ...............................584
viii Database Language SQL
21.3.TABLE_CONSTRAINTS base table ...............................585
21.3.KEY_COLUMN_USAGE base table ................................588
21.3.REFERENTIAL_CONSTRAINTS base table .........................590
21.3.CHECK_CONSTRAINTS base table ...............................593
21.3.CHECK_TABLE_USAGE base table ...............................595
21.3.CHECK_COLUMN_USAGE base table ..............................596
21.3.ASSERTIONS base table ......................................598
21.3.TABLE_PRIVILEGES base table ................................600
21.3.COLUMN_PRIVILEGES base table ...............................602
21.3.USAGE_PRIVILEGES base table ................................604
21.3.CHARACTER_SETS base table ..................................606
21.3.COLLATIONS base table ......................................608
21.3.TRANSLATIONS base table ....................................610
21.3.SQL_LANGUAGES base table ...................................612
21.4 Assertions on the base tables ..............................616
21.4.UNIQUE_CONSTRAINT_NAME assertion ...........................616
21.4.EQUAL_KEY_DEGREES assertion ................................617
21.4.KEY_DEGREE_GREATER_THAN_OR_EQUAL_TO_1 assertion ............618
22 Status codes .................................................619
22.1 SQLSTATE ...................................................619
22.2 SQLCODE ....................................................624
23 Conformance ..................................................625
23.1 Introduction ...............................................625
23.2 Claims of conformance ......................................625
23.3 Extensions and options .....................................626
23.4 Flagger requirements .......................................626
23.5 Processing methods .........................................627
Annex A Leveling the SQL Language..............................629
A.1 Intermediate SQL Specifications ............................629
A.2 Entry SQL Specifications ...................................640
Annex B Implementation-defined elements........................653
Annex C Implementation-dependent elements......................667
Annex D Deprecated features....................................675
Annex E Incompatibilities with ISO/IEC 9075:1989...............677
Annex F Maintenance and interpretation of SQL..................685
Index
Table of Contents ix
TABLES
Table Page
1 Collating coercibility rules for monadic operators .........24
2 Collating coercibility rules for dyadic operators ..........24
3 Collating sequence usage for comparisons ...................25
4 Fields in datetime items ...................................30
5 Fields in year-month INTERVAL items ........................32
6 Fields in day-time INTERVAL items ..........................32
7 Valid values for fields in INTERVAL items ..................33
8 Valid operators involving datetimes and intervals ..........34
9 SQL-transaction isolation levels and the three phenomena ...69
10 Valid values for fields in datetime items ..................112
11 Valid values for fields in INTERVAL items ..................113
12 <null predicate> semantics .................................218
13 Truth table for the AND boolean ............................230
14 Truth table for the OR boolean .............................230
15 Truth table for the IS boolean .............................230
16 Standard programming languages .............................243
17 Data types of <key word>s used in SQL item descriptor areas
............................................................427
18 Codes used for SQL data types in Dynamic SQL ...............429
19 Codes associated with datetime data types in Dynamic SQL ...429
20 Codes used for <interval qualifier>s in Dynamic SQL ........430
21 <identifier>s for use with <get diagnostics statement> .....481
22 SQL-statement character codes for use in the diagnostics
area........................................................482
23 SQLSTATE class and subclass values .........................619
24 SQLCODE values .............................................624
x Database Language SQL
X3H2-92-154/DBL CBR-002
Foreword
ISO (the International Organization for Standardization) is a
worldwide federation of national standards bodies (ISO member
bodies). The work of preparing International Standards is nor-
mally carried out through ISO technical committees. Each member
body interested in a subject for which a technical committee has
been established has the right to be represented on that committee.
International organizations, governmental and non-governmental,
in liaison with ISO, also take part in the work. ISO collaborates
closely with the International Electrotechnical Commission (IEC) on
all matters of electrotechnical standardization.
Draft International Standards adopted by the technical committees
are circulated to the member bodies for approval before their ac-
ceptance as International Standards by the ISO Council. They are
approved in accordance with ISO procedures requiring at least 75%
approval by the member bodies voting.
International Standard ISO/IEC 9075:1992 was prepared by Joint
Technical Committee ISO/IEC JTC1, Information Processing Systems.
It cancels and replaces International Standard ISO/IEC 9075:1989,
Database Language-SQL, of which it constitutes a technical revi-
sion.
This International Standard contains seven informative annexes:
- Annex A (informative): Leveling the SQL Language;
- Annex B (informative): Implementation-defined elements;
- Annex C (informative): Implementation-dependent elements;
- Annex D (informative): Deprecated features;
- Annex E (informative): Incompatibilities with ISO/IEC 9075:1989;
and
- Annex F (informative): Maintenance and interpretation of SQL.
Foreword xi
X3H2-92-154/DBL CBR-002
Introduction
This International Standard was approved in 1992.
This International Standard was developed from ISO/IEC 9075:1989,
Information Systems, Database Language SQL with Integrity
Enhancements, and replaces that International Standard. It adds
significant new features and capabilities to the specifications.
It is generally compatible with ISO/IEC 9075:1989, in the sense
that, with very few exceptions, SQL language that conforms to
ISO/IEC 9075:1989 also conforms to this International Standard,
and will be treated in the same way by an implementation of this
International Standard as it would by an implementation of ISO/IEC
9075:1989. The known incompatibilities between ISO/IEC 9075:1989
and this International Standard are stated in informative Annex E,
"Incompatibilities with ISO/IEC 9075:1989".
Technical changes between ISO/IEC 9075:1989 and this International
Standard include both improvements or enhancements to existing fea-
tures and the definition of new features. Significant improvements
in existing features include:
- A better definition of direct invocation of SQL language;
- Improved diagnostic capabilities, especially a new status param-
eter (SQLSTATE), a diagnostics area, and supporting statements.
Significant new features are:
1) Support for additional data types (DATE, TIME, TIMESTAMP,
INTERVAL, BIT string, variable-length character and bit strings,
and NATIONAL CHARACTER strings),
2) Support for character sets beyond that required to express SQL
language itself and support for additional collations,
3) Support for additional scalar operations, such as string opera-
tions for concatenate and substring, date and time operations,
and a form for conditional expressions,
4) Increased generality and orthogonality in the use of scalar-
valued and table-valued query expressions,
5) Additional set operators (for example, union join, natural join,
set difference, and set intersection),
6) Capability for domain definitions in the schema,
7) Support for Schema Manipulation capabilities (especially DROP
and ALTER statements),
Introduction xiii
X3H2-92-154/DBL CBR-002
8) Support for bindings (modules and embedded syntax) in the Ada,
C, and MUMPS languages,
9) Additional privilege capabilities,
10)Additional referential integrity facilities, including ref-
erential actions, subqueries in CHECK constraints, separate
assertions, and user-controlled deferral of constraints,
11)Definition of an Information Schema,
12)Support for dynamic execution of SQL language,
13)Support for certain facilities required for Remote Database
Access (especially connection management statements and quali-
fied schema names),
14)Support for temporary tables,
15)Support for transaction consistency levels,
16)Support for data type conversions (CAST expressions among data
types),
17)Support for scrolled cursors, and
18)A requirement for a flagging capability to aid in portability of
application programs.
The organization of this International Standard is as follows:
1) Clause 1, "Scope", specifies the scope of this International
Standard.
2) Clause 2, "Normative references", identifies additional stan-
dards that, through reference in this International Standard,
constitute provisions of this International Standard.
3) Clause 3, "Definitions, notations, and conventions", defines the
notations and conventions used in this International Standard.
4) Clause 4, "Concepts", presents concepts used in the definition
of SQL.
5) Clause 5, "Lexical elements", defines the lexical elements of
the language.
6) Clause 6, "Scalar expressions", defines the elements of the
language that produce scalar values.
7) Clause 7, "Query expressions", defines the elements of the lan-
guage that produce rows and tables of data.
8) Clause 8, "Predicates", defines the predicates of the language.
xiv Database Language SQL
X3H2-92-154/DBL CBR-002
9) Clause 9, "Data assignment rules", specifies the rules for
assignments that retrieve data from or store data into the
database, and formation rules for set operations.
10)Clause 10, "Additional common elements", defines additional lan-
guage elements that are used in various parts of the language.
11)Clause 11, "Schema definition and manipulation", defines facili-
ties for creating and managing a schema.
12)Clause 12, "Module", defines modules and procedures.
13)Clause 13, "Data manipulation", defines the data manipulation
statements.
14)Clause 14, "Transaction management", defines the SQL-transaction
management statements.
15)Clause 15, "Connection management" defines the SQL-connection
management statements.
16)Clause 16, "Session management", defines the SQL-session manage-
ment statements.
17)Clause 17, "Dynamic SQL", defines the facilities for executing
SQL-statements dynamically.
18)Clause 18, "Diagnostics management", defines the diagnostics
management facilities.
19)Clause 19, "Embedded SQL", defines syntax for embedding SQL in
certain standard programming languages.
20)Clause 20, "Direct invocation of SQL", defines the direct invo-
cation of SQL language.
21)Clause 21, "Information Schema and Definition Schema", defines
viewed tables that contain schema information.
22)Clause 22, "Status codes", defines values that identify the
status of the execution of SQL-statements and the mechanisms by
which those values are returned.
23)Clause 23, "Conformance", defines the criteria for conformance
to this International standard.
24)Annex A, "Leveling the SQL Language", is an informative
Annex. It lists the leveling rules defining the Entry SQL and
Intermediate SQL subset levels of the SQL language.
25)Annex B, "Implementation-defined elements", is an informa-
tive Annex. It lists those features for which the body of the
International Standard states that the syntax or meaning or ef-
fect on the database is partly or wholly implementation-defined,
and describes the defining information that an implementor shall
provide in each case.
Introduction xv
X3H2-92-154/DBL CBR-002
26)Annex C, "Implementation-dependent elements", is an informa-
tive Annex. It lists those features for which the body of the
International Standard states explicitly that the meaning or
effect on the database is implementation-dependent.
27)Annex D, "Deprecated features", is an informative Annex. It
lists features that the responsible Technical Committee in-
tends will not appear in a future revised version of this
International Standard.
28)Annex E, "Incompatibilities with ISO/IEC 9075:1989", is an in-
formative Annex. It lists the incompatibilities between this
version of this International Standard and ISO/IEC 9075:1989.
29)Annex F, "Maintenance and interpretation of SQL", is an infor-
mative Annex. It identifies SQL interpretations and corrections
that have been processed by ISO/IEC JTC1/SC21 since adoption of
ISO/IEC 9075:1989.
In the text of this International Standard, Clauses begin a new
odd-numbered page, and in Clause 5, "Lexical elements", through
Clause 22, "Status codes", Subclauses begin a new page. Any result-
ing blank space is not significant.
xvi Database Language SQL
X3H2-92-154/DBL CBR-002
Information Technology - Database Language SQL
1 Scope
This International Standard defines the data structures and basic
operations on SQL-data. It provides functional capabilities for
creating, accessing, maintaining, controlling, and protecting SQL-
data.
Note: The framework for this International Standard is described by
the Reference Model of Data Management (ISO/IEC DIS 10032:1991).
This International Standard specifies the syntax and semantics of a
database language
- for specifying and modifying the structure and the integrity
constraints of SQL-data,
- for declaring and invoking operations on SQL-data and cursors,
and
- for declaring database language procedures and embedding them
into a standard programming language.
It also specifies an Information Schema that describes the struc-
ture and the integrity constraints of SQL-data.
This International Standard
- provides a vehicle for portability of data definitions and com-
pilation units between SQL-implementations,
- provides a vehicle for interconnection of SQL-implementations,
- specifies syntax for embedding SQL-statements in a compilation
unit that otherwise conforms to the standard for a particular
programming language. It defines how an equivalent compilation
unit may be derived that conforms to the particular programming
language standard. In that equivalent compilation unit, each
embedded SQL-statement has been replaced by statements that
invoke a database language procedure that contains the SQL-
statement, and
- specifies syntax for direct invocation of SQL-statements.
Scope 1
X3H2-92-154/DBL CBR-002
This International Standard does not define the method or time of
binding between any of:
- database management system components,
- SQL data definition declarations,
- SQL procedures, or
- compilation units, including those containing embedded SQL.
Implementations of this International Standard may exist in en-
vironments that also support application programming languages,
end-user query languages, report generator systems, data dictionary
systems, program library systems, and distributed communication
systems, as well as various tools for database design, data admin-
istration, and performance optimization.
2 Database Language SQL
X3H2-92-154/DBL CBR-002
2 Normative references
The following standards contain provisions that, through reference
in this text, constitute provisions of this International Standard.
At the time of publication, the editions indicated were valid.
All standards are subject to revision, and parties to agreements
based on this International Standard are encouraged to investigate
the possibility of applying the most recent editions of the stan-
dards listed below. Members of IEC and ISO maintain registers of
currently valid International Standards.
- ISO/IEC 646:1991, Information technology-ISO 7-bit coded charac-
ter set for information interchange.
- ISO/IEC 1539:1991, Information technology-Programming languages-
Fortran.
- ISO 1989:1985, Programming languages-COBOL.
(Endorsement of ANSI X3.23-1985).
- ISO 2022:1986, Information technology-ISO 7-bit and 8-bit coded
character sets-code extension techniques.
- ISO 6160:1979, Programming languages-PL/I
(Endorsement of ANSI X3.53-1976).
- ISO 7185:1990, Information technology-Programming languages-
Pascal.
- ISO 8601:1988, Data elements and interchange formats - Information
interchange-Representation of dates and times.
- ISO 8652:1987, Programming languages-Ada.
(Endorsement of ANSI/MIL-STD-1815A-1983).
- ISO/IEC 8824:1990, Information technology-Open Systems Interconnection-
Specification of Abstract Syntax Notation One (ASN.1).
- ISO/IEC 9579-2:[1], Information technology - Open Systems
Interconnection - Remote Database Access, Part 2: SQL special-
ization.
- ISO/IEC 9899:1990, Programming languages - C.
- ISO/IEC 10206:1991, Information technology-Programming languages-
Extended Pascal.
- ISO/IEC 10646:[1], Information technology-Multiple-octet coded
character set.
____________________
[1] To be published
Normative references 3
X3H2-92-154/DBL CBR-002
- ISO/IEC 11756:[1], Information technology-Programming languages-
MUMPS.
4 Database Language SQL
X3H2-92-154/DBL CBR-002
3 Definitions, notations, and conventions
3.1 Definitions
For the purposes of this International Standard, the following
definitions apply.
3.1.1 Definitions taken from ISO/IEC 10646
This International Standard makes use of the following terms de-
fined in ISO/IEC 10646:
a) character
b) octet
c) variable-length coding
d) fixed-length coding
3.1.2 Definitions taken from ISO 8601
This International Standard makes use of the following terms de-
fined in ISO 8601:
a) Coordinated Universal Time (UTC)
b) date ("date, calendar" in ISO 8601)
3.1.3 Definitions provided in this International Standard
This International Standard defines the following terms:
a) assignable: The characteristic of a value or of a data type
that permits that value or the values of that data type to be
assigned to data instances of a specified data type.
b) cardinality (of a collection): The number of objects in that
collection. Those objects need not necessarily have distinct
values.
c) character repertoire; repertoire: A set of characters used for a
specific purpose or application. Each character repertoire has
an implied default collating sequence.
d) coercibility: An attribute of character string data items that
governs how a collating sequence for the items is determined.
Definitions, notations, and conventions 5
X3H2-92-154/DBL CBR-002
3.1 Definitions
e) collation; collating sequence: A method of ordering two com-
parable character strings. Every character set has a default
collation.
f) comparable: The characteristic of two data objects that per-
mits the value of one object to be compared with the value of
the other object. Also said of data types: Two data types are
comparable if objects of those data types are comparable.
g) descriptor: A coded description of an SQL object. It includes
all of the information about the object that a conforming SQL-
implementation requires.
h) distinct: Two values are said to be not distinct if either:
both are the null value, or they compare equal according to
Subclause 8.2, "<comparison predicate>". Otherwise they are
distinct. Two rows (or partial rows) are distinct if at least
one of their pairs of respective values is distinct. Otherwise
they are not distinct. The result of evaluating whether or not
two values or two rows are distinct is never unknown.
i) duplicate: Two or more values or rows are said to be duplicates
(of each other) if and only if they are not distinct.
j) dyadic operator: An operator having two operands: a left operand
and a right operand. An example of a dyadic arithmetic operator
in this International Standard is "-", specifying the subtrac-
tion of the right operand from the left operand.
k) form-of-use: A convention (or encoding) for representing charac-
ters (in character strings). Some forms-of-use are fixed-length
codings and others are variable-length codings.
l) form-of-use conversion: A method of converting character strings
from one form-of-use to another form-of-use.
m) implementation-defined: Possibly differing between SQL-
implementations, but specified by the implementor for each
particular SQL-implementation.
n) implementation-dependent: Possibly differing between SQL-
implementations, but not specified by this International
Standard and not required to be specified by the implementor
for any particular SQL-implementations.
o) monadic operator: An operator having one operand. An example of
a monadic arithmetic operator in this International Standard is
"-", specifying the negation of the operand.
p) multiset: An unordered collection of objects that are not neces-
sarily distinct. The collection may be empty.
q) n-adic operator: An operator having a variable number of
operands (informally: n operands). An example of an n-adic
operator in this International Standard is COALESCE.
6 Database Language SQL
X3H2-92-154/DBL CBR-002
3.1 Definitions
r) null value (null): A special value, or mark, that is used to
indicate the absence of any data value.
s) persistent: Continuing to exist indefinitely, until destroyed
deliberately. Referential and cascaded actions are regarded
as deliberate. Actions incidental to the ending of an SQL-
transaction (see Subclause 4.28, "SQL-transactions") or an SQL-
session (see Subclause 4.30, "SQL-sessions") are not regarded as
deliberate.
t) redundant duplicates: All except one of any multiset of dupli-
cate values or rows.
u) repertoire: See character repertoire.
v) sequence: An ordered collection of objects that are not neces-
sarily distinct.
w) set: An unordered collection of distinct objects. The collection
may be empty.
x) SQL-implementation: A database management system that conforms
to this International Standard.
y) translation: A method of translating characters in one character
repertoire into characters of the same or a different character
repertoire.
3.2 Notation
The syntactic notation used in this International Standard is
an extended version of BNF ("Backus Naur Form" or "Backus Normal
Form").
In BNF, each syntactic element of the language is defined by means
of a production rule. This defines the element in terms of a for-
mula consisting of the characters, character strings, and syntactic
elements that can be used to form an instance of it.
The version of BNF used in this International Standard makes use of
the following symbols:
SymbolMeaning
< > Angle brackets delimit character strings that are the names
of syntactic elements, the non-terminal symbols of the SQL
language.
::= The definition operator. This is used in a production rule to
separate the element defined by the rule from its definition.
The element being defined appears to the left of the opera-
tor and the formula that defines the element appears to the
right.
Definitions, notations, and conventions 7
X3H2-92-154/DBL CBR-002
3.2 Notation
[ ] Square brackets indicate optional elements in a formula. The
portion of the formula within the brackets may be explicitly
specified or may be omitted.
{ } Braces group elements in a formula. The portion of the for-
mula within the braces shall be explicitly specified.
| The alternative operator. The vertical bar indicates that
the portion of the formula following the bar is an alterna-
tive to the portion preceding the bar. If the vertical bar
appears at a position where it is not enclosed in braces
or square brackets, it specifies a complete alternative for
the element defined by the production rule. If the vertical
bar appears in a portion of a formula enclosed in braces or
square brackets, it specifies alternatives for the contents
of the innermost pair of such braces or brackets.
. . . The ellipsis indicates that the element to which it applies
in a formula may be repeated any number of times. If the el-
lipsis appears immediately after a closing brace "}", then it
applies to the portion of the formula enclosed between that
closing brace and the corresponding opening brace "{". If
an ellipsis appears after any other element, then it applies
only to that element.
!! Introduces ordinary English text. This is used when the defi-
nition of a syntactic element is not expressed in BNF.
Spaces are used to separate syntactic elements. Multiple spaces and
line breaks are treated as a single space. Apart from those symbols
to which special functions were given above, other characters and
character strings in a formula stand for themselves. In addition,
if the symbols to the right of the definition operator in a produc-
tion consist entirely of BNF symbols, then those symbols stand for
themselves and do not take on their special meaning.
Pairs of braces and square brackets may be nested to any depth,
and the alternative operator may appear at any depth within such a
nest.
A character string that forms an instance of any syntactic element
may be generated from the BNF definition of that syntactic element
by application of the following steps:
1) Select any one option from those defined in the right hand side
of a production rule for the element, and replace the element
with this option.
2) Replace each ellipsis and the object to which it applies with
one or more instances of that object.
3) For every portion of the string enclosed in square brackets,
either delete the brackets and their contents or change the
brackets to braces.
8 Database Language SQL
X3H2-92-154/DBL CBR-002
3.2 Notation
4) For every portion of the string enclosed in braces, apply steps
1 through 5 to the substring between the braces, then remove the
braces.
5) Apply steps 1 through 5 to any non-terminal syntactic element
(i.e., name enclosed in angle brackets) that remains in the
string.
The expansion or production is complete when no further non-
terminal symbols remain in the character string.
3.3 Conventions
3.3.1 Informative elements
In several places in the body of this International Standard, in-
formative notes appear. For example:
Note: This is an example of a note.
Those notes do not belong to the normative part of this International
Standard and conformance to material specified in those notes shall
not be claimed.
3.3.2 Specification of syntactic elements
Syntactic elements are specified in terms of:
- Function: A short statement of the purpose of the element.
- Format: A BNF definition of the syntax of the element.
- Syntax Rules: A specification of the syntactic properties of the
element, or of additional syntactic constraints, not expressed
in BNF, that the element shall satisfy, or both.
- Access Rules: A specification of rules governing the accessibil-
ity of schema objects that shall hold before the General Rules
may be successfully applied.
- General Rules: A specification of the run-time effect of the
element. Where more than one General Rule is used to specify the
effect of an element, the required effect is that which would be
obtained by beginning with the first General Rule and applying
the Rules in numerical sequence unless a Rule is applied that
specifies or implies a change in sequence or termination of the
application of the Rules. Unless otherwise specified or implied
by a specific Rule that is applied, application of General Rules
terminates when the last in the sequence has been applied.
- Leveling Rules: A specification of how the element shall be
supported for each of the levels of SQL.
Definitions, notations, and conventions 9
X3H2-92-154/DBL CBR-002
3.3 Conventions
The scope of notational symbols is the Subclause in which those
symbols are defined. Within a Subclause, the symbols defined in
Syntax Rules, Access Rules, or General Rules can be referenced in
other rules provided that they are defined before being referenced.
3.3.3 Specification of the Information Schema
The objects of the Information Schema in this International
Standard are specified in terms of:
- Function: A short statement of the purpose of the definition.
- Definition: A definition, in SQL, of the object being defined.
- Description: A specification of the run-time value of the ob-
ject, to the extent that this is not clear from the definition.
The definitions used to define the views in the Information Schema
are used only to specify clearly the contents of those viewed
tables. The actual objects on which these views are based are
implementation-dependent.
3.3.4 Use of terms
3.3.4.1 Exceptions
The phrase "an exception condition is raised:", followed by the
name of a condition, is used in General Rules and elsewhere to
indicate that the execution of a statement is unsuccessful, ap-
plication of General Rules, other than those of Subclause 12.3,
"<procedure>", and Subclause 20.1, "<direct SQL statement>", may
be terminated, diagnostic information is to be made available,
and execution of the statement is to have no effect on SQL-data or
schemas. The effect on <target specification>s and SQL descriptor
areas of an SQL-statement that terminates with an exception condi-
tion, unless explicitly defined by this International Standard, is
implementation-dependent.
The phrase "a completion condition is raised:", followed by the
name of a condition, is used in General Rules and elsewhere to
indicate that application of General Rules is not terminated and
diagnostic information is to be made available; unless an excep-
tion condition is also raised, the execution of the statement is
successful.
If more than one condition could have occurred as a result of a
statement, it is implementation-dependent whether diagnostic infor-
mation pertaining to more than one condition is made available.
10 Database Language SQL
X3H2-92-154/DBL CBR-002
3.3 Conventions
3.3.4.2 Syntactic containment
In a Format, a syntactic element <A> is said to immediately contain
a syntactic element <B> if <B> appears on the right-hand side of
the BNF production rule for <A>. A syntactic element <A> is said
to contain or specify a syntactic element <C> if <A> immediately
contains <C> or if <A> immediately contains a syntactic element <B>
that contains <C>.
In SQL language, an instance A1 of <A> is said to immediately con-
tain an instance B1 of <B> if <A> immediately contains <B> and the
text of B1 is part of the text of A1. An instance A1 of <A> is said
to contain or specify an instance C1 of <C> if A1 immediately con-
tains C1 or if A1 immediately contains an instance B1 of <B> that
contains C1.
An instance A1 of <A> is said to contain an instance B1 of <B> with
an intervening <C> if A1 contains B1 and A1 contains an instance C1
of <C> that contains B1. An instance A1 of <A> is said to contain
an instance B1 of <B> without an intervening <C> if A1 contains B1
and A1 does not contain an instance C1 of <C> that contains B1.
An instance A1 of <A> simply contains an instance B1 of <B> if
A1 contains B1 without an intervening instance A2 of <A> or an
intervening instance B2 of <B>.
If <A> contains <B>, then <B> is said to be contained in <A> and
<A> is said to be a containing production symbol for <B>. If <A>
simply contains <B>, then <B> is said to be simply contained in
<A> and <A> is said to be a simply containing production symbol for
<B>.
Let A1 be an instance of <A> and let B1 be an instance of <B>. If
<A> contains <B>, then A1 is said to contain B1 and B1 is said to
be contained in A1. If <A> simply contains <B>, then A1 is said to
simply contain B1 and B1 is said to be simply contained in A1.
An instance A1 of <A> is the innermost <A> satisfying a condition
C if A1 satisfies C and A1 does not contain an instance A2 of <A>
that satisfies C. An instance A1 of <A> is the outermost <A> satis-
fying a condition C if A1 satisfies C and A1 is not contained in an
instance A2 of <A> that satisfies C.
If <A> contains a <table name> that identifies a view that is
defined by a <view definition> V, then <A> is said to generally
contain the <query expression> contained in V. If <A> contains <B>,
then <A> generally contains <B>. If <A> generally contains <B> and
<B> generally contains <C>, then <A> generally contains <C>.
An instance A1 of <A> directly contains an instance B1 of <B> if A1
contains B1 without an intervening <set function specification> or
<subquery>.
Definitions, notations, and conventions 11
X3H2-92-154/DBL CBR-002
3.3 Conventions
3.3.4.3 Terms denoting rule requirements
In the Syntax Rules, the term shall defines conditions that are
required to be true of syntactically conforming SQL language. When
such conditions depend on the contents of the schema, then they
are required to be true just before the actions specified by the
General Rules are performed. The treatment of language that does
not conform to the SQL Formats and Syntax Rules is implementation-
dependent. If any condition required by Syntax Rules is not sat-
isfied when the evaluation of Access or General Rules is attempted
and the implementation is neither processing non-conforming SQL
language nor processing conforming SQL language in a non-conforming
manner, then an exception condition is raised: syntax error or
access rule violation (if this situation occurs during dynamic ex-
ecution of an SQL-statement, then the exception that is raised is
syntax error or access rule violation in dynamic SQL statement; if
the situation occurs during direct invocation of an SQL-statement,
then the exception that is raised is syntax error or access rule
violation in direct SQL statement).
In the Access Rules, the term shall defines conditions that are
required to be satisfied for the successful application of the
General Rules. If any such condition is not satisfied when the
General Rules are applied, then an exception condition is raised:
syntax error or access rule violation (if this situation occurs
during dynamic execution of an SQL-statement, then the exception
that is raised is syntax error or access rule violation in dynamic
SQL statement; if the situation occurs during direct invocation of
an SQL-statement, then the exception that is raised is syntax error
or access rule violation in direct SQL statement).
In the Leveling Rules, the term shall defines conditions that are
required to be true of SQL language for it to syntactically conform
to the specified level of conformance.
3.3.4.4 Rule evaluation order
A conforming implementation is not required to perform the exact
sequence of actions defined in the General Rules, but shall achieve
the same effect on SQL-data and schemas as that sequence. The term
effectively is used to emphasize actions whose effect might be
achieved in other ways by an implementation.
The Syntax Rules and Access Rules for contained syntactic elements
are effectively applied at the same time as the Syntax Rules and
Access Rules for the containing syntactic elements. The General
Rules for contained syntactic elements are effectively applied be-
fore the General Rules for the containing syntactic elements. Where
the precedence of operators is determined by the Formats of this
International Standard or by parentheses, those operators are ef-
fectively applied in the order specified by that precedence. Where
the precedence is not determined by the Formats or by parentheses,
effective evaluation of expressions is generally performed from
12 Database Language SQL
X3H2-92-154/DBL CBR-002
3.3 Conventions
left to right. However, it is implementation-dependent whether ex-
pressions are actually evaluated left to right, particularly when
operands or operators might cause conditions to be raised or if
the results of the expressions can be determined without completely
evaluating all parts of the expression. In general, if some syn-
tactic element contains more than one other syntactic element, then
the General Rules for contained elements that appear earlier in the
production for the containing syntactic element are applied before
the General Rules for contained elements that appear later.
For example, in the production:
<A> ::= <B> <C>
the Syntax Rules and Access Rules for <A>, <B>, and <C> are ef-
fectively applied simultaneously. The General Rules for <B> are
applied before the General Rules for <C>, and the General Rules for
<A> are applied after the General Rules for both <B> and <C>.
If the result of an expression or search condition can be deter-
mined without completely evaluating all parts of the expression or
search condition, then the parts of the expression or search condi-
tion whose evaluation is not necessary are called the inessential
parts. If the Access Rules pertaining to inessential parts are not
satisfied, then the syntax error or access rule violation exception
condition is raised regardless of whether or not the inessential
parts are actually evaluated. If evaluation of the inessential
parts would cause an exception condition to be raised, then it is
implementation-dependent whether or not that exception condition is
raised.
3.3.4.5 Conditional rules
Conditional rules are specified with "If" or "Case" conventions.
Rules specified with "Case" conventions include a list of con-
ditional sub-rules using "If" conventions. The first such "If"
sub-rule whose condition is true is the effective sub-rule of
the "Case" rule. The last sub-rule of a "Case" rule may specify
"Otherwise". Such a sub-rule is the effective sub-rule of the
"Case" rule if no preceding "If" sub-rule in the "Case" rule has
a true condition.
3.3.4.6 Syntactic substitution
In the Syntax and General Rules, the phrase "X is implicit" indi-
cates that the Syntax and General Rules are to be interpreted as if
the element X had actually been specified.
In the Syntax and General Rules, the phrase "the following <X> is
implicit: Y" indicates that the Syntax and General Rules are to be
interpreted as if a syntactic element <X> containing Y had actually
been specified.
Definitions, notations, and conventions 13
X3H2-92-154/DBL CBR-002
3.3 Conventions
In the Syntax Rules and General Rules, the phrase "former is equiv-
alent to latter" indicates that the Syntax Rules and General Rules
are to be interpreted as if all instances of former in the element
had been instances of latter.
If a BNF nonterminal is referenced in a Subclause without speci-
fying how it is contained in a BNF production that the Subclause
defines, then
Case:
- If the BNF nonterminal is itself defined in the Subclause, then
the reference shall be assumed to be the occurrence of that BNF
nonterminal on the left side of the defining production.
- Otherwise, the reference shall be assumed to be to a BNF pro-
duction in which the particular BNF nonterminal is immediately
contained.
3.3.4.7 Other terms
Some Syntax Rules define terms, such as T1, to denote named or
unnamed tables. Such terms are used as table names or correlation
names. Where such a term is used as a correlation name, it does
not imply that any new correlation name is actually defined for
the denoted table, nor does it affect the scopes of any actual
correlation names.
An SQL-statement S1 is said to be executed as a direct result of
executing an SQL-statement if S1 is the SQL-statement contained
in a <procedure> that has been executed, or if S1 is the value of
an <SQL statement variable> referenced by an <execute immediate
statement> contained in a <procedure> that has been executed, or if
S1 was the value of the <SQL statement variable> that was associ-
ated with an <SQL statement name> by a <prepare statement> and that
same <SQL statement name> is referenced by an <execute statement>
contained in a <procedure> that has been executed.
3.3.5 Descriptors
A descriptor is a conceptual structured collection of data that
defines the attributes of an instance of an object of a specified
type. The concept of descriptor is used in specifying the seman-
tics of SQL. It is not necessary that any descriptor exist in any
particular form in any database or environment.
Some SQL objects cannot exist except in the context of other SQL
objects. For example, columns cannot exist except in tables. Those
objects are independently described by descriptors, and the de-
scriptors of enabling objects (e.g., tables) are said to include
the descriptors of enabled objects (e.g., columns or table con-
straints). Conversely, the descriptor of an enabled object is said
to be included in the descriptor of an enabling object.
14 Database Language SQL
X3H2-92-154/DBL CBR-002
3.3 Conventions
In other cases, certain SQL objects cannot exist unless some other
SQL object exists, even though there is not an inclusion relation-
ship. For example, SQL does not permit an assertion to exist if the
tables referenced by the assertion do not exist. Therefore, an as-
sertion descriptor is dependent on or depends on zero or more table
descriptors (equivalently, an assertion is dependent on or depends
on zero or more tables). In general, a descriptor D1 can be said to
depend on, or be dependent on, some descriptor D2.
There are two ways of indicating dependency of one construct on
another. In many cases, the descriptor of the dependent construct
is said to "include the name of" the construct on which it is de-
pendent. In this case "the name of" is to be understood as meaning
"sufficient information to identify the descriptor of"; thus an
implementor might choose to use a pointer or a concatenation of
<catalog name>, <schema name>, etc. Alternatively, the descrip-
tor may be said to include text (e.g., <query expression>, <search
condition>). In such cases, whether the implementation includes ac-
tual text (with defaults and implications made explicit) or its own
style of parse tree is irrelevant; the validity of the descriptor
is clearly "dependent on" the existence of descriptors for objects
that are referred to in it.
The statement that a column "is based on" a domain, is equivalent
to a statement that a column "is dependent on" that domain.
An attempt to destroy a descriptor may fail if other descriptors
are dependent on it, depending on how the destruction is specified.
Such an attempt may also fail if the descriptor to be destroyed
is included in some other descriptor. Destruction of a descriptor
results in the destruction of all descriptors included in it, but
has no effect on descriptors on which it is dependent.
3.3.6 Index typography
In the Index to this International Standard, the following conven-
tions are used:
- Index entries appearing in boldface indicate the page where the
word, phrase, or BNF nonterminal was defined;
- Index entries appearing in italics indicate a page where the BNF
nonterminal was used in a Format; and
- Index entries appearing in roman type indicate a page where
the word, phrase, or BNF nonterminal was used in a heading,
Function, Syntax Rule, Access Rule, General Rule, Leveling Rule,
Table, or other descriptive text.
Definitions, notations, and conventions 15
X3H2-92-154/DBL CBR-002
3.4 Object identifier for Database Language SQL
3.4 Object identifier for Database Language SQL
Function
The object identifier for Database Language SQL identifies the
characteristics of an SQL-implementation to other entities in an
open systems environment.
Format
<SQL object identifier> ::=
<SQL provenance> <SQL variant>
<SQL provenance> ::= <arc1> <arc2> <arc3>
<arc1> ::= iso | 1 | iso <left paren> 1 <right paren>
<arc2> ::= standard | 0 | standard <left paren> 0 <right paren>
<arc3> ::= 9075
<SQL variant> ::= <SQL edition> <SQL conformance>
<SQL edition> ::= <1987> | <1989> | <1992>
<1987> ::= 0 | edition1987 <left paren> 0 <right paren>
<1989> ::= <1989 base> <1989 package>
<1989 base> ::= 1 | edition1989 <left paren> 1 <right paren>
<1989 package> ::= <integrity no> | <integrity yes>
<integrity no> ::= 0 | IntegrityNo <left paren> 0 <right paren>
<integrity yes> ::= 1 | IntegrityYes <left paren> 1 <right paren>
<1992> ::= 2 | edition1992 <left paren> 2 <right paren>
<SQL conformance> ::= <low> | <intermediate> | <high>
<low> ::= 0 | Low <left paren> 0 <right paren>
<intermediate> ::= 1 | Intermediate <left paren> 1 <right paren>
<high> ::= 2 | High <left paren> 2 <right paren>
16 Database Language SQL
X3H2-92-154/DBL CBR-002
3.4 Object identifier for Database Language SQL
Syntax Rules
1) An <SQL conformance> of <high> shall not be specified unless the
<SQL edition> is specified as <1992>.
2) The value of <SQL conformance> identifies the level at which
conformance is claimed as follows:
a) If <SQL edition> specifies <1992>, then
Case:
i) <low>, then Entry SQL level.
ii) <intermediate>, then Intermediate SQL level.
iii) <high>, then Full SQL level.
b) Otherwise:
i) <low>, then level 1.
ii) <intermediate>, then level 2.
3) A specification of <1989 package> as <integrity no> implies
that the integrity enhancement feature is not implemented. A
specification of <1989 package> as <integrity yes> implies that
the integrity enhancement feature is implemented.
Definitions, notations, and conventions 17
X3H2-92-154/DBL CBR-002
18 Database Language SQL
X3H2-92-154/DBL CBR-002
4 Concepts
4.1 Data types
A data type is a set of representable values. The logical represen-
tation of a value is a <literal>. The physical representation of a
value is implementation-dependent.
A value is primitive in that it has no logical subdivision within
this International Standard. A value is a null value or a non-null
value.
A null value is an implementation-dependent special value that
is distinct from all non-null values of the associated data type.
There is effectively only one null value and that value is a member
of every SQL data type. There is no <literal> for a null value,
although the keyword NULL is used in some places to indicate that a
null value is desired.
SQL defines distinct data types named by the following <key word>s:
CHARACTER, CHARACTER VARYING, BIT, BIT VARYING, NUMERIC, DECIMAL,
INTEGER, SMALLINT, FLOAT, REAL, DOUBLE PRECISION, DATE, TIME,
TIMESTAMP, and INTERVAL.
Subclause 6.1, "<data type>", describes the semantic properties of
each data type.
For reference purposes, the data types CHARACTER and CHARACTER
VARYING are collectively referred to as character string types.
The data types BIT and BIT VARYING are collectively referred to
as bit string types. Character string types and bit string types
are collectively referred to as string types and values of string
types are referred to as strings. The data types NUMERIC, DECIMAL,
INTEGER, and SMALLINT are collectively referred to as exact numeric
types. The data types FLOAT, REAL, and DOUBLE PRECISION are col-
lectively referred to as approximate numeric types. Exact numeric
types and approximate numeric types are collectively referred to as
numeric types. Values of numeric type are referred to as numbers.
The data types DATE, TIME, and TIMESTAMP are collectively referred
to as datetime types. Values of datetime types are referred to as
datetimes. The data type INTERVAL is referred to as an interval
type. Values of interval types are called intervals.
Each data type has an associated data type descriptor. The contents
of a data type descriptor are determined by the specific data type
that it describes. A data type descriptor includes an identifica-
tion of the data type and all information needed to characterize an
instance of that data type.
Concepts 19
X3H2-92-154/DBL CBR-002
4.1 Data types
Each host language has its own data types, which are separate and
distinct from SQL data types, even though similar names may be
used to describe the data types. Mappings of SQL data types to data
types in host languages are described in Subclause 12.3, "<pro-
cedure>", and Subclause 19.1, "<embedded SQL host program>". Not
every SQL data type has a corresponding data type in every host
language.
4.2 Character strings
A character string data type is described by a character string
data type descriptor. A character string data type descriptor con-
tains:
- the name of the specific character string data type (CHARACTER
or CHARACTER VARYING; NATIONAL CHARACTER and NATIONAL CHARACTER
VARYING are represented as CHARACTER and CHARACTER VARYING,
respectively);
- the length or maximum length in characters of the character
string data type;
- the catalog name, schema name, and character set name of the
character set of the character string data type; and
- the catalog name, schema name, and collation name of the colla-
tion of the character string data type.
Character sets fall into three categories: those defined by na-
tional or international standards, those provided by implemen-
tations, and those defined by applications. All character sets,
however defined, always contain the <space> character. Character
sets defined by applications can be defined to "reside" in any
schema chosen by the application. Character sets defined by stan-
dards or by implementations reside in the Information Schema (named
INFORMATION_SCHEMA) in each catalog, as do collations defined by
standards and collations and form-of-use conversions defined by
implementations.
The <implementation-defined character repertoire name> SQL_TEXT
specifies the name of a character repertoire and implied form-of-
use that can represent every character that is in <SQL language
character> and all other characters that are in character sets
supported by the implementation.
4.2.1 Character strings and collating sequences
A character string is a sequence of characters chosen from the
same character repertoire. The character repertoire from which
the characters of a particular string are chosen may be specified
explicitly or implicitly. A character string has a length, which
is the number of characters in the sequence. The length is 0 or a
positive integer.
20 Database Language SQL
X3H2-92-154/DBL CBR-002
4.2 Character strings
All character strings of a given character repertoire are mutu-
ally comparable, subject to the restrictions specified in Table 3,
"Collating sequence usage for comparisons".
A collating sequence, also known as a collation, is a set of rules
determining comparison of character strings in a particular char-
acter repertoire. There is a default collating sequence for each
character repertoire, but additional collating sequences can be
defined for any character repertoire.
Note: A column may be defined as having a default collating se-
quence. This default collating sequence for the column may be
different from the default collating sequence for its character
repertoire, e.g., if the <collate clause> is specified in the
<column reference>. It will be clear from context when the term
"default collating sequence" is used whether it is meant for a
column or for a character repertoire.
Given a collating sequence, two character strings are identical if
and only if they are equal in accordance with the comparison rules
specified in Subclause 8.2, "<comparison predicate>". The collat-
ing sequence used for a particular comparison is determined as in
Subclause 4.2.3, "Rules determining collating sequence usage".
The <key word>s NATIONAL CHARACTER are used to specify a character
string data type with a particular implementation-defined character
repertoire. Special syntax (N'string') is provided for representing
literals in that character repertoire.
A character set is described by a character set descriptor. A char-
acter set descriptor includes:
- the name of the character set or character repertoire,
- if the character set is a character repertoire, then the name of
the form-of-use,
- an indication of what characters are in the character set, and
- the name of the default collation of the character set.
For every character set, there is at least one collation. A colla-
tion is described by a collation descriptor. A collation descriptor
includes:
- the name of the collation,
- the name of the character set on which the collation operates,
- whether the collation has the NO PAD or the PAD SPACE attribute,
and
- an indication of how the collation is performed.
Concepts 21
X3H2-92-154/DBL CBR-002
4.2 Character strings
4.2.2 Operations involving character strings
4.2.2.1 Operators that operate on character strings and return
character strings
<concatenation operator> is an operator, |, that returns the char-
acter string made by joining its character string operands in the
order given.
<character substring function> is a triadic function, SUBSTRING,
that returns a string extracted from a given string according
to a given numeric starting position and a given numeric length.
Truncation occurs when the implied starting and ending positions
are not both within the given string.
<fold> is a pair of functions for converting all the lower case
characters in a given string to upper case (UPPER) or all the upper
case ones to lower case (LOWER), useful only in connection with
strings that may contain <simple Latin letter>s.
<form-of-use conversion> is a function that invokes an installation-
supplied form-of-use conversion to return a character string S2
derived from a given character string S1. It is intended, though
not enforced by this International Standard, that S2 be exactly the
same sequence of characters as S1, but encoded according some dif-
ferent form-of-use. A typical use might be to convert a character
string from two-octet UCS to one-octet Latin1 or vice versa.
<trim function> is a function that returns its first string ar-
gument with leading and/or trailing pad characters removed. The
second argument indicates whether leading, or trailing, or both
leading and trailing pad characters should be removed. The third
argument specifies the pad character that is to be removed.
<character translation> is a function for changing each charac-
ter of a given string according to some many-to-one or one-to-one
mapping between two not necessarily distinct character sets. The
mapping, rather than being specified as part of the function, is
some external function identified by a <translation name>.
For any pair of character sets, there are zero or more translations
that may be invoked by a <character translation>. A translation
is described by a translation descriptor. A translation descriptor
includes:
- the name of the translation,
- the name of the character set from which it translates,
- the name of the character set to which it translates, and
- an indication of how the translation is performed.
22 Database Language SQL
X3H2-92-154/DBL CBR-002
4.2 Character strings
4.2.2.2 Other operators involving character strings
<length expression> returns the length of a given character string,
as an integer, in characters, octets, or bits according to the
choice of function.
<position expression> determines the first position, if any, at
which one string, S1, occurs within another, S2. If S1 is of length
zero, then it occurs at position 1 for any value of S2. If S1 does
not occur in S2, then zero is returned.
<like predicate> uses the triadic operator LIKE (or the inverse,
NOT LIKE), operating on three character strings and returning
a Boolean. LIKE determines whether or not a character string
"matches" a given "pattern" (also a character string). The char-
acters '%' (percent) and '_' (underscore) have special meaning when
they occur in the pattern. The optional third argument is a charac-
ter string containing exactly one character, known as the "escape
character", for use when a percent or underscore is required in the
pattern without its special meaning.
4.2.3 Rules determining collating sequence usage
The rules determining collating sequence usage for character
strings are based on the following:
- Expressions where no columns are involved (e.g., literals, host
variables) are by default compared using the default collating
sequence for their character repertoire.
Note: The default collating sequence for a character repertoire
is defined in Subclause 10.4, "<character set specification>",
and Subclause 11.28, "<character set definition>".
- When columns are involved (e.g., comparing two columns, or com-
paring a column to a literal), by default the default collating
sequence of the columns involved is used so long as the columns
have the same default collating sequence.
- When columns are involved having different default collating
sequences, explicit specification of the collating sequence in
the expression is required via the <collate clause> when the
expression participates in a comparison.
- Any explicit specification of collating sequence in an expres-
sion overrides any default collating sequence.
To formalize this, <character value expression>s effectively have
a coercibility attribute. This attribute has the values Coercible,
Implicit, No collating sequence, and Explicit. <character value
expression>s with the Coercible, Implicit, or Explicit attributes
have a collating sequence.
Concepts 23
X3H2-92-154/DBL CBR-002
4.2 Character strings
A <character value expression> consisting of a column reference has
the Implicit attribute, with collating sequence as defined when the
column was created. A <character value expression> consisting of a
value other than a column (e.g., a host variable or a literal) has
the Coercible attribute, with the default collation for its char-
acter repertoire. A <character value expression> simply containing
a <collate clause> has the Explicit attribute, with the collating
sequence specified in the <collate clause>.
Note: When the coercibility attribute is Coercible, the collating
sequence is uniquely determined as specified in Subclause 8.2,
"<comparison predicate>".
The tables below define how the collating sequence and the co-
ercibility attribute is determined for the result of any monadic
or dyadic operation. Table 1, "Collating coercibility rules for
monadic operators", shows the collating sequence and coercibility
rules for monadic operators, and Table 2, "Collating coercibil-
ity rules for dyadic operators", shows the collating sequence and
coercibility rules for dyadic operators. Table 3, "Collating se-
quence usage for comparisons", shows how the collating sequence is
determined for a particular comparison.
_____Table_1-Collating_coercibility_rules_for_monadic_operators____
Operand Coercibility Result Coercibility
_____and_Collating_Sequence_____ _____and_Collating_Sequence___
| Collating | Collating |
|_Coercibility______Sequence______|_Coercibility______Sequence_____|
| | |
| Coercible | default | Coercible | default |
| | | | |
| Implicit | X | Implicit | X |
| | | | |
| Explicit | X | Explicit | X |
| | | | |
|_______No_collati|g_sequence_____|______No_collatin|_sequence_____|
| | | | |
_____Table_2-Collating_coercibility_rules_for_dyadic_operators_____
Result
Coercibility
Operand 1 Coercibility Operand 2 Coercibility and Collating
_and_Collating_Sequence _and_Collating_Sequence ___Sequence___
| Collating | Collating | Col|ating
|_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence
| | | |
| Coercible | default | Coercible | default | Coercible| def|ult
| | | | | | |
| Coercible | default | Implicit | Y | Implicit | Y |
| | | | | | |
| Coercible | default | No collati|g sequence | No colla|ing |
sequence
24 Database Language SQL
X3H2-92-154/DBL CBR-002
4.2 Character strings
_Table_2-Collating_coercibility_rules_for_dyadic_operators_(Cont.)_
Result
Coercibility
Operand 1 Coercibility Operand 2 Coercibility and Collating
_and_Collating_Sequence _and_Collating_Sequence ___Sequence___
| Collating | Collating | Col|ating
|_Coercibility_Sequence__|_Coercibility_Sequence__|__CoercibilitySe|uence
| | | |
| Coercible | default | Explicit | Y | Explicit | Y |
| | | | | | |
| Implicit | X | Coercible | default | Implicit | X |
| | | | | | |
| Implicit | X | Implicit | X | Implicit | X |
| | | | | | |
| Implicit | X | Implicit | Y /= X | No colla|ing |
sequence
| Implicit | X | No collati|g sequence | No collating |
| | | | | sequence |
| | | | | |
| Implicit | X | Explicit Y | Explicit Y |
| | | | |
| No collati|g sequence | Any, | Any | No colla|ing |
except sequence
Explicit
| No collating sequence | Explicit | X | Explicit X |
| | | | |
| Explicit X | Coercible | default | Explicit | X |
| | | | | |
| Explicit | X | Implicit | Y | Explicit | X |
| | | | | | |
| Explicit | X | No collati|g sequence | Explicit | X |
| | | | | | |
| Explicit | X | Explicit X | Explicit | X |
| | | | | |
| Explicit | X | Explicit | Y /= X | Not permi|ted:|
____________________________________________________invalid_syntax_
|__________Ta|le_3-Collat|ng_sequence_|sage_for_co|parisons________|
Comparand 1 Comparand 2
Coercibility and Coercibility and
_Collating_Sequence _Collating_Sequence
| | | Collating Sequence |
| Collatin| Collatin| Used For The |
|_CoercibilitSequence|_CoercibilitSequence|__Comparison____________|
| | | |
| Coercible| default | Coercible| default | default |
| | | | | |
| Coercible| default | Implicit | Y | Y |
| | | | | |
| Coercible| default | No co|lating | Not permitted: invalid|
sequence syntax
| Coercible| default | Explicit Y | Y |
| | | | |
Concepts 25
X3H2-92-154/DBL CBR-002
4.2 Character strings
______Table_3-Collating_sequence_usage_for_comparisons_(Cont.)_____
Comparand 1 Comparand 2
Coercibility and Coercibility and
_Collating_Sequence _Collating_Sequence
| | | Collating Sequence |
| Collatin| Collatin| Used For The |
|_CoercibilitSequence|_CoercibilitSequence|__Comparison____________|
| | | |
| Implicit | X | Coercible| default | X |
| | | | | |
| Implicit | X | Implicit | X | X |
| | | | | |
| Implicit | X | Implicit | Y /= X | Not permitted: invalid|
syntax
| Implicit | X | No co|lating | Not permitted: invalid|
| | | seq|ence | syntax |
| | | | | |
| Implicit | X | Explicit Y | Y |
| | | | |
| No co|lating | Any | Any | Not permitted: invalid|
sequence except syntax
Explicit
| No collating | Explicit | X | X |
| sequence | | | |
| | | | |
| Explicit X | Coercible| default | X |
| | | | |
| Explicit | X | Implicit | Y | X |
| | | | | |
| Explicit | X | No co|lating | X |
sequence
| Explicit | X | Explicit X | X |
| | | | |
| Explicit | X | Explicit | Y /= X | Not permitted: invalid|
____________________________________________syntax_________________
|For n-adic|operation| (e.g., <c|se expres|ion>) with operands X1, |
X2, . . . , n , the collating sequence is effectively determined by
considering X1 and X2, then combining this result with X3, and so
on.
4.3 Bit strings
A bit string is a sequence of bits, each having the value of 0 or
1. A bit string has a length, which is the number of bits in the
string. The length is 0 or a positive integer.
A bit string data type is described by a bit string data type de-
scriptor. A bit string data type descriptor contains:
- the name of the specific bit string data type (BIT or BIT
VARYING); and
26 Database Language SQL
X3H2-92-154/DBL CBR-002
4.3 Bit strings
- the length of the bit string data type (in bits).
4.3.1 Bit string comparison and assignment
All bit strings are mutually comparable. A bit string is identical
to another bit string if and only if it is equal to that bit string
in accordance with the comparison rules specified in Subclause 8.2,
"<comparison predicate>".
Assignment of a bit string to a bit string variable is performed
from the most significant bit to the least significant bit in the
source string to the most significant bit in the target string, one
bit at a time.
4.3.2 Operations involving bit strings
4.3.2.1 Operators that operate on bit strings and return bit
strings
<bit concatenation> is an operator, |, that returns the bit string
made by concatenating the two bit string operands in the order
given.
<bit substring function> is a triadic function identical in syntax
and semantics to <character substring function> except that the
first argument and the returned value are both bit strings.
4.3.2.2 Other operators involving bit strings
<length expression> returns the length (as an integer number of
octets or bits according to the choice of function) of a given bit
string.
<position expression> determines the first position, if any, at
which one string, S1, occurs within another, S2. If S1 is of length
zero, then it occurs at position 1 for any value of S2. If S1 does
not occur in S2, then zero is returned.
4.4 Numbers
A number is either an exact numeric value or an approximate numeric
value. Any two numbers are mutually comparable to each other.
A numeric data type is described by a numeric data type descriptor.
A numeric data type descriptor contains:
- the name of the specific numeric data type (NUMERIC, DECIMAL,
INTEGER, SMALLINT, FLOAT, REAL, or DOUBLE PRECISION);
- the precision of the numeric data type;
Concepts 27
X3H2-92-154/DBL CBR-002
4.4 Numbers
- the scale of the numeric data type, if it is an exact numeric
data type; and
- an indication of whether the precision (and scale) are expressed
in decimal or binary terms.
4.4.1 Characteristics of numbers
An exact numeric value has a precision and a scale. The precision
is a positive integer that determines the number of significant
digits in a particular radix (binary or decimal). The scale is a
non-negative integer. A scale of 0 indicates that the number is an
integer. For a scale of S, the exact numeric value is the integer
value of the significant digits multiplied by 10-S.
An approximate numeric value consists of a mantissa and an expo-
nent. The mantissa is a signed numeric value, and the exponent is
a signed integer that specifies the magnitude of the mantissa. An
approximate numeric value has a precision. The precision is a posi-
tive integer that specifies the number of significant binary digits
in the mantissa. The value of an approximate numeric value is the
mantissa multiplied by 10exponent.
Whenever an exact or approximate numeric value is assigned to a
data item or parameter representing an exact numeric value, an
approximation of its value that preserves leading significant dig-
its after rounding or truncating is represented in the data type
of the target. The value is converted to have the precision and
scale of the target. The choice of whether to truncate or round is
implementation-defined.
An approximation obtained by truncation of a numerical value N
for an <exact numeric type> T is a value V representable in T such
that N is not closer to zero than the numerical value of V and such
that the absolute value of the difference between N and the numer-
ical value of V is less than the absolute value of the difference
between two successive numerical values representable in T.
An approximation obtained by rounding of a numerical value N for
an <exact numeric type> T is a value V representable in T such
that the absolute value of the difference between N and the nu-
merical value of V is not greater than half the absolute value
of the difference between two successive numerical values repre-
sentable in T. If there are more than one such values V, then it is
implementation-defined which one is taken.
All numerical values between the smallest and the largest value,
inclusive, representable in a given exact numeric type have an
approximation obtained by rounding or truncation for that type; it
is implementation-defined which other numerical values have such
approximations.
28 Database Language SQL
X3H2-92-154/DBL CBR-002
4.4 Numbers
An approximation obtained by truncation or rounding of a numerical
value N for an <approximate numeric type> T is a value V repre-
sentable in T such that there is no numerical value representable
in T and distinct from that of V that lies between the numerical
value of V and N, inclusive.
If there are more than one such values V then it is implementation-
defined which one is taken. It is implementation-defined which
numerical values have approximations obtained by rounding or trun-
cation for a given approximate numeric type.
Whenever an exact or approximate numeric value is assigned to a
data item or parameter representing an approximate numeric value,
an approximation of its value is represented in the data type of
the target. The value is converted to have the precision of the
target.
Operations on numbers are performed according to the normal rules
of arithmetic, within implementation-defined limits, except as
provided for in Subclause 6.12, "<numeric value expression>".
4.4.2 Operations involving numbers
As well as the usual arithmetic operators, plus, minus, times,
divide, unary plus, and unary minus, there are the following func-
tions that return numbers:
- <position expression> (see Subclause 4.2.2, "Operations involv-
ing character strings", and Subclause 4.3.2, "Operations involv-
ing bit strings") takes two strings as arguments and returns an
integer;
- <length expression> (see Subclause 4.2.2, "Operations involving
character strings", and Subclause 4.3.2, "Operations involv-
ing bit strings") operates on a string argument and returns an
integer;
- <extract expression> (see Subclause 4.5.3, "Operations involving
datetimes and intervals") operates on a datetime or interval
argument and returns an integer.
4.5 Datetimes and intervals
A datetime data type is described by a datetime data type descrip-
tor. An interval data type is described by an interval data type
descriptor.
A datetime data type descriptor contains:
- the name of the specific datetime data type (DATE, TIME,
TIMESTAMP, TIME WITH TIME ZONE, or TIMESTAMP WITH TIME ZONE);
and
Concepts 29
X3H2-92-154/DBL CBR-002
4.5 Datetimes and intervals
- the value of the <time fractional seconds precision>, if it is
a TIME, TIMESTAMP, TIME WITH TIME ZONE, or TIMESTAMP WITH TIME
ZONE type.
An interval data type descriptor contains:
- the name of the interval data type (INTERVAL);
- an indication of whether the interval data type is a year-month
interval or a day-time interval; and
- the <interval qualifier> that describes the precision of the
interval data type.
Every datetime or interval data type has an implied length in po-
sitions. Let D denote a value in some datetime or interval data
type DT. The length in positions of DT is constant for all D. The
length in positions is the number of characters from the character
set SQL_TEXT that it would take to represent any value in a given
datetime or interval data type.
4.5.1 Datetimes
Table 4, "Fields in datetime items", specifies the fields that can
make up a date time value; a datetime value is made up of a subset
of those fields. Not all of the fields shown are required to be in
the subset, but every field that appears in the table between the
first included primary field and the last included primary field
shall also be included. If either timezone field is in the subset,
then both of them shall be included.
__________________Table_4-Fields_in_datetime_items_________________
_Keyword____________Meaning________________________________________
|__________________|___Primary_datetime_fields_____________________|
| | |
| YEAR Year |
| |
| MONTH | Month within year |
| | |
| DAY | Day within month |
| | |
| HOUR | Hour within day |
| | |
| MINUTE | Minute within hour |
| | |
| SECOND | Second and possibly fraction of a second |
____________________within_minute__________________________________
|__________________|___Timezone_datetime_fields____________________|
| | |
| TIMEZONE_HOUR | Hour value of time zone displacement |
| |
|_TIMEZONE_MINUTE__|_Minute_value_of_time_zone_displacement________|
| | |
30 Database Language SQL
X3H2-92-154/DBL CBR-002
4.5 Datetimes and intervals
There is an ordering of the significance of <datetime field>s. This
is, from most significant to least significant: YEAR, MONTH, DAY,
HOUR, MINUTE, and SECOND.
The <datetime field>s other than SECOND contain non-negative in-
teger values, constrained by the natural rules for dates using the
Gregorian calendar. SECOND, however, can be defined to have a <time
fractional seconds precision> that indicates the number of decimal
digits maintained following the decimal point in the seconds value,
a non-negative exact numeric value.
There are three classes of datetime data types defined within this
International Standard:
- DATE - contains the <datetime field>s YEAR, MONTH, and DAY;
- TIME - contains the <datetime field>s HOUR, MINUTE, and SECOND;
and
- TIMESTAMP - contains the <datetime field>s YEAR, MONTH, DAY,
HOUR, MINUTE, and SECOND.
Items of type datetime are mutually comparable only if they have
the same <datetime field>s.
Datetimes only have absolute meaning in the context of additional
information. Time zones are political divisions of the earth's
surface that allow the convention that time is measured the same
at all locations within the time zone, regardless of the precise
value of "sun time" at specific locations. Political entities often
change the "local time" within a time zone for certain periods of
the year, e.g., in the summer. However, different political enti-
ties within the same time zone are not necessarily synchronized in
their local time changes. When a datetime is specified (in SQL-data
or elsewhere) it has an implied or explicit time zone specifier as-
sociated with it. Unless that time zone specifier, and its meaning,
is known, the meaning of the datetime value is ambiguous.
Therefore, datetime data types that contain time fields (TIME and
TIMESTAMP) are maintained in Universal Coordinated Time (UTC), with
an explicit or implied time zone part.
The time zone part is an interval specifying the difference between
UTC and the actual date and time in the time zone represented by
the time or timestamp data item. The time zone displacement is
defined as
INTERVAL HOUR TO MINUTE
A TIME or TIMESTAMP that does not specify WITH TIME ZONE has an im-
plicit time zone equal to the local time zone for the SQL-session.
The value of time represented in the data changes along with the
local time zone for the SQL-session. However, the meaning of the
time does not change because it is effectively maintained in UTC.
Concepts 31
X3H2-92-154/DBL CBR-002
4.5 Datetimes and intervals
Note: On occasion, UTC is adjusted by the omission of a second or
the insertion of a "leap second" in order to maintain synchro-
nization with sidereal time. This implies that sometimes, but very
rarely, a particular minute will contain exactly 59, 61, or 62
seconds.
4.5.2 Intervals
There are two classes of intervals. One class, called year-month
intervals, has an express or implied datetime precision that in-
cludes no fields other than YEAR and MONTH, though not both are
required. The other class, called day-time intervals, has an ex-
press or implied interval precision that can include any fields
other than YEAR or MONTH.
Table 5, "Fields in year-month INTERVAL items", specifies the
fields that make up a year-month interval. A year-month interval
is made up of a contiguous subset of those fields.
____________Table_5-Fields_in_year-month_INTERVAL_items____________
_Keyword______Meaning______________________________________________
| YEAR | Years |
| | |
|_MONTH______|_Months______________________________________________|
| | |
Table 6, "Fields in day-time INTERVAL items", specifies the fields
that make up a day-time interval. A day-time int 
