book

SQL Cookbook, 2nd Edition

by Anthony Molinaro, Robert de Graaf

November 2020

Intermediate to advanced

567 pages

11h 48m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who This Book Is ForWhat’s Missing from This BookPlatform and VersionTables Used in This BookConventions Used in This BookO’Reilly Online LearningHow to Contact UsSecond Edition AcknowledgmentsFirst Edition Acknowledgments
1.1. Retrieving All Rows and Columns from a Table1.2. Retrieving a Subset of Rows from a Table1.3. Finding Rows That Satisfy Multiple Conditions1.4. Retrieving a Subset of Columns from a Table1.5. Providing Meaningful Names for Columns1.6. Referencing an Aliased Column in the WHERE Clause1.7. Concatenating Column Values1.8. Using Conditional Logic in a SELECT Statement1.9. Limiting the Number of Rows Returned1.10. Returning n Random Records from a Table1.11. Finding Null Values1.12. Transforming Nulls into Real Values1.13. Searching for Patterns1.14. Summing Up
2.1. Returning Query Results in a Specified Order2.2. Sorting by Multiple Fields2.3. Sorting by Substrings2.4. Sorting Mixed Alphanumeric Data2.5. Dealing with Nulls When Sorting2.6. Sorting on a Data-Dependent Key2.7. Summing Up
3.1. Stacking One Rowset atop Another3.2. Combining Related Rows3.3. Finding Rows in Common Between Two Tables3.4. Retrieving Values from One Table That Do Not Exist in Another3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another3.6. Adding Joins to a Query Without Interfering with Other Joins3.7. Determining Whether Two Tables Have the Same Data3.8. Identifying and Avoiding Cartesian Products3.9. Performing Joins When Using Aggregates3.10. Performing Outer Joins When Using Aggregates3.11. Returning Missing Data from Multiple Tables3.12. Using NULLs in Operations and Comparisons3.13. Summing Up
4.1. Inserting a New Record4.2. Inserting Default Values4.3. Overriding a Default Value with NULL4.4. Copying Rows from One Table into Another4.5. Copying a Table Definition4.6. Inserting into Multiple Tables at Once4.7. Blocking Inserts to Certain Columns4.8. Modifying Records in a Table4.9. Updating When Corresponding Rows Exist4.10. Updating with Values from Another Table4.11. Merging Records4.12. Deleting All Records from a Table4.13. Deleting Specific Records4.14. Deleting a Single Record4.15. Deleting Referential Integrity Violations4.16. Deleting Duplicate Records4.17. Deleting Records Referenced from Another Table4.18. Summing Up
5.1. Listing Tables in a Schema5.2. Listing a Table’s Columns5.3. Listing Indexed Columns for a Table5.4. Listing Constraints on a Table5.5. Listing Foreign Keys Without Corresponding Indexes5.6. Using SQL to Generate SQL5.7. Describing the Data Dictionary Views in an Oracle Database5.8. Summing Up
6.1. Walking a String6.2. Embedding Quotes Within String Literals6.3. Counting the Occurrences of a Character in a String6.4. Removing Unwanted Characters from a String6.5. Separating Numeric and Character Data6.6. Determining Whether a String Is Alphanumeric6.7. Extracting Initials from a Name6.8. Ordering by Parts of a String6.9. Ordering by a Number in a String6.10. Creating a Delimited List from Table Rows6.11. Converting Delimited Data into a Multivalued IN-List6.12. Alphabetizing a String6.13. Identifying Strings That Can Be Treated as Numbers6.14. Extracting the nth Delimited Substring6.15. Parsing an IP Address6.16. Comparing Strings by Sound6.17. Finding Text Not Matching a Pattern6.18. Summing Up
7.1. Computing an Average7.2. Finding the Min/Max Value in a Column7.3. Summing the Values in a Column7.4. Counting Rows in a Table7.5. Counting Values in a Column7.6. Generating a Running Total7.7. Generating a Running Product7.8. Smoothing a Series of Values7.9. Calculating a Mode7.10. Calculating a Median7.11. Determining the Percentage of a Total7.12. Aggregating Nullable Columns7.13. Computing Averages Without High and Low Values7.14. Converting Alphanumeric Strings into Numbers7.15. Changing Values in a Running Total7.16. Finding Outliers Using the Median Absolute Deviation7.17. Finding Anomalies Using Benford’s Law7.18. Summing Up
8.1. Adding and Subtracting Days, Months, and Years8.2. Determining the Number of Days Between Two Dates8.3. Determining the Number of Business Days Between Two Dates8.4. Determining the Number of Months or Years Between Two Dates8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates8.6. Counting the Occurrences of Weekdays in a Year8.7. Determining the Date Difference Between the Current Record and the Next Record8.8. Summing Up
9.1. Determining Whether a Year Is a Leap Year9.2. Determining the Number of Days in a Year9.3. Extracting Units of Time from a Date9.4. Determining the First and Last Days of a Month9.5. Determining All Dates for a Particular Weekday Throughout a Year9.6. Determining the Date of the First and Last Occurrences of a Specific Weekday in a Month9.7. Creating a Calendar9.8. Listing Quarter Start and End Dates for the Year9.9. Determining Quarter Start and End Dates for a Given Quarter9.10. Filling in Missing Dates9.11. Searching on Specific Units of Time9.12. Comparing Records Using Specific Parts of a Date9.13. Identifying Overlapping Date Ranges9.14. Summing Up

10.1. Locating a Range of Consecutive Values10.2. Finding Differences Between Rows in the Same Group or Partition10.3. Locating the Beginning and End of a Range of Consecutive Values10.4. Filling in Missing Values in a Range of Values10.5. Generating Consecutive Numeric Values10.6. Summing Up
11.1. Paginating Through a Result Set11.2. Skipping n Rows from a Table11.3. Incorporating OR Logic When Using Outer Joins11.4. Determining Which Rows Are Reciprocals11.5. Selecting the Top n Records11.6. Finding Records with the Highest and Lowest Values11.7. Investigating Future Rows11.8. Shifting Row Values11.9. Ranking Results11.10. Suppressing Duplicates11.11. Finding Knight Values11.12. Generating Simple Forecasts11.13. Summing Up
12.1. Pivoting a Result Set into One Row12.2. Pivoting a Result Set into Multiple Rows12.3. Reverse Pivoting a Result Set12.4. Reverse Pivoting a Result Set into One Column12.5. Suppressing Repeating Values from a Result Set12.6. Pivoting a Result Set to Facilitate Inter-Row Calculations12.7. Creating Buckets of Data, of a Fixed Size12.8. Creating a Predefined Number of Buckets12.9. Creating Horizontal Histograms12.10. Creating Vertical Histograms12.11. Returning Non-GROUP BY Columns12.12. Calculating Simple Subtotals12.13. Calculating Subtotals for All Possible Expression Combinations12.14. Identifying Rows That Are Not Subtotals12.15. Using Case Expressions to Flag Rows12.16. Creating a Sparse Matrix12.17. Grouping Rows by Units of Time12.18. Performing Aggregations over Different Groups/Partitions Simultaneously12.19. Performing Aggregations over a Moving Range of Values12.20. Pivoting a Result Set with Subtotals12.21. Summing Up
13.1. Expressing a Parent-Child Relationship13.2. Expressing a Child-Parent-Grandparent Relationship13.3. Creating a Hierarchical View of a Table13.4. Finding All Child Rows for a Given Parent Row13.5. Determining Which Rows Are Leaf, Branch, or Root Nodes13.6. Summing Up
14.1. Creating Cross-Tab Reports Using SQL Server’s PIVOT Operator14.2. Unpivoting a Cross-Tab Report Using SQL Server’s UNPIVOT Operator14.3. Transposing a Result Set Using Oracle’s MODEL Clause14.4. Extracting Elements of a String from Unfixed Locations14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)14.6. Searching for Mixed Alphanumeric Strings14.7. Converting Whole Numbers to Binary Using Oracle14.8. Pivoting a Ranked Result Set14.9. Adding a Column Header into a Double Pivoted Result Set14.10. Converting a Scalar Subquery to a Composite Subquery in Oracle14.11. Parsing Serialized Data into Rows14.12. Calculating Percent Relative to Total14.13. Testing for Existence of a Value Within a Group14.14. Summing Up
GroupingWindowing
SubqueriesCommon Table ExpressionsSumming Up

Content preview from SQL Cookbook, 2nd Edition

Chapter 10. Working with Ranges

This chapter is about “everyday” queries that involve ranges. Ranges are common in everyday life. For example, projects that we work on range over consecutive periods of time. In SQL, it’s often necessary to search for ranges, or to generate ranges, or to otherwise manipulate range-based data. The queries you’ll read about here are slightly more involved than the queries found in the preceding chapters, but they are just as common, and they’ll begin to give you a sense of what SQL can really do for you when you learn to take full advantage of it.

10.1 Locating a Range of Consecutive Values

Problem

You want to determine which rows represent a range of consecutive projects. Consider the following result set from view V, which contains data about a project and its start and end dates:

select *
  from V

PROJ_ID PROJ_START  PROJ_END
------- ----------- -----------
      1 01-JAN-2020 02-JAN-2020
      2 02-JAN-2020 03-JAN-2020
      3 03-JAN-2020 04-JAN-2020
      4 04-JAN-2020 05-JAN-2020
      5 06-JAN-2020 07-JAN-2020
      6 16-JAN-2020 17-JAN-2020
      7 17-JAN-2020 18-JAN-2020
      8 18-JAN-2020 19-JAN-2020
      9 19-JAN-2020 20-JAN-2020
     10 21-JAN-2020 22-JAN-2020
     11 26-JAN-2020 27-JAN-2020
     12 27-JAN-2020 28-JAN-2020
     13 28-JAN-2020 29-JAN-2020
     14 29-JAN-2020 30-JAN-2020

Excluding the first row, each row’s PROJ_START should equal the PROJ_END of the row before it (“before” is defined as PROJ_ID–1 for the current row). Examining the first five rows from view V, PROJ_IDs 1 through 3 are part of the ...