SQL Syntax References

A Handy SQL Reference

Review: Having a good cross reference for SQL among the major databases is essential
Feature: Critical attributes are good examples and light enough to carry around

One of the things I have discovered while doing recent assignments is that interfacing with a number of different databases is becoming common place. I also have discovered that despite rising promises by the database vendors to implement ANSI SQL standards, like the problems in baseball without a strong commissioner, departures from the ANSI SQL standard is the rule not the exception. It occurs often enough to keep DBAs and programmers gainfully employed if not downright harassed (now who and where those DBAs and programmers are employed is another telling, but separate question).

The result is that I have been in the market for a good cross database reference to supplement my SQL database theory books from Date, Gray, Teorey and others. I need a book packed with reference facts on the major databases - maybe Oracle, VSAM, and DB2. Or Access, MySQL, SQL Server and Adaptive Server. Or Oracle, Pointbase and MySQL.

Okay - you get my points. First, more and more projects require interfacing with several databases either one-time or on an ongoing basis. Second, there are certain databases that are constantly recurring. My most frequently encountered databases are in alphabetical order: Access, DB2, MySQL, Oracle, Text/CSV. Of course the latter often been shipped from some other datasource/database over which I have no control or access to including how exactly the Text/CSV file is formatted. By the way, from this list readers can see why I chose to cover DB2, MySQL, and Oracle in my review of nearly free databases.

nut So one can easily see a market here. A SQL reference book that covers the major databases (my must haves are DB2, MySQL and Oracle) and provides good syntax coverage of the DML commands - Select, Delete, Insert, and Update. There has to be at least one example for each database for each command option. A comparison to SQL99 or 2003 standards would be highly useful.

Also I would be looking for a fairly comprehensive coverage of all the syntax and options. Oh and make it compact and light so I will not be reluctant to pack it in my briefcase.

Well I went to the Web and tried out Apress, Dorsey, Manning, Sams, and Wrox. I even tried the Dummies. Nothing really right. Then I stumbled upon this old edition of SQL in a Nutshell from O'Reilly in a second hand book shop. I didn't think O'Reilly did databases.

Nutshell is tantalizingly close. But they don't cover DB2, and the edition at 2001 is out of date with respect to MySQL and SQL Server which it does cover. However Nutshell has some very nice touches: a comparison to ANSI SQL99 standards plus at least one and often two examples showing the syntax being used. Finally the size and weight is right. There is a newer 2004 edition which is 700 pages (versus 212 pages for the first edition); but this is not available in the local bookstores. As soon as I get my hands on this version, I will provide a review.

SQL Pocket Guide

I should have known that if O'Reilly covers the topic, database, they do it pretty thoroughly. And sure enough, I found while looking for SQL in a Nutshell 2nd edition, the following SQL Pocket Guide by Jonathan Gennick. This was available in the bookstores, so I scooped up a copy - and I am glad I did.

Although the SQL Pocket Guide does not fit in my front shirt pocket (thank heavens , it would scream Nerd Nerd Nerd), it is very compact light and handy such that it always gets into my briefcase. And there it has rescued me in the past few weeks not a few times. Syntax of core SQL numeric functions are nearly identically, but not the the SQL string functions. SQL Pocket Guide shows the differences.

I have come to rely on the Oracle DECODE function, SQL Pocket Guide shows some CASE and other alternatives for MySQL and DB2. The discussion of Inner and Outer joins including examples is crystal clear for the five databases covered by SQL Pocket Guide - Oracle, DB2, SQL Server, MySQL and PostGreSQL.

And the handling of examples in the Pocket Guide is quite good. Not the one example per database per major option that I would like but rather a key example that applies to many databases (shared syntax) and then an additional example if the differences are major or the database is one of the big three - DB2, Oracle, MySQL. But even with this parsimony in examples, the book is nearly two hundred pages.

However, SQL Pocket Guide provides a comprehensive index and walks the users through the SELECT, INSERT, UPDATE, and DELETE command in sufficient detail and clarity to meet most CRUD- Create, Read, Update, and Delete tablerow programming requirement. However, the book leaves most DDL commands such as GRANTS, INDEX, TRIGGERS, and stored procedure syntax and coding untouched. Hopefully SQL in a Nutshell 2004 edition will cover those topics.

In sum, for the handy portable reference I have found what I want in SQL Pocket Guide. For the comprehensive, comparative SQL reference I have a couple of targets and I will post a review as soon as I know.