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
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
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
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
Chapter 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 Hours Between Two Dates
Counting the Occurrences of Weekdays in a Year
Determining the Date Difference Between the Current Record and the Next Record
Chapter 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 of a 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
Chapter 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
Chapter 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
Finding Knight Values
Generating Simple Forecasts
Chapter 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
Chapter 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
Chapter 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
Anthony Molinaro is a SQL developer and database administrator with many years experience in helping developers improve their SQL queries. SQL is particular passion of Anthony's, and he's become known as the go-to guy among his clients when it comes to solving difficult SQL query problems. He's well-read, understands relational theory well, and has nine years of hands-on experience solving tough, SQL problems. Anthony is particularly well-acquainted with new and powerful SQL features such as the windowing function syntax that was added to the most recent SQL standard.
Don't be fooled by the title -- this is not just a "Cookbook".
Through discussion of approaches to common SQL problems on various platforms the author provides insightful and practical advice on structuring queries and covers the gamut of need-to-know tools like pivoting, running totals, date differences between records and so on.
The addendum on window functions and the aside on "Frege's Axiom and Russell's Paradox" are fascinating reading for any wannabe DBA and put the book into another league.
I highly recommend this book.
Bottom Line Yes, I would recommend this to a friend
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.
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.
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!
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.
A MUST HAVE book for anyone who works with real-world databases
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!