SQL Cookbook

Query Solutions and Techniques for Database Developers

Larger Cover

SQL Cookbook

Query Solutions and Techniques for Database Developers

By Anthony Molinaro

Publisher: O'Reilly Media

Released: December 2005

Pages: 640

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 columns FROM table WHERE conditions. 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.

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

Appendix A Window Function Refresher
1. Grouping
2. Windowing
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"
Colophon

Title:

SQL Cookbook

By:

Anthony Molinaro

Publisher:

O'Reilly Media

Formats:

Print
Ebook
Safari Books Online

Print:

December 2005

Ebook:

February 2009

Pages:

640

Print ISBN:

978-0-596-00976-2

| ISBN 10:

0-596-00976-3

Ebook ISBN:

978-0-596-15897-2

| ISBN 10:

0-596-15897-1

Description

Table of Contents

Product Details

About the Author

Recommended for You

Recently Viewed

Java NIO

By Ron Hitchens

August 2002

Ebook: $31.99

Print & Ebook: $43.99

Print: $39.99

Learning MySQL

Get a Handle on Your Data

By Seyed M.M. Tahaghoghi, Hugh E. Williams

November 2006

Ebook: $35.99

Print & Ebook: $49.49

Print: $44.99

XMPP: The Definitive Guide

Building Real-Time Applications with Jabber Technologies

By Peter Saint-Andre , Kevin Smith, Remko Tronçon

April 2009

Ebook: $31.99

Print & Ebook: $43.99

Print: $39.99

Customer Reviews

REVIEW SNAPSHOT®

by PowerReviews

oreilly SQL Cookbook

4.8

(based on 6 reviews)

REVIEWS

Reviewed by 6 customers

Sort by

Displaying reviews 1-6

11/4/2008

(2 of 2 customers found this review helpful)

5.0

SQL Cookbook Does 'Fill the Bill'

By John Spencer

from Undisclosed

Comments about oreilly SQL Cookbook:

The SQL Cookbook has helped me in so many ways that I cannot express enough appreciation to the author Anthony Molinaro.

Especially well done are the short solutions. These make for a good foundation for developing quite complicated SQL code.

I can see why a downloadable 'package' would be nice. However, since the solutions are short and the text that accompanies the solution is very to the point, I found it not particularly necessary for a downloadable set of source. Ah, but it would be nice...

The coverage of the many many aspects SQL is quite amazing. The explanation of the 'window' functions is clear. I especially like the use of 'advanced' features early in the book and then seeing the explanations.

The book does really help generate ideas--As I am sure the author intended.

I will confess that I have two copies -- one a work and one at home. Overkill? I did not think so when I could grab the book for an solution/idea just about anywhere I happened to be.

10/5/2008

(1 of 1 customers found this review helpful)

5.0

SQL Toolbox Must-Have

By Larry

from Undisclosed

Comments about oreilly SQL Cookbook:

The thing I like about the O'Reilly 'Cookbook' series is that you may not have to use them for months, but when you need them they are there with the answers. And oftentimes finding the "one answer" that has been eluding you for hours or even days is worth the price of the book.

This book falls into that category. What's really nice is that the author provides solutions for 5(!) different databases. And he does a good job explaining the solutions, making it easy to extrapolate them to solve similar queries.

11/15/2007

(5 of 7 customers found this review helpful)

5.0

Not able to find code donload link!

By Gurjeet

from Undisclosed

Comments about oreilly SQL Cookbook:

Hi there.

The sql cook book book is really gr8...But some one pls tell me that where is the link to the code used in this book as this book doesnt have any cd an also there is no online link for code download...any help would me much appreciated!

7/3/2006

(1 of 1 customers found this review helpful)

5.0

Excellent desktop resource book!

By Roanoke Valley SQL Server Users Group

from Undisclosed

Comments about oreilly SQL Cookbook:

This book review was submitted by a Roanoke Valley SQL Server Users Group member as part of the Book Review Program.

Reviewed by Harold Buckner

This is a great book to have available at your fingertips. The table of contents is structured quite nicely and allows you to find what you need very quickly. The book covers a number of topics for solving everyday problems. It will also help you come up with ideas on how to go about handling particular queries by describing the problems and then listing possible solutions. And in these solutions you can find techniques on how to expand on them to resolve other issues.

The author Anthony Molinaro, included a chapter on Metadata Queries that would be great for the beginner DBA wanting to find information about the database schema as well as other chapters on String manipulation, working with numbers, dates, warehousing and hierarchical queries. The author includes solutions for various platforms that I thought were nice to see how the different platforms handled the solutions. I also thought the author did a great job expanding the solution to help you understand what was happening.

Great job Anthony and I hope to see more structured like this one.

Harold Buckner

6/12/2006

(3 of 3 customers found this review helpful)

4.0

A MUST HAVE book for anyone who works with real-world databases

By vCurios

from Undisclosed

Comments about oreilly SQL Cookbook:

Discovering Anthony Molinaro's excellent book was pure serendipity and a wonderful one at that. For a while now I have been cobbling together notes on how to programmatically access some 300 servers variously hosting Microsoft SQL Server, MySQL, and Oracle. The cookbook covers these three (as well as IBM DB2 and PostgresSQL).

I am 100% sure Mr. Molinaro's SQL Cookbook will save me dozens of hours of navigating through (a) the obtuse documentation of the three vendors and trying to sort out their respective dialects, and (b) the many well-meaning but poorly written SQL help sites that come up in Google. Indeed, as far as I am concerned, the book has already more than paid for itself in the time saved. A key strength of this cookbook (in addition to being very well written, edited, and produced) is it is very logically organized and makes it quite easy to find the topic you want.

The only reason I gave it a four-star rating is that it doesn't have all the answers I need. If it did, it would have been a goldmine! But, seriously, if you are "SQL-challenged" like some of us (i.e., not an SQL guru), and you think your time is worth more than $40 an hour, you should get this book, even if you are working with just one of the three SQL platforms. If you are working with two or more versions, get this book, regardless!

I'd like to take this opportunity to thank Mr. Molinaro for doing a great job helping me devote the time saved to other equally challenging adventures, like golf!

3/18/2006

(3 of 3 customers found this review helpful)

5.0

Great stuff!

By Dima

from Undisclosed

Comments about oreilly SQL Cookbook:

Simply great stuff you won't find anywhere else!

Displaying reviews 1-6

Save a Tree - Go Digital what is this?

Ebook: $31.99

Formats: APK, DAISY, ePub, Mobi, PDF

Print & Ebook: $43.95

Print: $39.95

Safari Books Online - Read now >

Chapter 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

Chapter 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

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

Chapter 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

Chapter 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

Chapter 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

Chapter 7 Working with Numbers

Computing an Average

Finding the Min/Max Value in a Column

Summing the Values in a Column