SQL Bible [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

SQL Bible [Electronic resources] - نسخه متنی

Alex Kriegel

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید







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.














































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































































Table J-1: SQL99-Defined Features across RDBMS


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








/ 207