Book Review: Practical Oracle SQL by Kim Berg Hansen

I work with SQL since decades, and I think to have some practical experience with writing SQL queries on Oracle databases. So why should I buy a book about practical Oracle SQL? If you are in a similar situation like me, read this book review.

The main reason for me to buy the book Practical Oracle SQL: Mastering the Full Power of Oracle Database, written by Kim Berg Hansen, was that I know Kim as a good colleague and know about his excellent knowledge in SQL. Like me, Kim works for Trivadis, is an Oracle ACE Director and a (part-time) beer brewer. Additionally, he is the SQL quizmaster of Oracle Dev Gym. I can’t remember when Kim first told me that he was going to write a book about SQL. It was either on a Trivadis TechEvent or ODTUG Kscope conference, and I remember that we were enjoying a beer together. For me it was clear immediately that I will order a copy of his book as soon as it will be available.

End of February the book was published, and beginning of March, I found a copy in my mailbox. Just right in time before the coronavirus lockdown started and I had plenty of time to sit on the sofa or on the balcony to read the book.

First Impression

One point is clear from the at first glimpse of the book: It is not an introduction for SQL beginners to learn the query language. To benefit from the content of each chapter, you should have good experience with SQL and Oracle databases. The book starts where many other textbooks stop: With all those advanced SQL features that are very useful for many purposes, if you master them properly. So, do not expect excessive syntax details about the basics of SQL, but enjoy the interesting use cases and how they can be solved with different advanced SQL features.

Good Beer Trading Co.

Speaking of interesting use cases: All the examples are based on a fictional beer trading company, the Good Beer Trading Co. This was another good reason for me to buy the book. While reading the examples, I recommend to have a good beer within easy reach, because the SQL examples can make you thirsty.

Although it’s nice to read a book while drinking a beer on the balcony, it’s also recommended to have a laptop nearby with access to an Oracle database. For some of the more complex examples, I wanted to know more details and tested them step by step. For this, you can use a set of scripts to create the example schema PRACTICAL with several tables and demo data. I think I will never have the chance to taste such interesting beers like Hoppy Crude Oil, Hercule Trippel or Hazy Pink Cloud (because they are only fictional), but at least I can run all the example queries of the book in my own database. The installation scripts and all SQL examples can be downloaded from GitHub.

Part 1: Core SQL

The book is devided in three parts. The first 10 chapters of Kim’s book are about “Core SQL”. This is definitely an understatement, because they already contain many SQL features that you (and I) probably don’t use every day. Kim writes at the beginning of the book that Part 1 contains “everything that does not fit in Part 2 and Part 3”. A very pragmatic description!

For example, I often use Set Operators (UNION, UNION ALL, MINUS, INTERSECT), but I never used so far Multiset Set Operators like MULTISET UNION DISTINCT or MULTISET EXCEPT ALL. I think every reader of the book – not only me – will find some details to improve his/her knowledge about SQL.

Each chapter starts with a particular use case of the Good Beer Trading Co. and shows with different SQL techniques how it can be solved. In the chapter about unpivoting columns to rows, Kim explains different ways to analyze webshop data. In another chapter he shows how we can create a comma-separated list of all beer names per brewery with the LISTAGG function, but also with some other SQL functions and extensions. A very nice use case – although it is the only example in the book not related to beer – is the implementation of Conway’s Game of Life with the SQL Model Clause.

Part 2: Analytic Functions

When Analytic Functions were introduced with Oracle 8i, and my first impression was: “I will never use this feature, the syntax is too complicated!”. Since years I use them almost every day. I agree with Kim that Analytic Functions are one of the most important features of the SQL language. He dedicates them the whole Part 2, six chapters of his book.

After an introduction chapter about purpose and syntax of the Query Partition Clause, the Order By Clause and the Windowing Clause, Kim describes several use cases for Analytic Functions in additional chapters. For example, top-N queries on sales data of the Good Beer Trading Co., ordered subsets with rolling sums, analyzing activity logs or forecasting with linear regression.

When starting to read a new chapter, I always thought to know how it works, but a few pages later, I always found some specialities and interesting details I wasn’t aware of. For me, reading these chapters was an efficient way to improve my knowledge about Analytic Functions.

Part 3: Row Pattern Matching

I had several ups and downs while reading the book. Fortunately, Part 3 explains how these ups and downs can be analyzed with SQL. Oracle introduced Row Pattern Matching with the database version 12.2, which allows to find for example such “up and down” patterns in row sets of a query.

As in the previous two parts of the book, Kim explains with interesting use cases how the match_recognize clause helps to find patterns in the data of the Good Beer Trading Co. Six chapters are dedicated to this quite new and for many developers still unknown feature. Here again: I knew Row Pattern Matching before, but learnt much more details about it than I expected.

Conclusion

If you are new with SQL and still struggling with the syntax basics, the book Practical Oracle SQL: Mastering the Full Power of Oracle Database is not the right reading for you right now. However, if you are already working with SQL for a while and want to learn more about the advanced features of SQL in Oracle databases, I highly recommend that you read this book. And if you think you know everything about SQL and won’t learn anything new, then I recommend Kim’s book even more. If only because of the wonderful use cases.

So, buy the book in your favourite book store, download the scripts from GitHub, install them on your Oracle database, open a good bottle of beer and start to read. Have fun!