SQL Cookbook

SQL Cookbook

by Anthony Molinaro
Released December 2005
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596009762

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

You know the rudiments of the SQL query language, yet you feel you aren't taking full advantage of SQL's expressive power. You'd like to learn how to do more work with SQL inside the database before pushing data across the network to your applications. You'd like to take your SQL skills to the next level.

Let's face it, SQL is a deceptively simple language to learn, and many database developers never go far beyond the simple statement: SELECT columns FROM table WHERE conditions. But there is so much more you can do with the language. In the SQL Cookbook, experienced SQL developer Anthony Molinaro shares his favorite SQL techniques and features. You'll learn about:



  • Window functions, arguably the most significant enhancement to SQL in the past decade. If you're not using these, you're missing out


  • Powerful, database-specific features such as SQL Server's PIVOT and UNPIVOT operators, Oracle's MODEL clause, and PostgreSQL's very useful GENERATE_SERIES function


  • Pivoting rows into columns, reverse-pivoting columns into rows, using pivoting to facilitate inter-row calculations, and double-pivoting a result set


  • Bucketization, and why you should never use that term in Brooklyn.


  • How to create histograms, summarize data into buckets, perform aggregations over a moving range of values, generate running-totals and subtotals, and other advanced, data warehousing techniques


  • The technique of walking a string, which allows you to use SQL to parse through the characters, words, or delimited elements of a string

Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Publisher resources

View/Submit Errata

Table of contents

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

Product information

  • Title: SQL Cookbook
  • Author(s): Anthony Molinaro
  • Release date: December 2005
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596009762