SQL Cookbook: Query Solutions and Techniques for All SQL Users

Description

Yugabyte Copyright Table of Contents Preface Who This Book Is For What’s Missing from This Book Platform and Version Tables Used in This Book Conventions Used in This Book Typographical Conventions Coding Conventions O’Reilly Online Learning How to Contact Us Second Edition Acknowledgments First Edition Acknowledgments Chapter 1. Retrieving Records 1.1 Retrieving All Rows and Columns from a Table Problem Solution Discussion 1.2 Retrieving a Subset of Rows from a Table Problem Solution Discussion 1.3 Finding Rows That Satisfy Multiple Conditions Problem Solution Discussion 1.4 Retrieving a Subset of Columns from a Table Problem Solution Discussion 1.5 Providing Meaningful Names for Columns Problem Solution Discussion 1.6 Referencing an Aliased Column in the WHERE Clause Problem Solution Discussion 1.7 Concatenating Column Values Problem Solution Discussion 1.8 Using Conditional Logic in a SELECT Statement Problem Solution Discussion 1.9 Limiting the Number of Rows Returned Problem Solution Discussion 1.10 Returning n Random Records from a Table Problem Solution Discussion 1.11 Finding Null Values Problem Solution Discussion 1.12 Transforming Nulls into Real Values Problem Solution Discussion 1.13 Searching for Patterns Problem Solution Discussion 1.14 Summing Up Chapter 2. Sorting Query Results 2.1 Returning Query Results in a Specified Order Problem Solution Discussion 2.2 Sorting by Multiple Fields Problem Solution Discussion 2.3 Sorting by Substrings Problem Solution Discussion 2.4 Sorting Mixed Alphanumeric Data Problem Solution Discussion 2.5 Dealing with Nulls When Sorting Problem Solution Discussion 2.6 Sorting on a Data-Dependent Key Problem Solution Discussion 2.7 Summing Up Chapter 3. Working with Multiple Tables 3.1 Stacking One Rowset atop Another Problem Solution Discussion 3.2 Combining Related Rows Problem Solution Discussion 3.3 Finding Rows in Common Between Two Tables Problem Solution Discussion 3.4 Retrieving Values from One Table That Do Not Exist in Another Problem Solution Discussion 3.5 Retrieving Rows from One Table That Do Not Correspond to Rows in Another Problem Solution Discussion 3.6 Adding Joins to a Query Without Interfering with Other Joins Problem Solution Discussion See Also 3.7 Determining Whether Two Tables Have the Same Data Problem Solution Discussion 3.8 Identifying and Avoiding Cartesian Products Problem Solution Discussion 3.9 Performing Joins When Using Aggregates Problem Solution Discussion 3.10 Performing Outer Joins When Using Aggregates Problem Solution Discussion 3.11 Returning Missing Data from Multiple Tables Problem Solution Discussion 3.12 Using NULLs in Operations and Comparisons Problem Solution Discussion 3.13 Summing Up Chapter 4. Inserting, Updating, and Deleting 4.1 Inserting a New Record Problem Solution Discussion 4.2 Inserting Default Values Problem Solution Discussion 4.3 Overriding a Default Value with NULL Problem Solution Discussion 4.4 Copying Rows from One Table into Another Problem Solution Discussion 4.5 Copying a Table Definition Problem Solution Discussion 4.6 Inserting into Multiple Tables at Once Problem Solution Discussion 4.7 Blocking Inserts to Certain Columns Problem Solution Discussion 4.8 Modifying Records in a Table Problem Solution Discussion 4.9 Updating When Corresponding Rows Exist Problem Solution Discussion 4.10 Updating with Values from Another Table Problem Solution Discussion 4.11 Merging Records Problem Solution Discussion 4.12 Deleting All Records from a Table Problem Solution Discussion 4.13 Deleting Specific Records Problem Solution Discussion 4.14 Deleting a Single Record Problem Solution Discussion 4.15 Deleting Referential Integrity Violations Problem Solution Discussion 4.16 Deleting Duplicate Records Problem Solution Discussion 4.17 Deleting Records Referenced from Another Table Problem Solution Discussion 4.18 Summing Up Chapter 5. Metadata Queries 5.1 Listing Tables in a Schema Problem Solution Discussion 5.2 Listing a Table’s Columns Problem Solution Discussion 5.3 Listing Indexed Columns for a Table Problem Solution Discussion 5.4 Listing Constraints on a Table Problem Solution Discussion 5.5 Listing Foreign Keys Without Corresponding Indexes Problem Solution Discussion 5.6 Using SQL to Generate SQL Problem Solution Discussion 5.7 Describing the Data Dictionary Views in an Oracle Database Problem Solution Discussion 5.8 Summing Up Chapter 6. Working with Strings 6.1 Walking a String Problem Solution Discussion 6.2 Embedding Quotes Within String Literals Problem Solution Discussion 6.3 Counting the Occurrences of a Character in a String Problem Solution Discussion 6.4 Removing Unwanted Characters from a String Problem Solution Discussion 6.5 Separating Numeric and Character Data Problem Solution Discussion 6.6 Determining Whether a String Is Alphanumeric Problem Solution Discussion 6.7 Extracting Initials from a Name Problem Solution Discussion 6.8 Ordering by Parts of a String Problem Solution Discussion 6.9 Ordering by a Number in a String Problem Solution Discussion 6.10 Creating a Delimited List from Table Rows Problem Solution Discussion 6.11 Converting Delimited Data into a Multivalued IN-List Problem Solution Discussion 6.12 Alphabetizing a String Problem Solution Discussion 6.13 Identifying Strings That Can Be Treated as Numbers Problem Solution Discussion 6.14 Extracting the nth Delimited Substring Problem Solution Discussion 6.15 Parsing an IP Address Problem Solution Discussion 6.16 Comparing Strings by Sound Problem Solution Discussion 6.17 Finding Text Not Matching a Pattern Problem Solution Discussion 6.18 Summing Up Chapter 7. Working with Numbers 7.1 Computing an Average Problem Solution Discussion See Also 7.2 Finding the Min/Max Value in a Column Problem Solution Discussion See Also 7.3 Summing the Values in a Column Problem Solution Discussion See Also 7.4 Counting Rows in a Table Problem Solution Discussion See Also 7.5 Counting Values in a Column Problem Solution Discussion 7.6 Generating a Running Total Problem Solution Discussion 7.7 Generating a Running Product Problem Solution Discussion 7.8 Smoothing a Series of Values Problem Solution Discussion 7.9 Calculating a Mode Problem Solution Discussion See Also 7.10 Calculating a Median Problem Solution Discussion 7.11 Determining the Percentage of a Total Problem Solution Discussion 7.12 Aggregating Nullable Columns Problem Solution Discussion 7.13 Computing Averages Without High and Low Values Problem Solution Discussion 7.14 Converting Alphanumeric Strings into Numbers Problem Solution Discussion 7.15 Changing Values in a Running Total Problem Solution Discussion 7.16 Finding Outliers Using the Median Absolute Deviation Problem Solution Discussion 7.17 Finding Anomalies Using Benford’s Law Problem Solution Discussion 7.18 Summing Up Chapter 8. Date Arithmetic 8.1 Adding and Subtracting Days, Months, and Years Problem Solution Discussion 8.2 Determining the Number of Days Between Two Dates Problem Solution Discussion 8.3 Determining the Number of Business Days Between Two Dates Problem Solution Discussion 8.4 Determining the Number of Months or Years Between Two Dates Problem Solution Discussion 8.5 Determining the Number of Seconds, Minutes, or Hours Between Two Dates Problem Solution Discussion 8.6 Counting the Occurrences of Weekdays in a Year Problem Solution Discussion 8.7 Determining the Date Difference Between the Current Record and the Next Record Problem Solution Discussion 8.8 Summing Up Chapter 9. Date Manipulation 9.1 Determining Whether a Year Is a Leap Year Problem Solution Discussion 9.2 Determining the Number of Days in a Year Problem Solution Discussion 9.3 Extracting Units of Time from a Date Problem Solution Discussion 9.4 Determining the First and Last Days of a Month Problem Solution Discussion 9.5 Determining All Dates for a Particular Weekday Throughout a Year Problem Solution Discussion 9.6 Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month Problem Solution Discussion 9.7 Creating a Calendar Problem Solution Discussion 9.8 Listing Quarter Start and End Dates for the Year Problem Solution Discussion 9.9 Determining Quarter Start and End Dates for a Given Quarter Problem Solution Discussion 9.10 Filling in Missing Dates Problem Solution Discussion 9.11 Searching on Specific Units of Time Problem Solution Discussion 9.12 Comparing Records Using Specific Parts of a Date Problem Solution Discussion 9.13 Identifying Overlapping Date Ranges Problem Solution Discussion 9.14 Summing Up Chapter 10. Working with Ranges 10.1 Locating a Range of Consecutive Values Problem Solution Discussion 10.2 Finding Differences Between Rows in the Same Group or Partition Problem Solution 10.3 Locating the Beginning and End of a Range of Consecutive Values Problem Solution Discussion 10.4 Filling in Missing Values in a Range of Values Problem Solution Discussion 10.5 Generating Consecutive Numeric Values Problem Solution Discussion 10.6 Summing Up Chapter 11. Advanced Searching 11.1 Paginating Through a Result Set Problem Solution Discussion 11.2 Skipping n Rows from a Table Problem Solution Discussion 11.3 Incorporating OR Logic When Using Outer Joins Problem Solution Discussion 11.4 Determining Which Rows Are Reciprocals Problem Solution Discussion 11.5 Selecting the Top n Records Problem Solution Discussion 11.6 Finding Records with the Highest and Lowest Values Problem Solution Discussion 11.7 Investigating Future Rows Problem Solution Discussion See Also 11.8 Shifting Row Values Problem Solution Discussion 11.9 Ranking Results Problem Solution Discussion 11.10 Suppressing Duplicates Problem Solution Discussion 11.11 Finding Knight Values Problem Solution Discussion 11.12 Generating Simple Forecasts Problem Solution Discussion 11.13 Summing Up Chapter 12. Reporting and Reshaping 12.1 Pivoting a Result Set into One Row Problem Solution Discussion 12.2 Pivoting a Result Set into Multiple Rows Problem Solution Discussion 12.3 Reverse Pivoting a Result Set Problem Solution Discussion 12.4 Reverse Pivoting a Result Set into One Column Problem Solution Discussion 12.5 Suppressing Repeating Values from a Result Set Problem Solution Discussion 12.6 Pivoting a Result Set to Facilitate Inter-Row Calculations Problem Solution Discussion 12.7 Creating Buckets of Data, of a Fixed Size Problem Solution Discussion 12.8 Creating a Predefined Number of Buckets Problem Solution Discussion 12.9 Creating Horizontal Histograms Problem Solution Discussion 12.10 Creating Vertical Histograms Problem Solution Discussion 12.11 Returning Non-GROUP BY Columns Problem Solution Discussion 12.12 Calculating Simple Subtotals Problem Solution Discussion 12.13 Calculating Subtotals for All Possible Expression Combinations Problem Solution Discussion 12.14 Identifying Rows That Are Not Subtotals Problem Solution Discussion 12.15 Using Case Expressions to Flag Rows Problem Solution Discussion 12.16 Creating a Sparse Matrix Problem Solution Discussion 12.17 Grouping Rows by Units of Time Problem Solution Discussion 12.18 Performing Aggregations over Different Groups/Partitions Simultaneously Problem Solution Discussion 12.19 Performing Aggregations over a Moving Range of Values Problem Solution Discussion 12.20 Pivoting a Result Set with Subtotals Problem Solution Discussion 12.21 Summing Up Chapter 13. Hierarchical Queries 13.1 Expressing a Parent-Child Relationship Problem Solution Discussion 13.2 Expressing a Child-Parent-Grandparent Relationship Problem Solution Discussion 13.3 Creating a Hierarchical View of a Table Problem Solution Discussion 13.4 Finding All Child Rows for a Given Parent Row Problem Solution Discussion 13.5 Determining Which Rows Are Leaf, Branch, or Root Nodes Problem Solution Discussion 13.6 Summing Up Chapter 14. Odds ’n’ Ends 14.1 Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator Problem Solution Discussion 14.2 Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator Problem Solution 14.3 Transposing a Result Set Using Oracle’s MODEL Clause Problem Solution Discussion 14.4 Extracting Elements of a String from Unfixed Locations Problem Solution Discussion 14.5 Finding the Number of Days in a Year (an Alternate Solution for Oracle) Problem Solution Discussion 14.6 Searching for Mixed Alphanumeric Strings Problem Solution Discussion 14.7 Converting Whole Numbers to Binary Using Oracle Problem Solution Discussion 14.8 Pivoting a Ranked Result Set Problem Solution Discussion 14.9 Adding a Column Header into a Double Pivoted Result Set Problem Solution Discussion 14.10 Converting a Scalar Subquery to a Composite Subquery in Oracle Problem Solution Discussion 14.11 Parsing Serialized Data into Rows Problem Solution Discussion 14.12 Calculating Percent Relative to Total Problem Solution Discussion 14.13 Testing for Existence of a Value Within a Group Problem Solution Discussion 14.14 Summing Up Appendix A. Window Function Refresher Grouping Definition of an SQL Group Paradoxes Relationship Between SELECT and GROUP BY Windowing A Simple Example Order of Evaluation Partitions Effect of NULLs When Order Matters The Framing Clause A Framing Finale Readability + Performance = Power Providing a Base Appendix B. Common Table Expressions Subqueries Common Table Expressions Summing Up Index About the Authors