SQL Cookbook (Cookbooks (OReilly))
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 235
Pages: 235
Authors: Anthony Molinaro
- SQL Cookbook
- Table of Contents
- Copyright
- 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
- Recipe1.1.Retrieving All Rows and Columns from a Table
- Recipe1.2.Retrieving a Subset of Rows from a Table
- Recipe1.3.Finding Rows That Satisfy Multiple Conditions
- Recipe1.4.Retrieving a Subset of Columns from a Table
- Recipe1.5.Providing Meaningful Names for Columns
- Recipe1.6.Referencing an Aliased Column in the WHERE Clause
- Recipe1.7.Concatenating Column Values
- Recipe1.8.Using Conditional Logic in a SELECT Statement
- Recipe1.9.Limiting the Number of Rows Returned
- Recipe1.10.Returning n Random Records from a Table
- Recipe1.11.Finding Null Values
- Recipe1.12.Transforming Nulls into Real Values
- Recipe1.13.Searching for Patterns
- Chapter 2. Sorting Query Results
- Recipe2.1.Returning Query Results in a Specified Order
- Recipe2.2.Sorting by Multiple Fields
- Recipe2.3.Sorting by Substrings
- Recipe2.4.Sorting Mixed Alphanumeric Data
- Recipe2.5.Dealing with Nulls when Sorting
- Recipe2.6.Sorting on a Data Dependent Key
- Chapter 3. Working with Multiple Tables
- Recipe3.1.Stacking One Rowset atop Another
- Recipe3.2.Combining Related Rows
- Recipe3.3.Finding Rows in Common Between Two Tables
- Recipe3.4.Retrieving Values from One Table That Do Not Exist in Another
- Recipe3.5.Retrieving Rows from One Table That Do Not Correspond to Rows in Another
- Recipe3.6.Adding Joins to a Query Without Interfering with Other Joins
- Recipe3.7.Determining Whether Two Tables Have the Same Data
- Recipe3.8.Identifying and Avoiding Cartesian Products
- Recipe3.9.Performing Joins when Using Aggregates
- Recipe3.10.Performing Outer Joins when Using Aggregates
- Recipe3.11.Returning Missing Data from Multiple Tables
- Recipe3.12.Using NULLs in Operations and Comparisons
- Chapter 4. Inserting, Updating, Deleting
- Recipe4.1.Inserting a New Record
- Recipe4.2.Inserting Default Values
- Recipe4.3.Overriding a Default Value with NULL
- Recipe4.4.Copying Rows from One Table into Another
- Recipe4.5.Copying a Table Definition
- Recipe4.6.Inserting into Multiple Tables at Once
- Recipe4.7.Blocking Inserts to Certain Columns
- Recipe4.8.Modifying Records in a Table
- Recipe4.9.Updating when Corresponding Rows Exist
- Recipe4.10.Updating with Values from Another Table
- Recipe4.11.Merging Records
- Recipe4.12.Deleting All Records from a Table
- Recipe4.13.Deleting Specific Records
- Recipe4.14.Deleting a Single Record
- Recipe4.15.Deleting Referential Integrity Violations
- Recipe4.16.Deleting Duplicate Records
- Recipe4.17.Deleting Records Referenced from Another Table
- Chapter 5. Metadata Queries
- Recipe5.1.Listing Tables in a Schema
- Recipe5.2.Listing a Table s Columns
- Recipe5.3.Listing Indexed Columns for a Table
- Recipe5.4.Listing Constraints on a Table
- Recipe5.5.Listing Foreign Keys Without Corresponding Indexes
- Recipe5.6.Using SQL to Generate SQL
- Recipe5.7.Describing the Data Dictionary Views in an Oracle Database
- Chapter 6. Working with Strings
- Recipe6.1.Walking a String
- Recipe6.2.Embedding Quotes Within String Literals
- Recipe6.3.Counting the Occurrences of a Character in a String
- Recipe6.4.Removing Unwanted Characters from a String
- Recipe6.5.Separating Numeric and Character Data
- Recipe6.6.Determining Whether a String Is Alphanumeric
- Recipe6.7.Extracting Initials from a Name
- Recipe6.8.Ordering by Parts of a String
- Recipe6.9.Ordering by a Number in a String
- Recipe6.10.Creating a Delimited List from Table Rows
- Recipe6.11.Converting Delimited Data into a Multi-Valued IN-List
- Recipe6.12.Alphabetizing a String
- Recipe6.13.Identifying Strings That Can Be Treated as Numbers
- Recipe6.14.Extracting the nth Delimited Substring
- Recipe6.15.Parsing an IP Address
- Chapter 7. Working with Numbers
- Recipe7.1.Computing an Average
- Recipe7.2.Finding the MinMax Value in a Column
- Recipe7.3.Summing the Values in a Column
- Recipe7.4.Counting Rows in a Table
- Recipe7.5.Counting Values in a Column
- Recipe7.6.Generating a Running Total
- Recipe7.7.Generating a Running Product
- Recipe7.8.Calculating a Running Difference
- Recipe7.9.Calculating a Mode
- Recipe7.10.Calculating a Median
- Recipe7.11.Determining the Percentage of a Total
- Recipe7.12.Aggregating Nullable Columns
- Recipe7.13.Computing Averages Without High and Low Values
- Recipe7.14.Converting Alphanumeric Strings into Numbers
- Recipe7.15.Changing Values in a Running Total
- Chapter 8. Date Arithmetic
- Recipe8.1.Adding and Subtracting Days, Months, and Years
- Recipe8.2.Determining the Number of Days Between Two Dates
- Recipe8.3.Determining the Number of Business Days Between Two Dates
- Recipe8.4.Determining the Number of Months or Years Between Two Dates
- Recipe8.5.Determining the Number of Seconds, Minutes, or Hours Between Two Dates
- Recipe8.6.Counting the Occurrences of Weekdays in a Year
- Recipe8.7.Determining the Date Difference Between the Current Record and the Next Record
- Chapter 9. Date Manipulation
- Recipe9.1.Determining if a Year Is a Leap Year
- Recipe9.2.Determining the Number of Days in a Year
- Recipe9.3.Extracting Units of Time from a Date
- Recipe9.4.Determining the First and Last Day of a Month
- Recipe9.5.Determining All Dates for a Particular Weekday Throughout a Year
- Recipe9.6.Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month
- Recipe9.7.Creating a Calendar
- Recipe9.8.Listing Quarter Start and End Dates for the Year
- Recipe9.9.Determining Quarter Start and End Dates for a Given Quarter
- Recipe9.10.Filling in Missing Dates
- Recipe9.11.Searching on Specific Units of Time
- Recipe9.12.Comparing Records Using Specific Parts of a Date
- Recipe9.13.Identifying Overlapping Date Ranges
- Chapter 10. Working with Ranges
- Recipe10.1.Locating a Range of Consecutive Values
- Recipe10.2.Finding Differences Between Rows in the Same Group or Partition
- Recipe10.3.Locating the Beginning and End of a Range of Consecutive Values
- Recipe10.4.Filling in Missing Values in a Range of Values
- Recipe10.5.Generating Consecutive Numeric Values
- Chapter 11. Advanced Searching
- Recipe11.1.Paginating Through a Result Set
- Recipe11.2.Skipping n Rows from a Table
- Recipe11.3.Incorporating OR Logic when Using Outer Joins
- Recipe11.4.Determining Which Rows Are Reciprocals
- Recipe11.5.Selecting the Top n Records
- Recipe11.6.Finding Records with the Highest and Lowest Values
- Recipe11.7.Investigating Future Rows
- Recipe11.8.Shifting Row Values
- Recipe11.9.Ranking Results
- Recipe11.10.Suppressing Duplicates
- Recipe11.11.Finding Knight Values
- Recipe11.12.Generating Simple Forecasts
- Chapter 12. Reporting and Warehousing
- Recipe12.1.Pivoting a Result Set into One Row
- Recipe12.2.Pivoting a Result Set into Multiple Rows
- Recipe12.3.Reverse Pivoting a Result Set
- Recipe12.4.Reverse Pivoting a Result Set into One Column
- Recipe12.5.Suppressing Repeating Values from a Result Set
- Recipe12.6.Pivoting a Result Set to Facilitate Inter-Row Calculations
- Recipe12.7.Creating Buckets of Data, of a Fixed Size
- Recipe12.8.Creating a Predefined Number of Buckets
- Recipe12.9.Creating Horizontal Histograms
- Recipe12.10.Creating Vertical Histograms
- Recipe12.11.Returning Non-GROUP BY Columns
- Recipe12.12.Calculating Simple Subtotals
- Recipe12.13.Calculating Subtotals for All Possible Expression Combinations
- Recipe12.14.Identifying Rows That Are Not Subtotals
- Recipe12.15.Using Case Expressions to Flag Rows
- Recipe12.16.Creating a Sparse Matrix
- Recipe12.17.Grouping Rows by Units of Time
- Recipe12.18.Performing Aggregations over Different GroupsPartitions Simultaneously
- Recipe12.19.Performing Aggregations over a Moving Range of Values
- Recipe12.20.Pivoting a Result Set with Subtotals
- Chapter 13. Hierarchical Queries
- Recipe13.1.Expressing a Parent-Child Relationship
- Recipe13.2.Expressing a Child-Parent-Grandparent Relationship
- Recipe13.3.Creating a Hierarchical View of a Table
- Recipe13.4.Finding All Child Rows for a Given Parent Row
- Recipe13.5.Determining Which Rows Are Leaf, Branch, or Root Nodes
- Chapter 14. Odds n Ends
- Recipe14.1.Creating Cross-Tab Reports Using SQL Server s PIVOT Operator
- Recipe14.2.Unpivoting a Cross-Tab Report Using SQL Server s UNPIVOT Operator
- Recipe14.3.Transposing a Result Set Using Oracle s MODEL Clause
- Recipe14.4.Extracting Elements of a String from Unfixed Locations
- Recipe14.5.Finding the Number of Days in a Year (an Alternate Solution for Oracle)
- Recipe14.6.Searching for Mixed Alphanumeric Strings
- Recipe14.7.Converting Whole Numbers to Binary Using Oracle
- Recipe14.8.Pivoting a Ranked Result Set
- Recipe14.9.Adding a Column Header into a Double Pivoted Result Set
- Recipe14.10.Converting a Scalar Subquery to a Composite Subquery in Oracle
- Recipe14.11.Parsing Serialized Data into Rows
- Recipe14.12.Calculating Percent Relative to Total
- Recipe14.13.Creating CSV Output from Oracle
- Recipe14.14.Finding Text Not Matching a Pattern (Oracle)
- Recipe14.15.Transforming Data with an Inline View
- Recipe14.16.Testing for Existence of a Value Within a Group
- Appendix A. Window Function Refresher
- RecipeA.1.Grouping
- RecipeA.2.Windowing
- Appendix B. Rozenshtein Revisited
- RecipeB.1.Rozenshtein s Example Tables
- RecipeB.2.Answering Questions Involving Negation
- RecipeB.3.Answering Questions Involving
- RecipeB.4.Answering Questions Involving
- RecipeB.5.Answering Questions Involving
- RecipeB.6.Answering Questions Involving
- About the Author
- Colophon
- Index
- SYMBOL
- A
- B
- C
- D
- E
- F
- G
- H
- I
- J
- K
- L
- M
- N
- O
- P
- Q
- R
- S
- T
- U
- V
- W
- Y
- Z
SQL Cookbook (Cookbooks (OReilly))
ISBN: 0596009763
EAN: 2147483647
EAN: 2147483647
Year: 2005
Pages: 235
Pages: 235
Authors: Anthony Molinaro