ODTUG Day 3 – Quiz on SQL and PL/SQL

Today Alex and I did our “Oracle Quiz on SQL and PL/SQL Quiz – See Water Burning” presentation. We wanted to demonstrate a number of new and old features in SQL and PL/SQL that are not very well known and deserve more attention. We chose a Quiz format: instead of having the Q&A session at the end, with started with it. And instead of the audience asking the questions, we did. Alex was the host of our Quiz, I acted as the -goodlooking – assistant who did the demonstrations.

We had a pretty good crowd turn up; Maggie Tompkins, our ambassador, counted 92. Some had to stand. Initially I had the impression that the audience was a bit baffled by the format. They had to tick boxes on a form for the correct answers, in order to qualify for the prize, a free book from the bookstall. And since this conference does not have one, we will accept any Internet Bookstore as the bookstall.

We had thirteen questions, introducing concepts like In Line Views, Case expression, User Defined Aggregates, Analytical Functions, the Translate function, usage of Unique Function Based Index (to enforce constraints like Only one Clerk per Department is allowed (but multiple managers is acceptable), Table Functions and External Tables.

You can download our Presentation ODTUG2005_WaterBurningAlx.ppt and a zip-file with all demo-scripts: quizSQL_PLSQL_ODTUG2005_scripts.zip.

After these 13 questions, we had one guy witl questions answered correctly – but he honestly admitted to have read the paper beforehand- which disqualified him. The next contenders had 11 questions answered correctly and we added the third as well, with 9 questions answered correctly. These three now went in for the decisive question. Here we asked how often a before row update trigger can fire for an update statement that will update three records. They all answered 3 where in fact the answer is any number between 3 and 6, inclusive. We demonstrated that in our case it was 6. See the slides and demoscripts for the explanation.

Finally we put up a map of Europe. The candidate who could point out The Netherlands on the map was to win the prize. The three contenders indicated Norway, Finland and Denmark. Since the last was closest, he was the winner. That means: Matthew J. Stone from Burlington Cost Factory out of Lebanon (NH) receives his free copy of a book of his choice.

After the presentation, we had many very nice reactions from people who really enjoyed the format we had used for presenting these concepts. One said he considered it “the most entertaining way to learn about this stuff.” It is nice to receive feedback like this. I’d say one of the very good things about this conference is the very informal, open and constructive way with which people approach each other. Perhaps it is a common American thing, but I find it special about this conference. When you present, you feel almost loved. People really appreciate that you are making the effort. And that helps enormously to enjoy it. And make it worth the effort.

Doing this presentation together was a very good conclusion of my active role in this conference. Now I can sit back and relax…