Humble SQL

Do find more about him on his Stand-Up Maths Youtube channel.

Humble Pi by Matt Parker

Also, have a look at his recent book Humble Pi. Maybe even buy yourself a copy. I did so and can’t stop reading. OK, well, enough of this free advertising… #notspon. You now should have picked up the reason why I went for the remarkable blog post title though.

I guess I love both maths and SQL. And coming to think of it, when visiting Oracle conferences like Tech Experience or UKOUG Technology Conference, sessions tending to a comedy act on real-world SQL problems do appear to stick the best. And luckily, the SQL-subject presenters of these conferences have been doing quite well  on that!

What does it take to present on a SQL subject in a comedian style? Besides the obvious presenter skills, you would need real world SQL experience, like actually having been involved in a SQL problem, preferably in context of a bug in an application in production. Also, you would need to be able to camouflage more than a bit. That’s because problem owners obviously don’t like te be discomforted by being confronted with mistakes in their organisation in public. This is not an easy task. Let me try with one realistic example though.

The customer demanded a report with a preface mentioning the total number of “inhabitant registrations” (note the camouflage) followed by a more detailed paragraph. Which had to feature a table of subtotals representing the amount of people in a group of age ranges. Just like the example below:

The research includes 127,015,265 inhabitant registrations. These can be divided into the following age ranges:

Ages	Subtotal
0-1	170023
2-5	2396859
6-10	3605589
11-20	8248278
21-50	48913234
51-100	52634295
101-200	5337274

The number mentioned in the first paragraph had been calculated by counting all the records in the REGISTRATIONS table. The subsequent table had been calculated by a query like this

SELECT  rng.range
,       COUNT(*) subtotal
FROM    registrations reg
JOIN    ranges rng
ON      (MONTHS_BETWEEN(SYSDATE, prs.date_of_birth)/12)
        BETWEEN rng.lower_bound AND rng.upper_bound
WHERE   rng.range_type = 'Age ranges'
GROUP   BY rng.range

At first it seemed there were no problems. But soon it turned out that the numbers in the table didn’t add up to the total mentioned in the preface. In fact it was off by an amount of 5,709,713 ‘missing values’, which couldn’t sufficiently be explained by the usual “bad data” excuse (like nullified or out of range values for the referenced date_of_birth column).

To give you a hint on what had happened: suppose one calculates the age of a person born just over fifty years ago. That would result in a number between 50 and 51. But wait, that individual would not fall into the range of ages between 21 and 50, nor between 51 and 100… Of course, the query join expression calculating the age suffered from a missing TRUNCATE function.

ON      TRUNC(MONTHS_BETWEEN(SYSDATE, prs.date_of_birth)/12)
        BETWEEN rng.lower_bound AND rng.upper_bound

Not so much a hilarious example, however it does demonstrate how sharing your mistakes can have a learning effect on others!