Get a free copy of our DBMS
Order our book
Book Review of UNDERSTANDING THE NEW SQL
Ocelot's now-defunct newsletter, THE CAT'S MEOW, was mostly about
Ocelot, but also had lots of expert information about the broader SQL world.
The following article is an excerpt from THE CAT'S MEOW's occasional
book review section.
(Copyright© 1998 by Ocelot Computer Services Inc.)
... Extract begins. Title: "Peter's Column: Ask the Swami" ...
Jim Melton and Alan R. Simon
UNDERSTANDING THE NEW SQL: A COMPLETE GUIDE
This book's getting old, but no older than Date
and Darwen's A Guide to the SQL Standard.
Besides, that's my fault -- I should have read
and reviewed this years ago. It's an important
book, and a possible contender for the title of
"the SQL-92 reference".
MINOR LAPSES. Typos in the text are
frequent. Typos in the actual SQL statements
(the only kind that really matter) are rare; all I
noticed was a missing comma on page 80 and a
missing quote mark on page 81. Style of
examples was occasionally inconsistent, for
example, keywords are not always in upper case
and statements don't always end with a
semicolon. The examples sometimes show
features which aren't explained yet, for instance
they use a literal 'All the president''s men'
before they get around to describing what ''
means within a CHAR literal. Sometimes the
explanations are somewhat sloppy, e.g. (p.128)
"the query ["SELECT title FROM movie_titles
WHERE title > 'Q';"] will return all rows
where the title begins with a character greater
than a capital Q: the letters R through Z and
anything else of a greater value in that collating
sequence ..." (What about Queen?) There are
three minor errors in this two-sentence quote:
(p. 30) "SQL-92 times are specified with a
relationship to UCT, or universal coordinated
time (previously called GMT, or Greenwich
mean time). Different places on the earth
experience different sun times, or sidereal times,
because of their relative positions to the sun."
Did you find all three? Well if you didn't it's no
big deal. In fact none of these slips are a big
deal. Still, editors get a salary.
OBSOLESCENCE. There is no criticism in
pointing out that the book contains no recent
information about the CLI, the corrigenda, or
SQL3 -- the book came out in 1993, and I only
note that those are areas you'll have to get some
supplementary material on. Jim Melton told an
amazon.com interviewer that he's working on a
new book for PSM (persistent stored modules).
OMISSIONS. Once again, we have a book with
the word "complete" in the title; so it's notable
that I found nothing about ALTER DOMAIN,
ALTER TABLE ... ADD CONSTRAINT ..., or
DROP TRANSLATION. I didn't look hard for
other omissions. It's enough to realize that the
word "complete" means little.
FALSEHOODS. Now here, I must get severe.
I expect that Jim Melton can look up a point,
and has read his own book. Obviously he must
know that these statements are false:
p. 81: "View definitions can, however, be
DROPped, as in DROP VIEW MOVIES_ON_SALE ;"
No. That is illegal syntax in SQL-92 -- the
Standard requires you to specify either
CASCADE or RESTRICT for the DROP. The
<drop view statement> ::=
DROP VIEW <table name> <drop behavior>
<drop behavior> ::=
RESTRICT | CASCADE"
It's interesting to note that an early (July, 1990)
draft of the Standard would have allowed Melton's example.
p. 99: "SQL has a rule that says if any column
of the select list is (or uses) a set function, then
all of them have to be (or use) set functions"
[Note: the phrase "set function" means "aggregate function"]
No. This is too simplistic, although correct for
the example. SQL-92 defines a <select list> to
contain a series of <value expression>s. The
s may be <column
reference>s, <literal>s, <parameter>s,
<set function specification>s, etc. -- in this
case, providing each <value expression>
evaluates to a single-valued result. The Standard
"If T is a grouped table, then in each <value
expression>, each <column reference> that
references a column of T shall reference a
grouping column or be specified within a <set
function specification>. If T is not a grouped
table and any <value expression> contains a
<set function specification> that contains a
reference to a column of T or any <value
expression> directly contains a <set function
specification> that does not contain an outer
reference, then in each <value expression>,
each <column reference> that references a
column of T shall be specified within a <set
p.102: "the result of the SUM function must be
within the range of the source data type"
No. The result of SUM(smallint_column) can be
INTEGER, or indeed, any exact numeric data
type. The Standard says:
"If SUM is specified and [column data type] is
exact numeric with scale S, then the data type
of the result is exact numeric with
implementation-defined precision and scale S."
p.106: "A couple of quick notes regarding
UPPER and LOWER: if you use either of these
functions on an accented character (example: the
German o-umlaut), the case conversion would be
applied (UPPER(o-umlaut -- Ö) would become
an O-umlaut -- ö)."
No. UPPER and LOWER are specifically
defined to operate on "simple Latin letters"
only. The Standard says:
"If UPPER is specified, then the result ... is a
copy ... in which every <simple Latin lower
case letter> ... that has a corresponding
<simple Latin upper case letter> ... is
replaced by that <simple Latin upper case
letter>. If LOWER is specified, then the result
... is a copy ... in which every <simple Latin
upper case letter> that has a corresponding
<simple Latin lower case letter> ... is
replaced by that <simple Latin lower case
<simple Latin upper case letter> ::=
A | B | C | D | E | F | G | H | I | J
| K | L | M | N | O | P | Q | R | S
| T | U | V | W | X | Y | Z
<simple Latin lower case letter> ::=
a | b | c | d | e | f | g | h | i | j | k
| l | m | n | o | p | q | r | s | t | u |
v | w | x | y | z"
p.108: "if the operation is division, the result
will have some approximate numeric type
regardless of the data types of the operands"
No. If the operands are exact numerics, the
result is exact numeric. The Standard says:
"If the data type of both operands of a dyadic
arithmetic operator is exact numeric, then the
data type of the result is exact numeric .. the
precision and scale of the result of division is
p.264: "One tricky thing about ASC and DESC:
They are "sticky" when you specify them, but
the initial default is ASC. That is, whenever you
specify either ASC or DESC on some column in
an ORDER BY clause, all columns specified
after that will have the same sort order until you
use the other one. Therefore, if you specify
ORDER BY markup DESC, current_sales_price
you will get BOTH columns sorted in
No. The default is ASC but each key is sorted
separately. The Standard says:
"Each <sort specification> specifies the sort
direction for the corresponding <sort key>.
If DESC is not specified in the i-th <sort
specification>, then the sort direction is
ascending. Otherwise the sort direction is
p.279: "CREATE TABLE dummy (dummy_col
INTEGER, CHECK ((SELECT salary FROM
employees WHERE title = 'President')
BETWEEN :x and :y)) ... SQL-92 requires that
you have the SELECT privilege on the
No. First, the SQL statement is illegal because
it contains parameters in a CHECK clause.
Second, even if it were legal, the applicable
privilege would be not SELECT, but
REFERENCES. The Standard says:
<check constraint definition> ::=
CHECK ( <search condition> )
The <search condition> shall not contain a
<parameter name>, a <embedded variable
name>, or a <dynamic parameter
If a <column name> is contained in the
<search condition>, then the applicable
privileges shall include REFERENCES for
each <column name> of the table ...
contained in the <search condition>.
Otherwise, the applicable privileges shall include
REFERENCES for at least one column of the
p.283: "If you then execute REVOKE SELECT
ON movie_titles FROM USER2 ; you'll find
that USER2 still is able to SELECT data from
No. As with the DROP VIEW example, this is
illegal syntax. The Standard requires you to
specify either CASCADE or RESTRICT for the
REVOKE. The Standard says:
<revoke statement> ::=
REVOKE [GRANT OPTION FOR]
<privileges> ON <object name>
<drop behavior> ::=
RESTRICT | CASCADE"
I only skimmed the chapters on embedded SQL
and the appendices, so the above list probably
shows only about 50% of what I would call
Falsehoods. For fair comparison, remember that
SQL For Dummies has many more. (Curiously,
some of the howlers are the same in both
NULLs. C. J. Date thinks NULLs are
nincompoopish, so his book defers explaining
them until a late chapter. Thus it's hard to get
an immediate idea of what's going on (who
knows, maybe that's what they wanted to
happen). Melton is the SQL defender: he has to
take the pro-NULL side, although he fairly
states that "some notable database personalities
have strongly urged the SQL standards
committees to abandon the notion of null values
in favour of default values". Anyway,
Melton+Simon integrate the discussion of
NULLs in the general discussion, and don't
bother to add footnotes adding "[*Yeck! Pfui!]"
every time. The result is easier to follow.
Towards the end, though, they do switch to
using defaults instead of NULLs, so I couldn't
say that they win the debate.
CLARITY. The fact is that we're all tired of
supplier/parts/cities tables, so I suppose it's nice
that Melton+Simon use a different model (I
didn't try all the examples like Trudy did with
Joe Celko's book, so there's no knowing
whether they all work or not). In the initial part
of the book they use "railroad diagrams" rather
than BNF for the formal syntax description; I
am more used to BNF so I didn't like this, but
doubtless others would feel differently. The
discussion of query expressions is hard sledding,
and I will condemn Melton+Simon for that as
soon as I find someone who makes query
expressions seem easy. They address the
readership as "you"; they try a few feeble jokes;
they try to explain the committee's decisions
rather than critique them; they tackle subjects
that everyone else avoids (such as multi-table
outer joins and the full effect of REVOKE with
examples thereof). There is no glossary.
And now, we come to the place we come to in
every book review, where the question must be
asked: can the book go head-to-head against
Date+Darwen's? So far we have examined
several potential challengers, but none were
really in the Heavyweight Division. This book is
serious. It is a contender.
Pro: The Melton+Simon book has more
examples, which accounts for its slightly greater
bulk. Particularly, the description of the actions
of the precompiler is much thicker. I have
already mentioned some things (see above:
"Clarity") which make Melton+Simon a more
pleasant book to just pick up and read.
Con: The Date+Darwen book has only a few
minor errors, and certainly nothing in the
"falsehood" category like the ones I've described
above. Date seems to understand dates and times
better, and his description of character sets /
collations / translations is about 80% complete
(I would say that the Melton+Simon book is
about 20% complete).
The winner and still champion: Date+Darwen.
Now I'm really looking forward to the next
round, when the SQL3 books come out. By the
way, the Melton+Simon book predicted that
would happen around 1996, but my prediction is
2001. Meantime, do look for
UNDERSTANDING THE NEW SQL: A
COMPLETE GUIDE. It complements
Date+Darwen, and it's good to have an upbeat
take on the subject.
... end of book-review extract from THE CAT'S MEOW ...
Editor's Note: Shortly after this review appeared, Mr Melton wrote a
kind letter acknowledging some of the points, and pointing out that
the reviewer was working with an early printing, and that some of these
matters have already been fixed.
Copyright (c) 1998-2002 by
Ocelot Computer Services Inc.