SQL: Visual QuickStart Guide

objectboy writes with a review of Chris Fehily's SQL: Visual QuickStart Guide, writing "This book teaches ANSI SQL-92 programming to database beginners and intermediates. The publisher, Peachpit Press, publishes mostly end-user and novice titles that usually go unnoticed by professional programmers. Its Perl and PHP books, for example, are of little practical or tutorial use to an experienced developer. In fact, I noticed this SQL book only because a junior developer was using it for a course. The book's table of contents, index, and a sample chapter are posted on Amazon.com. The book's official web site contains errata and other information." Objectboy's review continues below. SQL: Visual QuickStart Guide author Chris Fehily pages 424 publisher Peachpit Press rating 9/10 reviewer objectboy ISBN 0321118030 summary A lucid SQL tutorial and professional reference

What this book does right: The myth that it's more important for a programming book to be technically accurate than well written endures even though the opposite situation is true: A lucid explanation of a difficult concept or clever algorithm is more valuable than a bug-free implementation of same.

Consider Ken Henderson's The Guru's Guide to Transact-SQL , a book full of useful examples but so marred by the author's bloated style and disrespect for the language that I cringe every time I'm forced to read the text rather than simply lift a code snippet. Henderson even goes so far as to include an introductory section, titled "On Formality," about how he is going to split infinitives (even though their syntax is a burden for the brain to parse) and how he is going to use "data" in the singular sense (even though doing so can cause confusion) and how he considers "record," "row", and "tuple" to be interchangeable terms (even though they're not) and on and on. Readers would be aghast to find such self-exculpatory nonsense in the pages of Donald Knuth or Patrick Henry Winston. As for SQL: Visual QuickStart Guide, the author, a statistical programmer, presents each topic with a mathematician's sense of restraint and order. I've found few typos, no technical errors, and consistent use of technical terms.

Almost every aspect of SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, and DROP is covered. What distinguishes this book is that every ANSI SQL statement -- and there are hundreds of examples -- was tested on six separate DBMSes: Microsoft Access 2002, Microsoft SQL Server 2000, MySQL 4.0, PostgreSQL 7.1, Oracle 8i, and Oracle 9i (8i and 9i differ considerably in SQL-92 compliance). The examples in each section increase in depth and complexity, so you can stop reading once you've learned what you need to know. When an ANSI SQL statement doesn't work as-is on a particular DBMS, the author shows you how to fix it or offers workarounds (which is particularly useful for MySQL, whose adherence to the SQL standard is poor). These DBMS-specific fixes are given as separate "DBMS Tips" apart from the main body of text, so they don't interfere with the conceptual flow. This organization is especially useful for consultants who have difficulty keeping track of how each implementation deviates from the ANSI standard, and is superior to the alphabetical, segregated approach of O'Reilly's SQL in a Nutshell.

This book was shoehorned into the publisher's Visual QuickStart format, which, as I implied earlier, doesn't work well for procedural languages, but does work for a declarative language like SQL. A two-column layout separates examples from explanatory text. Red type highlights the relevant portions of code and results. The book is extensively cross-referenced and has an 18-page index. This layout also makes the book a good quick reference for experienced programmers. Almost all the examples use a single, sample database (so there's no need to memorize multiple schemas). The code listings and sample database are available for download.

The derivative nature of programming books makes it difficult to determine whether the author truly has mastered the material. Writing a book is a difficult task (perhaps even harder than programming) but, at the risk of exaggerating my point, I suspect that any determined, organized, and competent programmer could write any O'Reilly Nutshell book by paraphrasing existing materials. But if an author establishes his credentials early, the reader gains a sense of trust that remains throughout the entire book. In the introduction to this book, the author avoids an error that almost every other SQL-book author commits: that SQL stands for structured query language. According to ANSI (the only legitimate arbiter here), it stands for S-Q-L and nothing more. Fehily even offers an amusing explanation of why structured query language is the worst possible description of SQL. Throughout the book, the author also scatters bits of practical advice (job candidates are wise to say my-es-kyu-el, not my-sequel), beginner-friendly insights ("Although SELECT is powerful, it's not dangerous: You can't use it add, change, or delete data or database objects."), and advanced topics (optimization, concurrency control, logical data independence). It is these asides and respect for basic research, rather than swaths of expository text, that lend authority.

This book describes the effects of nulls in almost every aspect of SQL, including the interpretation of null-contaminated query results. You can no more be a competent SQL programmer without understanding nulls than you can be a competent LISP programmer without understanding recursion. Particularly useful are the discussion of three-value logic (true/false/unknown) and an algebraic derivation of how a null can cause a subquery to return an empty result unexpectedly (which has bitten me more than once).

As a wizened developer weary of hand-holding users and junior programmers through routine queries, I've found it mollifying to give away copies of this book (it's cheap) to reduce my interrupt stack.

What's Missing: Some missing items that I would have found useful:

• A glossary
• A quick syntax reference
• A chapter about statistics
• A chapter about advanced SQL "tricks"
• DB2 coverage
• Coverage of security commands (GRANT/REVOKE)
• An expanded query-optimization discussion
• Improved normalization examples
• A little more mathematical rigor in the set-theory discussion

You can purchase SQL: Visual QuickStart Guide from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Comments

Peachpit press is kinda useful.

[crazyphilman]

I liked their javascript and XML quickstart guides; they're basically ways of getting up to speed on something really quickly, and for relatively simple subjects (like Javascript, html, and xml) they might be all you need outside of web searches. The xml book had some really nice info about stylesheets and XSLT, for instance, and I use the stylesheet reference even when I'm just doing some HTML.

The books are cheap (twenty or thirty bucks) and small (easy to carry in a backpack). I think they're a really good value. Although, I wouldn't buy one for a more in-depth programming language, for the simpler stuff (html, xml) they're great.

Try the Codenotes series, too

[Wee]

I don't have any Peachpit books, but if you like small form factor, backpack-sized references try the Codenotes series of books. They're really small (roughly 5x8 inches), and get right to the point. They're very good references when you can't get to online sources. You need to have some passing familiarity with the material, however.

I have the XML and the Web-Based UI ones. They're both very handy to have around, and easier to carry than toting O'Reilly books everywhere. They also cover more ground than the O'Reilly "Pocket Reference" series tend to.

-B

Re:One thing I've become enamored of

[Anonymous Coward]

Yes--there is a huge problem with using this as a generic solution. It doubles the time needed to get an answer back. For a query that is rarely run and is fast, this probably isn't a problem. For a query that is either frequently run or takes a long time to return (or worst, both), you have just doubled the impact of an already expensive statement. Impact not just equaling time, of course, but also CPU usage and lengthening the duration of sort-space usage (for queries whose plans require sorts/hash joins).

So--in a _limited_ number of cases the above might be OK, but I would shy away from suggesting that as a "generally good thing to do".

Re:I believe MySQL is SQL-92 compliant (mostly)

[Sxooter]

MySQL is so full of little incompatibilities with SQL92 it's not even funny. Here's one (of about a hundred examples you can come up with if you scour the SQL92 spec and compare what it says to what MySQL does.)

SQL92 says that if you multiply two numerics with a precision of say x and y digits to the right of the decimal place respectively, the result should have x+y digits of accuracy to the right of the decimal point.

i.e.

create table test (num1 numeric (10,2), num2 numeric(10,3));
insert into test values (4.54,8.679);
select num1*num2 from test;

you get 39.403 for an answer. You're supposed to get 39.40266 for an answer, but MySQL just chops off the extra digits, in clear violation of the SQL92 standard.

Because of this, it's unlikely MySQL will be used by anyone (with a brain and capable of testing MySQL for compliance anyway) in a financial application like Oracle or Postgresql would be suited for.

There are plenty of other issues with MySQL. Anyone who's used databases a lot before seeing MySQL just shakes their head and laughs at the idea that someone would use it to store critical data.

SQL:1999

[tarvin]

SQL:1999 has been defined for years and some of the DBMSs have actually implemented some of its features. Yes, SQL:1999 is big and perhaps bloated in comparison with SQL-92, but it's not that bad if you concentrate on the core parts. Some of the news in SQL:1999 are actually clarifications on stuff in SQL-92.

SQL:2003 will probably be agreed on this year.

- So I find it strange to use the more than a decade old SQL-92 as the platform for a book published in the year 2002.

Another thing: I'd say it's "ISO SQL" nowadays (or ISO/IEC SQL), not "ANSI SQL".

Apart from that, I've put the book on my Safari bookshelf and look forward to reading (at least parts of) it. Unfortunately, it seems that Safari's index of the book is currently lost.

I like the approach where you start by thinking about the standard way to do it, and then try to squeeze your design into a real-World product. It's too bad that the official standard isn't online; fortunately, it's possible to get something close to being official. When trying to set up an initial, standards compliant schema, it's nice to have an evaluation-installation of Mimer SQL to play with. I wish it were open source or at least had an official price tag.

I expect that the book could be relevant in connection with a page about SQL differences that I've started writing, after having had to port some SQL from PostgreSQL to MSSQL.

Re:DBAs?

[AKAImBatman]

The difference may not be much but what you save in network bandwidth, and optimizer time adds up. Besides it's not like it's hard to write a simple stored proc in *any* SQL language.

But a list of procs is way more difficult to manage than a Java API (for example). Procs have no concept of package structure and as a result, they all end up in one big jumble with 3 or 4 different styles of naming (to get around the problem) competing for your visual attention. Bandwidth internal to a network is nothing when it comes to SQL statements and optimizer time is negligable for most simple select, insert, and update queries. Most databases expect the little cases. It's the rare, big ones that test its metal.

These aren't things you can have all at the time, sometimes you can when I'm your DBA ;). Much of the time the 'get it in production now' attitude is what leads to performance problems 2 years down the road when the new DBA (re: Me) takes over to fix the deadlocks, timeouts, and poor usage of DB resources.

I'm probably didn't come across as clear as I'd like. There are well know, well understood patterns for developing databases. The two most common and easiest to apply are applying the rules of normalization, and object/relational design.

The rules of normalization tell you how to build the database so that everything functions well in a relational world. Too many "database brains" ignore these to try "fun" concepts like a "winding heirarchical" structure. It's supposedly faster than a simple parent key design, but you have to be a friggin' PhD to follow the blasted thing.

Object relational is a good design principle that trips up many DBAs. Often, a DBA will take the attitude that as long as the data is _somewhere_ and it follows the relational rules, it's okay, right? Wrong. Every table/view should follow a well defined concept. For example, you should have a table for a Product, not a table of ids over here and details over there, and other weird ass ideas. This sort of structure also encourages object type heirarchies. (i.e. The "Product_Color" table gives potential options for the color field of a product).

Follow these simple design concepts and your database will come out right the first time and will be quite simple to build. Plus it leaves plenty of open breathing room to do optimizations and additions to the database later on.