3 Modern SQL
This chapter covers
- Why modern SQL is important to learn
- Using common table expressions for concise, readable, and manageable queries
- Processing hierarchical or tree-like data using recursive queries
- Using window functions to perform calculations over groups of related rows
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.
In this chapter, you are introduced to modern SQL and explore some of its most prominent capabilities. Using a music streaming service as an example, you will learn how common table expressions (CTEs), recursive queries, and window functions can solve sophisticated data processing tasks in a simple manner.