Preface
SQL is the language in the database world. If you’re developing for or reporting from relational databases, your ability to put data into a database and then get it back out again ultimately comes down to your knowledge of SQL. Yet many practitioners use SQL in a perfunctory manner, and are unaware of the power at their disposal. This book aims to change all that, by opening your eyes to what SQL can really do for you.
The book you’re holding in your hands is a cookbook. It’s a collection of common SQL problems and their solutions that I hope you’ll find helpful in your day-to-day work. Recipes are categorized into chapters of related topics. When faced with a new SQL problem that you haven’t solved before, find the chapter that best seems to apply, skim through the recipe titles, and hopefully you will find a solution, or at least inspiration for a solution.
More than 150 recipes are available in this 600-plus page book, and I’ve only scratched the surface of what can be done using SQL. The number of different SQL solutions available for solving our daily programming problems is eclipsed only by the number of problems we need to solve. You won’t find all possible problems covered in this book. Indeed, such coverage would be impossible. You will, however, find many common problems and their solutions. And in those solutions lie techniques that you’ll learn how to expand upon and apply to other, new problems that I never thought to cover.
Tip
My publisher and I are constantly on the lookout for new, cookbook-worthy SQL recipes. If you come across a good or clever SQL solution to a problem, consider sharing it; consider sending it in for inclusion in the next edition of this book. See “Comments and Questions” for our contact information.
Why I Wrote This Book
Queries, queries, queries. My goal from the beginning of this project has not been so much to write a “SQL Cookbook” as to write a “Query Cookbook.” I’ve aimed to create a book comprised of queries ranging from the relatively easy to the relatively difficult in hopes the reader will grasp the techniques behind those queries and use them to solve his own particular business problems. I hope to pass on many of the SQL programming techniques I’ve used in my career so that you, the reader, will take them, learn from them, and eventually improve upon them; through this cycle we all benefit. Being able to retrieve data from a database seems so simple, yet in the world of Information Technology (IT) it’s crucial that the operation of data retrieval be done as efficiently as possible. Techniques for efficient data retrieval should be shared so that we can all be efficient and help each other improve.
Consider for a moment the outstanding contribution to mathematics by Georg Cantor, who was the first to realize the vast benefit of studying sets of elements (studying the set itself rather than its constituents). At first, Cantor’s work wasn’t accepted by many of his peers. In time, though, it was not only accepted, but set theory is now considered the foundation of mathematics! More importantly, however, it was not through Cantor’s work alone that set theory became what it is today; rather, by sharing his ideas, others such as Ernst Zermelo, Gottlob Frege, Abraham Fraenkel, Thoralf Skolem, Kurt Gödel, and John von Neumann developed and improved the theory. Such sharing not only provided everyone with a better understanding of the theory, it made for a better set theory than was first conceived.
Objectives of This Book
Ultimately, the goal of this book is to give you, the reader, a glimpse of what can be done using SQL outside of what is considered the typical SQL problem domain. SQL has come a very long way in the last ten years. Problems typically solved using a procedural language such as C or JAVA can now be solved directly in SQL, but many developers are simply unaware of this fact. This book is to help make you aware.
Now, before you take what I just said the wrong way, let me state that I am a firm believer in, “If it ain’t broke, don’t fix it.” For example, let’s say you have a particular business problem to solve, and you currently use SQL to simply retrieve your data while applying your complex business logic using a language other than SQL. If your code works and performance is acceptable, then great. I am in no way suggesting that you scrap your code for a SQL-only solution; I only ask that you open your mind and realize that the SQL you programmed with in 1995 is not the same SQL being used in 2005. Today’s SQL can do so much more.
Audience for This Book
This text is unique in that the target audience is wide, but the quality of the material presented is not compromised. Consider that both complex and simple solutions are provided, and that solutions for five different vendors are available when a common solution does not exist. The target audience is indeed wide:
- The SQL novice
Perhaps you have just purchased a text on learning SQL, or you are fresh into your first semester of a required database course and you want to supplement your new knowledge with some challenging real world examples. Maybe you’ve seen a query that magically transforms rows to columns, or that parses a serialized string into a result set. The recipes in this book explain techniques for performing these seemingly impossible queries.
- The non-SQL programmer
Perhaps your background is in another language and you’ve been thrown into the fire at your current job and are expected to support complex SQL written by someone else. The recipes shown in this book, particularly in the later chapters, break down complex queries and provide a gentle walk-through to help you understand complex code that you may have inherited.
- The SQL journeyman
For the intermediate SQL developer, this book is the gold at the end of the rainbow (OK, maybe that’s too strong; please forgive an author’s enthusiasm for his topic). In particular, if you’ve been coding SQL for quite some time and have not found your way onto window functions, you’re in for a treat. For example, the days of needing temporary tables to store intermediate results are over; window functions can get you to an answer in a single query! Allow me to again state that I have no intention of trying to force-feed my ideas to an already experienced practitioner. Instead, consider this book as a way to update your skill set if you haven’t caught on to some of the newer additions to the SQL language.
- The SQL expert
Undoubtedly you’ve seen these recipes before, and you probably have your own variations. Why, then, is this book useful to you? Perhaps you’ve been a SQL expert on one platform your whole career, say, SQL Server, and now wish to learn Oracle. Perhaps you’ve only ever used MySQL, and you wonder what the same solutions in PostgreSQL would look like. This text covers different relational database management systems (RDBMSs) and displays their solutions side by side. Here’s your chance to expand your knowledge base.
How to Use This Book
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.
What’s Missing from This Book
Due to constraints on time and book size, it isn’t possible for a single book to provide solutions for all the possible SQL problems you may encounter. That said, here are some additional items that did not make the list:
- Data Definition
Aspects of SQL such as creating indexes, adding constraints, and loading data are not covered in this book. Such tasks typically involve syntax that is highly vendor-specific, so you’re best off referring to vendor manuals. In addition, such tasks do not represent the type of “hard” problem for which one would purchase a book to solve. Chapter 4, however, does provide recipes for common problems involving the insertion, updating, and deleting of data.
- XML
It is my strong opinion that XML recipes do not belong in a book on SQL. Storing XML documents in relational databases is becoming increasingly popular, and each RDBMS has their own extensions and tools for retrieving and manipulating such data. XML manipulation often involves code that is procedural and thus outside the scope of this book. Recent developments such as XQUERY represent completely separate topics from SQL and belong in their own book (or books).
- Object-Oriented Extensions to SQL
Until a language more suitable for dealing with objects comes along, I am strongly against using object-oriented features and designs in relational databases. At the present time, the object-oriented features available from some vendors are more suitable for use in procedural programming than in the sort of setoriented problem-solving for which SQL is designed.
- Debates on Points of Theory
You won’t find arguments in this book about whether SQL is relational, or about whether NULL values should exist. These sort of theoretical discussions have their place, but not in a book centered on delivering SQL solutions to real-life problems. To solve real-life problems, you simply have to work with the tools available to you at the time. You have to deal with what you have, not what you wish you had.
- Vendor Politics
This text provides solutions for five different RDBMSs. It is only natural to want to know which vendor’s solution is “best” or “fastest.” There is plenty of information that each vendor would gladly provide to show that their product is “best”; I have no intention of doing so here.
- ANSI Politics
Many texts shy away from the proprietary functions supplied by different vendors. This text embraces proprietary functions. I have no intention of writing convoluted, poorly performing SQL code simply for the sake of portability. I have never worked in an environment where the use of vendor-specific extensions was prohibited. You are paying for these features; why not use them?
Vendor extensions exist for a reason, and many times offer better performance and readability than you could otherwise achieve using standard SQL. If you prefer ANSI-only solutions, fine. As I mentioned before, I am not here to tell you to turn all your code upside down. If what you have is strictly ANSI and it works for you, great. When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what’s still left of our days. So, I’m not suggesting that ANSI-only is wrong. Do what works and is best for you. But, I want to make clear that if you’re looking for ANSI-only solutions, you should look elsewhere.
- Legacy Politics
The recipes in this text make use of the newest features available at the time of writing. If you are using old versions of the RDBMSs that I cover, many of my solutions will simply not work for you. Technology does not stand still, and neither should you. If you need older solutions, you’ll find that many of the SQL texts available from years past have plenty of examples using older versions of the RDBMSs covered in this book.
Structure of This Book
This book is divided into 14 chapters and 2 appendices:
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 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, I strongly recommend you read this chapter before reading 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 in your schema. The simple recipes here allow you to gain information about your schema. Additionally, “dynamic” SQL examples are shown here as well, i.e., SQL generated by SQL.
Chapter 6, Working with Strings, introduces recipes for manipulating strings. SQL is not known for its string parsing capabilities, but with a little creativity (usually involving Cartesian products) along with the vast array of vendor-specific functions, you can accomplish quite a bit. This chapter is where the book begins to get interesting. Some of the more interesting examples include counting the occurrences of a character in a string, creating delimited lists from table rows, converting delimited lists and strings into rows, and separating numeric and character data from a string of alphanumeric characters.
Chapter 7, Working with Numbers, introduces recipes for common number crunching. The recipes found here are extremely common and you’ll learn how easily window functions solve problems involving moving calculations and aggregations. Examples include creating running totals; finding mean, median, and mode; calculating percentiles; and accounting for NULL while performing aggregations.
Chapter 8, Date Arithmetic, is the first of two chapters dealing with dates. Being able to perform simple date arithmetic is crucial to everyday tasks. Examples include determining the number of business days between two dates, calculating the difference between two dates in different units of time (day, month, year, etc.), and counting occurrences of days in a month.
Chapter 9, Date Manipulation, is the second of the two chapters dealing with dates. In this chapter you will find recipes for some of the most common date operations you will encounter in a typical work day. Examples include returning all days in a year, finding leap years, finding first and last days of a month, creating a calendar, and filling in missing dates for a range of dates.
Chapter 10, Working with Ranges, introduces recipes for identifying values in ranges, and for creating ranges of values. Examples include automatically generating a sequence of rows, filling in missing numeric values for a range of values, locating the beginning and end of a range of values, and locating consecutive values.
Chapter 11, Advanced Searching, introduces recipes that are crucial for everyday development and yet sometimes slip through the cracks. These recipes are not any more difficult than others, yet I see many developers making very inefficient attempts at solving the problems these recipes solve. Examples from this chapter include finding knight values, paginating through a result set, skipping rows from a table, finding reciprocals, selecting the top n records, and ranking results.
Chapter 12, Reporting and Warehousing, introduces queries typically used in warehousing or generating complex reports. This chapter was meant to be the majority of the book as it existed in my original vision. Examples include converting rows into columns and vice versa (cross-tab reports), creating buckets or groups of data, creating histograms, calculating simple and complete subtotals, performing aggregations over a moving window of rows, and grouping rows based on given units of time.
Chapter 13, Hierarchical Queries, introduces hierarchical recipes. Regardless of how your data is modeled, at some point you will be asked to format data such that it represents a tree or parent-child relationship. This chapter provides recipes accomplishing these tasks. Creating tree-structured result sets can be cumbersome with traditional SQL, so vendor-supplied functions are particularly useful in this chapter. Examples include expressing a parent-child relationship, traversing a hierarchy from root to leaf, and rolling up a hierarchy.
Chapter 14, Odds ‘n’ Ends, is a collection of miscellaneous recipes that didn’t seem to fit into any other problem domain, but that nevertheless are interesting and useful. This chapter is different from the rest in that it focuses on vendor-specific solutions only. This is the only chapter of the book where each recipe highlights only one vendor. The reasons are twofold: first, this chapter was meant to serve as more of a fun, geeky chapter. Second, some recipes exist only to highlight a vendor-specific function that has no equivalent in the other RDBMSs (examples include SQL Server’s PIVOT/UNPIVOT operators and Oracle’s MODEL clause). In some cases, though, you’ll be able to easily tweak a solution provided in this chapter to work for a platform not covered in the recipe.
Appendix A, Window Function Refresher, is a window function refresher along with a solid discussion of groups in SQL. Window functions are new to most, so it is appropriate that this appendix serves as a brief tutorial. Additionally, in my experience I have noticed that the use of GROUP BY in queries is a source of confusion for many developers. This chapter defines exactly what a SQL group is, and then proceeds to use various queries as proofs to validate that definition. The chapter then goes into the effects of NULLs on groups, aggregates, and partitions. Lastly, you’ll find discussion on the more obscure and yet extremely powerful syntax of the window function’s OVER clause (i.e., the “framing” or “windowing” clause).
Appendix B, Rozenshtein Revisited, is a tribute to David Rozenshtein, to whom I owe my success in SQL development. Rozenshtein’s book, The Essence of SQL (Coriolis Group Books) was the first book I purchased on SQL that was not required by a class. It was from that book that I learned how to “think in SQL.” To this day I attribute much of my understanding of how SQL works to David’s book. It truly is different from any other SQL book I’ve read, and I’m grateful that it was the first one I picked up on my own volition. Appendix B focuses on some of the queries presented in The Essence of SQL, and provides alternative solutions using window functions (which weren’t available when The Essence of SQL was written) for those queries.
Platform and Version
SQL is a moving target. Vendors are constantly pumping new features and functionality into their products. Thus you should know up front which versions of the various platforms were used in the preparation of this text:
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
MySQL 5
Tables Used in This Book
The majority of the examples in this book involve the use of two tables, EMP and DEPT. The EMP table is a simple 14-row table with only numeric, string, and date fields. The DEPT table is a simple four-row table with only numeric and string fields. These tables appear in many old database texts, and the many-to-one relationship between departments and employees is well understood.
While I’m on the topic of the example tables, I want to mention that all but a very few solutions in this book run against these tables. Nowhere do I tweak my example data to set up a solution that you would be unlikely to have a chance of implementing in the real world, as some books do.
And while I’m on the topic of solutions, let me just mention that whenever possible I’ve tried to provide a generic solution that will run on all five RDBMSs covered in this book. Often that’s not possible. Even so, in many cases more than one vendor shares a solution. Because of their mutual support for window functions, for example, Oracle and DB2 often share solutions. Whenever solutions are shared, or at least are very similar, discussions are shared as well.
The contents of EMP and DEPT are shown below, respectively:
select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ---- ----------- ---- ---- ------- 7369 SMITH CLERK 7902 17-DEC-1980 800 20 7499 ALLEN SALESMAN 7698 20-FEB-1981 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-1981 1250 500 30 7566 JONES MANAGER 7839 02-APR-1981 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-1981 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-1981 2850 30 7782 CLARK MANAGER 7839 09-JUN-1981 2450 10 7788 SCOTT ANALYST 7566 09-DEC-1982 3000 20 7839 KING PRESIDENT 17-NOV-1981 5000 10 7844 TURNER SALESMAN 7698 08-SEP-1981 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-1983 1100 20 7900 JAMES CLERK 7698 03-DEC-1981 950 30 7902 FORD ANALYST 7566 03-DEC-1981 3000 20 7934 MILLER CLERK 7782 23-JAN-1982 1300 10select * from dept;DEPTNO DNAME LOC ------ -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
Additionally, you will find four pivot tables used in this book; T1, T10, T100, and T500. Because these tables exist only to facilitate pivots, I did not find it necessary to give them clever names. The number following the “T” in each of the pivot tables signifies the number of rows in each table starting from 1. For example, the values for T1 and T10:
select id from t1; ID ---------- 1 select id from t10; ID ---------- 1 2 3 4 5 6 7 8 9 10
As an aside, some vendors allow partial SELECT statements. For example, you can have SELECT without a FROM clause. I don’t particularly like this, thus I select against a support table, T1, with a single row, rather than using partial queries.
Any other tables are specific to particular recipes and chapters, and will be introduced in the text when appropriate.
Conventions Used in This Book
I use a number of typographical and coding conventions in this book. Take time to become familiar with them. Doing so will enhance your understanding of the text. Coding conventions in particular are important, because I can’t discuss them anew for each recipe in the book. Instead, I list the important conventions here.
Typographical Conventions
The following typographical conventions are used in this book:
- UPPERCASE
Used to indicate SQL keywords within text
- lowercase
Used for all queries in code examples. Other languages such as C and JAVA use lowercase for most keywords and I find it infinitely more readable than uppercase. Thus all queries will be lowercase.
Constant width boldIndicates user input in examples showing an interaction.
Coding Conventions
My preference for case in SQL statements is to always use lowercase, for both keywords and user-specified identifiers. For example:
select empno, ename from emp;
Your preference may be otherwise. For example, many prefer to uppercase SQL keywords. Use whatever coding style you prefer, or whatever your project requires.
Despite my use of lowercase in code examples, I consistently uppercase SQL keywords and identifiers in the text. I do this to make those items stand out as something other than regular prose. For example:
The preceding query represents a SELECT against the EMP table.
While this book covers databases from five different vendors, I’ve decided to use one format for all the output:
EMPNO ENAME ----- ------ 7369 SMITH 7499 ALLEN …
Many solutions make use of inline views, or subqueries in the FROM clause. The ANSI SQL standard requires that such views be given table aliases. (Oracle is the only vendor that lets you get away without specifying such aliases.) Thus, my solutions use aliases such as x and y to identify the result sets from inline views:
select job, sal
from (select job, max(sal) sal
from emp
group by job)x;Notice the letter X following the final, closing parenthesis. That letter X becomes the name of the “table” returned by the subquery in the FROM clause. While column aliases are a valuable tool for writing self-documenting code, aliases on inline views (for most recipes in this book) are simply formalities. They are typically given trivial names such as X, Y, Z, TMP1, and TMP2. In cases where I feel a better alias will provide more understanding, I do so.
You will notice that the SQL in the SOLUTION section of the recipes is typically numbered, for example:
1 select ename 2 from emp 3 where deptno = 10
The number is not part of the syntax; I have included it so I can reference parts of the query by number in the discussion section.
Using Code Examples
This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact O’Reilly for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.
We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: SQL Cookbook, by Anthony Molinaro. Copyright 2006 O’Reilly Media, Inc., 0-596-00976-3.
If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at permissions@oreilly.com.
Comments and Questions
We have tested and verified the information in this book to the best of our ability, but you may find that features have changed or that we have made mistakes. If so, please notify us by writing to:
| O’Reilly Media, Inc. |
| 1005 Gravenstein Highway North |
| Sebastopol, CA 95472 |
| (800) 998-9938 (in the United States or Canada) |
| (707) 829-0515 (international or local) |
| (707) 829-0104 (fax) |
You can also send messages electronically. To be put on the mailing list or request a catalog, send email to:
| info@oreilly.com |
To ask technical questions or comment on the book, or to suggest additional recipes for future editions, send email to:
| bookquestions@oreilly.com |
We have a web site for this book where you can find examples and errata (previously reported errors and corrections are available for public view there). You can access this page at:
| http://www.oreilly.com/catalog/sqlckbk |
Safari® Enabled
When you see a Safari® Enabled icon on the cover of your favorite technology book, it means the book is available online through the O’Reilly Network Safari Bookshelf.
Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top technology books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.
Acknowledgments
This book would not exist without all the support I’ve received from a great many people. I would like to thank my mother, Connie, to whom this book is dedicated. Without your hard work and sacrifice I would not be where I am today. Thank you for everything, Mom. I am thankful and appreciative of everything you’ve done for my brother and me. I have been blessed to have you as my mother.
To my brother, Joe: every time I came home from Baltimore to take a break from writing, you were there to remind me how great things are when we’re not working, and how I should finish writing so I can get back to the more important things in life. You’re a good man and I respect you. I am extremely proud of you, and proud to call you my brother.
To my wonderful fiancee, Georgia: Without your support I would not have made it through all 600-plus pages of this book. You were here sharing this experience with me, day after day. I know it was just as hard on you as it was on me. I spent all day working and all night writing, but you were great through it all. You were understanding and supportive and I am forever grateful. Thank you. I love you.
To my future in-laws: to my mother-in-law and father-in-law, Kiki and George. Thank you for your support throughout this whole experience. You always made me feel at home whenever I took a break and came to visit, and you made sure Georgia and I were always well fed. To my sister-in-laws, Anna and Kathy, it was always fun coming home and hanging out with you guys, giving Georgia and I a much needed break from the book and from Baltimore.
To my editor Jonathan Gennick, without whom this book would not exist. Jonathan, you deserve a tremendous amount of credit for this book. You went above and beyond what an editor would normally do and for that you deserve much thanks. From supplying recipes, to tons of rewrites, to keeping things humorous despite oncoming deadlines, I could not have done it without you. I am grateful to have had you as my editor and grateful for the opportunity you have given me. An experienced DBA and author yourself, it was a pleasure to work with someone of your technical level and expertise. I can’t imagine there are too many editors out there that can, if they decided to, stop editing and work practically anywhere as a database administrator (DBA); Jonathan can. Being a DBA certainly gives you an edge as an editor as you usually know what I want to say even when I’m having trouble expressing it. O’Reilly is lucky to have you on staff and I am lucky to have you as an editor.
I would like to thank Ales Spetic and Jonathan Gennick for Transact-SQL Cookbook. Isaac Newton famously said, “If I have seen a little further it is by standing on the shoulders of giants.” In the acknowledgments section of the Transact-SQL Cookbook, Ales Spetic wrote something that is a testament to this famous quote and I feel should be in every SQL book. I include it here:
I hope that this book will complement the exiting opuses of outstanding authors like Joe Celko, David Rozenshtein, Anatoly Abramovich, Eugine Berger, Iztik Ben-Gan, Richard Snodgrass, and others. I spent many nights studying their work, and I learned almost everything I know from their books. As I am writing these lines, I’m aware that for every night I spent discovering their secrets, they must have spent 10 nights putting their knowledge into a consistent and readable form. It is an honor to be able to give something back to the SQL community.
I would like to thank Sanjay Mishra for his excellent Mastering Oracle SQL book, and also for putting me in touch with Jonathan. If not for Sanjay, I may have never been in touch with Jonathan and never would have written this book. Amazing how a simple email can change your life. I would like to thank David Rozenshtein, especially, for his Essence of SQL book, which provided me with a solid understanding of how to think and problem solve in sets/SQL. I would like to thank David Rozenshtein, Anatoly Abramovich, and Eugene Birger for their book Optimizing Transact-SQL, from which I learned many of the advanced SQL techniques I use today.
I would like to thank the whole team at Wireless Generation, a great company with great people. A big thank you to all of the people who took the time to review, critique, or offer advice to help me complete this book: Jesse Davis, Joel Patterson, Philip Zee, Kevin Marshall, Doug Daniels, Otis Gospodnetic, Ken Gunn, John Stewart, Jim Abramson, Adam Mayer, Susan Lau, Alexis Le-Quoc, and Paul Feuer. I would like to thank Maggie Ho for her careful review of my work and extremely useful feedback regarding the window function refresher. I would like to thank Chuck Van Buren and Gillian Gutenberg for their great advice about running. Early morning workouts helped me clear my mind and unwind. I don’t think I would have been able to finish this book without getting out a bit. I would like to thank Steve Kang and Chad Levinson for putting up with all my incessant talk about different SQL techniques on the nights when all they wanted was to head to Union Square to get a beer and a burger at Heartland Brewery after a long day of work. I would like to thank Aaron Boyd for all his support, kind words, and, most importantly, good advice. Aaron is honest, hardworking, and a very straightforward guy; people like him make a company better. I would like to thank Olivier Pomel for his support and help in writing this book, in particular for the DB2 solution for creating delimited lists from rows. Olivier contributed that solution without even having a DB2 system to test it with! I explained to him how the WITH clause worked, and minutes later he came up with the solution you see in this book.
Jonah Harris and David Rozenshtein also provided helpful technical review feedback on the manuscript. And Arun Marathe, Nuno Pinto do Souto, and Andrew Odewahn weighed in on the outline and choice of recipes while this book was in its formative stages. Thanks, very much, to all of you.
I want to thank John Haydu and the MODEL clause development team at Oracle Corporation for taking the time to review the MODEL clause article I wrote for O’Reilly, and for ultimately giving me a better understanding of how that clause works. I would like to thank Tom Kyte of Oracle Corporation for allowing me to adapt his TO_BASE function into a SQL-only solution. Bruno Denuit of Microsoft answered questions I had regarding the functionality of the window functions introduced in SQL Server 2005. Simon Riggs of PostgreSQL kept me up to date about new SQL features in PostgreSQL (very big thanks: Simon, by knowing what was coming out and when, I was able to incorporate some new SQL features such as the ever-so-cool GENERATE_SERIES function, which I think made for more elegant solutions compared to pivot tables).
Last but certainly not least, I’d like to thank Kay Young. When you are talented and passionate about what you do, it is great to be able to work with people who are likewise as talented and passionate. Many of the recipes you see in this text have come from working with Kay and coming up with SQL solutions for everyday problems at Wireless Generation. I want to thank you and let you know I absolutely appreciate all the help you given me throughout all of this; from advice, to grammar corrections, to code, you played an integral role in the writing of this book. It’s been great working with you, and Wireless Generation is a better company because you are there.
—Anthony Molinaro
September 2005
Get SQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
