| |
| |
| Preface | |
| |
| |
| |
| Retrieving Records | |
| |
| |
| Retrieving All Rows and Columns from a Table | |
| |
| |
| Retrieving a Subset of Rows from a Table | |
| |
| |
| Finding Rows That Satisfy Multiple Conditions | |
| |
| |
| Retrieving a Subset of Columns from a Table | |
| |
| |
| Providing Meaningful Names for Columns | |
| |
| |
| Referencing an Aliased Column in the WHERE Clause | |
| |
| |
| Concatenating Column Values | |
| |
| |
| Using Conditional Logic in a SELECT Statement | |
| |
| |
| Limiting the Number of Rows Returned | |
| |
| |
| Returning n Random Records from a Table | |
| |
| |
| Finding Null Values | |
| |
| |
| Transforming Nulls into Real Values | |
| |
| |
| Searching for Patterns | |
| |
| |
| |
| Sorting Query Results | |
| |
| |
| Returning Query Results in a Specified Order | |
| |
| |
| Sorting by Multiple Fields | |
| |
| |
| Sorting by Substrings | |
| |
| |
| Sorting Mixed Alphanumeric Data | |
| |
| |
| Dealing with Nulls when Sorting | |
| |
| |
| Sorting on a Data Dependent Key | |
| |
| |
| |
| Working with Multiple Tables | |
| |
| |
| Stacking One Rowset atop Another | |
| |
| |
| Combining Related Rows | |
| |
| |
| Finding Rows in Common Between Two Tables | |
| |
| |
| Retrieving Values from One Table That Do Not Exist in Another | |
| |
| |
| Retrieving Rows from One Table That Do Not Correspond to Rows in Another | |
| |
| |
| Adding Joins to a Query Without Interfering with Other Joins | |
| |
| |
| Determining Whether Two Tables Have the Same Data | |
| |
| |
| Identifying and Avoiding Cartesian Products | |
| |
| |
| Performing Joins when Using Aggregates | |
| |
| |
| Performing Outer Joins when Using Aggregates | |
| |
| |
| Returning Missing Data from Multiple Tables | |
| |
| |
| Using NULLs in Operations and Comparisons | |
| |
| |
| |
| Inserting, Updating, Deleting | |
| |
| |
| Inserting a New Record | |
| |
| |
| Inserting Default Values | |
| |
| |
| Overriding a Default Value with NULL | |
| |
| |
| Copying Rows from One Table into Another | |
| |
| |
| Copying a Table Definition | |
| |
| |
| Inserting into Multiple Tables at Once | |
| |
| |
| Blocking Inserts to Certain Columns | |
| |
| |
| Modifying Records in a Table | |
| |
| |
| Updating when Corresponding Rows Exist | |
| |
| |
| Updating with Values from Another Table | |
| |
| |
| Merging Records | |
| |
| |
| Deleting All Records from a Table | |
| |
| |
| Deleting Specific Records | |
| |
| |
| Deleting a Single Record | |
| |
| |
| Deleting Referential Integrity Violations | |
| |
| |
| Deleting Duplicate Records | |
| |
| |
| Deleting Records Referenced from Another Table | |
| |
| |
| |
| Metadata Queries | |
| |
| |
| Listing Tables in a Schema | |
| |
| |
| Listing a Table's Columns | |
| |
| |
| Listing Indexed Columns for a Table | |
| |
| |
| Listing Constraints on a Table | |
| |
| |
| Listing Foreign Keys Without Corresponding Indexes | |
| |
| |
| Using SQL to Generate SQL | |
| |
| |
| Describing the Data Dictionary Views in an Oracle Database | |
| |
| |
| |
| Working with Strings | |
| |
| |
| Walking a String | |
| |
| |
| Embedding Quotes Within String Literals | |
| |
| |
| Counting the Occurrences of a Character in a String | |
| |
| |
| Removing Unwanted Characters from a String | |
| |
| |
| Separating Numeric and Character Data | |
| |
| |
| Determining Whether a String Is Alphanumeric | |
| |
| |
| Extracting Initials from a Name | |
| |
| |
| Ordering by Parts of a String | |
| |
| |
| Ordering by a Number in a String | |
| |
| |
| Creating a Delimited List from Table Rows | |
| |
| |
| Converting Delimited Data into a Multi-Valued IN-List | |
| |
| |
| Alphabetizing a String | |
| |
| |
| Identifying Strings That Can Be Treated as Numbers | |
| |
| |
| Extracting the nth Delimited Substring | |
| |
| |
| Parsing an IP Address | |
| |
| |
| |
| Working with Numbers | |
| |
| |
| Computing an Average | |
| |
| |
| Finding the Min/Max Value in a Column | |
| |
| |
| Summing the Values in a Column | |
| |
| |
| Counting Rows in a Table | |
| |
| |
| Counting Values in a Column | |
| |
| |
| Generating a Running Total | |
| |
| |
| Generating a Running Product | |
| |
| |
| Calculating a Running Difference | |
| |
| |
| Calculating a Mode | |
| |
| |
| Calculating a Median | |
| |
| |
| Determining the Percentage of a Total | |
| |
| |
| Aggregating Nullable Columns | |
| |
| |
| Computing Averages Without High and Low Values | |
| |
| |
| Converting Alphanumeric Strings into Numbers | |
| |
| |
| Changing Values in a Running Total | |
| |
| |
| |
| Date Arithmetic | |
| |
| |
| Adding and Subtracting Days, Months, and Years | |
| |
| |
| Determining the Number of Days Between Two Dates | |
| |
| |
| Determining the Number of Business Days Between Two Dates | |
| |
| |
| Determining the Number of Months or Years Between Two Dates | |
| |
| |
| Determining the Number of Seconds, Minutes, or Hours Between Two Dates | |
| |
| |
| Counting the Occurrences of Weekdays in a Year | |
| |
| |
| Determining the Date Difference Between the Current Record and the Next Record | |
| |
| |
| |
| Date Manipulation | |
| |
| |
| Determining if a Year Is a Leap Year | |
| |
| |
| Determining the Number of Days in a Year | |
| |
| |
| Extracting Units of Time from a Date | |
| |
| |
| Determining the First and Last Day of a Month | |
| |
| |
| Determining All Dates for a Particular Weekday Throughout a Year | |
| |
| |
| Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month | |
| |
| |
| Creating a Calendar | |
| |
| |
| Listing Quarter Start and End Dates for the Year | |
| |
| |
| Determining Quarter Start and End Dates for a Given Quarter | |
| |
| |
| Filling in Missing Dates | |
| |
| |
| Searching on Specific Units of Time | |
| |
| |
| Comparing Records Using Specific Parts of a Date | |
| |
| |
| Identifying Overlapping Date Ranges | |
| |
| |
| |
| Working with Ranges | |
| |
| |
| Locating a Range of Consecutive Values | |
| |
| |
| Finding Differences Between Rows in the Same Group or Partition | |
| |
| |
| Locating the Beginning and End of a Range of Consecutive Values | |
| |
| |
| Filling in Missing Values in a Range of Values | |
| |
| |
| Generating Consecutive Numeric Values | |
| |
| |
| |
| Advanced Searching | |
| |
| |
| Paginating Through a Result Set | |
| |
| |
| Skipping n Rows from a Table | |
| |
| |
| Incorporating OR Logic when Using Outer Joins | |
| |
| |
| Determining Which Rows Are Reciprocals | |
| |
| |
| Selecting the Top n Records | |
| |
| |
| Finding Records with the Highest and Lowest Values | |
| |
| |
| Investigating Future Rows | |
| |
| |
| Shifting Row Values | |
| |
| |
| Ranking Results | |
| |
| |
| Suppressing Duplicates | |
| |
| |
| Finding Knight Values | |
| |
| |
| Generating Simple Forecasts | |
| |
| |
| |
| Reporting and Warehousing | |
| |
| |
| Pivoting a Result Set into One Row | |
| |
| |
| Pivoting a Result Set into Multiple Rows | |
| |
| |
| Reverse Pivoting a Result Set | |
| |
| |
| Reverse Pivoting a Result Set into One Column | |
| |
| |
| Suppressing Repeating Values from a Result Set | |
| |
| |
| Pivoting a Result Set to Facilitate Inter-Row Calculations | |
| |
| |
| Creating Buckets of Data, of a Fixed Size | |
| |
| |
| Creating a Predefined Number of Buckets | |
| |
| |
| Creating Horizontal Histograms | |
| |
| |
| Creating Vertical Histograms | |
| |
| |
| Returning Non-GROUP BY Columns | |
| |
| |
| Calculating Simple Subtotals | |
| |
| |
| Calculating Subtotals for All Possible Expression Combinations | |
| |
| |
| Identifying Rows That Are Not Subtotals | |
| |
| |
| Using Case Expressions to Flag Rows | |
| |
| |
| Creating a Sparse Matrix | |
| |
| |
| Grouping Rows by Units of Time | |
| |
| |
| Performing Aggregations over Different Groups/Partitions Simultaneously | |
| |
| |
| Performing Aggregations over a Moving Range of Values | |
| |
| |
| Pivoting a Result Set with Subtotals | |
| |
| |
| |
| Hierarchical Queries | |
| |
| |
| Expressing a Parent-Child Relationship | |
| |
| |
| Expressing a Child-Parent-Grandparent Relationship | |
| |
| |
| Creating a Hierarchical View of a Table | |
| |
| |
| Finding All Child Rows for a Given Parent Row | |
| |
| |
| Determining Which Rows Are Leaf, Branch, or Root Nodes | |
| |
| |
| |
| Odds 'n' Ends | |
| |
| |
| Creating Cross-Tab Reports Using SQL Server's PIVOT Operator | |
| |
| |
| Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator | |
| |
| |
| Transposing a Result Set Using Oracle's MODEL Clause | |
| |
| |
| Extracting Elements of a String from Unfixed Locations | |
| |
| |
| Finding the Number of Days in a Year (an Alternate Solution for Oracle) | |
| |
| |
| Searching for Mixed Alphanumeric Strings | |
| |
| |
| Converting Whole Numbers to Binary Using Oracle | |
| |
| |
| Pivoting a Ranked Result Set | |
| |
| |
| Adding a Column Header into a Double Pivoted Result Set | |
| |
| |
| Converting a Scaiar Subquery to a Composite Subquery in Oracle | |
| |
| |
| Parsing Serialized Data into Rows | |
| |
| |
| Calculating Percent Relative to Total | |
| |
| |
| Creating CSV Output from Oracle | |
| |
| |
| Finding Text Not Matching a Pattern (Oracle) | |
| |
| |
| Transforming Data with an Inline View | |
| |
| |
| Testing for Existence of a Value Within a Group | |
| |
| |
| |
| Window Function Refresher | |
| |
| |
| |
| Rozenshtein Revisited | |
| |
| |
| Index | |