SQL Cookbook: Query Solutions and Techniques for All SQL Users

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