Path: utzoo!utgpu!news-server.csri.toronto.edu!rpi!zaphod.mps.ohio-state.edu!think.com!mintaka!bloom-beacon!eru!hagbard!sunic!news.funet.fi!uta!kielo!av
From: av@kielo.uta.fi (Arto V. Viitanen)
Newsgroups: comp.databases
Subject: Re: ANSI SQL Levels
Message-ID: <AV.91Apr24083942@kielo.uta.fi>
Date: 24 Apr 91 05:39:42 GMT
References: <DAVIDS.91Apr22163243@nimitz.uunet!texsun!gdfwc3>
Sender: news@uta.fi
Reply-To: av@uta.fi (Arto V. Viitanen)
Distribution: comp
Organization: University of Tampere, Finland
Lines: 86
Nntp-Posting-Host: uta.fi
In-reply-to: davids@uunet!texsun!gdfwc3's message of 22 Apr 91 21:32:43 GMT

>>>>> 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
Finland