View Larger Image	Anthony Molinaro O'Reilly Media, Paperback, Published December 2005, 504 pages, ISBN 0596009763
	List Price: $39.95 Our Price: $24.95 You Save: $15.00 (38% Off)
	Availability: In-Stock

Read an excerpt: Chapter 11: Advanced Searching       Excerpt provided courtesy of O'Reilly Media. Copyright © O'Reilly Media, Inc Written permission from the publisher is required for any use of this material.

People who purchase this book frequently purchase:

• SQL in a Nutshell, 2nd Edition; Kevin E. Kline, et al, $27.95, 38% Off!
• SQL Hacks; Andrew Cumming, et al, $18.95, 37% Off!
• The Art of SQL; Stephane Faroult, et al, $27.50, 39% Off!
• SQL Tuning; Dan Tow, $24.95, 38% Off!

• Databases : SQL
• * Series - Int/Adv Users : O'Reilly Cookbooks

• O'Reilly Media

You know the rudiments of the SQL query language, yet you feel you aren't taking full advantage of SQL's expressive power. You'd like to learn how to do more work with SQL inside the database before pushing data across the network to your applications. You'd like to take your SQL skills to the next level.

Let's face it, SQL is a deceptively simple language to learn, and many database developers never go far beyond the simple statement: SELECT FROM WHERE . But there is so much more you can do with the language. In the SQL Cookbook, experienced SQL developer Anthony Molinaro shares his favorite SQL techniques and features. You'll learn about:

* Window functions, arguably the most significant enhancement to SQL in the past decade. If you're not using these, you're missing out

* Powerful, database-specific features such as SQL Server's PIVOT and UNPIVOT operators, Oracle's MODEL clause, and PostgreSQL's very useful GENERATE_SERIES function

* Pivoting rows into columns, reverse-pivoting columns into rows, using pivoting to facilitate inter-row calculations, and double-pivoting a result set

* Bucketization, and why you should never use that term in Brooklyn.

* How to create histograms, summarize data into buckets, perform aggregations over a moving range of values, generate running-totals and subtotals, and other advanced, data warehousing techniques

* The technique of walking a string, which allows you to use SQL to parse through the characters, words, or delimited elements of a string

Written in O'Reilly's popular Problem/Solution/Discussion style, the SQL Cookbook is sure to please. Anthony's credo is: "When it comes down to it, we all go to work, we all have bills to pay, and we all want to go home at a reasonable time and enjoy what's still available of our days." The SQL Cookbook moves quickly from problem to solution, saving you time each step of the way.

Table of Contents

Preface

1. 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

2. 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

3. 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 Correspondto 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

4. 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

5. 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

6. 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

7. 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

8. 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 HoursBetween Two Dates
      Counting the Occurrences of Weekdays in a Year
      Determining the Date Difference Between the Current Recordand the Next Record

9. 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 ofa 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

10. 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

11. 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

12. 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

13. 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

14. 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 Scalar 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

A. Window Function Refresher

B. Rozenshtein Revisited

Index

About the Author

Anthony Molinaro is a database developer at Wireless Generation. His interests include databases, databases, and, oh yeah... databases. He currently resides in Baltimore, Maryland with Georgia, his fiancee and favorite periodontist.

Forgot your password? FAQs Shipping Options Returns Your Orders Your Account