gdfwc3 (David Reed Smith) (04/23/91)
Can anyone summarize the differences between ANSI SQL level 1 and level 2? Thank you. -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ David Smith davids@gdfwc3, General Dynamics, gdfwc3!davids@central.sun.com, Fort Worth Division or texsun!gdfwc3!davids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
av@kielo.uta.fi (Arto V. Viitanen) (04/24/91)
>>>>> On 22 Apr 91 21:32:43 GMT, davids@uunet!texsun!gdfwc3 (David Reed Smith) said:
David> Can anyone summarize the differences between ANSI SQL level 1 and
David> level 2? Thank you.
According to Rick F. van der Lans, "The SQL Standard, A Complete Reference",
Prentice Hall (ISBN 0-13-840059-8), pages 97-98:
Two levels of the SQL standard have been defined. ... Level 1 is a subset of
level 2. In fact, level 2 encompasses level 1. The following definitions do
not occur in level 1:
* The length of identifiers for objects such as tables and views is
restricted to twelve positions.
* In definition of a table name it is not possible to give an
authorization identifier
* The system variable USER, the indicator parameter and the indicator
variable dissappear from the definition of a value specification.
* The word 'ALL' is missing from the set function specification. In
level 1 it is automatically assumed that if the word 'DISTINCT' is
left out, duplicate rows may not be deleted.
* AVG, MAX, MIN and SUM may not be used with the distinct set function;
only the COUNT set function is permitted
* The comparison operator <> dissappears. To formulate the search
condition 'a <> b' on writes 'NOT (a = b)'
* The escape character is not available with the LIKE operator
* As well, with the LIKE operator, 'a NOT LIKE b' is not supported.
This search is rewritten as 'NOT (a LIKE b)'
* EXISTS as a predicate is removed.
* The way in which NULL values are grouped is left up to the supplier
to decide
* There are no schemas
* It is not possible to specify a unique constraint definition or
UNIQUE in the CREATE TABLE statement.
* The data types REAL, DOUBLE PRECISION and NUMERIC dissappear
* NOT NULL _must_ be specified in every column definition in a CREATE
TABLE statement.
* The WITH CHECK OPTION in the CREATE VIEW statement is omitted.
* The WITH GRANT OPTION in the GRANT statement dissappears.
* The only parameters which may be passed in a procedure are those with
a data type of CHARACTER
* In SQL standard the SQLCODE 100 means that no rows have been found.
In level 1 the SQLCODE variable for the equivalent situation is
decided by the supplier.
* It is not possible to use sequence numbers to indicate sorting in the
ORDER BY clause
* The possibility of specifying ASC in the ORDER BY clause dissappers.
The omission of the word 'DESC' automatically implies ascending
order.
* It is not possible to combine results of SELECT statements with
UNION.
* There is no facility for placing query specification in INSERT
statements.
* The search condition 'CURRENT OF ...' dissappears from the DELETE and
UPDATE statements.
* The definition of a transaction beomes: 'A transaction is a sequence
of operations, including database operations, that is atomic with
respect to recovery.'
--
Arto V. Viitanen email: av@kielo.uta.fi
University Of Tampere, av@ohdake.cs.uta.fi
Finlandbobm@server.Berkeley.EDU (Bob Muller) (04/25/91)
In article <DAVIDS.91Apr22163243@nimitz.uunet!texsun!gdfwc3>, davids@uunet!texsun!gdfwc3 (David Reed Smith) writes: |> Can anyone summarize the differences between ANSI SQL level 1 and |> level 2? Thank you. Taken from ANSI X3.135-1989, the current Database Language--SQL with Integrity Enhancement Standard, in summary form, from section 9, p. 99-101. "Level 2 is the complete SQL database language excluding the integrity enhancement feature. Level 1 is the subset of Level 2 that objeys the following additional rules." The integrity enhancement feature is the default clause, the column constraint syntax other than NOT NULL and NOT NULL UNIQUE, the table constraint definition except for UNIQUE, and "the <action> REFERENCES [(<grant column list>)]". 1. No transaction semantics, just an atomic sequence of transactions. 2. 12 characters or less for identifiers 3. No authorization identifiers in table names. 4. No indicators in parameters or variables. 5. No outer references in column specifications. 6. No ALL in set functions 7. No AVG, MAX, MIN, or SUM distinct set functions. 8. No "<>" operator, just "NOT A=B". 9. No ESCAPE or NOT in like predicates. 10. No exists predicates. 11. "The grouping of rows in which the value of one or more grouping column is null is implementor defined." 12. "The determination of whether a <query specification> is updatable or read-only is implementor defined." 13. No "schema", meaning the SCHEMA keyword and authorization info. 14. No unique constraint definition in table definition. 15. No REAL, DOUBLE PRECISION, or NUMERIC column data types. All columns NOT NULL. No UNIQUE. 16. No WITH CHECK OPTION in view defs. 17. No WITH GRANT OPTION in privilege defs. 18. Restrictions in <procedure>: data types all CHARACTER; SQLCODE arbitrary, not 100; no cancelation semantics. 19. Cursor sort specs have no integer specs or ASC (just use default for ASC). No UNION in query expression. 20. No query spec in insert statement. 21. No positioned update and delete. You should also note that there is a SQL2 standard that is wending its way through ANSI and ISO in draft form which is MUCH different from SQL1 with or without the integrity enhancement option (500 pages instead of 120 in the current standard!). Most vendors seem to be talking about SQL2 compliance now, if not delivering. --Bob Muller
jfr@locus.com (Jon Rosen) (04/25/91)
In article <AV.91Apr24083942@kielo.uta.fi> av@uta.fi (Arto V. Viitanen) writes: >>>>>> On 22 Apr 91 21:32:43 GMT, davids@uunet!texsun!gdfwc3 (David Reed Smith) said: > >David> Can anyone summarize the differences between ANSI SQL level 1 and >David> level 2? Thank you. > >According to Rick F. van der Lans, "The SQL Standard, A Complete Reference", >Prentice Hall (ISBN 0-13-840059-8), pages 97-98: > >Two levels of the SQL standard have been defined. ... Level 1 is a subset of >level 2. In fact, level 2 encompasses level 1. The following definitions do >not occur in level 1: > ><all detail stuff deleted> Just to remind everyone... ANSI SQL Level 2 as described in Arto's post is very distinct from ANSI SQL2 which is a completely new draft standard that has not been approved as of yet (Level 1 and Level 2 of ANSI SQL are already approved, along with the Referential Integrity addendum). ANSI SQL2 adds the proverbial kitchen sink to SQL, including triggers, CASE constructs, enhanced orthogonal use of queries (in just about any case, any query expression can be used where a table name could otherwise be used), additional functions, DATE/TIME/INTERVAL definitions, extended referential integrity, domains, and on and on and on... Jon Rosen ========================================================= "Another birthday? Well, don't worry about getting old until you can't make sense out of the simplest things... ... isn't it?" -- from my favorite 40th birthday card =========================================================
gupta@cai.com (04/27/91)
In article <1991Apr24.182915.25040@objy.com>, bobm@server.Berkeley.EDU (Bob Muller) writes: > In article <DAVIDS.91Apr22163243@nimitz.uunet!texsun!gdfwc3>, davids@uunet!texsun!gdfwc3 (David Reed Smith) writes: > |> Can anyone summarize the differences between ANSI SQL level 1 and > |> level 2? Thank you. > > Taken from ANSI X3.135-1989, the current Database Language--SQL with Integrity > Enhancement Standard, in summary form, from section 9, p. 99-101. > > "Level 2 is the complete SQL database language excluding the integrity enhancement > feature. Level 1 is the subset of Level 2 that objeys the following additional > rules." > > [Rules deleted] > > You should also note that there is a SQL2 standard that is wending its way > through ANSI and ISO in draft form which is MUCH different from SQL1 with or > without the integrity enhancement option (500 pages instead of 120 in the > current standard!). Most vendors seem to be talking about SQL2 compliance > now, if not delivering. > > --Bob Muller Talking is very different from delivering. Most of the DBMSs frequently discussed in this group (Ingres, Oracle, Sybase, etc) do NOT even conform to SQL1 to what the ANSI standard calls "Full SQL conformance to level 2" ("Full SQL" implies DML and DDL) along with "the integrity enhancement feature". Regarding SQL2, one can only guess what the claims mean. Yogesh Gupta