Advanced SQL and database books and resources

For people who've worked through Mastery with SQL, a common question I get is "What's next?" If you completed the course and all 150+ exercises, at this point you'll have a very strong grasp of SQL and should be comfortable writing all sorts of complex queries. However, there's more to working with databases effectively - you might start to become interested in topics like database design, performance, transactions, locking, logging, security, configuration, and much more. Or you might want to dive deeper on the specific capabilities of the database you're using (e.g. Microsoft SQL Server, MySQL, PostgreSQL, etc.).

So, I've put together a brief list of some of the books and videos I recommend. Note that most of the resources are focused around relational databases since that's my wheelhouse (and I've found that Postgres can be a pretty awesome non-relational database in a pinch too thanks to some great extensions). Enjoy!

Going deeper with databases

To get the most out of whatever database you're using, particularly when it comes to performance, you're going to want to understand more about how it works under the hood. There are two great resources that I recommend.

CMU's Intro to Database Systems

Taught by the rather peculiar but always funny Andy Pavlo from Carnegie Mellon University (I'm sure he'd consider that opener a compliment), this is a fantastic first course in database internals. All lectures are fully available on YouTube, covering everything from indexes to transactions, logging, and how different query algorithms get implemented. What's fantastic as well is that you can access the full course curriculum, including the assignments and projects which you can work along on your own if you want some hands-on practice.

CMU's Advanced Database Systems

I've only recently started my way through these lectures, but from what I've seen so far, they're equally as excellent as the starter content. The focus in particular seems to be on introducing a bit more rigor to concepts introduced in the introductory lectures.

Advanced SQL

SQL Performance Explained - Winand

SQL Performance Explained

What's great about SQL Performance Explained is that it starts with just a little bit of theory (covering B-trees and what an index really is) and then very quickly jumps in to a great rhythm of slowly, bit by bit, building up queries and then looking at and explaining their execution plans. In this way, you build up a very strong foundational understanding of how the different ways you can write a query alter the execution plan. It's database agnostic as well, so the concepts apply equally regardless of whether you're using Oracle, MySQL, etc.

SQL Antipatterns - Karwin

SQL Antipatterns

SQL Antipatterns covers 25 or so different so-called antipatterns, across the spectrum of database design, query writing, and in to the software development stack. Each anti-pattern is accompanied by a description of what it is, why you generally want to avoid it, and a solution. I think it's a great first book for someone who is comfortable with both SQL and database design and is ready to take a first step back and reflect on not just the what, but the how and why of database and query design - prompting reflection on, for example, whether a big query should really be written as several smaller queries (Chapter 18: Spaghetti Query), etc. If you're ready to take that first step, this is a good one.

The Art of SQL - Faroult

The Art of SQL

The Art of SQL is a great book to read after SQL Antipatterns - in many ways, it's a step up the ladder of abstraction, reflecting more on strategies and approaches to writing correct SQL that will stand the test of time (and along the way making allusions to Sun Tzu's The Art of War). If that all sounds a little bit too pretentious to you - as it did me, at first - don't worry, the Sun Tzu stuff is kept to a minimum and you can mostly ignore it. As I saw one other reviewer mention, this isn't a cookbook and it won't teach you recipes, but it will make you a better cook - I think that's a fantastic way to put it.

Database specific - PostgreSQL

There are two databases that I've worked extensively with in my career - PostgreSQL and SQL Server. So, I can only share recommendations for these. If you've got any recommendations for MySQL, Oracle, etc. let me know!

PostgreSQL: Up and Running - Obe, Hsu

PostgreSQL: Up and Running

While you'll find a lot of people these days who say "Just use Postgres", unfortunately, there aren't that many great guides on how to administer PostgreSQL well, how to set it up securely, how to run it in production, etc. Unfortunately, this book doesn't completely address that problem - but it does get you at least some of the way. It covers how to do some core database administrative tasks (setup users, roles, backup and restoring), use of psql and pgAdmin, and also how to setup replication. A good book to get you started with PostgreSQL administration if this is a new area for you.

The Art of PostgreSQL - Fontaine

The Art of PostgreSQL

The Art of PostgreSQL is a great book written by Dimitri Fontaine, a major contributor to Postgres. What I love about the book is its full focus on just what PostgreSQL can do for you, whether it be in terms of special PostgreSQL specific data types, SQL enhancements, writing PL/pgSQL functions, or using the built-in notification system - this book makes no attempt to show you the ANSI way. This book gave me a far deeper appreciation for Postgres.

Database specific - SQL Server

T-SQL Querying - Ben-Gan, Machanic, Sarka, Farlee

T-SQL Querying

Microsoft SQL Server was the first database I worked with extensively and I first learned SQL working through an earlier edition of Itzik Ben-gan's T-SQL Fundamentals. So I'm really happy to recommend here T-SQL Querying. This is a big meaty book and is probably the most advanced book in the entire list. It's really closer to a reference vs something you sit down and read end-to-end. I personally found the chapters on window functions and T-SQL for BI practitioners to be invaluable when implementing some analytics dashboards at a previous company.