You may know SQL basics, but are you taking advantage of its expressive power? This second edition applies a highly practical approach to Structured Query Language (SQL) so you can create and manipulate large stores of data. Based on real-world examples, this updated cookbook provides a framework to help you construct solutions and executable examples in several flavors of SQL, including Oracle, DB2, SQL Server, MySQL, and PostgreSQL.
SQL programmers, analysts, data scientists, database administrators, and even relatively casual SQL users will find SQL Cookbook to be a valuable problem-solving guide for everyday issues. No other resource offers recipes in this unique format to help you tackle nagging day-to-day conundrums with SQL.
The second edition includes:
• Fully revised recipes that recognize the greater adoption of window functions in SQL implementations
• Additional recipes that reflect the widespread adoption of common table expressions (CTEs) for more readable, easier-to-implement solutions
• New recipes to make SQL more useful for people who aren’t database experts, including data scientists
• Expanded solutions for working with numbers and strings
• Up-to-date SQL recipes throughout the book to guide you through the basics
Author(s): Anthony Molinaro, Robert de Graaf
Edition: 2
Publisher: O'Reilly Media
Year: 2020
Language: English
Commentary: Vector PDF
Pages: 572
City: Sebastopol, CA
Tags: SQL; Relational Databases; Cookbook; Best Practices
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
