SQL cookbook [query solutions and techniques for database developers covers SQL server, PostgreSQL, Oracle, MySQL, and DB2] (Anthony Molinaro) (Z-Library)

SQL Cookbook ™

Other resources from O’Reilly Related titles SQL Tuning Mastering Oracle SQL SQL Pocket Reference SQL in a Nutshell MySQL Cookbook™ Learning SQL Database in Depth Transact-SQL Cookbook™ oreilly.com oreilly.com is more than a complete catalog of O’Reilly books. You’ll also find links to news, events, articles, weblogs, sample chapters, and code examples. oreillynet.com is the essential portal for developers interested in open and emerging technologies, including new platforms, pro- gramming languages, and operating systems. Conferences O’Reilly brings diverse innovators together to nurture the ideas that spark revolutionary industries. We specialize in docu- menting the latest tools and systems, translating the innovator’s knowledge into useful skills for those in the trenches. Visit conferences.oreilly.com for our upcoming events. Safari Bookshelf (safari.oreilly.com) is the premier online refer- ence library for programmers and IT professionals. Conduct searches across more than 1,000 books. Subscribers can zero in on answers to time-critical questions in a matter of seconds. Read the books on your Bookshelf from cover to cover or sim- ply flip to the page you need. Try it today for free.

SQL Cookbook™ Anthony Molinaro Beijing • Cambridge • Farnham • Köln • Sebastopol • Tokyo

SQL Cookbook™ by Anthony Molinaro Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472. O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (safari.oreilly.com). For more information, contact our corporate/insti- tutional sales department: (800) 998-9938 or corporate@oreilly.com. Editor: Jonathan Gennick Production Editor: Darren Kelly Production Services: nSight, Inc. Cover Designer: Karen Montgomery Interior Designer: David Futato Printing History: December 2005: First Edition. Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. The Cookbook series designations, SQL Cookbook, the image of an Agamid lizard, and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc. was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. ISBN: 978-0-596-00976-2 [LSI] [2012-12-14]

To my mom: You’re the best! Thank you for everything.

vii Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii 1. Retrieving Records . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Retrieving All Rows and Columns from a Table 1 1.2 Retrieving a Subset of Rows from a Table 2 1.3 Finding Rows That Satisfy Multiple Conditions 2 1.4 Retrieving a Subset of Columns from a Table 3 1.5 Providing Meaningful Names for Columns 3 1.6 Referencing an Aliased Column in the WHERE Clause 4 1.7 Concatenating Column Values 5 1.8 Using Conditional Logic in a SELECT Statement 7 1.9 Limiting the Number of Rows Returned 8 1.10 Returning n Random Records from a Table 9 1.11 Finding Null Values 11 1.12 Transforming Nulls into Real Values 11 1.13 Searching for Patterns 12 2. Sorting Query Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 2.1 Returning Query Results in a Specified Order 14 2.2 Sorting by Multiple Fields 15 2.3 Sorting by Substrings 16 2.4 Sorting Mixed Alphanumeric Data 17 2.5 Dealing with Nulls When Sorting 20 2.6 Sorting on a Data Dependent Key 26

viii | Table of Contents 3. Working with Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 3.1 Stacking One Rowset Atop Another 28 3.2 Combining Related Rows 30 3.3 Finding Rows in Common Between Two Tables 31 3.4 Retrieving Values from One Table That Do Not Exist in Another 33 3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another 38 3.6 Adding Joins to a Query Without Interfering with Other Joins 40 3.7 Determining Whether Two Tables Have the Same Data 42 3.8 Identifying and Avoiding Cartesian Products 49 3.9 Performing Joins When Using Aggregates 50 3.10 Performing Outer Joins When Using Aggregates 55 3.11 Returning Missing Data from Multiple Tables 58 3.12 Using NULLs in Operations and Comparisons 62 4. Inserting, Updating, Deleting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 4.1 Inserting a New Record 64 4.2 Inserting Default Values 64 4.3 Overriding a Default Value with NULL 66 4.4 Copying Rows from One Table into Another 66 4.5 Copying a Table Definition 67 4.6 Inserting into Multiple Tables at Once 68 4.7 Blocking Inserts to Certain Columns 70 4.8 Modifying Records in a Table 71 4.9 Updating When Corresponding Rows Exist 72 4.10 Updating with Values from Another Table 73 4.11 Merging Records 77 4.12 Deleting All Records from a Table 78 4.13 Deleting Specific Records 79 4.14 Deleting a Single Record 79 4.15 Deleting Referential Integrity Violations 80 4.16 Deleting Duplicate Records 80 4.17 Deleting Records Referenced from Another Table 82 5. Metadata Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 5.1 Listing Tables in a Schema 84 5.2 Listing a Table’s Columns 85 5.3 Listing Indexed Columns for a Table 86 5.4 Listing Constraints on a Table 88

Table of Contents | ix 5.5 Listing Foreign Keys Without Corresponding Indexes 89 5.6 Using SQL to Generate SQL 93 5.7 Describing the Data Dictionary Views in an Oracle Database 95 6. Working with Strings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 6.1 Walking a String 97 6.2 Embedding Quotes Within String Literals 100 6.3 Counting the Occurrences of a Character in a String 101 6.4 Removing Unwanted Characters from a String 102 6.5 Separating Numeric and Character Data 103 6.6 Determining Whether a String Is Alphanumeric 107 6.7 Extracting Initials from a Name 112 6.8 Ordering by Parts of a String 116 6.9 Ordering by a Number in a String 117 6.10 Creating a Delimited List from Table Rows 123 6.11 Converting Delimited Data into a Multi-Valued IN-List 129 6.12 Alphabetizing a String 135 6.13 Identifying Strings That Can Be Treated As Numbers 141 6.14 Extracting the nth Delimited Substring 147 6.15 Parsing an IP Address 154 7. Working with Numbers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 7.1 Computing an Average 157 7.2 Finding the Min/Max Value in a Column 159 7.3 Summing the Values in a Column 161 7.4 Counting Rows in a Table 162 7.5 Counting Values in a Column 165 7.6 Generating a Running Total 165 7.7 Generating a Running Product 168 7.8 Calculating a Running Difference 171 7.9 Calculating a Mode 172 7.10 Calculating a Median 175 7.11 Determining the Percentage of a Total 179 7.12 Aggregating Nullable Columns 182 7.13 Computing Averages Without High and Low Values 183 7.14 Converting Alphanumeric Strings into Numbers 185 7.15 Changing Values in a Running Total 187

x | Table of Contents 8. Date Arithmetic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 8.1 Adding and Subtracting Days, Months, and Years 190 8.2 Determining the Number of Days Between Two Dates 193 8.3 Determining the Number of Business Days Between Two Dates 195 8.4 Determining the Number of Months or Years Between Two Dates 200 8.5 Determining the Number of Seconds, Minutes, or Hours Between Two Dates 202 8.6 Counting the Occurrences of Weekdays in a Year 204 8.7 Determining the Date Difference Between the Current Record and the Next Record 216 9. Date Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 9.1 Determining If a Year Is a Leap Year 222 9.2 Determining the Number of Days in a Year 229 9.3 Extracting Units of Time from a Date 232 9.4 Determining the First and Last Day of a Month 235 9.5 Determining All Dates for a Particular Weekday Throughout a Year 237 9.6 Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month 244 9.7 Creating a Calendar 251 9.8 Listing Quarter Start and End Dates for the Year 270 9.9 Determining Quarter Start and End Dates for a Given Quarter 275 9.10 Filling in Missing Dates 282 9.11 Searching on Specific Units of Time 291 9.12 Comparing Records Using Specific Parts of a Date 292 9.13 Identifying Overlapping Date Ranges 295 10. Working with Ranges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301 10.1 Locating a Range of Consecutive Values 301 10.2 Finding Differences Between Rows in the Same Group or Partition 306 10.3 Locating the Beginning and End of a Range of Consecutive Values 315 10.4 Filling in Missing Values in a Range of Values 320 10.5 Generating Consecutive Numeric Values 324 11. Advanced Searching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328 11.1 Paginating Through a Result Set 328 11.2 Skipping n Rows from a Table 331 11.3 Incorporating OR Logic When Using Outer Joins 334 11.4 Determining Which Rows Are Reciprocals 337 11.5 Selecting the Top n Records 338

Table of Contents | xi 11.6 Finding Records with the Highest and Lowest Values 340 11.7 Investigating Future Rows 342 11.8 Shifting Row Values 345 11.9 Ranking Results 348 11.10 Suppressing Duplicates 350 11.11 Finding Knight Values 352 11.12 Generating Simple Forecasts 359 12. Reporting and Warehousing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 12.1 Pivoting a Result Set into One Row 368 12.2 Pivoting a Result Set into Multiple Rows 370 12.3 Reverse Pivoting a Result Set 378 12.4 Reverse Pivoting a Result Set into One Column 380 12.5 Suppressing Repeating Values from a Result Set 383 12.6 Pivoting a Result Set to Facilitate Inter-Row Calculations 387 12.7 Creating Buckets of Data, of a Fixed Size 388 12.8 Creating a Predefined Number of Buckets 392 12.9 Creating Horizontal Histograms 397 12.10 Creating Vertical Histograms 399 12.11 Returning Non-GROUP BY Columns 403 12.12 Calculating Simple Subtotals 408 12.13 Calculating Subtotals for All Possible Expression Combinations 412 12.14 Identifying Rows That Are Not Subtotals 421 12.15 Using Case Expressions to Flag Rows 423 12.16 Creating a Sparse Matrix 425 12.17 Grouping Rows by Units of Time 426 12.18 Performing Aggregations over Different Groups/Partitions Simultaneously 430 12.19 Performing Aggregations over a Moving Range of Values 432 12.20 Pivoting a Result Set with Subtotals 439 13. Hierarchical Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444 13.1 Expressing a Parent-Child Relationship 445 13.2 Expressing a Child-Parent-Grandparent Relationship 448 13.3 Creating a Hierarchical View of a Table 454 13.4 Finding All Child Rows for a Given Parent Row 462 13.5 Determining Which Rows Are Leaf, Branch, or Root Nodes 466

xii | Table of Contents 14. Odds ‘n’ Ends . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474 14.1 Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator 474 14.2 Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator 476 14.3 Transposing a Result Set Using Oracle’s MODEL Clause 478 14.4 Extracting Elements of a String from Unfixed Locations 482 14.5 Finding the Number of Days in a Year (an Alternate Solution for Oracle) 485 14.6 Searching for Mixed Alphanumeric Strings 486 14.7 Converting Whole Numbers to Binary Using Oracle 489 14.8 Pivoting a Ranked Result Set 492 14.9 Adding a Column Header into a Double Pivoted Result Set 496 14.10 Converting a Scalar Subquery to a Composite Subquery in Oracle 507 14.11 Parsing Serialized Data into Rows 509 14.12 Calculating Percent Relative to Total 513 14.13 Creating CSV Output from Oracle 515 14.14 Finding Text Not Matching a Pattern (Oracle) 520 14.15 Transforming Data with an Inline View 523 14.16 Testing for Existence of a Value Within a Group 524 A. Window Function Refresher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529 B. Rozenshtein Revisited . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 591

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. xiii 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 practi- tioners use SQL in a perfunctory manner, and are unaware of the power at their dis- posal. 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 cov- ered in this book. Indeed, such coverage would be impossible. You will, however, find many common problems and their solutions. And in those solutions lie tech- niques that you’ll learn how to expand upon and apply to other, new problems that I never thought to cover. 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.

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. xiv | Preface 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 Can- tor, who was the first to realize the vast benefit of studying sets of elements (study- ing 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 shar- ing his ideas, others such as Ernst Zermelo, Gottlob Frege, Abraham Fraenkel, Tho- ralf 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 pro- cedural 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 particu- lar 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.

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. Preface | xv 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 serial- ized string into a result set. The recipes in this book explain techniques for per- forming 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 rain- bow (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 rela- tional database management systems (RDBMSs) and displays their solutions side by side. Here’s your chance to expand your knowledge base.

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. xvi | Preface 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 spe- cial 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 guide- lines 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 sup- plement 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 com- monality 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 pro- duction code until you fully understand them and how they will perform against your particular data.

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. Preface | xvii • 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 avail- able 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 com- plete 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 cod- ing 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

This is the Title of the Book, eMatter Edition Copyright © 2012 O’Reilly & Associates, Inc. All rights reserved. xviii | Preface 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 pro- vide 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 pur- chase 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. Stor- ing XML documents in relational databases is becoming increasingly popular, and each RDBMS has their own extensions and tools for retrieving and manipu- lating such data. XML manipulation often involves code that is procedural and thus outside the scope of this book. Recent developments such as XQUERY rep- resent 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 data- bases. At the present time, the object-oriented features available from some ven- dors are more suitable for use in procedural programming than in the sort of set- oriented 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.