SQL Cookbook: Query Solutions and Techniques for All SQL Users. 2 Ed

Preface....20 Who This Book Is For....21 What’s Missing from This Book....22 Platform and Version....22 Tables Used in This Book....23 Conventions Used in This Book....26 O’Reilly Online Learning....29 How to Contact Us....29 Second Edition Acknowledgments....30 First Edition Acknowledgments....30 1. Retrieving Records....36 1.1. Retrieving All Rows and Columns from a Table....36 1.2. Retrieving a Subset of Rows from a Table....37 1.3. Finding Rows That Satisfy Multiple Conditions....38 1.4. Retrieving a Subset of Columns from a Table....40 1.5. Providing Meaningful Names for Columns....40 1.6. Referencing an Aliased Column in the WHERE Clause....42 1.7. Concatenating Column Values....44 1.8. Using Conditional Logic in a SELECT Statement....45 1.9. Limiting the Number of Rows Returned....47 1.10. Returning n Random Records from a Table....50 1.11. Finding Null Values....53 1.12. Transforming Nulls into Real Values....53 1.13. Searching for Patterns....54 1.14. Summing Up....56 2. Sorting Query Results....57 2.1. Returning Query Results in a Specified Order....57 2.2. Sorting by Multiple Fields....59 2.3. Sorting by Substrings....60 2.4. Sorting Mixed Alphanumeric Data....62 2.5. Dealing with Nulls When Sorting....67 2.6. Sorting on a Data-Dependent Key....75 2.7. Summing Up....77 3. Working with Multiple Tables....78 3.1. Stacking One Rowset atop Another....78 3.2. Combining Related Rows....81 3.3. Finding Rows in Common Between Two Tables....84 3.4. Retrieving Values from One Table That Do Not Exist in Another....86 3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another....95 3.6. Adding Joins to a Query Without Interfering with Other Joins....98 3.7. Determining Whether Two Tables Have the Same Data....102 3.8. Identifying and Avoiding Cartesian Products....113 3.9. Performing Joins When Using Aggregates....116 3.10. Performing Outer Joins When Using Aggregates....123 3.11. Returning Missing Data from Multiple Tables....127 3.12. Using NULLs in Operations and Comparisons....133 3.13. Summing Up....134 4. Inserting, Updating, and Deleting....135 4.1. Inserting a New Record....136 4.2. Inserting Default Values....137 4.3. Overriding a Default Value with NULL....140 4.4. Copying Rows from One Table into Another....141 4.5. Copying a Table Definition....142 4.6. Inserting into Multiple Tables at Once....144 4.7. Blocking Inserts to Certain Columns....147 4.8. Modifying Records in a Table....149 4.9. Updating When Corresponding Rows Exist....151 4.10. Updating with Values from Another Table....153 4.11. Merging Records....158 4.12. Deleting All Records from a Table....161 4.13. Deleting Specific Records....162 4.14. Deleting a Single Record....163 4.15. Deleting Referential Integrity Violations....164 4.16. Deleting Duplicate Records....165 4.17. Deleting Records Referenced from Another Table....168 4.18. Summing Up....170 5. Metadata Queries....172 5.1. Listing Tables in a Schema....173 5.2. Listing a Table’s Columns....174 5.3. Listing Indexed Columns for a Table....176 5.4. Listing Constraints on a Table....178 5.5. Listing Foreign Keys Without Corresponding Indexes....180 5.6. Using SQL to Generate SQL....185 5.7. Describing the Data Dictionary Views in an Oracle Database....188 5.8. Summing Up....191 6. Working with Strings....192 6.1. Walking a String....193 6.2. Embedding Quotes Within String Literals....196 6.3. Counting the Occurrences of a Character in a String....198 6.4. Removing Unwanted Characters from a String....200 6.5. Separating Numeric and Character Data....202 6.6. Determining Whether a String Is Alphanumeric....209 6.7. Extracting Initials from a Name....216 6.8. Ordering by Parts of a String....222 6.9. Ordering by a Number in a String....224 6.10. Creating a Delimited List from Table Rows....233 6.11. Converting Delimited Data into a Multivalued IN-List....239 6.12. Alphabetizing a String....248 6.13. Identifying Strings That Can Be Treated as Numbers....255 6.14. Extracting the nth Delimited Substring....266 6.15. Parsing an IP Address....276 6.16. Comparing Strings by Sound....280 6.17. Finding Text Not Matching a Pattern....283 6.18. Summing Up....288 7. Working with Numbers....289 7.1. Computing an Average....289 7.2. Finding the Min/Max Value in a Column....292 7.3. Summing the Values in a Column....296 7.4. Counting Rows in a Table....298 7.5. Counting Values in a Column....302 7.6. Generating a Running Total....303 7.7. Generating a Running Product....305 7.8. Smoothing a Series of Values....307 7.9. Calculating a Mode....310 7.10. Calculating a Median....314 7.11. Determining the Percentage of a Total....318 7.12. Aggregating Nullable Columns....322 7.13. Computing Averages Without High and Low Values....324 7.14. Converting Alphanumeric Strings into Numbers....327 7.15. Changing Values in a Running Total....331 7.16. Finding Outliers Using the Median Absolute Deviation....334 7.17. Finding Anomalies Using Benford’s Law....340 7.18. Summing Up....343 8. Date Arithmetic....344 8.1. Adding and Subtracting Days, Months, and Years....345 8.2. Determining the Number of Days Between Two Dates....349 8.3. Determining the Number of Business Days Between Two Dates....352 8.4. Determining the Number of Months or Years Between Two Dates....360 8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates....364 8.6. Counting the Occurrences of Weekdays in a Year....367 8.7. Determining the Date Difference Between the Current Record and the Next Record....384 8.8. Summing Up....393 9. Date Manipulation....394 9.1. Determining Whether a Year Is a Leap Year....395 9.2. Determining the Number of Days in a Year....405 9.3. Extracting Units of Time from a Date....410 9.4. Determining the First and Last Days of a Month....414 9.5. Determining All Dates for a Particular Weekday Throughout a Year....418 9.6. Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month....428 9.7. Creating a Calendar....440 9.8. Listing Quarter Start and End Dates for the Year....460 9.9. Determining Quarter Start and End Dates for a Given Quarter....466 9.10. Filling in Missing Dates....477 9.11. Searching on Specific Units of Time....490 9.12. Comparing Records Using Specific Parts of a Date....492 9.13. Identifying Overlapping Date Ranges....497 9.14. Summing Up....506 10. Working with Ranges....507 10.1. Locating a Range of Consecutive Values....507 10.2. Finding Differences Between Rows in the Same Group or Partition....512 10.3. Locating the Beginning and End of a Range of Consecutive Values....524 10.4. Filling in Missing Values in a Range of Values....528 10.5. Generating Consecutive Numeric Values....534 10.6. Summing Up....539 11. Advanced Searching....540 11.1. Paginating Through a Result Set....540 11.2. Skipping n Rows from a Table....544 11.3. Incorporating OR Logic When Using Outer Joins....546 11.4. Determining Which Rows Are Reciprocals....549 11.5. Selecting the Top n Records....551 11.6. Finding Records with the Highest and Lowest Values....553 11.7. Investigating Future Rows....555 11.8. Shifting Row Values....559 11.9. Ranking Results....562 11.10. Suppressing Duplicates....564 11.11. Finding Knight Values....568 11.12. Generating Simple Forecasts....579 11.13. Summing Up....592 12. Reporting and Reshaping....593 12.1. Pivoting a Result Set into One Row....593 12.2. Pivoting a Result Set into Multiple Rows....597 12.3. Reverse Pivoting a Result Set....606 12.4. Reverse Pivoting a Result Set into One Column....610 12.5. Suppressing Repeating Values from a Result Set....614 12.6. Pivoting a Result Set to Facilitate Inter-Row Calculations....617 12.7. Creating Buckets of Data, of a Fixed Size....620 12.8. Creating a Predefined Number of Buckets....623 12.9. Creating Horizontal Histograms....625 12.10. Creating Vertical Histograms....628 12.11. Returning Non-GROUP BY Columns....631 12.12. Calculating Simple Subtotals....637 12.13. Calculating Subtotals for All Possible Expression Combinations....642 12.14. Identifying Rows That Are Not Subtotals....658 12.15. Using Case Expressions to Flag Rows....662 12.16. Creating a Sparse Matrix....665 12.17. Grouping Rows by Units of Time....668 12.18. Performing Aggregations over Different Groups/Partitions Simultaneously....675 12.19. Performing Aggregations over a Moving Range of Values....678 12.20. Pivoting a Result Set with Subtotals....691 12.21. Summing Up....699 13. Hierarchical Queries....700 13.1. Expressing a Parent-Child Relationship....701 13.2. Expressing a Child-Parent-Grandparent Relationship....707 13.3. Creating a Hierarchical View of a Table....714 13.4. Finding All Child Rows for a Given Parent Row....720 13.5. Determining Which Rows Are Leaf, Branch, or Root Nodes....722 13.6. Summing Up....734 14. Odds ’n’ Ends....735 14.1. Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator....735 14.2. Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator....739 14.3. Transposing a Result Set Using Oracle’s MODEL Clause....742 14.4. Extracting Elements of a String from Unfixed Locations....749 14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)....753 14.6. Searching for Mixed Alphanumeric Strings....755 14.7. Converting Whole Numbers to Binary Using Oracle....759 14.8. Pivoting a Ranked Result Set....764 14.9. Adding a Column Header into a Double Pivoted Result Set....770 14.10. Converting a Scalar Subquery to a Composite Subquery in Oracle....787 14.11. Parsing Serialized Data into Rows....791 14.12. Calculating Percent Relative to Total....798 14.13. Testing for Existence of a Value Within a Group....801 14.14. Summing Up....808 Window Function Refresher....809 Grouping....809 Windowing....827 Common Table Expressions....854 Subqueries....854 Common Table Expressions....855 Summing Up....857 Index....859