Yesterday I led the the second SQL coding dojo held as part of the Madgex ILP programme. As with the first one, I chose a puzzle from my copy of Joe Celko's SQL Puzzles and Answers. This time I chose one called One in Ten which I described as follows:

During a data migration exercise a table has been created which takes the outputs of an array and flattens them into a table named Dojo2 which is defined as:

Id INTEGER – Primary Key
F1 INTEGER
F2 INTEGER
...
F10 INTEGER

Produce a list of rows which have exactly one non zero value in the columns F1 to F10 using any method you like – select, views, creating new tables etc


As with last time, I've put a copy of the SQL file I produced to create the tables and populate the data here, and this time I've also provided two of the solutions from the book here.

Learning from last time's feedback I had a proper keyboard plugged in to my laptop this time. Other than that I ran it very similarly. There were only 3 attendees this time, so at each time we had one driver and 2 co-pilots as this seemed more practical. All of them had keyboard time, and all had co-pilot time. Again like last time I provided a word document detailing the table layout, the SQL used to create and populate the data, and the expected results - this can be downloaded here.

During the practice 2 different approaches were tried, one using string manipulation the other using ABS and max value checking. The ABS and max value checking method worked for the first 3 columns and so was assumed that it would work across all 10 columns but the participants got a bit fed up of typing as the method chosen - using lots of CASE statements would have been many lines long on completion :-)

The retrospective raised the following points:

  • Generally the session was enjoyed
  • Each participant learn something and found some areas to follow up on - mainly SIGN, ABS and PIVOT
  • The screen resolution of the laptop when plugged into the big screen was an issue as it was hard to get enough data on the screen at one time - given the number of participants a large standard monitor would have been better
  • Participants felt that it was good to watch how people tried to solve the problem and learnt from others approaches

As with last time, it was enjoyable for me to watch, and I learnt both from setting up the problem in the first place trying out the provided solutions, but also from watching my colleagues try different approaches.