book

SQL Cookbook

by Anthony Molinaro

December 2005

Intermediate to advanced

633 pages

14h 34m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Why I Wrote This BookObjectives of This BookAudience for This BookHow to Use This BookWhat’s Missing from This BookStructure of This BookPlatform and VersionTables Used in This BookConventions Used in This BookUsing Code ExamplesComments and QuestionsSafari® EnabledAcknowledgments
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 Patterns
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 Key
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 Comparisons
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 Table
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 Database
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 Multi-Valued 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 Address
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. Calculating a Running Difference7.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 Total

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 Record
9.1. Determining if 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 Day of a Month9.5. Determining All Dates for a Particular Weekday Throughout a Year9.6. Determining the Date of the First and Last Occurrence 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 Ranges
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 Values
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 Forecasts
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 Subtotals
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 Nodes
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. Creating CSV Output from Oracle14.14. Finding Text Not Matching a Pattern (Oracle)14.15. Transforming Data with an Inline View14.16. Testing for Existence of a Value Within a Group
A.1. GroupingA.2. Windowing
B.1. Rozenshtein’s Example TablesB.2. Answering Questions Involving NegationB.3. Answering Questions Involving “at Most”B.4. Answering Questions Involving “at Least”B.5. Answering Questions Involving “Exactly”B.6. Answering Questions Involving “Any” or “All”

Content preview from SQL Cookbook

Preface

SQL is the language in the database world. If you’re developing for or reporting from relational databases, your ability to put data into a database and then get it back out again ultimately comes down to your knowledge of SQL. Yet many practitioners use SQL in a perfunctory manner, and are unaware of the power at their disposal. This book aims to change all that, by opening your eyes to what SQL can really do for you.

The book you’re holding in your hands is a cookbook. It’s a collection of common SQL problems and their solutions that I hope you’ll find helpful in your day-to-day work. Recipes are categorized into chapters of related topics. When faced with a new SQL problem that you haven’t solved before, find the chapter that best seems to apply, skim through the recipe titles, and hopefully you will find a solution, or at least inspiration for a solution.

More than 150 recipes are available in this 600-plus page book, and I’ve only scratched the surface of what can be done using SQL. The number of different SQL solutions available for solving our daily programming problems is eclipsed only by the number of problems we need to solve. You won’t find all possible problems covered in this book. Indeed, such coverage would be impossible. You will, however, find many common problems and their solutions. And in those solutions lie techniques that you’ll learn how to expand upon and apply to other, new problems that I never thought to cover.