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 Keyhere. 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:
F10 INTEGERProduce 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
- 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.