Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.
Ditto! For anyone who isn't familiar with SQLite's testing procedures, read this[1] fascinating page.
The SQLite project has a mind boggling 711 times more test code than SQLite itself has. Put another way, only 0.1% of the project's code is SQLite itself. The other 99.9% consists of tests for that 0.1%.
In a similar vein, I rarely (if ever) seen a library that handles dynamic memory allocation more robustly than SQLite. This page is a glory to behold: https://www.sqlite.org/malloc.html
That page is inspiring, for a commodity, open source and old software project everything is very clearly defined and the language is very definitive which I hope is indicative of the actual library quality (I’ve never used SQLite).
Although it was more a typo than a math error (as the 99.9% figure was correct). I'll chalk it up to posting when I should be sleeping (4:30am local time).
This looks great, but I couldn't get through the first question on aggregate functions. Are there any SQL books/tutorials that go over things like this?
A lot of material I've seen has been like the classic image of "How to draw an owl. First draw two circles, then draw the rest of the owl", where they tell you the super basic stuff, then assume you know everything.
Having an understanding of relational algebra (not the symbols, but the concept; math is always about the concept) generally helps a lot as well; it's the reason why compsci database lectures often start with relational algebra.
> We would like to find the total weight of cats grouped by age. But only return those groups with a total weight larger than 12.
The total weight of cats grouped by age.
SELECT sum(weight), age
FROM cats
GROUP BY age
But only return those groups with a total weight larger than 12.
SELECT sum(weight), age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
Ordered by age.
SELECT sum(weight), age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
ORDER BY age
The total weight column should be called total_weight.
SELECT sum(weight) AS total_weight, age
FROM cats
GROUP BY age
HAVING sum(weight) > 12
ORDER BY age
(self plug) try pgexercises.com. It's in a very similar vein to windowfunctions - I understand it was one of the original inspirations - but it covers a much wider range of material, with a gradual growth from very basic stuff up to slightly advanced.
I'll vouch for this plug! I've spent quite a bit of time learning SQL (and Postgres specific stuff), and this was one of my favorite resources.
I can also recommend Learning SQL by Alan Beaulieu, which was recommended in some HN comment. After looking into various books, some of them Postgres specific, I found this one to be the best for learning from scratch.
The discussion at https://news.ycombinator.com/item?id=17231349 has many more links and descriptions of how to use window functions in practice. (Including my comment at the top about how in practice I find that you usually want to combine window functions with nested queries.)
Besides that, the comprehensive testing and evaluation of SQLite never ceases to amaze me. I'm usually hesitant to call software development "engineering", but SQLite is definitely well-engineered.