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

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects.

The animal on the cover of SQL Cookbook is an Agamid lizard. These lizards belong to the Agamidae family and have more than 300 species among them. Agamids can be found in Africa, Asia, Australia, and Southern Europe, and are characterized by strong legs and—in some varieties—the ability to change color. Unlike other species of lizards, agamids cannot regenerate their tails if they lose them. They can be found in varied environments from hot deserts to warm, wet tropical rainforests.

Several species of agamids are popular as pets. Among these are the Bearded Dragon (genus Pogona). Calm, yet curious, these creatures grow to be only about 20 inches. Even with their small stature, they are still considered “giant” lizards, and therefore require ample space. Males are generally territorial and, although they are social animals, overcrowding can lead to stress, especially when the animals have no place to hide. Overcrowding can lead to injuries from fighting such as lost toes and tails, as well as a loss of appetite.

The head of the bearded lizard is triangular in shape and features many spikes protruding from its chin. These spikes resemble whiskers (thus the name). The spikes are also found along its side. Bearded dragons open their ...