| Be sure to read this preface thoroughly. It contains necessary background and other information that you might otherwise miss if you dive into individual recipes. The section on "Platform and Version" tells you what RDBMSs this book covers. Pay special attention to "Tables Used in This Book," so that you become familiar with the example tables used in most of the recipes. You'll also find important coding and font conventions in "Conventions Used in This Book." All these sections come later in this preface. Remember that this is a cookbook, a collection of code examples to use as guidelines for solving similar (or identical) problems that you may have. Do not try to learn SQL from this book, at least not from scratch. This book should act as a supplement to, not a replacement for, a complete text on learning SQL. Additionally, following the tips below will help you use this book more productively: This book takes advantage of vendor-specific functions. SQL Pocket Guide by Jonathan Gennick has all of them and is convenient to have close to you in case you don't know what some of the functions in my recipes do. If you've never used window functions, or have had problems with queries using GROUP BY, read Appendix A first. It will define and prove what a group is in SQL. More importantly, it gives a basic idea of how window functions work. Window functions are one of the most important SQL developments of the past decade. Use common sense! Realize that it is impossible to write a book that provides a solution to every possible business problem in existence. Instead, use the recipes from this book as templates or guidelines to teach yourself the techniques required to solve your own specific problems. If you find yourself saying, "Great, this recipe works for this particular data set, but mine is different and thus the recipe doesn't work quite correctly," that's expected. In that case, try to find commonality between the data in the book and your data. Break down the book's query to its simplest form and add complexity as you go. All queries start with SELECT…FROM…, so in their simplest form, all queries are the same. If you add complexity as you go, "building" a query one step, one function, one join at a time, you will not only understand how those constructs change the result set, but you will see how the recipe is different from what you actually need. And from there you can modify the recipe to work for your particular data set. Test, test, and test. Undoubtedly any table of yours is bigger than the 14 row EMP table used in this book, so please test the solutions against your data, at the very least to ensure that they perform well. I can't possibly know what your tables look like, what columns are indexed, and what relationships are present in your schema. So please, do not blindly implement these techniques in your production code until you fully understand them and how they will perform against your particular data. Don't be afraid to experiment. Be creative! Feel free to use techniques different from what I've used. I make it a point to use many of the functions supplied by the different vendors in this book, and often there are several other functions that may work as well as the one I've chosen to use in a particular recipe. Feel free to plug your own variations into the recipes of this book. Newer does not always mean better. If you're not using some of the more recent features of the SQL language (for example, window functions), that does not necessarily mean your code is not as efficient as it can be. There are many cases in which traditional SQL solutions are as good or better than any new solution. Please keep this in mind, particularly in the Appendix B, Rozenshtein Revisited. After reading this book, you should not come away with the idea that you need to update or change all your existing code. Instead, only realize there are many new and extremely efficient features of SQL available now that were not available 10 years ago, and they are worth the time taken to learn them. Don't be intimidated. When you get to the solution section of a recipe and a query looks impossible to understand, don't fear. I've gone to great lengths to not only break down each query starting from its simplest form, but to show the intermediate results of each portion of a query as we work our way to the complete solution. You may not be able to see the big picture immediately, but once you follow the discussion and see not only how a query is built, but the results of each step, you'll find that even convoluted-looking queries are not hard to grasp. Program defensively when necessary. In an effort to make the queries in this book as terse as humanly possible without obscuring their meaning, I've removed many "defensive measures" from the recipes. For example, consider a query computing a running total for a number of employee salaries. It could be the case that you have declared the column of type VARCHAR and are (sadly) storing a mix of numeric and string data in one field. You'll find the running total recipe in this book does not check for such a case (and it will fail as the function SUM doesn't know what to do with character data), so if you have this type of "data" ("problem" is a more accurate description), you will need to code around it or (hopefully) fix your data, because the recipes provided do not account for such design practices as the mixing of character and numeric data in the same column. The idea is to focus on the technique; once you understand the technique, sidestepping such problems is trivial. Repetition is the key. The best way to master the recipes in this book is to sit down and code them. When it comes to code, reading is fine, but actually coding is even better. You must read to understand why things are done a certain way, but only by coding will you be able to create these queries yourself.
Be advised that many of the examples in this book are contrived. The problems are not contrived. They are real. However, I've built all examples around a small set of tables containing employee data. I've done that to help you get familiar with the example data, so that, having become familiar with the data, you can focus on the technique that each recipe illustrates. You might look at a specific problem and think: "I would never need to do that with employee data." But try to look past the example data in those cases and focus on the technique that I'm illustrating. The techniques are useful. My colleagues and I use them daily. We think you will too. |
