Each part of this course can be read individually, or we can go through the contents of the course in order—from the first lesson to the last. A lot of thought has been put into the ordering of the chapters, so a linear reading will provide a more conducive learning experience.

This course is not an alternative to the PostgreSQL manual. This course offers a different approach than what is expected from a reference manual, and it is in no way to be considered a replacement. Bits and pieces from the PostgreSQL documentation are quoted when necessary. This course also contains lots of links to the reference pages of the functions and SQL commands we utilize in our practical use cases. It’s a good idea to refer to the PostgreSQL documentation now and then and read it carefully.

Finally, the psql application also includes online assistance with \h <sql command>.

The skill progression throughout the course is not linear. Each time a new SQL concept is introduced, you’re presented with simple queries to make it possible for you to focus on these new concepts. Then, more queries are introduced to answer more interesting and advanced questions.

Sometimes, when a new chapter introduces a new SQL concept, the complexity is reset to simple queries again. That’s because, for most people, learning a new skill set does not happen in a linear way. Having this kind of difficulty organization also makes it easier to dive into a given chapter out of order.

Here’s a flow of the content used in this course:

Introduction to Structured Query Language

This course intends to highlight that there is more to SQL than application developers might think.

The course begins with straightforward datasets and simple queries that we compare to their equivalent Python code. We then expand on the concepts with a very important trick that’s not well-known and is a pretty advanced variation.

Writing SQL queries

The next part of the course covers how to write an SQL query.

We answer several important questions in that part of the course, such as:

  • Why use SQL rather than our usual programming language?
  • How do we integrate SQL in our application source code?
  • How do we work at the SQL prompt, the psql REPL?
  • What’s an indexing strategy, and how do we approach indexing?

A simple Python application is introduced as a practical example illustrating the different answers provided. In particular, this part insists on when to use SQL to implement business logic.

The SQL toolbox

Part four of this course introduces most of the SQL concepts that we need to master as application developers. It begins with the basics and we’ll build our knowledge and skill set on top of these foundations.

Advanced SQL concepts are introduced with practical examples: every query refers to a data model that’s easy to understand and is given in the context of a “business case” or “user story.”

This part covers SQL clauses and features such as ORDER BY and k-NN sorts, the GROUP BY and HAVING clauses, and GROUPING SETS, along with classic and advanced aggregates and then window functions. This part also covers the infamous NULL and what’s a relation and a join.

Data types

The next part covers the main PostgreSQL data types we can use and benefit from. PostgreSQL is an object-oriented relational database manager (ORDBMS), which means that data types in PostgreSQL are not just classic numbers, dates, and text. There are more than the typical data types, and this part covers those.

Data modeling

This chapter covers the basics of relational data modeling, which is the most important skill we need to master as application developers. If we’re using a good database model, every single SQL query is easy to write, things are kept logical, and data is kept clean.

This part comes later in the course for a reason: without knowledge of some of the advanced SQL facilities, it’s hard to anticipate that a data model is going to be easy enough to work with, and developers then tend to apply early optimizations to the model to try to simplify writing the code. Most of those optimizations are detrimental to our ability to benefit from SQL.

Data manipulation and concurrency control

This part of the course covers DML and concurrency, the heart of any live database. DML stands for data manipulation language; it’s part of SQL that includes INSERT, UPDATE, and DELETE statements.

The main feature of any RDBMS is how it deals with concurrent access to a single dataset in both reading and writing. This part covers isolation and locking, computing and caching in SQL (complete with cache invalidation techniques), and more.

PostgreSQL extensions

The last part covers a selection of very useful PostgreSQL extensions and their impact on simplifying application development when using PostgreSQL.

We’ll cover auditing changes with hstore, the pg_trgm extension to implement autosuggestions and autocorrect in our application search forms, user-defined tags and how to efficiently use them in search queries, and then we use ip4r for implementing geolocation-oriented features.

Finally, HyperLogLog is introduced to solve a classic problem with high cardinality estimates and how to combine these estimates.

Intended audience

This course is intended for developers working on applications that use a database server. The course specifically addresses the PostgreSQL RDBMS and is the world’s most advanced open-source relational database, just like it says in the tagline on the official website.

In most cases, developers are not familiar with most of the functionalities of SQL. With a small and efficient SQL query, we can replace hundreds of lines of application code but in some cases, developers don’t know how to integrate a raw SQL query in their codebase.

Integrating an SQL query and thinking about SQL as code means using the same advanced tools we use with other programming languages: versioning, automated testing, code reviewing, and deployment. Really, this is more about the developer’s workflow than the SQL code itself.

In this course, you’ll learn best practices that help with integrating SQL into your own workflow, and through the many examples provided, you’ll see all the reasons why you might be interested in doing more in SQL—primarily, writing fewer lines of code. As DijkstraEdsger W. Dijkstra is a renowned Dutch computer scientist and software engineer. said, we should count lines of code as lines spent, so by learning how to use SQL, you’ll be able to spend less to write the same application!