SQL Cookbook: Query Solutions and Techniques for Database Developers

It doesn't take long to learn the basics of SQL. Once you start to do multi-table joins and you get to sub-selects and outer joins you can almost convince yourself you are an expert. Trust me. You aren't. I've read at least ten books on using SQL. This is the first one to blow my horizons away and open a completely new landscape. The "recipies" are well explained and almost entirely practical and useful. This book showed me dozens of things I did not ever think of using SQL for, even though I have the BNF for SQL pretty much in my head after more than 18 years of being a practicing programmer. Before this book I used to recommend "generic" SQL books to students and newbies because such books apply to virtually all SQL databases and it can be harmful for one to use a specific "dialect" (such as Oracle or SQL Server) that won't port to other SQL databases. I would urge people to learn "generic" SQL. You can always add the features of you specific dialect, but it is hard to give up features you assume will be in all dialects. This book neatly avoids that problem by providing dialect-specific sections when you can or must code differently for a particular database. Even better, they explain the differences and the pros and cons of those differences. So this book can even be an aid to selecting the "right" database for your application. I happen to be a fan of Open Source and Free Software and I'm pleased to say that this book covers both MySQL and PostgreSQL. Naturally it provides the "big three" of Oracle, Microsoft SQL Server, and IBM's DB2. This book really opened my eyes to possibilities in SQL that I really didn't know existed in spite of the fact that I have been using the language (apparently without mastering it) for almost two decades. I still recommend using a "generic SQL" book to learn the basics, but this should literally be ordered at the same time. It is the best of the "Cookbook" series that I have seen. (And I have used the Java Cookbook, Perl Cookbook, and Python Cookbook).

Ultimately, the goal of this book is to give you a glimpse of what can be done using SQL outside of what is considered the typical SQL problem domain. This text is unique in that the target audience is wide, incorporating all levels of SQL programmers as well as those who are completely unfamiliar with SQL. Both complex and simple solutions are provided, and solutions for five different vendors are available when a common solution does not exist. These five databases are DB2 v.8, Oracle Database 10g (with the exception of a handful of recipes, the solutions will work for Oracle8i Database and Oracle9i Database as well), PostgreSQL 8, SQL Server 2005 and MySQL 5. All of the examples are built around a small set of tables containing employee data. This helps the reader get familiar with the example data, so that, having become familiar with the data, you can focus on the technique that each recipe illustrates. Chapter 1, Retrieving Records, introduces very simple queries. Examples include how to use a WHERE clause to restrict rows from your result set, providing aliases for columns in your result set, using an inline view to reference aliased columns, using simple conditional logic, limiting the number of rows returned by a query, returning random records, and finding NULL values. Most of the examples are very simple, but some of them appear in later more complex recipes, so it's a good idea to read this chapter if you're relatively new to SQL or aren't familiar with any of the examples listed for this chapter. Chapter 2, Sorting Query Results, introduces recipes for sorting query results. The ORDER BY clause is introduced and is used to sort query results. Examples increase in complexity ranging from simple, single-column ordering, to ordering by substrings, to ordering based on conditional expressions. Chapter 3, Working with Multiple Tables, introduces recipes for combining data from multiple tables. If you are new to SQL or are a bit rusty on joins, this is a good chapter to read before going on to Chapter 5 and later. Joining tables is what SQL is all about; you must understand joins to be successful. Examples in this chapter include performing both inner and outer joins, identifying Cartesian productions, basic set operations (set difference, union, intersection), and the effects of joins on aggregate functions. Chapter 4, Inserting, Updating, Deleting, introduces recipes for inserting, updating, and deleting data, respectively. Most of the examples are very straightforward, perhaps even pedestrian. Nevertheless, operations such as inserting rows into one table from another table, the use of correlated subqueries in updates, an understanding of the effects of NULLs, and knowledge of new features such as multi-table inserts and the MERGE command are extremely useful for your toolbox. Chapter 5, Metadata Queries, introduces recipes for getting at your database metadata. It's often very useful to find the indexes, constraints, and tables

because the focus is on queries (the author states this on the cover and preface) you'll need a more traditional book on learning sql if you are completely new to it. once you know the basics, this is a great "where to go next" book. there are chapters for complete novices, but being a "cookbook" (q & a format), i would not suggest this text as your only text on sql. instead this would make a good supplement to any of the intro sql books avaialable. This book does a nice job of showing when and how to use different constructs of sql. for example, once you learn GROUP BY (there is a window/group by tutorial in the back of the book), you might wonder when exactly do you use it or what queries do you use it for? the recipes in this book do a nice job of explaining how various sql constructs (aggregates, window functions, pivot queries, etc) work and when to use them by tying verbal descriptions to real queries. this is a good book for novice or intermediate developers looking to take their sql to the next level or dbas looking to improve their sql skills.

Of the many things i like about this book is that many times the queries accomplish so much with so little code. Two examples that immediately caught my eye are the join-less versions of computing medians and running totals. Those two operations typically involve self-joins and can be inneffiicent if the tables are large. Though I haven't tried the proposed solutions, they look promising simply because joins are no longer necessary! I also thought the "walking a string" technique was very clever considering sql does not have loops. Besides the "new-style" sql, what real sets this book apart are three things: 1. 5 vendors shown side by side. this is the first text I've seen that shows solutions for oracle, sql server, db2, mysql, and postgresql side by side, and all in their native syntax. When i saw all the different vendors listed on the cover, I thought the author was going to mix and match(ie, show one vendor here, one vendor there). To my surprise, each solution shows all 5 versions when a single universal solution isn't available! 2. Longer queries are given longer explanations and shorter queries are given shorter ones. brilliant. 3. The intermediate steps are extremely helpful. when given more involved queries the author not only explains each portion of the query but shows their results. I find this particularly helpful as i dont have to try to visualize what each particular join or function-call does to the result set. The author takes complex sql, breaks it down to simple form, and builds it back up again to the final solution showing the intermediate result sets all the way. This makes the recipes easy to understand.

When you've been programming for quite sometime rarely will any books surprise you. To my surprise molinaro offers solutions i haven't seen before. There is a heavy focus on "window functions" (which have been available to other vendors but are brand new to sql server so i was quite excited to see a deep treatment of that) along with use of vendor specific functionality which makes for elegant solutions. While there are things i typically would not do in sql, for example, building delimited lists from result sets or parsing strings, very clever and readabable solutions for both are provided. The window function "refresher" is a welcome chapter and the author provides a very nice treatment of GROUP BY (if you've ever had trouble with GROUP BY queries, you'll appreciate this chapter). Molinaro provides an interesting spin on what groups in SQL are. Having a background in sybase,access, and sql server, i appreciate the tribute chapter to David Rozenshtein. While Molinaro's solutions are very different, he acknowledges the traditional techniques and in some cases shows old and new, side by side. all in all, a great book, showing off new and interesting sql techniques.