SQL Cookbook. 2nd Edition

Spis treści książki

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

pokaż cały spis treści

ukryj recenzje