/
sql-2003-core-features.html
260 lines (236 loc) · 44.8 KB
/
sql-2003-core-features.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
<html>
<head>
<title> SQL 2003 Feature Taxonomy and Definition for Core SQL </title>
</head>
<body>
<h1> SQL 2003 (Annex F, Table 34) Feature Taxonomy and Definition for Core SQL </h1>
Derived from Final Committee Draft (FCD) of ISO/IEC 9075-2:2003.
<p>
<table border=1>
<tr><th> Number </th><th> Feature ID </th><th> Feature Name </th><th> Feature Description </th></tr>
<tr><td> 1 </td><td> B011 </td><td> Embedded Ada<sup>1</sup> </td><td> - Subclause 20.3, "<embedded SQL Ada program>" </td></tr>
<tr><td> 2 </td><td> B012 </td><td> Embedded C<sup>1</sup> </td><td> - Subclause 20.4, "<embedded SQL C program>" </td></tr>
<tr><td> 3 </td><td> B013 </td><td> Embedded COBOL<sup>1</sup> </td><td> - Subclause 20.5, "<embedded SQL COBOL program>" </td></tr>
<tr><td> 4 </td><td> B014 </td><td> Embedded Fortran<sup>1</sup> </td><td> - Subclause 20.6, "<embedded SQL Fortran program>" </td></tr>
<tr><td> 5 </td><td> B015 </td><td> Embedded MUMPS<sup>1</sup> </td><td> - Subclause 20.7, "<embedded SQL MUMPS program>" </td></tr>
<tr><td> 6 </td><td> B016 </td><td> Embedded Pascal<sup>1</sup> </td><td> - Subclause 20.8, "<embedded SQL Pascal program>" </td></tr>
<tr><td> 7 </td><td> B017 </td><td> Embedded PL/I<sup>1</sup> </td><td> - Subclause 20.9, "<embedded SQL PL/I program>" </td></tr>
<tr><td colspan=4> <sup>1</sup> A conforming SQL-implementation is required (by Clause 8, "Conformance", in ISO/IEC 9075-1) to support at least one embedded language or to support the SQL-client module binding for at least one host language. </td></tr>
<tr><td> 8 </td><td> E011 </td><td> Numeric data types </td><td> - Subclause 6.1, "<data type>", <numeric type>, including numeric expressions, numeric literals, numeric comparisons, and numeric assignments </td></tr>
<tr><td> 9 </td><td> E011-01 </td><td> INTEGER and SMALLINT data types (including all spellings) </td><td> - Subclause 5.2, "<token> and <separator>": The <reserved word>s INT, INTEGER, and SMALLINT </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 5.3, "<literal>": [<sign>] <unsigned integer> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The INTEGER and SMALLINT <exact numeric type>s </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 13.6, "Data type correspondences": Type correspondences for INTEGER and SMALLINT for all supported languages </td></tr>
<tr><td> 10 </td><td> E011-02 </td><td> REAL, DOUBLE PRECISON, and FLOAT data types </td><td> - Subclause 5.2, "<token> and <separator>": The <reserved word>s REAL, DOUBLE, FLOAT, and PRECISION </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 5.3, "<literal>": [<sign>] <approximate numeric literal> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": <approximate numeric type> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 13.6, "Data type correspondences": Type correspondences for REAL, DOUBLE PRECISION, and FLOAT for all supported languages </td></tr>
<tr><td> 11 </td><td> E011-03 </td><td> DECIMAL and NUMERIC data types </td><td> - Subclause 5.2, "<token> and <separator>": The <reserved word>s DEC, DECIMAL, and NUMERIC </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 5.3, "<literal>": [<sign>] <exact numeric literal> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The DECIMAL and NUMERIC <exact numeric type>s </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 13.6, "Data type correspondences": Type correspondences for DECIMAL and NUMERIC for all supported languages </td></tr>
<tr><td> 12 </td><td> E011-04 </td><td> Arithmetic operators </td><td> - Subclause 6.26, "<numeric value expression>": When the <numeric primary> is a <value expression primary> </td></tr>
<tr><td> 13 </td><td> E011-05 </td><td> Numeric comparison </td><td> - Subclause 8.2, "<comparison predicate>": For the numeric data types, without support for <table subquery> and without support for Feature F131, "Grouped operations" </td></tr>
<tr><td> 14 </td><td> E011-06 </td><td> Implicit casting among the numeric data types </td><td> - Subclause 8.2, "<comparison predicate>": Values of any of the numeric data types can be compared to each other; such values are compared with respect to their algebraic values </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 9.1, "Retrieval assignment", and Subclause 9.2, "Store assignment": Values of one numeric type can be assigned to another numeric type, subject to rounding, truncation, and out of range conditions </td></tr>
<tr><td> 15 </td><td> E021 </td><td> Character data types </td><td> - Subclause 6.1, "<data type>": <character string type>, including character expressions, character literals, character comparisons, character assignments, and other operations on character data </td></tr>
<tr><td> 16 </td><td> E021-01 </td><td> CHARACTER data type (including all its spellings) </td><td> - Subclause 5.2, "<token> and <separator>": The <reserved word>s CHAR and CHARACTER </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The CHARACTER <character string type> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.28, "<string value expression>": For values of type CHARACTER </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 13.6, "Data type correspondences": Type correspondences for CHARACTER for all supported languages </td></tr>
<tr><td> 17 </td><td> E021-02 </td><td> CHARACTER VARYING data type (including all its spellings) </td><td> - Subclause 5.2, "<token> and <separator>": The <reserved word>s VARCHAR and VARYING </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The CHARACTER VARYING <character string type> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.28, "<string value expression>": For values of type CHARACTER VARYING </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 13.6, "Data type correspondences": Type correspondences for CHARACTER VARYING for all supported languages </td></tr>
<tr><td> 18 </td><td> E021-03 </td><td> Character literals </td><td> - Subclause 5.3, "<literal>": <quote> [ <character representation>... ] <quote> </td></tr>
<tr><td> 19 </td><td> E021-04 </td><td> CHARACTER_LENGTH function </td><td> - Subclause 6.27, "<numeric value function>": The <char length expression> </td></tr>
<tr><td> 20 </td><td> E021-05 </td><td> OCTET_LENGTH function </td><td> - Subclause 6.27, "<numeric value function>": The <octet length expression> </td></tr>
<tr><td> 21 </td><td> E021-06 </td><td> SUBSTRING function </td><td> - Subclause 6.29, "<string value function>": The <character substring function> </td></tr>
<tr><td> 22 </td><td> E021-07 </td><td> Character concatenation </td><td> - Subclause 6.28, "<string value expression>": The <concatenation> expression </td></tr>
<tr><td> 23 </td><td> E021-08 </td><td> UPPER and LOWER functions </td><td> - Subclause 6.29, "<string value function>": The <fold> function </td></tr>
<tr><td> 24 </td><td> E021-09 </td><td> TRIM function </td><td> - Subclause 6.29, "<string value function>": The <trim function> </td></tr>
<tr><td> 25 </td><td> E021-10 </td><td> Implicit casting among the character data types </td><td> - Subclause 8.2, "<comparison predicate>": Values of either the CHARACTER or CHARACTER VARYING data types can be compared to each other </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 9.1, "Retrieval assignment", and Subclause 9.2, "Store assignment": Values of either the CHARACTER or CHARACTER VARYING data type can be assigned to the other type, subject to truncation conditions </td></tr>
<tr><td> 26 </td><td> E021-11 </td><td> POSITION function </td><td> - Subclause 6.27, "<numeric value function>": The <position expression> </td></tr>
<tr><td> 27 </td><td> E021-12 </td><td> Character comparison </td><td> - Subclause 8.2, "<comparison predicate>": For the CHARACTER and CHARACTER VARYING data types, without support for <table subquery> and without support for Feature F131, "Grouped operations" </td></tr>
<tr><td> 28 </td><td> E031 </td><td> Identifiers </td><td> - Subclause 5.2, "<token> and <separator>": <regular identifier> and <delimited identifier> </td></tr>
<tr><td> 29 </td><td> E031-01 </td><td> Delimited identifiers </td><td> - Subclause 5.2, "<token> and <separator>": <delimited identifier> </td></tr>
<tr><td> 30 </td><td> E031-02 </td><td> Lower case identifiers </td><td> - Subclause 5.2, "<token> and <separator>": An alphabetic character in a <regular identifier> can be either lower case or upper case (meaning that non-delimited identifiers need not comprise only upper case letters) </td></tr>
<tr><td> 31 </td><td> E031-03 </td><td> Trailing underscore </td><td> - Subclause 5.2, "<token> and <separator>": The list <identifier part> in a <regular identifier> can be an <underscore> </td></tr>
<tr><td> 32 </td><td> E051 </td><td> Basic query specification </td><td> - Subclause 7.12, "<query specification>": When <table reference> is a <table or query name> that is a <table name>, without the support of Feature F131, "Grouped operations" </td></tr>
<tr><td> 33 </td><td> E051-01 </td><td> SELECT DISTINCT </td><td> - Subclause 7.12, "<query specification>": With a <set quantifier> of DISTINCT, but without subfeatures E051-02 through E051-09 </td></tr>
<tr><td> 34 </td><td> E051-02 </td><td> GROUP BY clause </td><td> - Subclause 7.4, "<table expression>": <group by clause>, but without subfeatures E051-03 through E051-09 </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 7.9, "<group by clause>": With the restrictions that the <group by clause> must contain all non-aggregated columns in the <select list> and that any column in the <group by clause> must also appear in the <select list> </td></tr>
<tr><td> 35 </td><td> E051-04 </td><td> GROUP BY can contain columns not in <select list> </td><td> - Subclause 7.9, "<group by clause>": Without the restriction that any column in the <group by clause> must also appear in the <select list> </td></tr>
<tr><td> 36 </td><td> E051-05 </td><td> Select list items can be renamed </td><td> - Subclause 7.12, "<query specification>": <as clause> </td></tr>
<tr><td> 37 </td><td> E051-06 </td><td> HAVING clause </td><td> - Subclause 7.4, "<table expression>": <having clause> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 7.10, "<having clause>" </td></tr>
<tr><td> 38 </td><td> E051-07 </td><td> Qualified * in select list </td><td> - Subclause 7.12, "<query specification>": <qualified asterisk> </td></tr>
<tr><td> 39 </td><td> E051-08 </td><td> Correlation names in the FROM clause </td><td> - Subclause 7.6, "<table reference>": [ AS ] <correlation name> </td></tr>
<tr><td> 40 </td><td> E051-09 </td><td> Rename columns in the FROM clause </td><td> - Subclause 7.6, "<table reference>": [ AS ] <correlation name> [ <left paren> <derived column list> <right paren> ] </td></tr>
<tr><td> 41 </td><td> E061 </td><td> Basic predicates and search conditions </td><td> - Subclause 8.19, "<search condition>", and Subclause 8.1, "<predicate>" </td></tr>
<tr><td> 42 </td><td> E061-01 </td><td> Comparison predicate </td><td> - Subclause 8.2, "<comparison predicate>": For supported data types, without support for <table subquery> </td></tr>
<tr><td> 43 </td><td> E061-02 </td><td> BETWEEN predicate </td><td> - Subclause 8.3, "<between predicate>" </td></tr>
<tr><td> 44 </td><td> E061-03 </td><td> IN predicate with list of values </td><td> - Subclause 8.4, "<in predicate>": Without support for <table subquery> </td></tr>
<tr><td> 45 </td><td> E061-04 </td><td> LIKE predicate </td><td> - Subclause 8.5, "<like predicate>": Without [ ESCAPE <escape character> ] </td></tr>
<tr><td> 46 </td><td> E061-05 </td><td> LIKE predicate: ESCAPE clause </td><td> - Subclause 8.5, "<like predicate>": With [ ESCAPE <escape character> ] </td></tr>
<tr><td> 47 </td><td> E061-06 </td><td> NULL predicate </td><td> - Subclause 8.7, "<null predicate>": Without Feature F481, "Expanded NULL predicate" </td></tr>
<tr><td> 48 </td><td> E061-07 </td><td> Quantified comparison predicate </td><td> - Subclause 8.8, "<quantified comparison predicate>": Without support for <table subquery> </td></tr>
<tr><td> 49 </td><td> E061-08 </td><td> EXISTS predicate </td><td> - Subclause 8.9, "<exists predicate>" </td></tr>
<tr><td> 50 </td><td> E061-09 </td><td> Subqueries in comparison predicate </td><td> - Subclause 8.2, "<comparison predicate>": For supported data types, with support for <table subquery> </td></tr>
<tr><td> 51 </td><td> E061-11 </td><td> Subqueries in IN predicate </td><td> - Subclause 8.4, "<in predicate>": With support for <table subquery> </td></tr>
<tr><td> 52 </td><td> E061-12 </td><td> Subqueries in quantified comparison predicate </td><td> - Subclause 8.8, "<quantified comparison predicate>": With support for <table subquery> </td></tr>
<tr><td> 53 </td><td> E061-13 </td><td> Correlated subqueries </td><td> - Subclause 8.1, "<predicate>": When a <correlation name> can be used in a <table subquery> as a correlated reference to a column in the outer query </td></tr>
<tr><td> 54 </td><td> E061-14 </td><td> Search condition </td><td> - Subclause 8.19, "<search condition>" </td></tr>
<tr><td> 55 </td><td> E071 </td><td> Basic query expressions </td><td> - Subclause 7.13, "<query expression>" </td></tr>
<tr><td> 56 </td><td> E071-01 </td><td> UNION DISTINCT table operator </td><td> - Subclause 7.13, "<query expression>": With support for UNION [ DISTINCT ] </td></tr>
<tr><td> 57 </td><td> E071-02 </td><td> UNION ALL table operator </td><td> - Subclause 7.13, "<query expression>": With support for UNION ALL </td></tr>
<tr><td> 58 </td><td> E071-03 </td><td> EXCEPT DISTINCT table operator </td><td> - Subclause 7.13, "<query expression>": With support for EXCEPT [ DISTINCT ] </td></tr>
<tr><td> 59 </td><td> E071-05 </td><td> Columns combined via table operators need not have exactly the same data type. </td><td> - Subclause 7.13, "<query expression>": Columns combined via UNION and EXCEPT need not have exactly the same data type </td></tr>
<tr><td> 60 </td><td> E071-06 </td><td> Table operators in subqueries </td><td> - Subclause 7.13, "<query expression>": <table subquery>s can specify UNION and EXCEPT </td></tr>
<tr><td> 61 </td><td> E081 </td><td> Basic Privileges </td><td> - Subclause 12.3, "<privileges>" </td></tr>
<tr><td> 62 </td><td> E081-01 </td><td> SELECT privilege </td><td> - Subclause 12.3, "<privileges>": With <action> of SELECT </td></tr>
<tr><td> 63 </td><td> E081-02 </td><td> DELETE privilege </td><td> - Subclause 12.3, "<privileges>": With <action> of DELETE </td></tr>
<tr><td> 64 </td><td> E081-03 </td><td> INSERT privilege at the table level </td><td> - Subclause 12.3, "<privileges>": With <action> of INSERT without <privilege column list> </td></tr>
<tr><td> 65 </td><td> E081-04 </td><td> UPDATE privilege at the table level </td><td> - Subclause 12.3, "<privileges>": With <action> of UPDATE without <privilege column list> </td></tr>
<tr><td> 66 </td><td> E081-05 </td><td> UPDATE privilege at the column level </td><td> - Subclause 12.3, "<privileges>": With <action> of UPDATE <left paren> <privilege column list> <right paren> </td></tr>
<tr><td> 67 </td><td> E081-06 </td><td> REFERENCES privilege at the table level </td><td> - Subclause 12.3, "<privileges>": with <action> of REFERENCES without <privilege column list> </td></tr>
<tr><td> 68 </td><td> E081-07 </td><td> REFERENCES privilege at the column level </td><td> - Subclause 12.3, "<privileges>": With <action> of REFERENCES <left paren> <privilege column list> <right paren> </td></tr>
<tr><td> 69 </td><td> E081-08 </td><td> WITH GRANT OPTION </td><td> - Subclause 12.2, "<grant privilege statement>": WITH GRANT OPTION </td></tr>
<tr><td> 70 </td><td> E091 </td><td> Set functions </td><td> - Subclause 6.9, "<set function specification>" </td></tr>
<tr><td> 71 </td><td> E091-01 </td><td> AVG </td><td> - Subclause 6.9, "<set function specification>": With <computational operation> of AVG </td></tr>
<tr><td> 72 </td><td> E091-02 </td><td> COUNT </td><td> - Subclause 6.9, "<set function specification>": With <computational operation> of COUNT </td></tr>
<tr><td> 73 </td><td> E091-03 </td><td> MAX </td><td> - Subclause 6.9, "<set function specification>": With <computational operation> of MAX </td></tr>
<tr><td> 74 </td><td> E091-04 </td><td> MIN </td><td> - Subclause 6.9, "<set function specification>": With <computational operation> of MIN </td></tr>
<tr><td> 75 </td><td> E091-05 </td><td> SUM </td><td> - Subclause 6.9, "<set function specification>": With <computational operation> of SUM </td></tr>
<tr><td> 76 </td><td> E091-06 </td><td> ALL quantifier </td><td> - Subclause 6.9, "<set function specification>": With <set quantifier> of ALL </td></tr>
<tr><td> 77 </td><td> E091-07 </td><td> DISTINCT quantifier </td><td> - Subclause 6.9, "<set function specification>": With <set quantifier> of DISTINCT </td></tr>
<tr><td> 78 </td><td> E101 </td><td> Basic data manipulation </td><td> - Clause 14, "Data manipulation": <insert statement>, <delete statement: searched>, and <update statement: searched> </td></tr>
<tr><td> 79 </td><td> E101-01 </td><td> INSERT statement </td><td> - Subclause 14.8, "<insert statement>": When a <contextually typed table value constructor> can consist of no more than a single <contextually typed row value expression> </td></tr>
<tr><td> 80 </td><td> E101-03 </td><td> Searched UPDATE statement </td><td> - Subclause 14.11, "<update statement: searched>": But without support either of Feature E153, "Updatable tables with subqueries", or Feature F221, "Explicit defaults" </td></tr>
<tr><td> 81 </td><td> E101-04 </td><td> Searched DELETE statement </td><td> - Subclause 14.7, "<delete statement: searched>" </td></tr>
<tr><td> 82 </td><td> E111 </td><td> Single row SELECT statement </td><td> - Subclause 14.5, "<select statement: single row>": Without support of Feature F131, "Grouped operations" </td></tr>
<tr><td> 83 </td><td> E121 </td><td> Basic cursor support </td><td> - Clause 14, "Data manipulation": <declare cursor>, <open statement>, <fetch statement>, <close statement>, <delete statement: positioned>, and <update statement: positioned> </td></tr>
<tr><td> 84 </td><td> E121-01 </td><td> DECLARE CURSOR </td><td> - Subclause 14.1, "<declare cursor>": When each <value expression> in the <sort key> must be a <column reference> and that <column reference> must also be in the <select list>, and <cursor holdability> is not specified </td></tr>
<tr><td> 85 </td><td> E121-02 </td><td> ORDER BY columns need not be in select list </td><td> - Subclause 14.1, "<declare cursor>": Extend subfeature E121-01 so that <column reference> need not also be in the <select list> </td></tr>
<tr><td> 86 </td><td> E121-03 </td><td> Value expressions in ORDER BY clause </td><td> - Subclause 14.1, "<declare cursor>": Extend subfeature E121-01 so that the <value expression> in the <sort key> need not be a <column reference> </td></tr>
<tr><td> 87 </td><td> E121-04 </td><td> OPEN statement </td><td> - Subclause 14.2, "<open statement>" </td></tr>
<tr><td> 88 </td><td> E121-06 </td><td> Positioned UPDATE statement </td><td> - Subclause 14.10, "<update statement: positioned>": Without support of either Feature E153, "Updateable tables with subqueries" or Feature F221, "Explicit defaults" </td></tr>
<tr><td> 89 </td><td> E121-07 </td><td> Positioned DELETE statement </td><td> - Subclause 14.6, "<delete statement: positioned>" </td></tr>
<tr><td> 90 </td><td> E121-08 </td><td> CLOSE statement </td><td> - Subclause 14.4, "<close statement>" </td></tr>
<tr><td> 91 </td><td> E121-10 </td><td> FETCH statement: implicit NEXT </td><td> - Subclause 14.3, "<fetch statement>" </td></tr>
<tr><td> 92 </td><td> E121-17 </td><td> WITH HOLD cursors </td><td> - Subclause 14.1, "<declare cursor>": Where the <value expression> in the <sort key> need not be a <column reference> and need not be in the <select list>, and <cursor holdability> may be specified </td></tr>
<tr><td> 93 </td><td> E131 </td><td> Null value support (nulls in lieu of values) </td><td> - Subclause 4.14, "Columns, fields, and attributes": Nullability characteristic </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.5, "<contextually typed value specification>": <null specification> </td></tr>
<tr><td> 94 </td><td> E141 </td><td> Basic integrity constraints </td><td> - Subclause 11.6, "<table constraint definition>": As specified by the subfeatures of this feature in this table </td></tr>
<tr><td> 95 </td><td> E141-01 </td><td> NOT NULL constraints </td><td> - Subclause 11.4, "<column definition>": With <column constraint> of NOT NULL </td></tr>
<tr><td> 96 </td><td> E141-02 </td><td> UNIQUE constraints of NOT NULL columns </td><td> - Subclause 11.4, "<column definition>": With <unique specification> of UNIQUE for columns specified as NOT NULL </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.7, "<unique constraint definition>": With <unique specification> of UNIQUE </td></tr>
<tr><td> 97 </td><td> E141-03 </td><td> PRIMARY KEY constraints </td><td> - Subclause 11.4, "<column definition>": With <unique specification> of PRIMARY KEY for columns specified as NOT NULL </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.7, "<unique constraint definition>": With <unique specification> of PRIMARY KEY </td></tr>
<tr><td> 98 </td><td> E141-04 </td><td> Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action. </td><td> - Subclause 11.4, "<column definition>": With <column constraint> of <references specification> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.8, "<referential constraint definition>": Where the columns in the <column name list>, if specified, must be in the same order as the names in the <unique column list> of the applicable <unique constraint definition> and the <data type>s of the matching columns must be the same </td></tr>
<tr><td> 99 </td><td> E141-06 </td><td> CHECK constraints </td><td> - Subclause 11.4, "<column definition>": With <column constraint> of <check constraint definition> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.9, "<check constraint definition>" </td></tr>
<tr><td> 100 </td><td> E141-07 </td><td> Column defaults </td><td> - Subclause 11.4, "<column definition>": With <default clause> </td></tr>
<tr><td> 101 </td><td> E141-08 </td><td> NOT NULL inferred on PRIMARY KEY </td><td> - Subclause 11.4, "<column definition>", and Subclause 11.7, "<unique constraint definition>": Remove the restriction in subfeatures E141-02 and E141-03 that NOT NULL be specified along with every PRIMARY KEY and UNIQUE constraint </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.4, "<column definition>": NOT NULL is implicit on PRIMARY KEY constraints </td></tr>
<tr><td> 102 </td><td> E141-10 </td><td> Names in a foreign key can be specified in any order </td><td> - Subclause 11.4, "<column definition>", and Subclause 11.8, "<referential constraint definition>": Extend subfeature E141-04 so that the columns in the <column name list>, if specified, need not be in the same order as the names in the <unique column list> of the applicable <unique constraint definition> </td></tr>
<tr><td> 103 </td><td> E141-11 </td><td> Foreign key"s data types need not be the same as the primary key"s </td><td> - Subclause 11.4, "<column definition>", and Subclause 11.8, "<referential constraint definition>": Extend subfeature E141-04 so that the data types of matching columns need not be the same. </td></tr>
<tr><td> 104 </td><td> E151 </td><td> Transaction support </td><td> - Clause 16, "Transaction management": <commit statement> and <rollback statement> </td></tr>
<tr><td> 105 </td><td> E151-01 </td><td> COMMIT statement </td><td> - Subclause 16.6, "<commit statement>" </td></tr>
<tr><td> 106 </td><td> E151-02 </td><td> ROLLBACK statement </td><td> - Subclause 16.7, "<rollback statement>" </td></tr>
<tr><td> 107 </td><td> E152 </td><td> Basic SET TRANSACTION statement </td><td> - Subclause 16.2, "<set transaction statement>" </td></tr>
<tr><td> 108 </td><td> E152-01 </td><td> SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause </td><td> - Subclause 16.2, "<set transaction statement>": With <transaction mode> of ISOLATION LEVEL SERIALIZABLE clause </td></tr>
<tr><td> 109 </td><td> E152-02 </td><td> SET TRANSACTION statement: READ ONLY and READ WRITE clauses </td><td> - Subclause 16.2, "<set transaction statement>": with <transaction access mode> of READ ONLY or READ WRITE </td></tr>
<tr><td> 110 </td><td> E153 </td><td> Updatable queries with subqueries </td><td> - Subclause 7.13, "<query expression>": A <query expression> is updatable even though its <where clause> contains a <subquery> </td></tr>
<tr><td> 111 </td><td> E161 </td><td> SQL comments using leading double minus </td><td> - Subclause 5.2, "<token> and <separator>": <simple comment> </td></tr>
<tr><td> 112 </td><td> E171 </td><td> SQLSTATE support </td><td> - Subclause 23.1, "SQLSTATE" </td></tr>
<tr><td> 113 </td><td> E182 </td><td> Module language </td><td> - Clause 13, "SQL-client modules" <br>(NOTE 450 - An SQL-implementation is required to supply at least one binding to a standard host language using either module language, embedded SQL, or both.) </td></tr>
<tr><td> 114 </td><td> F031 </td><td> Basic schema manipulation </td><td> - Clause 11, "Schema definition and manipulation": Selected facilities as indicated by the subfeatures of this Feature </td></tr>
<tr><td> 115 </td><td> F031-01 </td><td> CREATE TABLE statement to create persistent base tables </td><td> - Subclause 11.3, "<table definition>": Not in the context of a <schema definition> </td></tr>
<tr><td> 116 </td><td> F031-02 </td><td> CREATE VIEW statement </td><td> - Subclause 11.22, "<view definition>": Not in the context of a <schema definition>, and without support of Feature F081, "UNION and EXCEPT in views" </td></tr>
<tr><td> 117 </td><td> F031-03 </td><td> GRANT statement </td><td> - Subclause 12.1, "<grant statement>": Not in the context of a <schema definition> </td></tr>
<tr><td> 118 </td><td> F031-04 </td><td> ALTER TABLE statement: ADD COLUMN clause </td><td> - Subclause 11.10, "<alter table statement>": The <add column definition> clause </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.11, "<add column definition>" </td></tr>
<tr><td> 119 </td><td> F031-13 </td><td> DROP TABLE statement: RESTRICT clause </td><td> - Subclause 11.21, "<drop table statement>": With a <drop behavior> of RESTRICT </td></tr>
<tr><td> 120 </td><td> F031-16 </td><td> DROP VIEW statement: RESTRICT clause </td><td> - Subclause 11.23, "<drop view statement>": With a <drop behavior> of RESTRICT </td></tr>
<tr><td> 121 </td><td> F031-19 </td><td> REVOKE statement: RESTRICT clause </td><td> - Subclause 12.7, "<revoke statement>": With a <drop behavior> of RESTRICT, only where the use of this statement can be restricted to the owner of the table being dropped </td></tr>
<tr><td> 122 </td><td> F041 </td><td> Basic joined table </td><td> - Subclause 7.7, "<joined table>" </td></tr>
<tr><td> 123 </td><td> F041-01 </td><td> Inner join (but not necessarily the INNER keyword) </td><td> - Subclause 7.6, "<table reference>": The <joined table> clause, but without support for subfeatures F041-02 through F041-08 </td></tr>
<tr><td> 124 </td><td> F041-02 </td><td> INNER keyword </td><td> - Subclause 7.7, "<joined table>": <join type> of INNER </td></tr>
<tr><td> 125 </td><td> F041-03 </td><td> LEFT OUTER JOIN </td><td> - Subclause 7.7, "<joined table>": <outer join type> of LEFT </td></tr>
<tr><td> 126 </td><td> F041-04 </td><td> RIGHT OUTER JOIN </td><td> - Subclause 7.7, "<joined table>": <outer join type> of RIGHT </td></tr>
<tr><td> 127 </td><td> F041-05 </td><td> Outer joins can be nested </td><td> - Subclause 7.7, "<joined table>": Subfeature F041-1 extended so that a <table reference> within the <joined table> can itself be a <joined table> </td></tr>
<tr><td> 128 </td><td> F041-07 </td><td> The inner table in a left or right outer join can also be used in an inner join </td><td> - Subclause 7.7, "<joined table>": Subfeature F041-1 extended so that a <table name> within a nested <joined table> can be the same as a <table name> in an outer <joined table> </td></tr>
<tr><td> 129 </td><td> F041-08 </td><td> All comparison operators are supported (rather than just =) </td><td> - Subclause 7.7, "<joined table>": Subfeature F041-1 extended so that the <join condition> is not limited to a <comparison predicate> with a <comp op> of <equals operator> </td></tr>
<tr><td> 130 </td><td> F051 </td><td> Basic date and time </td><td> - Subclause 6.1, "<data type>": <datetime type> including datetime literals, datetime comparisons, and datetime conversions </td></tr>
<tr><td> 131 </td><td> F051-01 </td><td> DATE data type (including support of DATE literal) </td><td> - Subclause 5.3, "<literal>": The <date literal> form of <datetime literal> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The DATE <datetime type> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": For values of type DATE 132 F051-02 TIME data type (including support of TIME literal) with fractional seconds precision of at least 0. </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 5.3, "<literal>": The <time literal> form of <datetime literal>, where the value of <unquoted time string> is simply <time value> that does not include the optional <time zone interval> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The TIME <datetime type> without the <with or without timezone> clause </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": For values of type TIME 133 F051-03 TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6. </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 5.3, "<literal>": The <timestamp literal> form of <datetime literal>, where the value of <unquoted timestamp string> is simply <time value> that does not include the optional <time zone interval> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.1, "<data type>": The TIMESTAMP <datetime type> without the <with or without timezone> clause </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": For values of type TIMESTAMP </td></tr>
<tr><td> 134 </td><td> F051-04 </td><td> Comparison predicate on DATE, TIME, and TIMESTAMP data types </td><td> - Subclause 8.2, "<comparison predicate>": For comparison between values of the following types: DATE and DATE, TIME and TIME, TIMESTAMP and TIMESTAMP, DATE and TIMESTAMP, and TIME and TIMESTAMP </td></tr>
<tr><td> 135 </td><td> F051-05 </td><td> Explicit CAST between datetime types and character types </td><td> - Subclause 6.12, "<cast specification>": If support for Feature F201, "CAST function" is available, then CASTing between the following types: from character string to DATE, TIME, and TIMESTAMP; from DATE to DATE, TIMESTAMP, and character string; from TIME to TIME, TIMESTAMP, and character string; from TIMESTAMP to DATE, TIME, TIMESTAMP, and character string </td></tr>
<tr><td> 136 </td><td> F051-06 </td><td> CURRENT_DATE </td><td> - Subclause 6.31, "<datetime value function>": The <current date value function> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": When the value is a <current date value function> </td></tr>
<tr><td> 137 </td><td> F051-07 </td><td> LOCALTIME </td><td> - Subclause 6.31, "<datetime value function>": The <current local time value function> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": When the value is a <current local time value function> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.5, "<default clause>": LOCALTIME option of <datetime value function> </td></tr>
<tr><td> 138 </td><td> F051-08 </td><td> LOCALTIMESTAMP </td><td> - Subclause 6.31, "<datetime value function>": The <current local timestamp value function> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.30, "<datetime value expression>": When the value is a <current local timestamp value function> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.5, "<default clause>": LOCALTIMESTAMP option of <datetime value function> </td></tr>
<tr><td> 139 </td><td> F081 </td><td> UNION and EXCEPT in views </td><td> - Subclause 11.22, "<view definition>": A <query expression> in a <view definition> may specify UNION DISTINCT, UNION ALL, EXCEPT, and/or EXCEPT ALL </td></tr>
<tr><td> 140 </td><td> F131 </td><td> Grouped operations </td><td> - A grouped view is a view whose <query expression> contains a <group by clause> </td></tr>
<tr><td> 141 </td><td> F131-01 </td><td> WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views </td><td> - Subclause 7.4, "<table expression>": Even though a table in the <from clause> is a grouped view, the <where clause>, <group by clause>, and <having clause> may be specified </td></tr>
<tr><td> 142 </td><td> F131-02 </td><td> Multiple tables supported in queries with grouped views </td><td> - Subclause 7.5, "<from clause>": Even though a table in the <from clause> is a grouped view, the <from clause> may specify more than one <table reference> </td></tr>
<tr><td> 143 </td><td> F131-03 </td><td> Set functions supported in queries with grouped views </td><td> - Subclause 7.12, "<query specification>": Even though a table in the <from clause> is a grouped view, the <select list> may specify a <set function specification> </td></tr>
<tr><td> 144 </td><td> F131-04 </td><td> Subqueries with GROUP BY and HAVING clauses and grouped views </td><td> - Subclause 7.15, "<subquery>": A <subquery> in a <comparison predicate> is allowed to contain a <group by clause> and/or a <having clause and/or it may identify a grouped view </td></tr>
<tr><td> 145 </td><td> F131-05 </td><td> Single row SELECT with GROUP BY and HAVING clauses and grouped views </td><td> - Subclause 14.5, "<select statement: single row>": The table in a <from clause> can be a grouped view </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 14.5, "<select statement: single row>": The <table expression> may specify a <group by clause and/or a <having clause </td></tr>
<tr><td> 146 </td><td> F181 </td><td> Multiple module support <br>(NOTE 451 - The ability to associate multiple host compilation units with a single SQL-session at one time.) </td><td> - Subclause 13.1, "<SQL-client module definition>": An SQL-agent can be associated with more than one <SQL-client module definition> <br>(NOTE 452 - With this feature, it is possible to compile <SQL-client module definition>s or <embedded SQL host program>s separately and rely on the SQL-implementation to "link" the together properly at execution time. To ensure portability, applications should adhere to the following limitations: <br><bl><li> Avoid linking modules having cursors with the same <cursor name>. </li> <li> Avoid linking modules that prepare statements using the same <SQL statement name>. </li> <li> Avoid linking modules that allocate descriptors with the same <descriptor name>. </li> <li> Assume that the scope of an <embedded exception declaration> is a single compilation unit. </li> <li> Assume that an <embedded variable name> can be referenced only in the same compilation unit in which it is declared.) </li></bl> </td></tr>
<tr><td> 147 </td><td> F201 </td><td> CAST function <br>(NOTE 453 - This means the support of CAST, where relevant, among all supported data types.) </td><td> - Subclause 6.12, "<cast specification>": For all supported data types </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 6.25, "<value expression>": <cast specification> </td></tr>
<tr><td> 148 </td><td> F221 </td><td> Explicit defaults </td><td> - Subclause 6.5, "<contextually typed value specification>": <default specification> <br>(NOTE 454 - Including its use in UPDATE and INSERT statements.) </td></tr>
<tr><td> 149 </td><td> F261 </td><td> CASE expression </td><td> - Subclause 6.25, "<value expression>": <case expression> </td></tr>
<tr><td> 150 </td><td> F261-01 </td><td> Simple CASE </td><td> - Subclause 6.11, "<case expression>": The <simple case> variation </td></tr>
<tr><td> 151 </td><td> F261-02 </td><td> Searched CASE </td><td> - Subclause 6.11, "<case expression>": The <searched case variation> </td></tr>
<tr><td> 152 </td><td> F261-03 </td><td> NULLIF </td><td> - Subclause 6.11, "<case expression>": The NULLIF <case abbreviation </td></tr>
<tr><td> 153 </td><td> F261-04 </td><td> COALESCE </td><td> - Subclause 6.11, "<case expression>": The COALESCE <case abbreviation </td></tr>
<tr><td> 154 </td><td> F311 </td><td> Schema definition statement </td><td> - Subclause 11.1, "<schema definition>" </td></tr>
<tr><td> 155 </td><td> F311-01 </td><td> CREATE SCHEMA </td><td> - Subclause 11.1, "<schema definition>": Support for circular references in that <referential constraint definition>s in two different <table definition>s may reference columns in the other table </td></tr>
<tr><td> 156 </td><td> F311-02 </td><td> CREATE TABLE for persistent base tables </td><td> - Subclause 11.1, "<schema definition>": A <schema element> that is a <table definition> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.3, "<table definition>": In the context of a <schema definition> </td></tr>
<tr><td> 157 </td><td> F311-03 </td><td> CREATE VIEW </td><td> - Subclause 11.1, "<schema definition>": A <schema element> that is a <view definition> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 11.22, "<view definition>": In the context of a <schema definition> without the WITH CHECK OPTION clause and without support of Feature F081, "UNION and EXCEPT in views" </td></tr>
<tr><td> 158 </td><td> F311-04 </td><td> CREATE VIEW: WITH CHECK OPTION </td><td> - Subclause 11.22, "<view definition>": The WITH CHECK OPTION clause, in the context of a <schema definition>, but without support of Feature F081, "UNION and EXCEPT in views" </td></tr>
<tr><td> 159 </td><td> F311-05 </td><td> GRANT statement </td><td> - Subclause 11.1, "<schema definition>": A <schema element> that is a <grant statement> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 12.1, "<grant statement>": In the context of a <schema definition> </td></tr>
<tr><td> 160 </td><td> F471 </td><td> Scalar subquery values </td><td> - Subclause 6.25, "<value expression>": A <value expression primary> can be a <scalar subquery> </td></tr>
<tr><td> 161 </td><td> F481 </td><td> Expanded NULL predicate </td><td> - Subclause 8.7, "<null predicate>": The <row value expression> can be something other than a <column reference> </td></tr>
<tr><td> 162 </td><td> F812 </td><td> Basic flagging </td><td> - Part 1, Subclause 8.1.4, "SQL flagger": With "level of flagging" specified to be Core SQL Flagging and "extent of checking" specified to be Syntax Only <br>(NOTE 455 - This form of flagging identifies vendor extensions and other non-standard SQL by checking syntax only without requiring access to the catalog information.) </td></tr>
<tr><td> 163 </td><td> S011 </td><td> Distinct data types </td><td> - Subclause 11.41, "<user-defined type definition>": When <representation> is <predefined type> </td></tr>
<tr><td> 164 </td><td> T321 </td><td> Basic SQL-invoked routines </td><td> - Subclause 11.50, "<SQL-invoked routine>" </td></tr>
<tr><td> </td><td> </td><td> </td><td> - If Feature T041, "Basic LOB data type support", is supported, then the <locator indication> clause must also be supported <br>(NOTE 456 - "Routine" is the collective term for functions, methods, and procedures. This feature requires a conforming SQLimplementation to support both user-defined functions and user-defined procedures. An SQL-implementation that conforms to Core SQL must support at least one language for writing routines; that language may be SQL. If the language is SQL, then the basic specification capability in Core SQL is the ability to specify a one-statement routine. Support for overloaded functions and procedures is not part of Core SQL.) </td></tr>
<tr><td> 165 </td><td> T321-01 </td><td> User-defined functions with no overloading </td><td> - Subclause 11.50, "<SQL-invoked routine>": With <function specification> </td></tr>
<tr><td> 166 </td><td> T321-02 </td><td> User-defined stored procedures with no overloading </td><td> - Subclause 11.50, "<SQL-invoked routine>": With <SQL-invoked procedure> </td></tr>
<tr><td> 167 </td><td> T321-03 </td><td> Function invocation </td><td> - Subclause 6.4, "<value specification> and <target specification>": With a <value expression primary> that is a <routine invocation> </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 10.4, "<routine invocation>": For user-defined functions </td></tr>
<tr><td> 168 </td><td> T321-04 </td><td> CALL statement </td><td> - Subclause 10.4, "<routine invocation>": Used by <call statement>s </td></tr>
<tr><td> </td><td> </td><td> </td><td> - Subclause 15.1, "<call statement>" </td></tr>
<tr><td> 169 </td><td> T321-05 </td><td> RETURN statement </td><td> - Subclause 15.2, "<return statement>", if the SQL-implementation supports SQL routines </td></tr>
</table>
<hr>
<p>
Please send feedback to Jonathan Leffler:
<a href="mailto:jonathan.leffler@gmail.com"> jonathan.leffler@gmail.com </a>.
</p>
<p><font color=green><i><small>
@(#)$Id: sql-2003-core-features.html,v 1.3 2017/11/13 20:45:42 jleffler Exp $
</small></i></font></p>
</body>
</html>