26 March 2011

First quiz on user-defined aggregate functions draws comments (2124)

On Friday, 24 March, the PL/SQL Challenge offered its first quiz, by Randy Gettman, on user-defined aggregate functions. This topic, certainly an advanced one, presents its own challenge: how to offer a relatively brief quiz on a most complicated topic.

It drew these two comments:

morkCallingOracle(comeIn.oracle).... I mean what has "the world" become? Seriously, most non SF-quizzes need multiple times the number of code lines SF ones need. Then if you managed to work your way through the code - in fact this time I gave up - they often seem far fetched or even trivial. Ofc OO is not my thing at all. I think re-adjusting my expertise to Intermediate is appropriate here. How important is OO to the quiz' audience or strategically to PL/SQL? Keep it up people! :)

Thanks for yesterday's quiz. It might have been more fun if there was an answer using only built-ins "select exp(sum(ln(partnum))) from plch_parts;" (even though it will work only when partnum is always > 0) ..and even though the purpose of quiz was to teach custom aggregate. :)

I will respond to the first comment and leave the other for players to discuss.

This was a very code-heavy quiz (gee, maybe I really shouldn't do this sort of thing on a Friday!). In fact, when Randy first submitted it, my initial reaction was "This is just too much." Then I took a closer look and realized that so far as I could tell, this is just about the minimal amount of code you could possibly write to create such a function. So my choice became: never do a quiz on this topic (involving code) or give it a try. I am glad we "tried."

The first commenter might well be correct that on average player-contributed quizzes have more code than my own. The most likely explanation for that, however, is that quizzes provided by players often deal with more complex and edgy features, on which I am not an expert. If I were, I expect that my quizzes on such topics would have a similar volume of code.

Finally, in terms of "far fetched" or "trivial" or "how important is OO" - remember: we offer a new, fresh, delightful quiz every single weekday. That's a whole lot of quizzes, folks. And if we only covered "core" or "fundamental" features, well....we'd end up with lots of repetition or lots of trivial, boring quizzes. Plus, you wouldn't stretch and expand your knowledge of PL/SQL-related technologies.

The first player says "in fact this time I gave up" - and I can understand that. Sometimes a quiz will serve primarily as an introduction for you to a new area of technology. So you have to accept the fact that you are (temporarily) ignorant and delight in the opportunity to learn something new.

But go ahead and take the quiz anyway! "Give up," but still submit an answer! Unless it's one of those "only once choice correct:", you are bound to get some credit for the time spent realizing you have no idea what is going on in the quiz ( :-) )! And, by the way, we strive to avoid setting a quiz to "one choice correct" when it is advanced.

Cheers, Steven


  1. >It might have been more fun if there was an answer using only built-ins "select exp(sum(ln(partnum))) from plch_parts;" (even though it will work only when partnum is always > 0

    It will display "50000" on the screen, but the value will not itself is 50000 :)

    SQL> select exp(sum(ln(partnum))), exp(sum(ln(partnum)))-50000 from plch_parts;

    --------------------- ---------------------------
    50000 6.0000E-34

  2. Hello All,
    Regarding Nikotin's comment, this is exactly what I thought of when dealing with a product calculation.
    Just think that the "exp(sum(ln(..." variant
    can be "adapted" to deal with negative numbers as well, by using ABS values and separately
    counting the number of negative values for adding a minus to the result ...
    What a nice pure-SQL function could have resulted, to trick everybody !

    Regarding the custom aggregate feature itself:

    The fact is that practically anybody who had
    never read a little bit about this feature before the quiz did have almost no chance to answer it
    in a reasonable time ... and for sure no chance to choose the most (or I'd rather say the only)
    right answer !

    On the other hand, one who did know about the feature, could as easily be mislead in choosing
    ONLY the good answer that did in fact use the feature as intended, and not the other verbose one as well ...
    This is what in fact happened to me ...

    So, the question is:
    Did this quiz indeed check the desired knowledge about a (specialized) feature,
    or just rather checked (again !) the "eye compilation speed" of the player, without any relation
    to the subject ?

    I also was wondering up to where the PL/SQL Challenge will "extend out" to the very specialized
    PL/SQL features ... just think of the many hundreds of supplied packages ...
    is really any human supposed to know by heart all the parameters and all the usages for all of them ?

    I am pretty sure that each PL/SQL developer
    does have his own "speciality fields", where he is "swimming freely", and learning other ones or
    at least earning some familiarity with them can be a very wishful achievement,
    but, however, being "punished" for each such thing while playing the quiz is really tougher than desirable ...

    Yesterday quiz for example was an EXCELLENT one, without having code in excess, without tackling
    any narrow or super-specialized feature ... so there is still a lot of "digging field" for nice quizes
    about the more general features :) :) :)

    Regarding the OO features, they are really most welcome, the question (for the Challenge only !)
    is just to which depth could you "dive into it" for keeping people still passionate about it rather
    than frustrated for being too far from the whole issue for even giving it a try.

    As in every field of life, I thing that the secret lies in advancing progressively, and not at an
    "all-at-once" or "all-or-nothing" pace.

    After a few good months, I myself feel that the Challenge has matured in time and become more difficult
    than it was in the first months.
    Progress is a good thing that benefits everybody, while frustration is bad, so I think that climbing
    all the time, but at a rational pace is the secret of survival !

    Thanks & Best Regards,
    Iudith Mentzel

  3. Hi,

    I agree with you Steven. I come to PL/SQL Challenge to learn, at the same time having a bit of fun. This was a very challenging quiz and required me to look into the documentation to understand how it works. I will start using this feature myself now, as I see it usable to things I do at my work. And this is thanks to PL/SQL Challenge.

    Pls. bring more challenges with PL/SQL subjects that are a bit out of the ordinary.


  4. I spent (a lot) more time on that quiz than I typically do. Part of that was the reason for one of the incorrect answers which related to a missing initialization. With that, I went a bit further to check that the implementation of the aggregation didn't contain any subtle issues that might cause it to act in an unexpected manner.
    Add to that, the concept of multiplying product ids would be a peculiar business requirement. That makes it a bit harder to work out what the expected behaviour is.

    All that said, it was a good question.

  5. A very nice implementation of this concept is the STRAGG() function (STring AGGegrate) which is described on AskTom.
    SELECT stragg(myStr) FROM myTABLE gives as result 'AA,BB,CC' if the myStr field has the values 'AA' 'BB' and 'CC' in the three records.