はてなブックマーク

In December 2016, ISO released a new version of the international SQL standard (ISO/IEC 9075:2016). It supersedes the previous version from 2011. This article is a brief overview of the new features being introduced into the SQL language. Strictly speaking, this article covers the additions to part 2 of the standard (SQL/Foundation), i.e. the most commonly used part. This article also shows the av

Pivoting data is a rather common problem that comes in many different flavors. At its heart, the requirement is to transpose data from multiple rows into columns of a single row. This requirement is particularity common in a reporting context. The following explanation is therefore based on a query that reports monthly revenues: SELECT EXTRACT(YEAR FROM invoice_date) year , EXTRACT(MONTH FROM invo

Literate programming is an approach to programming introduced by Donald Knuth that, among other ideas, aims to arrange source code in order of human logic so that it can be understood by reading it like a novel: from the beginning to the end. SQL works in the opposite way: due to the nesting of subqueries we must find and understand the innermost query and then start working from inside out. Not e

In software engineering, it is common practice to group instructions as small and easily comprehensible units—namely functions or methods. This makes the code reusable and improves readability. Even though SQL has functions and procedures as well, they are not the natural tools for building easily understandable and reusable units. In SQL, neither functions nor procedures are first-class citizens

Since 1999, SQL is not limited to the relational model anymore. Back then ISO/IEC 9075 (the “SQL standard”) added arrays, objects and recursive queries. In the meanwhile the SQL standard has grown five times bigger than SQL-92. In other words: relational SQL is only about 20% of modern SQL.0 If you can spare 15 minutes I’d like to tell you this story in this video. Alternatively on YouTube. Record