SQL Cookbook

Simon P. Chappell writes "One of the staples of corporate I.S. development is processing data, and increasingly these days that data lives in a relational database. The lingua franca of relational database programming is the Structured Query Language (SQL), often pronounced "sequel". Many programmers find that the basics of SQL are easy to learn, but after that, it tends to get complicated. Enter the SQL Cookbook from O'Reilly." Read the rest of Simon's review. SQL Cookbook author Anthony Molinaro pages 595 (9 page index) publisher O'Reilly rating 8/10 reviewer Simon P. Chappell ISBN 0596009763 summary If you need help working with a database, this is the book for you.

The book is not for beginners and makes no efforts to teach any SQL. It concentrates purely on building on the base level of SQL knowledge that most programmers have. If you know the basics: Create, Read, Update and Delete (an unfortunate, yet memorable acronym) but rarely go beyond that, this book is for you. I know that I fall into this target market.

The point of the cookbook is that you need to get something done and you need to get it done sooner rather than later. Now, most of us can figure out most things given enough time, but in the real world, we rarely have enough time. The cookbooks objective is to save you time by giving you a successful approach that you could have figured out eventually anyway.

If you've previously read any other technology cookbook from O'Reilly, then you already know the structure of the recipes. For those new to the O'Reilly cookbook format, it's actually fairly straightforward. Each recipe starts out with a problem statement. Recipe one in chapter one, titled "Retrieving all Rows and Columns from a Table" has the problem statement "You have a table and want to see all of the data in it." Nice and clear. Then comes the solution. Naturally, for this problem statement we end up with a SELECT that looks like this:

select * from emp

Then the recipe has a discussion section where the solution is explained in more detail and the reasoning behind it is provided. For recipe one, the discussion explores the trade-offs between using the "*" to signify all columns versus naming each column explicitly.

The chapter structure through the book is very much one of building on the previous material. The first chapter starts with the fundamentals, the retrieving of records and then chapter two takes over with sorting the results of your query, while chapter three looks at using multiple tables.

Chapter four covers inserting, updating and deleting records. After that it's back into the world of queries, with chapter five exploring metadata queries for those times when you need to know just a little more about what's going on under the covers. Chapter six looks at working with Strings, a much harder topic than it would seem at first thought. Chapter seven addresses working with numbers and chapter eight does the same for date arithmetic with chapter nine bringing more understanding to general date manipulation. Chapter ten looks at working with ranges.

Chapter eleven dives into advanced searching in preparation for chapter twelve's information on reporting and (data) warehousing. Hierarchical queries are always challenging, so they're reserved for chapter thirteen. Finally, chapter fourteen is titled "Odds 'n' Ends" and is a general catch-all for some pretty advanced, but very infrequent problems.

I guess you either like the recipe approach or not. I love it, so I'm listing it as something to like about this book. The recipes are very well explained and while each one presents only one approach, where there are obviously multiple options, the discussion takes care to explain the reasoning behind the selection.

The writing is clear and the explanations are well laid out. Both the SQL code and the query results are presented well and are easy to read.

A very important part of the book is that it covers SQL variations for Oracle, IBM's DB2, Microsoft SQL Server and the open-source databases PostgresSQL and MySQL. Each of the recipes includes solutions for each of the five databases. While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard; hence the book has to present up to five solutions for each problem.

Many of the recipes are advanced. While the front of the book has the entry level material, it ramps up in complexity pretty quickly. For some of us with very straightforward SQL knowledge, some of the recipes are going to take a while to fully understand and be able to use.

Many of the recipes are obviously oriented towards corporate reporting. If this is a big need for you, then this book goes along way to meeting your needs. If you have no need for corporate reporting, it's wasted paper.

This is an excellent book; it does exactly what it sets out to do and fully equips you to handle the most sophisticated database transactions.

You can purchase SQL Cookbook 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

Recommendation

[PFI_Optix]

As most people I know learn SQL and PHP together, if you're to the point that this book would be of benefit to you, I'd suggest also getting the PHP Cookbook. That book has done wonders on improving the quality of my code.

Re:Recommendation

[PFI_Optix]

Google has failed me a few times with PHP issues, the cookbook has helped fill in the gaps there.

Problems with finding answers on the web:

1) The code for the answers to problem A and problem B don't necessarily work together

2) The code is of questionable quality. I'm six months in to teaching myself PHP (and CSS, SQL, and a few web server applications) and I can tell you that the code I wrote just two months ago was utter crap, largely because it was based on web tutorials that completely neglected good practices.

3) I have no prior experience or training with programming, which makes it hard to even know where to start looking for a solution. Books like this have helped me learn how to know what I need to search for.

Re:Recommendation

[jbarket]

The PHP manual is the absolute must use resource.

Not only is it obviously a complete reference guide to the language, but the user comments on each function often cover a wide variety of uses that can help solve your particular problem. More importantly, if poor quality code is posted, someone will often post a cleaned up version.

Not to knock books at all. Just saying, there's a great free resource out there that is vastly superior to the hundreds of ad ridden script sites.

Microsofties say "sequel"

[Anonymous Coward]

Unix MEN say S.Q.L.

Re:Microsofties say "sequel"

[filesiteguy]

I believe you're correct. http://en.wikipedia.org/wiki/SQL#History

During the 1970s, a group at IBM's San Jose research center developed a database system "System R" based upon, but not strictly faithful to, Codd's model. Structured English Query Language ("SEQUEL") was designed to manipulate and retrieve data stored in System R. The acronym SEQUEL was later condensed to SQL because the word 'SEQUEL' was held as a trademark by the Hawker-Siddeley aircraft company of the UK. Although SQL was influenced by Codd's work, Donald D. Chamberlin and Raymond F. Boyce at IBM were the authors of the SEQUEL language design.[1]. Their concepts were published to increase interest in SQL.

Oh, and as to the OP stating that SQL is difficult, try IMS.

I'll take any SQL system over an IMS or other heirarchical anyday. :P I had the unfortunate need to learn IMS after years of various SQL databases. Sure it is faster, but what a learning curve!

BN vs. Amazon

[CheeseTroll]

You could pay $39 from BN, or $26.37 at Amazon.

This looks like a very handy reference for those of us who can read SQL statements alright, but have grown lazy with all the GUI SQL-statement builders that exist now.

Re:BN vs. Amazon

[nuzak]

Or you could install BookBurro and compare prices yourself.

Just google for it. It's an extension these days, not a greasemonkey script, which has its good and bad points (hard to add another bookstore yourself).

Too bad SKU's aren't common in the public-facing interface of online merchants ... you could expand bookburro to nearly any product at all in that case.

The practical SQL handbook

[WindBourne]

For those of you just starting out, try the the practical sql handbook. I recently glanced through edition 3. I started at edition one years ago. It still remains an awesome book.

Joe Celko

[ytsejam-ppc]

There are a few books by Joe Celko like SQL For Smarties that are in the position of honor on my bookshelf. If you're beyond "select * from emp" and want to really learn how to efficiently get things done in a relational database, Celko is your guy.

Bullshit

[Reality Master 201]

First, SQL comes from SEQUEL ( Structured English Query Language), created by IBM in the 70's for getting data from System R.

Second, I know plenty of mainframe and UNIX guys from way back who say "sequel" for SQL, some former IBMers, others not. The only people I ever hear call it "ess-que-ell" are management types and some FOSS people who have only ever heard about databases from reading stuff on the web and have never had a real job working with real databases.

Re:Mispronunciation

[pclminion]

SQL started out being called SEQUEL (and pronounced as such) until it was changed to SQL due to trademark issues. IMHO, the historical pronunciation is a valid precedent. See the History section of the SQL Wikipedia entry.

There is also a MySQL Cookbook

[vrTeach]

As with the author of this review, I really like the cookbook format. I've made heavy use of the MySQL cookbook, also from O'Reilly, by Paul DuBois. It is excelent, and just about everything that Simon P. Chappell says about the SQL Cookbook applies, except of course the focus on just MySQL. I'm glad to hear of the SQL cookbook because I'll be developing for postgresql as well as MySQL in the near future. Thanks.

SQL is a standard. Is it?

[hypersql]

What I have always found funny about SQL is that, while it's very 'old' (in software terms), and mature, and widely used, there is in fact no real standard. There never was. From the article:

SQL variations ... While SQL is a standard, there seems to be some very relaxed definitions of full adherence to that standard...

Or, as Jim Starkey said: 'SQL isn't a standard but a theme'. For a book, it means list 5 different dialects. For regular developers (not database specialists) it means knowing only one dialect really well. For an application it means, running only with one database (mostly). It would be really cool the industry could get together and define a 'real' standard. Could be a subset of SQL (http://ldbc.sf.net/) or a new language (http://newsql.sf.net/). Things would get simpler then.

(Side note: LDBC and NewSQL are both projects I started, but interest was quite low; currently I'm working on a new database engine http://www.h2database.com/ where I try to be compatible as much as possible with existing databases)

Or is there some other solution? I don't think that that O/R mapping tools will solve the problem completely, as there is always the need interactive database queries. Maybe the Microsoft extension to C# (forgot the name) could be a solution? Other ideas?

Re:SQL is a standard. Is it?

[orthogonal]

there is in fact no real standard

Yes, there's a great deal of nonconformance and extensions.

But, there is a standard, in fact, five: SQL-86, SQL-89, SQL-92, SQL:1999 and SQL:2003 (yeah, dashes replaced by colons, go figure). SQL:2003 can be purchased from ANSI or ISO, just like the C or C++ standards.

Various sites list product conformance to the standards.

When I write SQL, I pretty rigorously stick to SQL-99, as that's likely to be supported by most vendors. If I need to deviate from that, I make sure I know how to replicate the vendor-specific code in SQL-99 (e.g, postgresql's inherits keyword -- it's useful for sub-typing, but it can be effected by using joins and views).

If the non-standard code is DML (not DDL), I'll do my best to encapsulate it in a stored procedure or a view, and let the rest of my code call the encapsulated abstraction. This is just the same layering you'd do in any programming language to wall off platform-specific code. And just as you'd typedef in C or create abstract types in C++, you create UDTs in SQL too.

Here's an example, using a useful testing date "function" that is cross-dialect, doesn't rely on any user-supplied function support, and can be incorporated into live code.

-- postgresql
CREATE DOMAIN datetime_type timestamp null;
create view system_datetime as select 1 as id, now() as date_now ;

-- MS SQL server
create type datetime_type from datetime null;
create view system_datetime as select 1 as id, getdate() as date_now ;


-- code below is SQL dialect independent
-- works on postgresql or MS SQL server

-- code to use actual current date or fake date for testing purposes
-- all DML that wants the current system timestamp should instead join on (or subselect) the view date_now
-- this view always returns a single row

create table test_date ( id int, test_date datetime_type ) ;
insert test_date values ( 1, null ) ;

create view date_now as
select
coalesce(
( select test_date from test_date where id = 1) ,
( select date_now from system_datetime where id = 1)
) as date_now ;

-- note: for Sybase, replace coalesce with isnull

-- see what invoices were due as of January 1:
update test_date set test_date = '1/1/2006' ;

select * from invoices a, date_now b
where a.issue_date <= b.date_now
and ( a.paid_date > b.date_now or a.paid_date is null )

-- look at real invoices due as of now
-- Query is the same, we just null out the fake date:
update test_date set test_date = null ;

select * from invoices a, date_now b
where a.issue_date <= b.date_now
and ( a.paid_date > b.date_now or a.paid_date is null )

(And yes, you can hire me.)

Re:SQL is a standard. Is it?

[clive_p]

There is a series of official SQL Standards, but the trouble is nobody any longer measures the extent to which database vendors conform to the official standards. Once upon a time NIST used to do this, but that part of their work was cancelled after some lobbying by the big database vendors: naturally they much prefer it if you use their dialect then you get locked in to their product. Who allowed this to happen? I think it was Bill Clinton.

Sample chapter

[sootman]

Chapter 11: Searching
40 pages, 500k PDF

Not for beginners?!? Doubtful

[GalacticCmdr]

I am not sure how anyone can resolve the following found in the review - emphasis mine.

The book is not for beginners and makes no efforts to teach any SQL. It concentrates purely on building on the base level of SQL knowledge that most programmers have. If you know the basics: Create, Read, Update and Delete (an unfortunate, yet memorable acronym) but rarely go beyond that, this book is for you. I know that I fall into this target market.

Recipe one in chapter one, titled "Retrieving all Rows and Columns from a Table" has the problem statement "You have a table and want to see all of the data in it." Nice and clear. Then comes the solution. Naturally, for this problem statement we end up with a SELECT that looks like this:

select * from emp

How can a book not be for beginners when the first recipe teaches the most basic SQL command possible? I have this book and it is a very good book, but like most technical books it kills a great deal of trees needlessly to pad its count. You can probably rip more than 100 pages out of the book that is mindless beginner crap and self-flogging and actually have a very good technical reference for mid-level SQL people.

Re:More Cooking with SQL

[Doctor Faustus]

NoLock is a SQL Server and Sybase thing (and maybe InGres?). They force select queries to wait until any updates that would affect their results are done and committed, unless you tell it to read the uncertain data with a NoLock. What Oracle does is to give you the data as it was before any uncommitted transactions began.

SQL for Smarties

[nrrd]

SQL for smarties is the best advanced SQL book I've seen. Highly recommended for developers, and should be on the bookshelf of any DBA.