Table of Contents | SQL Cookbook (Cookbooks (OReilly))

SQL Cookbook
By Anthony Molinaro
...............................................
Publisher: O'Reilly
Pub Date: December 2005
Print ISBN-10: 0-596-00976-3
Print ISBN-13: 978-0-59-600976-2
Pages: 628

Table of Contents | Index

Dedication

Preface

Why I Wrote This Book

Objectives of This Book

Audience for This Book

How to Use This Book

What's Missing from This Book

Structure of This Book

Platform and Version

Tables Used in This Book

Conventions Used in This Book

Using Code Examples

Comments and Questions

Safari® Enabled

Acknowledgments

Chapter 1. Retrieving Records

Recipe 1.1. Retrieving All Rows and Columns from a Table

Recipe 1.2. Retrieving a Subset of Rows from a Table

Recipe 1.3. Finding Rows That Satisfy Multiple Conditions

Recipe 1.4. Retrieving a Subset of Columns from a Table

Recipe 1.5. Providing Meaningful Names for Columns

Recipe 1.6. Referencing an Aliased Column in the WHERE Clause

Recipe 1.7. Concatenating Column Values

Recipe 1.8. Using Conditional Logic in a SELECT Statement

Recipe 1.9. Limiting the Number of Rows Returned

Recipe 1.10. Returning n Random Records from a Table

Recipe 1.11. Finding Null Values

Recipe 1.12. Transforming Nulls into Real Values

Recipe 1.13. Searching for Patterns

Chapter 2. Sorting Query Results

Recipe 2.1. Returning Query Results in a Specified Order

Recipe 2.2. Sorting by Multiple Fields

Recipe 2.3. Sorting by Substrings

Recipe 2.4. Sorting Mixed Alphanumeric Data

Recipe 2.5. Dealing with Nulls when Sorting

Recipe 2.6. Sorting on a Data Dependent Key

Chapter 3. Working with Multiple Tables

Recipe 3.1. Stacking One Rowset atop Another

Recipe 3.2. Combining Related Rows

Recipe 3.3. Finding Rows in Common Between Two Tables

Recipe 3.4. Retrieving Values from One Table That Do Not Exist in Another

Recipe 3.5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another

Recipe 3.6. Adding Joins to a Query Without Interfering with Other Joins

Recipe 3.7. Determining Whether Two Tables Have the Same Data

Recipe 3.8. Identifying and Avoiding Cartesian Products

Recipe 3.9. Performing Joins when Using Aggregates

Recipe 3.10. Performing Outer Joins when Using Aggregates

Recipe 3.11. Returning Missing Data from Multiple Tables

Recipe 3.12. Using NULLs in Operations and Comparisons

Chapter 4. Inserting, Updating, Deleting

Recipe 4.1. Inserting a New Record

Recipe 4.2. Inserting Default Values

Recipe 4.3. Overriding a Default Value with NULL

Recipe 4.4. Copying Rows from One Table into Another

Recipe 4.5. Copying a Table Definition

Recipe 4.6. Inserting into Multiple Tables at Once

Recipe 4.7. Blocking Inserts to Certain Columns

Recipe 4.8. Modifying Records in a Table

Recipe 4.9. Updating when Corresponding Rows Exist

Recipe 4.10. Updating with Values from Another Table

Recipe 4.11. Merging Records

Recipe 4.12. Deleting All Records from a Table

Recipe 4.13. Deleting Specific Records

Recipe 4.14. Deleting a Single Record

Recipe 4.15. Deleting Referential Integrity Violations

Recipe 4.16. Deleting Duplicate Records

Recipe 4.17. Deleting Records Referenced from Another Table

Chapter 5. Metadata Queries

Recipe 5.1. Listing Tables in a Schema

Recipe 5.2. Listing a Table's Columns

Recipe 5.3. Listing Indexed Columns for a Table

Recipe 5.4. Listing Constraints on a Table

Recipe 5.5. Listing Foreign Keys Without Corresponding Indexes

Recipe 5.6. Using SQL to Generate SQL

Recipe 5.7. Describing the Data Dictionary Views in an Oracle Database

Chapter 6. Working with Strings

Recipe 6.1. Walking a String

Recipe 6.2. Embedding Quotes Within String Literals

Recipe 6.3. Counting the Occurrences of a Character in a String

Recipe 6.4. Removing Unwanted Characters from a String

Recipe 6.5. Separating Numeric and Character Data

Recipe 6.6. Determining Whether a String Is Alphanumeric

Recipe 6.7. Extracting Initials from a Name

Recipe 6.8. Ordering by Parts of a String

Recipe 6.9. Ordering by a Number in a String

Recipe 6.10. Creating a Delimited List from Table Rows

Recipe 6.11. Converting Delimited Data into a Multi-Valued IN-List

Recipe 6.12. Alphabetizing a String

Recipe 6.13. Identifying Strings That Can Be Treated as Numbers

Recipe 6.14. Extracting the nth Delimited Substring

Recipe 6.15. Parsing an IP Address

Chapter 7. Working with Numbers

Recipe 7.1. Computing an Average

Recipe 7.2. Finding the Min/Max Value in a Column

Recipe 7.3. Summing the Values in a Column

Recipe 7.4. Counting Rows in a Table

Recipe 7.5. Counting Values in a Column

Recipe 7.6. Generating a Running Total

Recipe 7.7. Generating a Running Product

Recipe 7.8. Calculating a Running Difference

Recipe 7.9. Calculating a Mode

Recipe 7.10. Calculating a Median

Recipe 7.11. Determining the Percentage of a Total

Recipe 7.12. Aggregating Nullable Columns

Recipe 7.13. Computing Averages Without High and Low Values

Recipe 7.14. Converting Alphanumeric Strings into Numbers

Recipe 7.15. Changing Values in a Running Total

Chapter 8. Date Arithmetic

Recipe 8.1. Adding and Subtracting Days, Months, and Years

Recipe 8.2. Determining the Number of Days Between Two Dates

Recipe 8.3. Determining the Number of Business Days Between Two Dates

Recipe 8.4. Determining the Number of Months or Years Between Two Dates

Recipe 8.5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates

Recipe 8.6. Counting the Occurrences of Weekdays in a Year

Recipe 8.7. Determining the Date Difference Between the Current Record and the Next Record

Chapter 9. Date Manipulation

Recipe 9.1. Determining if a Year Is a Leap Year

Recipe 9.2. Determining the Number of Days in a Year

Recipe 9.3. Extracting Units of Time from a Date

Recipe 9.4. Determining the First and Last Day of a Month

Recipe 9.5. Determining All Dates for a Particular Weekday Throughout a Year

Recipe 9.6. Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month

Recipe 9.7. Creating a Calendar

Recipe 9.8. Listing Quarter Start and End Dates for the Year

Recipe 9.9. Determining Quarter Start and End Dates for a Given Quarter

Recipe 9.10. Filling in Missing Dates

Recipe 9.11. Searching on Specific Units of Time

Recipe 9.12. Comparing Records Using Specific Parts of a Date

Recipe 9.13. Identifying Overlapping Date Ranges

Chapter 10. Working with Ranges

Recipe 10.1. Locating a Range of Consecutive Values

Recipe 10.2. Finding Differences Between Rows in the Same Group or Partition

Recipe 10.3. Locating the Beginning and End of a Range of Consecutive Values

Recipe 10.4. Filling in Missing Values in a Range of Values

Recipe 10.5. Generating Consecutive Numeric Values

Chapter 11. Advanced Searching

Recipe 11.1. Paginating Through a Result Set

Recipe 11.2. Skipping n Rows from a Table

Recipe 11.3. Incorporating OR Logic when Using Outer Joins

Recipe 11.4. Determining Which Rows Are Reciprocals

Recipe 11.5. Selecting the Top n Records

Recipe 11.6. Finding Records with the Highest and Lowest Values

Recipe 11.7. Investigating Future Rows

Recipe 11.8. Shifting Row Values

Recipe 11.9. Ranking Results

Recipe 11.10. Suppressing Duplicates

Recipe 11.11. Finding Knight Values

Recipe 11.12. Generating Simple Forecasts

Chapter 12. Reporting and Warehousing

Recipe 12.1. Pivoting a Result Set into One Row

Recipe 12.2. Pivoting a Result Set into Multiple Rows

Recipe 12.3. Reverse Pivoting a Result Set

Recipe 12.4. Reverse Pivoting a Result Set into One Column

Recipe 12.5. Suppressing Repeating Values from a Result Set

Recipe 12.6. Pivoting a Result Set to Facilitate Inter-Row Calculations

Recipe 12.7. Creating Buckets of Data, of a Fixed Size

Recipe 12.8. Creating a Predefined Number of Buckets

Recipe 12.9. Creating Horizontal Histograms

Recipe 12.10. Creating Vertical Histograms

Recipe 12.11. Returning Non-GROUP BY Columns

Recipe 12.12. Calculating Simple Subtotals

Recipe 12.13. Calculating Subtotals for All Possible Expression Combinations

Recipe 12.14. Identifying Rows That Are Not Subtotals

Recipe 12.15. Using Case Expressions to Flag Rows

Recipe 12.16. Creating a Sparse Matrix

Recipe 12.17. Grouping Rows by Units of Time

Recipe 12.18. Performing Aggregations over Different Groups/Partitions Simultaneously

Recipe 12.19. Performing Aggregations over a Moving Range of Values

Recipe 12.20. Pivoting a Result Set with Subtotals

Chapter 13. Hierarchical Queries

Recipe 13.1. Expressing a Parent-Child Relationship

Recipe 13.2. Expressing a Child-Parent-Grandparent Relationship

Recipe 13.3. Creating a Hierarchical View of a Table

Recipe 13.4. Finding All Child Rows for a Given Parent Row

Recipe 13.5. Determining Which Rows Are Leaf, Branch, or Root Nodes

Chapter 14. Odds 'n' Ends

Recipe 14.1. Creating Cross-Tab Reports Using SQL Server's PIVOT Operator

Recipe 14.2. Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator

Recipe 14.3. Transposing a Result Set Using Oracle's MODEL Clause

Recipe 14.4. Extracting Elements of a String from Unfixed Locations

Recipe 14.5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)

Recipe 14.6. Searching for Mixed Alphanumeric Strings

Recipe 14.7. Converting Whole Numbers to Binary Using Oracle

Recipe 14.8. Pivoting a Ranked Result Set

Recipe 14.9. Adding a Column Header into a Double Pivoted Result Set

Recipe 14.10. Converting a Scalar Subquery to a Composite Subquery in Oracle

Recipe 14.11. Parsing Serialized Data into Rows

Recipe 14.12. Calculating Percent Relative to Total

Recipe 14.13. Creating CSV Output from Oracle

Recipe 14.14. Finding Text Not Matching a Pattern (Oracle)

Recipe 14.15. Transforming Data with an Inline View

Recipe 14.16. Testing for Existence of a Value Within a Group

Appendix A. Window Function Refresher

Recipe A.1. Grouping

Recipe A.2. Windowing

Appendix B. Rozenshtein Revisited

Recipe B.1. Rozenshtein's Example Tables

Recipe B.2. Answering Questions Involving Negation

Recipe B.3. Answering Questions Involving "at Most"

Recipe B.4. Answering Questions Involving "at Least"

Recipe B.5. Answering Questions Involving "Exactly"

Recipe B.6. Answering Questions Involving "Any" or "All"

About the Author

Colophon

Index