SQL Cookbook (Cookbooks (O'Reilly)) and over 2 million other books are available for Amazon Kindle . Learn more

Buy New

Sign in to turn on 1-Click ordering.
Buy Used
Used - Good See details
Price: £17.21

Trade in Yours
For a £5.25 Gift Card
Trade in
More Buying Choices
Have one to sell? Sell yours here
Start reading SQL Cookbook (Cookbooks (O'Reilly)) on your Kindle in under a minute.

Don't have a Kindle? Get your Kindle here, or download a FREE Kindle Reading App.

SQL Cookbook (Cookbooks (O'Reilly)) [Paperback]

Anthony Molinaro
4.5 out of 5 stars  See all reviews (6 customer reviews)
Price: £26.50 & FREE Delivery in the UK. Details
o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o o
Only 5 left in stock (more on the way).
Dispatched from and sold by Amazon. Gift-wrap available.
Want it Saturday, 7 March? Choose Express delivery at checkout. Details
‹  Return to Product Overview

Table of Contents

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; 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; Chapter 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; Chapter 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; Chapter 4: Inserting, Updating, 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; Chapter 5: Metadata Queries; 5.1 Listing Tables in a Schema; 5.2 Listing a Table's 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; Chapter 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 Multi-Valued 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; Chapter 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 Calculating a Running Difference; 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; Chapter 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; Chapter 9: Date Manipulation; 9.1 Determining if 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 Day 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 Occurrence 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; Chapter 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; Chapter 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; Chapter 12: Reporting and Warehousing; 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; Chapter 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; Chapter 14: Odds 'n' Ends; 14.1 Creating Cross-Tab Reports Using SQL Server's PIVOT Operator; 14.2 Unpivoting a Cross-Tab Report Using SQL Server's UNPIVOT Operator; 14.3 Transposing a Result Set Using Oracle's 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 Creating CSV Output from Oracle; 14.14 Finding Text Not Matching a Pattern (Oracle); 14.15 Transforming Data with an Inline View; 14.16 Testing for Existence of a Value Within a Group; Appendix A: Window Function Refresher; A.1 Grouping; A.2 Windowing; Appendix B: Rozenshtein Revisited; B.1 Rozenshtein's Example Tables; B.2 Answering Questions Involving Negation; B.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"; Colophon;

‹  Return to Product Overview