Appendix J: SQL99 Major Features Compliance Across Different RDBMS
|
|
Overview
Table J-1 shows which of 350 major features defining SQL99 standard compliance have been implemented in IBM UDB2 (version 7.2), Oracle 9i, and Microsoft SQL Server 2000. Whenever an alternative implementation is available the vendor's feature is marked as compliant. For example, the ANSI/ISO standard mandates a CHARACTER_LENGTH function, which Oracle and UDB2 implement"with their function LENGTH and Microsoft with its function LEN — for our purposes they are considered to be compliant, though strictly speaking they are not. For practicality's sake, we follow the spirit, not the letter, of the standard.
| Identifier | Description | IBM | Oracle | Microsoft |
|---|---|---|---|---|
| E011 | Numeric data types |
|
|
|
| E011-01 | INTEGER and SMALLINT data types |
|
|
|
| E011-02 | REAL, DOUBLE PRECISON, and FLOAT data types |
|
|
|
| E011-03 | DECIMAL and NUMERIC data types |
|
|
|
| E011-04 | Arithmetic operators |
|
|
|
| E011-05 | Numeric comparison |
|
|
|
| E011-06 | Implicit casting among the numeric data types |
|
|
|
| E021 | Character data types |
|
|
|
| E021-01 | CHARACTER data type |
|
|
|
| E021-02 | CHARACTER VARYING data type |
| Partial | Partial |
| E021-03 | Character literals |
| Partial |
|
| E021-04 | CHARACTER_LENGTH function |
|
|
|
| E021-05 | OCTET_LENGTH function |
|
|
|
| E021-06 | SUBSTRING function |
|
|
|
| E021-07 | Character concatenation |
|
|
|
| E021-08 | UPPER and LOWER functions |
|
|
|
| E021-09 | TRIM function |
|
|
|
| E021-10 | Implicit casting among the character data types |
|
|
|
| E021-11 | POSITION function |
|
|
|
| E011-12 | Character comparison |
|
|
|
| E031 | Identifiers |
|
|
|
| E031-01 | Delimited identifiers |
|
|
|
| E031-02 | Lower case identifiers |
|
|
|
| E031-03 | Trailing underscore | |||
| E051 | Basic query specification |
|
|
|
| E051-01 | SELECT DISTINCT |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| E051-02 | GROUP BY clause |
|
|
|
| E051-04 | GROUP BY can contain columns not in select list |
|
|
|
| E051-05 | Select list items can be renamed |
|
|
|
| E051-06 | HAVING clause |
|
|
|
| E051-07 | Qualified * in select list |
|
|
|
| E051-08 | Correlation names in the FROM clause |
|
|
|
| E061 | Basic predicates and search conditions |
|
|
|
| E061-01 | Comparison predicate |
|
| |
| E061-02 | BETWEEN predicate |
|
|
|
| E061-03 | IN predicate with list of values |
|
|
|
| E061-04 | LIKE predicate |
|
|
|
| E061-05 | LIKE predicate ESCAPE clause |
|
|
|
| E061-06 | NULL predicate |
|
|
|
| E061-07 | Quantified comparison predicate |
|
|
|
| E061-08 | EXISTS predicate |
| Partial |
|
| E061-09 | Subqueries in comparison predicate |
|
|
|
| E061-11 | Subqueries in IN predicate |
|
|
|
| E061-12 | Subqueries in quantified comparison predicate |
|
|
|
| E061-13 | Correlated subqueries |
|
|
|
| E061-14 | Search condition |
|
|
|
| E071 | Basic query expressions |
|
| |
| E071-01 | UNION DISTINCT table operator |
|
| |
| E071-02 | UNION ALL table operator |
|
|
|
| E071-03 | EXCEPT DISTINCT table operator |
| Partial | |
| E071-05 | Columns combined via table operators need not have exactly the same data type |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| E071-06 | Table operators in subqueries |
|
|
|
| E081 | Basic Privileges |
|
|
|
| E081-01 | SELECT privilege |
|
|
|
| E081-02 | DELETE privilege |
|
|
|
| E081-03 | INSERT privilege at the table level |
|
|
|
| E081-04 | UPDATE privilege at the table level |
|
|
|
| E081-06 | REFERENCES privilege at the table level |
|
|
|
| E081-08 | WITH GRANT OPTION |
|
|
|
| E081-05 | UPDATE privilege at the column level |
|
|
|
| E081-07 | REFERENCES privilege at the column level |
|
|
|
| E091 | Set functions |
|
|
|
| E091-01 | AVG |
|
|
|
| E091-02 | COUNT |
|
|
|
| E091-03 | MAX |
|
|
|
| E091-04 | MIN |
|
|
|
| E091-05 | SUM |
|
|
|
| E091-06 | ALL quantifier |
|
|
|
| E091-07 | DISTINCT quantifier |
|
|
|
| E101 | Basic data manipulation |
|
|
|
| E101-01 | INSERT statement |
|
|
|
| E101-03 | Searched UPDATE statement |
|
|
|
| E101-04 | Searched DELETE statement |
|
|
|
| E111 | Single row SELECT statement |
|
|
|
| E121 | Basic cursor support |
|
|
|
| E121-01 | DECLARE CURSOR |
|
|
|
| E121-02 | ORDER BY columns need not be in select list |
|
|
|
| E121-03 | Value expressions in ORDER BY clause |
|
| 3 |
| E121-04 | OPEN statement |
|
|
|
| E121-06 | Positioned UPDATE statement |
|
|
|
| E121-07 | Positioned DELETE statement |
|
|
|
| E121-08 | CLOSE statement |
|
|
|
| E121-10 | FETCH statement implicit NEXT |
| ||
| Identifier | Description | IBM | Oracle | Microsoft |
| E121-17 | WITH HOLD cursors |
|
| |
| E131 | Null value support (nulls in lieu of values) |
|
|
|
| E141 | Basic integrity constraints |
|
|
|
| E141-01 | NOT NULL constraints |
|
|
|
| E141-02 | UNIQUE constraints of NOT NULL columns |
|
|
|
| E141-03 | PRIMARY KEY constraints |
|
|
|
| E141-04 | Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential update action |
|
|
|
| E141-06 | CHECK constraints |
|
|
|
| E141-07 | Column defaults |
|
|
|
| E141-08 | NOT NULL inferred on PRIMARY KEY |
|
|
|
| E141-10 | Names in a foreign key can be specified in any order | |||
| E151 | Transaction support |
|
|
|
| E151-01 | COMMIT statement |
|
|
|
| E151-02 | ROLLBACK statement |
|
|
|
| E152 | Basic SET TRANSACTION statement |
|
|
|
| E152-01 | SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause |
|
|
|
| E152-02 | SET TRANSACTION statement: READ ONLY and READ WRITE clauses |
|
|
|
| E153 | Updateable queries with subqueries |
|
|
|
| E161 | SQL comments using leading double minus |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| E171 | SQLSTATE support |
|
|
|
| E182 | Module language |
| Partial | |
| F021 | Basic information schema |
|
|
|
| F021-01 | COLUMNS view |
|
|
|
| F021-02 | TABLES view |
|
|
|
| F021-03 | VIEWS view |
|
|
|
| F021-04 | TABLE_CONSTRAINTS view |
|
|
|
| F021-05 | REFERENTIAL_CONSTRAINTS view |
|
|
|
| F021-06 | CHECK_CONSTRAINTS view |
|
|
|
| F031 | Basic schema manipulation |
|
|
|
| F031-01 | CREATE TABLE statement to create persistent base tables |
|
|
|
| F031-02 | CREATE VIEW statement |
|
|
|
| F031-03 | GRANT statement |
|
|
|
| F031-04 | ALTER TABLE statement COLUMN clause |
|
|
|
| F031-13 | DROP TABLE statement clause |
|
| |
| F031-16 | DROP VIEW statement RESTRICT clause |
| ||
| F031-19 | REVOKE statement RESTRICT clause |
| ||
| F033 | ALTER TABLE statement: DROP COLUMN clause |
|
|
|
| F041 | Basic joined table |
|
|
|
| F041-01 | Inner join (but not necessarily the INNER keyword) |
|
|
|
| F041-02 | INNER keyword |
|
|
|
| F041-03 | LEFT OUTER JOIN |
|
|
|
| F041-04 | RIGHT OUTER JOIN |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| F041-05 | Outer joins can be nested |
|
|
|
| F041-07 | The inner table in a left or right outer join can also be used in an inner join |
|
|
|
| F041-08 | All comparison operators are supported (rather than just =) |
|
|
|
| F051 | Basic date and time |
|
|
|
| F051-01 | DATE data type (including support of DATE literal) |
|
|
|
| F051-02 | TIME data type (including support of TIME literal) with fractional seconds precision of at least 0 |
|
|
|
| F051-03 | TIMESTAMP data type (including support of TIMESTAMP literal) with fractional seconds precision of at least 0 and 6 |
|
|
|
| F051-04 | Comparison predicate on DATE TIMESTAMP data types |
|
|
|
| F051-05 | Explicit CAST between datetime types and character types |
|
| |
| F051-06 | CURRENT_DATE |
|
|
|
| F051-07 | LOCALTIME |
|
|
|
| F051-08 | LOCALTIMESTAMP |
|
|
|
| F081 | UNION and EXCEPT in views |
|
| Partial |
| F111 | Isolation levels other than SERIALIZABLE |
|
|
|
| F111-01 | READ UNCOMMITTED isolation level |
|
|
|
| F111-02 | READ COMMITTED isolation level |
|
|
|
| F111-03 | REPEATABLE READ isolation level |
|
|
|
| F121 | Basic diagnostics management |
|
|
|
| F121-01 | GET DIAGNOSTICS statement | |||
| Identifier | Description | IBM | Oracle | Microsoft |
| F121-02 | SET TRANSACTION statement: DIAGNOSTICS SIZE clause | |||
| F131 | Grouped operations |
|
|
|
| F131-01 | WHERE, GROUP BY and HAVING clauses supported in queries with grouped views |
|
| |
| F131-02 | Multiple tables supported in queries with grouped views |
|
|
|
| F131-03 | Set functions supported in queries with grouped views |
|
|
|
| F131-04 | Subqueries with GROUP BY and HAVING clauses and grouped views |
|
|
|
| F131-05 | Single row SELECT with GROUP BY and HAVING clauses and grouped views |
|
|
|
| F201 | CAST function |
|
|
|
| F221 | Explicit defaults |
|
|
|
| F231 | Privilege Tables |
|
|
|
| F231-01 | TABLE_PRIVILEGES view |
|
|
|
| F231-02 | COLUMN_PRIVILEGES view |
|
|
|
| F231-03 | USAGE_PRIVILEGES view |
|
|
|
| F261 | CASE expression |
|
|
|
| F261-01 | Simple CASE |
|
|
|
| F261-02 | Searched CASE |
| ||
| F261-03 | NULLIF |
|
|
|
| F261-04 | COALESCE |
|
| |
| F311 | Schema definition statement |
|
|
|
| F311-01 | CREATE SCHEMA |
|
|
|
| F311-02 | CREATE TABLE for persistent base tables |
|
|
|
| F311-03 | CREATE VIEW |
|
|
|
| F311-04 | CREATE VIEW: WITH CHECK OPTION |
|
|
|
| F311-05 | GRANT statement |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| F471 | Scalar subquery values |
|
|
|
| F481 | Expanded NULL predicate |
|
|
|
| F032 | CASCADE drop behavior |
|
|
|
| F034 | Extended REVOKE statement |
|
|
|
| F034-01 | REVOKE statement performed by other than the owner of a schema object |
|
|
|
| F052 | Intervals and datetime arithmetic |
|
|
|
| F171 | Multiple schemas per user |
|
|
|
| F191 | Referential delete actions |
|
|
|
| F222 | INSERT statement: DEFAULT VALUES clause |
|
|
|
| F251 | Domain support | |||
| F281 | LIKE enhancements |
|
|
|
| F291 | UNIQUE predicate |
|
|
|
| F301 | CORRESPONDING in query expressions |
| ||
| F302 | INTERSECT table operator |
|
| |
| F302-01 | INTERSECT DISTINCT table operator |
|
| |
| F302-02 | INTERSECT ALL table operator |
|
| |
| F304 | EXCEPT ALL table operator |
|
| |
| F321 | User authorization |
|
|
|
| F341 | Usage tables |
|
|
|
| F361 | Subprogram support |
|
|
|
| F381-01 | ALTER TABLE statement: ALTER COLUMN clause |
|
|
|
| F381-02 | ALTER TABLE statement: ADD CONSTRAINT clause |
|
|
|
| F381-03 | ALTER TABLE statement: DROP CONSTRAINT clause |
|
|
|
| F391 | Long identifiers |
|
|
|
| F401 | Extended joined table |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| F401-01 | NATURAL JOIN |
|
|
|
| F401-02 | FULL OUTER JOIN |
|
|
|
| F401-03 | UNION JOIN |
|
|
|
| F401-04 | CROSS JOIN |
|
|
|
| F411 | Time zone specification |
|
|
|
| F421 | National character |
|
|
|
| F431 | Read-only scrollable cursors |
|
|
|
| F431-01 | FETCH with explicit NEXT |
|
| |
| F431-02 | FETCH FIRST |
|
| |
| F431-03 | FETCH LAST |
|
| |
| F431-04 | FETCH PRIOR |
|
| |
| F431-05 | FETCH ABSOLUTE |
|
| |
| F431-06 | FETCH RELATIVE |
|
| |
| F451 | Character set definition |
|
|
|
| F461 | Named character sets |
|
|
|
| F491 | Constraint management |
|
|
|
| F501-01 | SQL_FEATURES view | |||
| F501-02 | SQL_SIZING view | |||
| F501-03 | SQL_LANGUAGES view | |||
| F502 | Enhanced documentation tables |
|
|
|
| F502-01 | SQL_SIZING_PROFILES view | |||
| F502-02 | SQL_IMPLEMENTATION_INFO view | |||
| F502-03 | SQL_PACKAGES view |
|
| |
| F511 | BIT data type |
|
|
|
| F521 | Assertions |
|
|
|
| F531 | Temporary tables |
|
|
|
| F555 | Enhanced seconds precision |
|
|
|
| F561 | Full value expressions |
|
|
|
| F571 | Truth value tests |
|
|
|
| F591 | Derived tables |
|
| |
| F641 | Row and table constructors |
|
| |
| F661 | Simple tables |
|
|
|
| F671 | Subqueries in CHECK |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| F691 | Collation and translation |
|
|
|
| F701 | Referential update actions |
|
|
|
| F711 | ALTER domain | |||
| F721 | Deferrable constraints |
|
|
|
| F731 | INSERT column privileges |
|
|
|
| F751 | View CHECK enhancements |
|
|
|
| F761 | Session management |
|
|
|
| F771 | Connection management |
|
|
|
| F781 | Self-referencing operations |
|
|
|
| F791 | Insensitive cursors |
|
|
|
| F801 | Full set function |
|
|
|
| F811 | Extended flagging | |||
| F812 | Basic flagging |
|
|
|
| F813 | Extended flagging for "Core SQL Flagging" and "Catalog Lookup" only |
|
| |
| F821 | Local table references |
|
|
|
| F831 | Full cursor update |
| ||
| F831-01 | Updateable scrollable cursors |
|
| |
| S011 | Distinct data types |
|
| |
| S011-01 | USER_DEFINED_TYPES view |
|
|
|
| S023 | Basic structured types |
|
|
|
| S024 | Enhanced structured types |
|
| |
| S041 | Basic reference types |
|
|
|
| S051 | Create table of type |
|
|
|
| S071 | SQL paths in function and type name resolution |
|
|
|
| S081 | Subtables |
|
| |
| S091 | Basic array support |
|
| Partial |
| Identifier | Description | IBM | Oracle | Microsoft |
| S091-01 | Arrays of built-in data types |
|
| |
| S091-02 | Arrays of distinct types |
| ||
| S091-03 | Array expressions | |||
| S092 | Arrays of user-defined types |
|
| |
| S094 | Arrays of reference types |
| ||
| S111 | ONLY in query expressions |
|
|
|
| S161 | Subtype treatment |
|
| |
| S201 | SQL routines on arrays |
|
| |
| S201-01 | Array parameters |
| ||
| S201-02 | Array as result type of functions |
|
| |
| S211 | User-defined cast functions |
|
|
|
| S232 | Array locators |
|
| |
| S241 | Transform functions |
|
|
|
| S251 | User-defined orderings | |||
| S261 | Specific type method |
|
| |
| T011 | Timestamp in INFORMATION_SCHEMA |
|
| |
| T031 | BOOLEAN data type | |||
| T041-01 | BLOB data type |
|
| |
| T041-02 | CLOB data type |
|
| |
| T051 | Row types |
|
| |
| T111 | Updateable joins, unions, and columns |
|
|
|
| T121 | WITH (excluding RECURSIVE) in query expression |
|
|
|
| T131 | Recursive query |
|
|
|
| T171 | LIKE clause in table definition | |||
| T271 | Savepoints |
|
|
|
| T281 | SELECT privilege with column granularity |
|
|
|
| T301 | Functional Dependencies |
|
|
|
| T141 | SIMILAR predicate | |||
| Identifier | Description | IBM | Oracle | Microsoft |
| T151 | DISTINCT predicate |
|
|
|
| T191 | Referential action RESTRICT |
| ||
| T201 | Comparable data types for referential constraints |
|
|
|
| T211 | Basic trigger capability |
|
|
|
| T211-01 | Triggers activated on UPDATE, INSERT, or DELETE of one base table |
|
|
|
| T211-02 | BEFORE triggers |
|
|
|
| T211-03 | AFTER triggers |
|
|
|
| T211-04 | FOR EACH ROW triggers |
|
|
|
| T211-05 | Ability to specify a search condition that must be true before the trigger is invoked |
|
| |
| T211-06 | Support for run-time rules for the interaction of triggers and constraints |
| ||
| T211-07 | TRIGGER privilege | |||
| T211-08 | Multiple triggers for the same the event are executed in the order in which they were created |
|
|
|
| T212 | Enhanced trigger capability |
| ||
| T231 | SENSITIVE cursors |
|
|
|
| T241 | START TRANSACTION statement |
| ||
| T251 | SET TRANSACTION statement: LOCAL option |
|
|
|
| T312 | OVERLAY function | |||
| T321 | Basic SQL-invoked routines |
|
|
|
| T321-01 | User-defined functions with no overloading |
|
|
|
| T321-02 | User-Defined procedures with no overloading |
|
|
|
| T321-03 | Function invocation |
|
|
|
| T321-04 | CALL statement |
|
| |
| T321-06 | ROUTINES view |
|
|
|
| T321-07 | PARAMETERS view |
|
|
|
| Identifier | Description | IBM | Oracle | Microsoft |
| T321-05 | RETURN statement |
| Partial |
|
| T322 | Overloading of SQL-invoked functions and procedures |
| Partial | |
| T323 | Explicit security for external routines |
|
|
|
| T331 | Basic roles |
|
|
|
| T332 | Extended roles |
|
|
|
| T351 | Bracketed SQL comments (/*...*/ comments) |
|
|
|
| T401 | INSERT into a cursor |
| ||
| T411 | UPDATE statement: SET ROW option |
|
|
|
| T431 | CUBE and ROLLUP operations |
|
|
|
| T471 | Result sets return value |
|
|
|
| T441 | ABS and MOD functions |
|
| Partial |
| T461 | Symmetric BETWEEN predicate |
|
|
|
| T501 | Enhanced EXISTS predicate |
|
| |
| T511 | Transaction counts |
|
|
|
| T541 | Updateable table references |
|
|
|
| T551 | OPTIONAL keyword for default syntax | |||
| T561 | Holdable locators |
|
| |
| T571 | Array-returning external SQL-invoked functions |
|
|
|
| T581 | Regular expression substring function |
|
| |
| T591 | UNIQUE constraints of possibly null columns |
|
| |
| T601 | Local cursor references |
|
|
|
|
|
EAN: 2147483647
Pages: 208
- ERP System Acquisition: A Process Model and Results From an Austrian Survey
- The Second Wave ERP Market: An Australian Viewpoint
- Context Management of ERP Processes in Virtual Communities
- Distributed Data Warehouse for Geo-spatial Services
- Development of Interactive Web Sites to Enhance Police/Community Relations