Recipes that make querying easy, even for the novice.
In today’s information age, we have at our fingertips almost an overabundance of teaching and information on any and every subject. In the midst of this glut of information, it can be hard to decide which is the best source for our needs. But, fear not; we are here to assist you as you navigate through decisions about books on SQL topics! Our first review will cover Anthony Molinaro’s “SQL Cookbook”.
“SQL Cookbook,” written by Anthony Molinaro and published by O’Reilly (famous for their computer science books), has been around since 2005, but its content is still current, standing the test of time. Now, let’s get cooking!
What is a cookbook?
In its essence, a cookbook is a book of recipes: ingredients and instructions which enable you to easily complete a task which might otherwise be out of your reach. What makes a cookbook such a practical and timesaving tool is its presentation of concise how-tos, with theoretical elements left out. It is the same with “SQL Cookbook,” which supplies detailed instructions allowing you to easily perform practical data management or processing tasks.
“SQL Cookbook” is a set of 150 “recipes” for common SQL problems. The 14 chapters increase in difficulty and problem complexity, starting with the simplest data retrieval problems and then working through aggregate functions, common table expressions and other problems, using different elements of SQL syntax.
Every recipe is divided into three parts:
- PROBLEM – Data-related task to be accomplished using SQL
- SOLUTION – SQL coding solution to the problem.
- DISCUSSION – Explanation of key points or difficult/new aspects of the solution.
Here is a brief example of one of many SQL recipes given in the book:
PROBLEM: We have columns with mixed alphanumeric data, and want to sort it by either string part or number part.
SOLUTION: (sample solution for PostgreSQL to order by number):
select data from table_name order by replace(data,replace(translate(data,'0123456789','##########'),'#','')
DISCUSSION: A brief explanation of the
By the way, don’t worry if this problem or solution seems confusing to you. The beginning of the book is simple. The author has ordered the chapters and queries such that the reader will learn one new syntax element at a time; so by the time you get to this problem, you will already have learned what you need to be able to understand it. In the beginning you will start with simple problems, like retrieving data that satisfy multiple conditions; transforming nulls into real values; sorting by substrings, and merging records. (Sorry, listing all 150 recipes is way out of the scope of this review.)
One thing I really liked about “SQL Cookbook” was that the author always provides different solutions for different relational database management systems (RDBMSs). In many cases, RDBMSs differ in syntax or function names (e.g. limiting the number of rows returned in a query requires using a different clause in SQL Server than in PostgreSQL). Giving an answer for every single vendor would be impractical, so the author focuses on five main databases: DB2, Oracle, PostgreSQL, SQL Server, and MySQL. If you use any other, the solutions in this book may not always be correct for you.
The author’s explanation for each of the 150 SQL recipes is both concise and rich in information. The presentation maintains a good balance between bare-minimum example and laborious discussion, as the author clearly but simply prescribes the solution to each problem, discussing in detail only the most important aspects of a given query. In my opinion the problems are well chosen, allowing demonstration of enough SQL function that the reader gains a nearly complete awareness of its many possibilities.
For those who are curious what’s inside the book, here is the list of chapters:
- Retrieving Records
- Sorting Query Results
- Working with Multiple Tables
- Inserting, Updating, Deleting
- Metadata Queries
- Working with Strings
- Working with Numbers
- Date Arithmetic
- Date Manipulation
- Working with Ranges
- Advanced Searching
- Reporting and Warehousing
- Hierarchical Queries
- Odd ‘n’ Ends
You probably won’t be surprised to learn that you will need to install some software to use “SQL Cookbook” to its fullest potential. Mere reading is not enough. Reading may explain how things work, but you have to practice in order to truly understand it. In this book, there are no exercises for readers to solve. You have to write queries, test them, check them, and experiment with the information presented to you. It is important, then, that you have an RDBMS installed so you can make use of the book’s recipes. If you don’t have a database management system installed, you can always download PostgreSQL, MySQL, or other vendors’ free versions.
The cookbook approach is only one of many ways of presenting SQL. Although it is definitely adequate for its purpose of prescribing ready solutions to particular problems, I personally prefer to see a bit more theory, leading to a more profound understanding of SQL and, consequently, more flexible usage of your acquired knowledge. So, I’m not convinced that this book is a perfect choice for a complete beginner, i.e. someone without any prior knowledge of SQL. The author himself seems to agree, as he warns the reader not to try learning SQL from scratch using this book. However, if you’ve worked with SQL syntax before and need a jumpstart in the area of practical application, you will find this book to be an excellent solutions reference. If you are just beginning, check out our SQL Basics course, which is a great place to begin your SQL adventure!