3 Modern SQL
This chapter covers
- Why it’s important to learn modern SQL
- Using common table expressions
- Processing hierarchical or tree-like data
- Using window functions to perform calculations
SQL was invented in the early 1970s, predating the creation of Altair, the first personal computer. Despite being invented decades ago, SQL, like many human languages, continues to evolve to meet the demands of the modern world.
Most people, however, are still familiar with SQL as defined by the SQL-92 standard, which fully completed the original relational model and idea. But much has changed since the introduction of that standard. The scope of relational databases has expanded far beyond the relational model, and these changes have been reflected in subsequent versions of the language. Modern SQL introduces new capabilities that not only make the language more readable and easier to follow but also simplify complex calculations and enable working with unstructured and semi-structured data.
Let’s explore the modern SQL capabilities of Postgres as we build a music streaming service that thousands of people use to listen to their favorite songs and artists. We’ll see how modern SQL makes it easy to analyze user preferences, song popularity, and the other data the service tracks.