Books & Videos

Table of Contents

  1. Chapter 1 Retrieving Records

    1. Retrieving All Rows and Columns from a Table

    2. Retrieving a Subset of Rows from a Table

    3. Finding Rows That Satisfy Multiple Conditions

    4. Retrieving a Subset of Columns from a Table

    5. Providing Meaningful Names for Columns

    6. Referencing an Aliased Column in the WHERE Clause

    7. Concatenating Column Values

    8. Using Conditional Logic in a SELECT Statement

    9. Limiting the Number of Rows Returned

    10. Returning n Random Records from a Table

    11. Finding Null Values

    12. Transforming Nulls into Real Values

    13. Searching for Patterns

  2. Chapter 2 Sorting Query Results

    1. Returning Query Results in a Specified Order

    2. Sorting by Multiple Fields

    3. Sorting by Substrings

    4. Sorting Mixed Alphanumeric Data

    5. Dealing with Nulls when Sorting

    6. Sorting on a Data Dependent Key

  3. Chapter 3 Working with Multiple Tables

    1. Stacking One Rowset atop Another

    2. Combining Related Rows

    3. Finding Rows in Common Between Two Tables

    4. Retrieving Values from One Table That Do Not Exist in Another

    5. Retrieving Rows from One Table That Do Not Correspond to Rows in Another

    6. Adding Joins to a Query Without Interfering with Other Joins

    7. Determining Whether Two Tables Have the Same Data

    8. Identifying and Avoiding Cartesian Products

    9. Performing Joins when Using Aggregates

    10. Performing Outer Joins when Using Aggregates

    11. Returning Missing Data from Multiple Tables

    12. Using NULLs in Operations and Comparisons

  4. Chapter 4 Inserting, Updating, Deleting

    1. Inserting a New Record

    2. Inserting Default Values

    3. Overriding a Default Value with NULL

    4. Copying Rows from One Table into Another

    5. Copying a Table Definition

    6. Inserting into Multiple Tables at Once

    7. Blocking Inserts to Certain Columns

    8. Modifying Records in a Table

    9. Updating when Corresponding Rows Exist

    10. Updating with Values from Another Table

    11. Merging Records

    12. Deleting All Records from a Table

    13. Deleting Specific Records

    14. Deleting a Single Record

    15. Deleting Referential Integrity Violations

    16. Deleting Duplicate Records

    17. Deleting Records Referenced from Another Table

  5. Chapter 5 Metadata Queries

    1. Listing Tables in a Schema

    2. Listing a Table's Columns

    3. Listing Indexed Columns for a Table

    4. Listing Constraints on a Table

    5. Listing Foreign Keys Without Corresponding Indexes

    6. Using SQL to Generate SQL

    7. Describing the Data Dictionary Views in an Oracle Database

  6. Chapter 6 Working with Strings

    1. Walking a String

    2. Embedding Quotes Within String Literals

    3. Counting the Occurrences of a Character in a String

    4. Removing Unwanted Characters from a String

    5. Separating Numeric and Character Data

    6. Determining Whether a String Is Alphanumeric

    7. Extracting Initials from a Name

    8. Ordering by Parts of a String

    9. Ordering by a Number in a String

    10. Creating a Delimited List from Table Rows

    11. Converting Delimited Data into a Multi-Valued IN-List

    12. Alphabetizing a String

    13. Identifying Strings That Can Be Treated as Numbers

    14. Extracting the nth Delimited Substring

    15. Parsing an IP Address

  7. Chapter 7 Working with Numbers

    1. Computing an Average

    2. Finding the Min/Max Value in a Column

    3. Summing the Values in a Column

    4. Counting Rows in a Table

    5. Counting Values in a Column

    6. Generating a Running Total

    7. Generating a Running Product

    8. Calculating a Running Difference

    9. Calculating a Mode

    10. Calculating a Median

    11. Determining the Percentage of a Total

    12. Aggregating Nullable Columns

    13. Computing Averages Without High and Low Values

    14. Converting Alphanumeric Strings into Numbers

    15. Changing Values in a Running Total

  8. Chapter 8 Date Arithmetic

    1. Adding and Subtracting Days, Months, and Years

    2. Determining the Number of Days Between Two Dates

    3. Determining the Number of Business Days Between Two Dates

    4. Determining the Number of Months or Years Between Two Dates

    5. Determining the Number of Seconds, Minutes, or Hours Between Two Dates

    6. Counting the Occurrences of Weekdays in a Year

    7. Determining the Date Difference Between the Current Record and the Next Record

  9. Chapter 9 Date Manipulation

    1. Determining if a Year Is a Leap Year

    2. Determining the Number of Days in a Year

    3. Extracting Units of Time from a Date

    4. Determining the First and Last Day of a Month

    5. Determining All Dates for a Particular Weekday Throughout a Year

    6. Determining the Date of the First and Last Occurrence of a Specific Weekday in a Month

    7. Creating a Calendar

    8. Listing Quarter Start and End Dates for the Year

    9. Determining Quarter Start and End Dates for a Given Quarter

    10. Filling in Missing Dates

    11. Searching on Specific Units of Time

    12. Comparing Records Using Specific Parts of a Date

    13. Identifying Overlapping Date Ranges

  10. Chapter 10 Working with Ranges

    1. Locating a Range of Consecutive Values

    2. Finding Differences Between Rows in the Same Group or Partition

    3. Locating the Beginning and End of a Range of Consecutive Values

    4. Filling in Missing Values in a Range of Values

    5. Generating Consecutive Numeric Values

  11. Chapter 11 Advanced Searching

    1. Paginating Through a Result Set

    2. Skipping n Rows from a Table

    3. Incorporating OR Logic when Using Outer Joins

    4. Determining Which Rows Are Reciprocals

    5. Selecting the Top n Records

    6. Finding Records with the Highest and Lowest Values

    7. Investigating Future Rows

    8. Shifting Row Values

    9. Ranking Results

    10. Suppressing Duplicates

    11. Finding Knight Values

    12. Generating Simple Forecasts

  12. Chapter 12 Reporting and Warehousing

    1. Pivoting a Result Set into One Row

    2. Pivoting a Result Set into Multiple Rows

    3. Reverse Pivoting a Result Set

    4. Reverse Pivoting a Result Set into One Column

    5. Suppressing Repeating Values from a Result Set

    6. Pivoting a Result Set to Facilitate Inter-Row Calculations

    7. Creating Buckets of Data, of a Fixed Size

    8. Creating a Predefined Number of Buckets

    9. Creating Horizontal Histograms

    10. Creating Vertical Histograms

    11. Returning Non-GROUP BY Columns

    12. Calculating Simple Subtotals

    13. Calculating Subtotals for All Possible Expression Combinations

    14. Identifying Rows That Are Not Subtotals

    15. Using Case Expressions to Flag Rows

    16. Creating a Sparse Matrix

    17. Grouping Rows by Units of Time

    18. Performing Aggregations over Different Groups/Partitions Simultaneously

    19. Performing Aggregations over a Moving Range of Values

    20. Pivoting a Result Set with Subtotals

  13. Chapter 13 Hierarchical Queries

    1. Expressing a Parent-Child Relationship

    2. Expressing a Child-Parent-Grandparent Relationship

    3. Creating a Hierarchical View of a Table

    4. Finding All Child Rows for a Given Parent Row

    5. Determining Which Rows Are Leaf, Branch, or Root Nodes

  14. Chapter 14 Odds 'n' Ends

    1. Creating Cross-Tab Reports Using SQL Server's PIVOT Operator

    2. Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator

    3. Transposing a Result Set Using Oracle's MODEL Clause

    4. Extracting Elements of a String from Unfixed Locations

    5. Finding the Number of Days in a Year (an Alternate Solution for Oracle)

    6. Searching for Mixed Alphanumeric Strings

    7. Converting Whole Numbers to Binary Using Oracle

    8. Pivoting a Ranked Result Set

    9. Adding a Column Header into a Double Pivoted Result Set

    10. Converting a Scalar Subquery to a Composite Subquery in Oracle

    11. Parsing Serialized Data into Rows

    12. Calculating Percent Relative to Total

    13. Creating CSV Output from Oracle

    14. Finding Text Not Matching a Pattern (Oracle)

    15. Transforming Data with an Inline View

    16. Testing for Existence of a Value Within a Group

  1. Appendix A Window Function Refresher

    1. Grouping

    2. Windowing

  2. Appendix B Rozenshtein Revisited

    1. Rozenshtein's Example Tables

    2. Answering Questions Involving Negation

    3. Answering Questions Involving "at Most"

    4. Answering Questions Involving "at Least"

    5. Answering Questions Involving "Exactly"

    6. Answering Questions Involving "Any" or "All"

  3. Colophon