Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!usc!samsung!uakari.primate.wisc.edu!sdd.hp.com!elroy.jpl.nasa.gov!decwrl!sgi!llustig!objy!server!bobm
From: bobm@server.Berkeley.EDU (Bob Muller)
Newsgroups: comp.databases
Subject: Re: ANSI SQL Levels
Keywords: SQL, ANSI, query language
Message-ID: <1991Apr24.182915.25040@objy.com>
Date: 24 Apr 91 18:29:15 GMT
References: <DAVIDS.91Apr22163243@nimitz.uunet!texsun!gdfwc3>
Sender: bobm@server (Bob Muller)
Distribution: comp
Organization: Objectivity, Inc.
Lines: 69

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