SQL Cookbook Query Solutions and Techniques for All SQL Users (Anthony Molinaro, Robert de Graaf) (Z-Library)

Anthony Molinaro and Robert de Graaf SQL Cookbook Query Solutions and Techniques for All SQL Users SECOND EDITION

978-1-098-10014-8 [LSI] SQL Cookbook by Anthony Molinaro and Robert de Graaf Copyright © 2021 Robert de Graaf. 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 (http://oreilly.com). For more information, contact our corporate/institutional sales department: 800-998-9938 or corporate@oreilly.com. Acquisitions Editor: Jessica Haberman Development Editor: Virginia Wilson Production Editor: Kate Galloway Copyeditor: Kim Wimpsett Proofreader: nSight, Inc. Indexer: WordCo Indexing Services, Inc. Interior Designer: David Futato Cover Designer: Karen Montgomery Illustrator: O’Reilly Media December 2005: First Edition December 2020: Second Edition Revision History for the Second Edition 2020-11-03: First Release See http://oreilly.com/catalog/errata.csp?isbn=9781492077442 for release details. The O’Reilly logo is a registered trademark of O’Reilly Media, Inc. SQL Cookbook, the cover image, and related trade dress are trademarks of O’Reilly Media, Inc. The views expressed in this work are those of the authors, and do not represent the publisher’s views. While the publisher and the authors have used good faith efforts to ensure that the information and instructions contained in this work are accurate, the publisher and the authors disclaim all responsibility for errors or omissions, including without limitation responsibility for damages resulting from the use of or reliance on this work. Use of the information and instructions contained in this work is at your own risk. If any code samples or other technology this work contains or describes is subject to open source licenses or the intellectual property rights of others, it is your responsibility to ensure that your use thereof complies with such licenses and/or rights. This work is part of a collaboration between O’Reilly and Yugabyte. See our statement of editorial inde‐ pendence.

To my mom: You’re the best! Thank you for everything. —Anthony To Clare, Maya, and Leda. —Robert

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 4 1.6 Referencing an Aliased Column in the WHERE Clause 5 1.7 Concatenating Column Values 6 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 10 1.11 Finding Null Values 11 1.12 Transforming Nulls into Real Values 12 1.13 Searching for Patterns 13 1.14 Summing Up 14 2. Sorting Query Results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.1 Returning Query Results in a Specified Order 15 2.2 Sorting by Multiple Fields 16 2.3 Sorting by Substrings 17 2.4 Sorting Mixed Alphanumeric Data 18 2.5 Dealing with Nulls When Sorting 21 2.6 Sorting on a Data-Dependent Key 27 2.7 Summing Up 28 vii

3. Working with Multiple Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 3.1 Stacking One Rowset atop Another 29 3.2 Combining Related Rows 31 3.3 Finding Rows in Common Between Two Tables 33 3.4 Retrieving Values from One Table That Do Not Exist in Another 34 3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another 40 3.6 Adding Joins to a Query Without Interfering with Other Joins 42 3.7 Determining Whether Two Tables Have the Same Data 44 3.8 Identifying and Avoiding Cartesian Products 51 3.9 Performing Joins When Using Aggregates 52 3.10 Performing Outer Joins When Using Aggregates 57 3.11 Returning Missing Data from Multiple Tables 60 3.12 Using NULLs in Operations and Comparisons 64 3.13 Summing Up 65 4. Inserting, Updating, and Deleting. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 4.1 Inserting a New Record 68 4.2 Inserting Default Values 68 4.3 Overriding a Default Value with NULL 70 4.4 Copying Rows from One Table into Another 70 4.5 Copying a Table Definition 71 4.6 Inserting into Multiple Tables at Once 72 4.7 Blocking Inserts to Certain Columns 74 4.8 Modifying Records in a Table 75 4.9 Updating When Corresponding Rows Exist 77 4.10 Updating with Values from Another Table 78 4.11 Merging Records 81 4.12 Deleting All Records from a Table 83 4.13 Deleting Specific Records 83 4.14 Deleting a Single Record 84 4.15 Deleting Referential Integrity Violations 85 4.16 Deleting Duplicate Records 85 4.17 Deleting Records Referenced from Another Table 87 4.18 Summing Up 89 5. Metadata Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 5.1 Listing Tables in a Schema 91 5.2 Listing a Table’s Columns 93 5.3 Listing Indexed Columns for a Table 94 5.4 Listing Constraints on a Table 95 5.5 Listing Foreign Keys Without Corresponding Indexes 97 viii | Table of Contents

5.6 Using SQL to Generate SQL 100 5.7 Describing the Data Dictionary Views in an Oracle Database 102 5.8 Summing Up 103 6. Working with Strings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 6.1 Walking a String 106 6.2 Embedding Quotes Within String Literals 108 6.3 Counting the Occurrences of a Character in a String 109 6.4 Removing Unwanted Characters from a String 110 6.5 Separating Numeric and Character Data 112 6.6 Determining Whether a String Is Alphanumeric 116 6.7 Extracting Initials from a Name 120 6.8 Ordering by Parts of a String 125 6.9 Ordering by a Number in a String 126 6.10 Creating a Delimited List from Table Rows 132 6.11 Converting Delimited Data into a Multivalued IN-List 136 6.12 Alphabetizing a String 141 6.13 Identifying Strings That Can Be Treated as Numbers 147 6.14 Extracting the nth Delimited Substring 153 6.15 Parsing an IP Address 160 6.16 Comparing Strings by Sound 162 6.17 Finding Text Not Matching a Pattern 164 6.18 Summing Up 167 7. Working with Numbers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 7.1 Computing an Average 169 7.2 Finding the Min/Max Value in a Column 171 7.3 Summing the Values in a Column 173 7.4 Counting Rows in a Table 175 7.5 Counting Values in a Column 177 7.6 Generating a Running Total 178 7.7 Generating a Running Product 179 7.8 Smoothing a Series of Values 181 7.9 Calculating a Mode 182 7.10 Calculating a Median 185 7.11 Determining the Percentage of a Total 187 7.12 Aggregating Nullable Columns 190 7.13 Computing Averages Without High and Low Values 191 7.14 Converting Alphanumeric Strings into Numbers 193 7.15 Changing Values in a Running Total 196 7.16 Finding Outliers Using the Median Absolute Deviation 197 7.17 Finding Anomalies Using Benford’s Law 201 Table of Contents | ix

7.18 Summing Up 203 8. Date Arithmetic. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205 8.1 Adding and Subtracting Days, Months, and Years 205 8.2 Determining the Number of Days Between Two Dates 208 8.3 Determining the Number of Business Days Between Two Dates 210 8.4 Determining the Number of Months or Years Between Two Dates 215 8.5 Determining the Number of Seconds, Minutes, or Hours Between Two Dates 218 8.6 Counting the Occurrences of Weekdays in a Year 220 8.7 Determining the Date Difference Between the Current Record and the Next Record 231 8.8 Summing Up 237 9. Date Manipulation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 9.1 Determining Whether a Year Is a Leap Year 240 9.2 Determining the Number of Days in a Year 246 9.3 Extracting Units of Time from a Date 249 9.4 Determining the First and Last Days of a Month 252 9.5 Determining All Dates for a Particular Weekday Throughout a Year 255 9.6 Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month 261 9.7 Creating a Calendar 268 9.8 Listing Quarter Start and End Dates for the Year 281 9.9 Determining Quarter Start and End Dates for a Given Quarter 286 9.10 Filling in Missing Dates 293 9.11 Searching on Specific Units of Time 301 9.12 Comparing Records Using Specific Parts of a Date 302 9.13 Identifying Overlapping Date Ranges 305 9.14 Summing Up 311 10. Working with Ranges. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 10.1 Locating a Range of Consecutive Values 313 10.2 Finding Differences Between Rows in the Same Group or Partition 317 10.3 Locating the Beginning and End of a Range of Consecutive Values 323 10.4 Filling in Missing Values in a Range of Values 326 10.5 Generating Consecutive Numeric Values 330 10.6 Summing Up 333 11. Advanced Searching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 11.1 Paginating Through a Result Set 335 11.2 Skipping n Rows from a Table 338 x | Table of Contents

11.3 Incorporating OR Logic When Using Outer Joins 339 11.4 Determining Which Rows Are Reciprocals 341 11.5 Selecting the Top n Records 343 11.6 Finding Records with the Highest and Lowest Values 344 11.7 Investigating Future Rows 345 11.8 Shifting Row Values 347 11.9 Ranking Results 350 11.10 Suppressing Duplicates 351 11.11 Finding Knight Values 353 11.12 Generating Simple Forecasts 359 11.13 Summing Up 367 12. Reporting and Reshaping. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 369 12.1 Pivoting a Result Set into One Row 369 12.2 Pivoting a Result Set into Multiple Rows 372 12.3 Reverse Pivoting a Result Set 377 12.4 Reverse Pivoting a Result Set into One Column 379 12.5 Suppressing Repeating Values from a Result Set 382 12.6 Pivoting a Result Set to Facilitate Inter-Row Calculations 384 12.7 Creating Buckets of Data, of a Fixed Size 386 12.8 Creating a Predefined Number of Buckets 388 12.9 Creating Horizontal Histograms 390 12.10 Creating Vertical Histograms 392 12.11 Returning Non-GROUP BY Columns 394 12.12 Calculating Simple Subtotals 397 12.13 Calculating Subtotals for All Possible Expression Combinations 400 12.14 Identifying Rows That Are Not Subtotals 410 12.15 Using Case Expressions to Flag Rows 412 12.16 Creating a Sparse Matrix 414 12.17 Grouping Rows by Units of Time 416 12.18 Performing Aggregations over Different Groups/Partitions Simultaneously 420 12.19 Performing Aggregations over a Moving Range of Values 422 12.20 Pivoting a Result Set with Subtotals 429 12.21 Summing Up 434 13. Hierarchical Queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 435 13.1 Expressing a Parent-Child Relationship 436 13.2 Expressing a Child-Parent-Grandparent Relationship 440 13.3 Creating a Hierarchical View of a Table 444 13.4 Finding All Child Rows for a Given Parent Row 449 13.5 Determining Which Rows Are Leaf, Branch, or Root Nodes 450 Table of Contents | xi

13.6 Summing Up 458 14. Odds ’n’ Ends. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 14.1 Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator 459 14.2 Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator 461 14.3 Transposing a Result Set Using Oracle’s MODEL Clause 463 14.4 Extracting Elements of a String from Unfixed Locations 467 14.5 Finding the Number of Days in a Year (an Alternate Solution for Oracle) 470 14.6 Searching for Mixed Alphanumeric Strings 472 14.7 Converting Whole Numbers to Binary Using Oracle 474 14.8 Pivoting a Ranked Result Set 477 14.9 Adding a Column Header into a Double Pivoted Result Set 481 14.10 Converting a Scalar Subquery to a Composite Subquery in Oracle 493 14.11 Parsing Serialized Data into Rows 495 14.12 Calculating Percent Relative to Total 500 14.13 Testing for Existence of a Value Within a Group 502 14.14 Summing Up 505 A. Window Function Refresher. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507 B. Common Table Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535 Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 xii | Table of Contents

Preface SQL is the lingua franca of the data professional. At the same time, it doesn’t always get the attention it deserves compared to the hot tool du jour. As result, it’s common to find people who use SQL frequently but rarely or never go beyond the simplest queries, often enough because they believe that’s all there is. This book shows how much SQL can do, expanding users’ tool boxes. By the end of the book you will have seen how SQL can be used for statistical analysis; to do report‐ ing in a manner similar to Business Intelligence tools; to match text data; to perform sophisticated analysis on date data; and much more. The first edition of SQL Cookbook has been a popular choice as the “second book on SQL”—the book people read after they learn the basics—since its original release. It has many strengths, such as its wide range of topics and its friendly style. However, computing is known to move fast, even when it comes to something as mature as SQL, which has roots going back to the 1970s. While this new edition doesn’t cover brand new language features, an important change is that features that were novel at the time of the first edition, and found in some implementations and not in others, are now stabilized and standardized. As a result, we have a lot more scope for developing standard solutions than was possible earlier. There are two key examples that are important to highlight. Common table expres‐ sions (CTEs), including recursive CTEs, were available in a couple of implementa‐ tions at the time the first edition was released, but are now available in all five. They were introduced to solve some practical limitations of SQL, some of which can be seen directly in these recipes. A new appendix on recursive CTEs in this edition underlines their importance and explains their relevance. Window functions were also new enough at the time of the first edition’s release that they weren’t available in every implementation. They were also new enough that a special appendix was written to explain them, which remains. Now, however, window functions are in all implementations in this book. They are also in every other SQL xiii

implementation that we’re aware of, although there are so many databases out there, it’s impossible to guarantee there isn’t one that neglects window functions and/or CTEs. In addition to standardizing queries where possible, we’ve brought new material into Chapters 6 and 7. The material in Chapter 7 unlocks new data analysis applications in recipes about the median absolute deviation and Benford’s law. In Chapter 6, we have a new recipe to help match data by the sound of the text, and we have moved material on regular expressions to Chapter 6 from Chapter 14. Who This Book Is For This book is meant to be for any SQL user who wants to take their queries further. In terms of ability, it’s meant for someone who knows at least some SQL—you might have read Alan Beaulieu’s Learning SQL, for example—and ideally you’ve had to write queries on data in the wild to answer a real-life problem. Other than those loose parameters, this is a book for all SQL users, including data engineers, data scientists, data visualization folk, BI people, etc. Some of these users may never or rarely access databases directly, but use their data visualization, BI, or statistical tool to query and fetch data. The emphasis is on practical queries that can solve real-world problems. Where a small amount of theory appears, it’s there to directly support the practical elements. What’s Missing from This Book This is a practical book, chiefly about using SQL to understand data. It doesn’t cover theoretical aspects of databases, database design, or the theory behind SQL except where needed to explain specific recipes or techniques. It also doesn’t cover extensions to databases to handle data types such as XML and JSON. There are other resources available for those specialist topics. Platform and Version SQL is a moving target. Vendors are constantly pumping new features and function‐ ality into their products. Thus, you should know up front which versions of the vari‐ ous platforms were used in the preparation of this text: • DB2 11.5 • Oracle Database 19c • PostgreSQL 12 xiv | Preface

• SQL Server 2017 • MySQL 8.0 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. All but a very few solutions in this book run against these tables. Nowhere do we tweak the 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. The contents of EMP and DEPT are shown here, respectively: select * from emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------ --------- ---- ----------- ---- ---- ------- 7369 SMITH CLERK 7902 17-DEC-2005 800 20 7499 ALLEN SALESMAN 7698 20-FEB-2006 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-2006 1250 500 30 7566 JONES MANAGER 7839 02-APR-2006 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-2006 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-2006 2850 30 7782 CLARK MANAGER 7839 09-JUN-2006 2450 10 7788 SCOTT ANALYST 7566 09-DEC-2007 3000 20 7839 KING PRESIDENT 17-NOV-2006 5000 10 7844 TURNER SALESMAN 7698 08-SEP-2006 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-2008 1100 20 7900 JAMES CLERK 7698 03-DEC-2006 950 30 7902 FORD ANALYST 7566 03-DEC-2006 3000 20 7934 MILLER CLERK 7782 23-JAN-2007 1300 10 select * from dept; DEPTNO DNAME LOC ------ -------------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON Preface | xv

Additionally, you will find four pivot tables used in this book: T1, T10, T100, and T500. Because these tables exist only to facilitate pivots, we didn’t 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, here are 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 The pivot tables are a useful shortcut when we need to create a series of rows to facili‐ tate a query. As an aside, some vendors allow partial SELECT statements. For example, you can have SELECT without a FROM clause. Sometimes in this book we will use a support table, T1, with a single row, rather than using partial queries for clarity. This is similar in usage to Oracle’s DUAL table, but by using the T1 table, we do the same thing in a standardized way across all the implementations we are looking at. Any other tables are specific to particular recipes and chapters and will be introduced in the text when appropriate. Conventions Used in This Book We 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 we can’t repeat them for each recipe in the book. Instead, we list the important conventions here. xvi | Preface

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 we find it far more readable than uppercase. Thus, all queries will be lowercase. Constant width bold Indicates user input in examples showing an interaction. Indicates a tip, suggestion, or general note. Indicates a warning or caution. Coding Conventions Our preference for case in SQL statements is to always use lowercase, for both key‐ words and user-specified identifiers. For example: select empno, ename from emp; Your preference may be otherwise. For example, many prefer to uppercase SQL key‐ words. Use whatever coding style you prefer, or whatever your project requires. Despite the use of lowercase in code examples, we consistently use uppercase for SQL keywords and identifiers in the text. We 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, we’ve decided to use one format for all the output: Preface | xvii

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, our 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 a better alias might provide more understanding, we use them. You will notice that the SQL in the “Solution” section of the recipes is typically num‐ bered, for example: 1 select ename 2 from emp 3 where deptno = 10 The number is not part of the syntax; it is just to reference parts of the query by num‐ ber in the “Discussion” section. O’Reilly Online Learning For more than 40 years, O’Reilly Media has provided technol‐ ogy and business training, knowledge, and insight to help companies succeed. Our unique network of experts and innovators share their knowledge and expertise through books, articles, and our online learning platform. O’Reilly’s online learning platform gives you on-demand access to live training courses, in-depth learning paths, interactive coding environments, and a vast collection of text and video from O’Reilly and 200+ other publishers. For more information, visit http://oreilly.com. xviii | Preface

How to Contact Us Please address comments and questions concerning this book to the publisher: 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) We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at https://oreil.ly/sql-ckbk-2e. Email bookquestions@oreilly.com to comment or ask technical questions about this book. For news and information about our books and courses, visit http://oreilly.com. Find us on Facebook: http://facebook.com/oreilly Follow us on Twitter: http://twitter.com/oreillymedia Watch us on YouTube: http://www.youtube.com/oreillymedia Second Edition Acknowledgments A bunch of great people have helped with this second edition. Thanks to Jess Haber‐ man, Virginia Wilson, Kate Galloway, and Gary O’Brien at O’Reilly. Thanks to Nicho‐ las Adams for repeatedly saving the day in Atlas. Many thanks to the tech reviewers: Alan Beaulieu, Scott Haines, and Thomas Nield. Finally, many thanks to my family—Clare, Maya, and Leda—for graciously bearing losing me to another book for a while. —Robert de Graaf First Edition Acknowledgments This book would not exist without all the support we’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. Preface | xix