13 April 2014

Take a Survey on Database Design Quizzes

Chris Saxon, who is the "domain admin" for the Database Design and author of many of the weekly quizzes, would love to get feedback from our players on these quizzes. He's been doing them now for six months and figures it's time to check in with all of you.

So he's used Google Forms to set up a quick, anonymous survey:

https://docs.google.com/forms/d/1mFdjTgX3qS-ze_Unu5UxLGof5yopvdspPqPvuLLqJLg/viewform 

We hope you can take a few moments to fill it out.

21 March 2014

Idea for a new type of quiz: Countdown PL/SQL

Always looking for ways to increase the entertainment value of the site, I came up with this idea last night.

Countdown PL/SQL

You have thirty seconds to answer ten true-false questions. 

With the clock on the page counting down, you are shown a statement about PL/SQL and you must quickly decide: true or false.

Of course, the same could be done with SQL, APEX, etc.

One concern I could see with this quiz is that comfort with the English language could have a big impact on how quickly you can get through the questions. It is also possible that we could offer versions that contained code, not (mostly) words.

Perhaps it is time to look at localization of content: offer it in different languages!

So what do you think? Sound interesting? Sound like something you'd bother playing?

17 March 2014

Q2 2014 Brings Big Changes to the PL/SQL Challenge

Last year, I (Steven Feuerstein) floated the idea that I would end the daily PL/SQL quiz. Many players protested, and several agreed to help me with the workload by writing quizzes. So I agreed to keep the daily quiz going into 2014 and we would see how it goes.

It went pretty well. Some players did write quizzes (a big thank you most especially to Jeroen Rutte, who wrote a total of 7 quizzes, 5 of which were used in Q1). But I still spent and spend lots of time writing quizzes, which I enjoy greatly, but unfortunately is not sustainable.

So starting in Q2 2014, the daily PL/SQL quiz will end and in its place we will offer three weekly quizzes in its place:

PL/SQL Challenge

The PL/SQL Challenge replaces the daily PL/SQL quiz that ran from April 2010 through March 2014. I will write all the quizzes for the PL/SQL Challenge quiz. They will focus on "core" PL/SQL features (including commonly-used supplied packages like DBMS_OUTPUT and UTL_FILE), up through the latest production version of  the Oracle database. Players who take this quiz are eligible to qualify for the annual PL/SQL Challenge Championship.[We may decide to increase the frequency of those championships, but for now: annual.]

PL/SQL Explore 

The PL/SQL Explore quiz explores the nooks and crannies of the PL/SQL language, and the many PL/SQL packages supplied by Oracle Corporation. Quizzes are provided by both Steven Feuerstein and players, and can range from "core" PL/SQL functionality to "edgy" elements of the language, like calling Java from PL/SQ or rarely used supplied packages. like DBMS_XA.

PL/SQL Deja Vu 

PL/SQL Deja Vu offers quizzes from the past - PL/SQL quizzes previously played on the daily PL/SQL quiz or one of the other PL/SQL quizzes. Use Deja Vu PL/SQL to reinforce your PL/SQL knowledge without worrying about competing with others. These quizzes are not ranked!

We will also "re-brand" the daily PL/SQL quiz to the "PL/SQL Challenge", so the daily quiz names will be of this form:

PL/SQL Challenge #276 D2011-04-12

and the weekly PL/SQL Challenge quiz names will look like:

PL/SQL Challenge #1240 WS2013-04-12

where "WS" is the "Weekly quiz Starting on".

We will look into ways to offer rankings across the various competitive PL/SQL quizzes, but we will not be able to get to that immediately.

Of course, the new quarter has not yet started. I am very interested to hear your feedback on these quizzes.

And, of course, you are welcome to shower me with flattery about how much you love those daily quizzes and want them to continue. Surely, my ego could use the stroking. :-)

But this time the decision is made: the daily quiz will end, and three new PL/SQL weekly quizzes will take its place.

10 March 2014

Results of 2013 Q4 PL/SQL Championship

You will find below the rankings for the 2013 Q4 PL/SQL championship; the number next to the player's name is the number of times that player has participated in a championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Peter Auer of Germany

2nd Place: Mike Pargeter of United Kingdom

3rd Place: Frank Schmitt of Germany


This was Peter's first championship. An impressive rookie performance, Peter!

Next, congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these players did.

My thanks to all my fine reviewers, but most particularly Elic, whose impact on the quality of our daily quizzes and championships cannot be overstated.

Steven Feuerstein

Note: Below the table of results for this championship, you will find another list showing the championship history of each of these players.

Rank Name Country Total Time % Correct Total Score
1Peter Auer (1)Germany27 mins 25 secs92%2882
2Mike Pargeter (12)United Kingdom22 mins 06 secs87%2788
3Frank Schmitt (8)Germany30 mins 05 secs87%2723
4Karel Prech (3)Czech Republic32 mins 25 secs87%2652
5Michal Cvan (11)Slovakia33 mins 58 secs87%2551
6Zoltan Fulop (7)Hungary29 mins 14 secs82%2485
7Janis Baiza (9)Latvia22 mins 35 secs76%2443
8Jerry Bull (11)United States29 mins 20 secs74%2443
9Ravshan Abbasov (1)Uzbekistan33 mins 11 secs79%2441
10Anna Onishchuk (8)Ireland16 mins 40 secs79%2432
11dmitrysk (1)Russia32 mins 52 secs84%2398
12Manfred Kleander (1)Austria34 mins 52 secs84%2393
13Vincent Malgrat (7)French Republic32 mins 35 secs79%2388
14Lukasz Kubicki (1)Poland22 mins 54 secs71%2362
15james su (8)Canada21 mins 52 secs71%2348
16Chad Lee (10)United States33 mins 00 secs84%2325
17Jeroen Rutte (7)Netherlands34 mins 46 secs82%2320
18Frank Puechl (4)Germany20 mins 50 secs74%2298
19Rytis Budreika (1)Lithuania18 mins 20 secs68%2253
20mentzel.iudith (13)Israel33 mins 13 secs84%2251
21João Barreto (4)Portugal22 mins 17 secs74%2214
22Telmoc (2)Portugal20 mins 30 secs66%2190
23Randy Gettman (12)United States34 mins 52 secs71%2183
24Yuri Pedan (4)Ukraine17 mins 06 secs71%2163
25Viacheslav Stepanov (12)Russia31 mins 00 secs76%2145
26Krzysztof Helbin (3)Poland26 mins 08 secs68%2112
27Anil Jha (3)United States34 mins 56 secs76%2101
28Kevan Gelling (9)Isle of Man30 mins 37 secs68%2078
29Siim Kask (13)Estonia27 mins 48 secs63%2054
30Leszek Grudzień (2)Poland30 mins 20 secs66%1998
31Yuan Tschang (8)United States34 mins 24 secs55%1972
32Niels Hecker (14)Germany35 mins 00 secs71%1960
33Oleksiy Varchyn (2)Norway21 mins 32 secs61%1919
34_tiki_4_ (6)Germany25 mins 06 secs58%1678
35Goran Stefanović (5)Serbia24 mins 33 secs61%1544
36Rakesh Dadhich (5)India26 mins 50 secs55%1443
37Stelios Vlasopoulos (10)Belgium24 mins 13 secs50%1406
38Thierry Poels (7)Belgium28 mins 05 secs58%1343
39Milibor Jovanovic (5)Serbia32 mins 24 secs53%1272
40Dan Kiser (6)United States33 mins 38 secs50%897

Championship Performance History

After each name, the quarter in which he or she played, and the ranking in that championship.
Name History
Peter AuerQ4 2013:1st
Mike PargeterQ4 2010:22nd, Q1 2011:15th, Q2 2011:8th, Q4 2011:5th, Q1 2012:5th, Q2 2012:17th, Q3 2012:5th, Q4 2012:20th, Q2 2013:12th, Q4 2013:2nd
Frank SchmittQ4 2011:22nd, Q2 2012:2nd, Q3 2012:21st, Q4 2012:3rd, Q1 2013:13th, Q2 2013:1st, Q3 2013:9th, Q4 2013:3rd
Karel PrechQ3 2012:36th, Q4 2012:23rd, Q4 2013:4th
Michal CvanQ3 2010:22nd, Q4 2010:25th, Q3 2011:21st, Q1 2012:11th, Q3 2012:14th, Q4 2012:14th, Q1 2013:14th, Q2 2013:11th, Q3 2013:1st, Q4 2013:5th
Zoltan FulopQ1 2012:15th, Q2 2012:26th, Q3 2012:22nd, Q4 2012:17th, Q1 2013:22nd, Q3 2013:22nd, Q4 2013:6th
Janis BaizaQ2 2010:3rd, Q4 2010:7th, Q3 2011:9th, Q4 2011:1st, Q3 2012:23rd, Q1 2013:4th, Q2 2013:4th, Q3 2013:3rd, Q4 2013:7th
Jerry BullQ2 2011:32nd, Q3 2011:8th, Q1 2012:12th, Q2 2012:11th, Q3 2012:13th, Q4 2012:15th, Q1 2013:9th, Q2 2013:15th, Q4 2013:8th
Ravshan AbbasovQ4 2013:9th
Anna OnishchukQ1 2011:5th, Q2 2011:20th, Q3 2011:19th, Q4 2011:17th, Q1 2012:21st, Q2 2012:8th, Q4 2013:10th
dmitryskQ4 2013:11th
Manfred KleanderQ4 2013:12th
Vincent MalgratQ4 2011:9th, Q1 2012:13th, Q2 2012:13th, Q4 2012:6th, Q1 2013:1st, Q2 2013:14th, Q4 2013:13th
Lukasz KubickiQ4 2013:14th
james suQ3 2010:56th, Q2 2011:7th, Q3 2011:11th, Q4 2011:14th, Q2 2012:15th, Q1 2013:7th, Q3 2013:15th, Q4 2013:15th
Chad LeeQ2 2011:26th, Q3 2011:17th, Q4 2011:12th, Q1 2012:1st, Q2 2012:23rd, Q3 2012:28th, Q4 2012:22nd, Q1 2013:20th, Q2 2013:8th, Q4 2013:16th
Jeroen RutteQ3 2010:19th, Q3 2012:10th, Q4 2012:11th, Q1 2013:8th, Q2 2013:18th, Q3 2013:4th, Q4 2013:17th
Frank PuechlQ3 2012:26th, Q1 2013:15th, Q3 2013:12th, Q4 2013:18th
Rytis BudreikaQ4 2013:19th
mentzel.iudithQ4 2010:4th, Q1 2011:17th, Q2 2011:23rd, Q3 2011:5th, Q4 2011:4th, Q1 2012:7th, Q2 2012:16th, Q3 2012:33rd, Q4 2012:4th, Q1 2013:2nd, Q2 2013:7th, Q3 2013:2nd, Q4 2013:20th
João BarretoQ3 2010:18th, Q4 2010:21st, Q2 2011:3rd, Q4 2013:21st
TelmocQ4 2013:22nd
Randy GettmanQ3 2010:8th, Q1 2011:25th, Q2 2011:10th, Q3 2011:3rd, Q4 2011:11th, Q1 2012:18th, Q2 2012:19th, Q3 2012:30th, Q4 2012:12th, Q1 2013:3rd, Q2 2013:13th, Q4 2013:23rd
Yuri PedanQ3 2010:45th, Q4 2010:9th, Q1 2011:2nd, Q4 2013:24th
Viacheslav StepanovQ1 2011:8th, Q2 2011:4th, Q3 2011:13th, Q4 2011:19th, Q1 2012:17th, Q2 2012:9th, Q3 2012:6th, Q4 2012:21st, Q1 2013:18th, Q2 2013:22nd, Q3 2013:11th, Q4 2013:25th
Krzysztof HelbinQ1 2012:10th, Q4 2012:18th, Q4 2013:26th
Anil JhaQ2 2012:28th, Q4 2012:19th, Q4 2013:27th
Kevan GellingQ2 2011:19th, Q3 2011:1st, Q4 2011:6th, Q1 2012:6th, Q2 2012:22nd, Q4 2012:16th, Q1 2013:12th, Q4 2013:28th
Siim KaskQ1 2011:28th, Q2 2011:6th, Q3 2011:10th, Q4 2011:3rd, Q1 2012:8th, Q2 2012:4th, Q3 2012:31st, Q4 2012:10th, Q1 2013:11th, Q2 2013:16th, Q3 2013:17th, Q4 2013:29th
Leszek GrudzieńQ2 2013:24th, Q4 2013:30th
Yuan TschangQ2 2012:24th, Q3 2012:24th, Q4 2012:27th, Q2 2013:30th, Q4 2013:31st
Niels HeckerQ2 2010:2nd, Q3 2010:1st, Q4 2010:15th, Q1 2011:6th, Q3 2011:7th, Q4 2011:10th, Q1 2012:2nd, Q2 2012:3rd, Q3 2012:2nd, Q4 2012:7th, Q1 2013:6th, Q2 2013:3rd, Q3 2013:13th, Q4 2013:32nd
Oleksiy VarchynQ3 2013:28th, Q4 2013:33rd
_tiki_4_Q4 2011:26th, Q1 2012:16th, Q2 2012:20th, Q4 2012:8th, Q3 2013:8th, Q4 2013:34th
Goran StefanovićQ1 2012:31st, Q2 2012:27th, Q3 2012:34th, Q4 2013:35th
Rakesh DadhichQ2 2012:10th, Q1 2013:23rd, Q2 2013:28th, Q3 2013:27th, Q4 2013:36th
Stelios VlasopoulosQ4 2010:37th, Q4 2011:20th, Q1 2012:27th, Q2 2012:30th, Q3 2012:1st, Q4 2012:1st, Q1 2013:27th, Q2 2013:23rd, Q3 2013:19th, Q4 2013:37th
Thierry PoelsQ3 2011:22nd, Q1 2012:24th, Q1 2013:26th, Q3 2013:25th, Q4 2013:38th
Milibor JovanovicQ4 2012:29th, Q1 2013:30th, Q3 2013:18th, Q4 2013:39th
Dan KiserQ4 2013:40th

26 February 2014

Results of First Annual Logic Championship (2013)

You will find below the rankings for the first ever 2013 Logic championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Viacheslav Stepanov of Russia

2nd Place: Jerry Bull of United States

3rd Place: Niels Hecker of Germany


Next, congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these players did.

Steven Feuerstein

Rank Name Country Total Time % Correct Total Score
1Viacheslav Stepanov (1)Russia20 mins 50 secs90%3033
2Jerry Bull (1)United States14 mins 48 secs76%2634
3Niels Hecker (1)Germany37 mins 57 secs81%2356
4mentzel.iudith (1)Israel59 mins 50 secs90%2253
5Ingimundur Gudmundsson (1)Norway34 mins 27 secs71%2051
6Zoltan Fulop (1)Hungary43 mins 38 secs76%2017
7Dan Jankowski (1)United States19 mins 58 secs62%1966
8Lieselotje (1)Belgium47 mins 08 secs76%1947
9Hudai Polat (1)Turkey21 mins 25 secs62%1902
10MarkM. (1)Germany59 mins 40 secs81%1847
11Randy Gettman (1)United States58 mins 17 secs81%1839
12Rytis Budreika (1)Lithuania09 mins 33 secs52%1804
13Elic (1)Belarus56 mins 49 secs76%1754
14Jason H (1)United States22 mins 30 secs57%1730
15Andres (1)Estonia58 mins 51 secs76%1713
16Stelios Vlasopoulos (1)Belgium57 mins 43 secs76%1701
17Sandra99 (1)Italy41 mins 56 secs67%1681
18Christoph Hillinger (1)Austria59 mins 11 secs76%1636
19Chris Saxon (1)United Kingdom34 mins 11 secs62%1606
20Cristinel Boboc (1)Romania47 mins 33 secs67%1604
21katuinbouter (1)Netherlands55 mins 59 secs71%1585
22PZOL (1)Hungary37 mins 58 secs62%1566
23Yuan Tschang (1)United States55 mins 14 secs71%1560
24danad (1)Czech Republic31 mins 09 secs57%1517
25Sean Molloy (1)United States54 mins 40 secs67%1462
26Tony Winn (1)Australia52 mins 56 secs67%1386
27Mihail Siscu (1)Moldova58 mins 35 secs67%1383
28Peter Chenery (1)United Kingdom44 mins 42 secs57%1361
29Enrico Rebecchi (1)Italy59 mins 30 secs67%1325
30tonyC (1)United Kingdom52 mins 29 secs57%1130
31Mehrab (1)United Kingdom56 mins 38 secs57%1082
32Rich Dellheim (1)United States53 mins 53 secs52%952
33macabre (1)Russia41 mins 27 secs48%936
34Milibor Jovanovic (1)Serbia58 mins 13 secs52%791
35Chad Lee (1)United States48 mins 28 secs38%461
36JasonC (1)United Kingdom58 mins 14 secs33%264

17 February 2014

A Glimpse into Feuerstein Refactoring....End Result Better?

As some of you may recall, we held our first non-PLSQL and non-quarterly championship since the PL/SQL Challenge started: the 12 February Annual SQL Championship.

Now, of course, if I had designed my database without any flaws, fully taking into account all possible directions in which the website could go, anticipating all possible user requests, etc., then we would not have encountered any bugs in the process of applying our code base to this new championship.

Ha. Ha. Ha.

We found many, many bugs, ranging from drawbacks in the design (worst, deepest impact, etc.) to ridiculous examples of hard-coding (the PL/SQL Championship competition_id = 2. So, sure, you could find "2"s in my code. What can I say? At least I admit it. Perhaps this is my own personal form of confessional therapy: dumping all this on you!).

So we did lots of work before the championship was held, and our efforts paid off with an error-free competition (well....not quite. With all players starting at the same time, there was a noticeable delay and some timeouts right at the beginning, but then it all settled down).

But then it was time to report on the results, award prizes, generate certificates, display results on the Quiz Details page....and we found many more bugs!

One of the steps we'd taken previously was to build in much more flexibility about specifying how and when players can see results. So now each competition event (daily quiz, championship, etc.) has a section that looks like (these are the settings for a championship):



But of course I then needed to write the code that would correctly combine these settings with the "state of play" and do the right thing.

It was hard for me to sort out all the logic, but I pushed my way through it, did some testing, seemed OK. And then I (deep shame) copy/pasted it for another function that had slightly different settings. I said to myself: "You have got to refactor this." to which I replied, "Yeah, right, will do. Someday."

You will find the code below. Feel free to read it, of course, but here's my overall take on it: besides the obvious awfulness of the copied code, those complex Boolean expressions are really, really difficult to understand and maintain. In trying to fix a problem right after the playoff, I introduced two more by getting mixed up on AND vs OR and where the parentheses should go.

Oh and then there are the comments:

 "/* 2.5 I do not see what this will do. */"

and"

/* 2.5 I do not see what value this adds.*/".

Real confidence builders! So like I say, feel free to check out this code, but what I mostly want to do is show you the refactored version and do a little reality check with anyone who wants to take the time: Is it easier to read? Do you think it was worth doing? Do you see a better way to do this?

And yes, sure, I should provide more of an explanation to what is going on here, but:

a. I don't have the time, and
b. I "pride" myself on writing self-documenting code. In other words, I am too lazy to write comments.

Thanks! Steven Feuerstein

Original UGLY Code
 
   FUNCTION results_available_for (comp_event_id_in   IN INTEGER,
                                   user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
      c_user_id    CONSTANT INTEGER
                               := NVL (user_id_in, qdb_user_mgr.guest_user_id) ;
      c_can_play   CONSTANT BOOLEAN
                               := can_play (comp_event_id_in, user_id_in) ;
      l_comp_event          qdb_comp_events%ROWTYPE;
      l_competition         qdb_competitions%ROWTYPE;
      l_return              BOOLEAN := FALSE;
   BEGIN
      /* Might be null from reviewer */
      IF comp_event_id_in IS NOT NULL
      THEN
         l_comp_event := qdb_comp_event_mgr.one_comp_event (comp_event_id_in);

         l_competition :=
            qdb_comp_event_mgr.competition_for_comp_event (comp_event_id_in);

         IF qdb_utilities.trace_is_on
         THEN
            qdb_utilities.trace_activity (
               'results_available_for sra-pas-sa',
                  l_comp_event.pl_see_results_after
               || '-'
               || l_competition.players_accept_scores
               || '-'
               || l_comp_event.scores_accepted);
         END IF;

         /* Precedence to access: ANSWERED  CLOSED  RANKED */

         l_return :=
            CASE          /* Can always see results of "Solve Problem" quiz */
               WHEN qdb_content.free_form_question (
                       qdb_quiz_mgr.single_question_id_for_compev (
                          comp_event_id_in))
               THEN
                  TRUE /* If answered, then can view if results are available after
                         answered or scores have been accepted. */
               WHEN ex_u_qdb_compev_answers (comp_event_id_in, c_user_id)
               THEN
                        (   (l_comp_event.pl_see_results_after =
                                 qdb_competition_mgr.c_resavail_answered)
                          OR (    l_comp_event.pl_see_results_after =
                                     qdb_competition_mgr.c_resavail_closed
                              AND l_comp_event.comp_event_status =
                                     qdb_competition_mgr.c_compev_closed)
                          OR (    l_comp_event.pl_see_results_after =
                                     qdb_competition_mgr.c_resavail_ranked
                              AND (   l_comp_event.comp_event_status =
                                         qdb_competition_mgr.c_compev_ranked
                                   OR (    l_comp_event.ignore_results =
                                              qdb_config.c_yes
                                       AND l_comp_event.comp_event_status =
                                              qdb_competition_mgr.c_compev_closed))))
                     or (    l_comp_event.pl_see_results_after =
                                 qdb_competition_mgr.c_quiz_accepted
                          AND (   l_competition.players_accept_quizzes =
                                     qdb_config.c_no
                               OR (    l_competition.players_accept_quizzes =
                                          qdb_config.c_yes
                                   AND l_comp_event.quizzes_accepted =
                                          qdb_config.c_yes)))
               /* Not answered but could play...can only see if closed/ranked*/

               WHEN c_can_play
               THEN
                  l_comp_event.comp_event_status IN (qdb_competition_mgr.c_compev_ranked,
                                                     qdb_competition_mgr.c_compev_closed)
               /* Not a player - what can everyone see? */
               WHEN comp_event_closed_for_user (comp_event_id_in, c_user_id)
               THEN
                     (    l_comp_event.ev_see_results_after =
                             qdb_competition_mgr.c_resavail_closed
                      AND l_comp_event.comp_event_status =
                             qdb_competition_mgr.c_compev_closed)
                  OR (    l_comp_event.ev_see_results_after =
                             qdb_competition_mgr.c_resavail_ranked
                      AND (   l_comp_event.comp_event_status =
                                 qdb_competition_mgr.c_compev_ranked
                           OR (    l_comp_event.ignore_results =
                                      qdb_config.c_yes
                               AND l_comp_event.comp_event_status =
                                      qdb_competition_mgr.c_compev_closed)))
               /* 2.5 I do not see what value this adds. */
               /* Can you see it when it's ranked */
               WHEN     l_comp_event.pl_see_results_after IN (qdb_competition_mgr.c_resavail_closed,
                                                              qdb_competition_mgr.c_resavail_answered,
                                                              qdb_competition_mgr.c_resavail_ranked)
                    AND l_comp_event.comp_event_status =
                           qdb_competition_mgr.c_compev_ranked
               THEN
                  TRUE
               ELSE
                  FALSE
            END;
      END IF;

      RETURN l_return;
   END results_available_for;

   FUNCTION results_available_for_sql (comp_event_id_in   IN INTEGER,
                                       user_id_in         IN INTEGER)
      RETURN PLS_INTEGER
   IS
   BEGIN
      RETURN CASE
                WHEN results_available_for (comp_event_id_in, user_id_in)
                THEN
                   1
                ELSE
                   0
             END;
   END;

   FUNCTION can_see_quiz (comp_event_id_in IN INTEGER, user_id_in IN INTEGER)
      RETURN BOOLEAN
   IS
      c_user_id    CONSTANT INTEGER
                               := NVL (user_id_in, qdb_user_mgr.guest_user_id) ;
      c_can_play   CONSTANT BOOLEAN
                               := can_play (comp_event_id_in, user_id_in) ;
      l_comp_event          qdb_comp_events%ROWTYPE;
      l_competition         qdb_competitions%ROWTYPE;
      l_return              BOOLEAN := FALSE;
   BEGIN
      /* Might be null from reviewer */
      IF comp_event_id_in IS NOT NULL
      THEN
         l_comp_event := qdb_comp_event_mgr.one_comp_event (comp_event_id_in);

         l_competition :=
            qdb_comp_event_mgr.competition_for_comp_event (comp_event_id_in);

         IF qdb_utilities.trace_is_on
         THEN
            qdb_utilities.trace_activity (
               'can_see_quiz sqa-pas-sa',
                  l_competition.pl_see_quiz_after
               || '-'
               || l_competition.players_accept_quizzes
               || '-'
               || l_comp_event.quizzes_accepted);
         END IF;

         /* if played, quizzes are accepted and can see after quiz taken. */

         /* Precedence to access: ANSWERED  CLOSED  RANKED */

         l_return :=
            CASE
               WHEN qdb_content.free_form_question (
                       qdb_quiz_mgr.single_question_id_for_compev (
                          comp_event_id_in))
               THEN
                  /* Can always see results of "Solve Problem" quiz */
                  TRUE
               WHEN ex_u_qdb_compev_answers (comp_event_id_in, c_user_id)
               THEN
                  /* If answered, then can view if results are available after
                            answered or quizzes have been accepted. */
                  (   (l_comp_event.pl_see_quiz_after =
                          qdb_competition_mgr.c_resavail_answered)
                   OR (    l_comp_event.pl_see_quiz_after =
                              qdb_competition_mgr.c_resavail_closed
                       AND l_comp_event.comp_event_status =
                              qdb_competition_mgr.c_compev_closed)
                   OR (    l_comp_event.ev_see_results_after =
                              qdb_competition_mgr.c_resavail_ranked
                       AND (   l_comp_event.comp_event_status =
                                  qdb_competition_mgr.c_compev_ranked
                            OR (    l_comp_event.ignore_results =
                                       qdb_config.c_yes
                                AND l_comp_event.comp_event_status =
                                       qdb_competition_mgr.c_compev_closed))))
               WHEN c_can_play
               THEN
                  /* Not answered but could play...can only see if closed/ranked*/
                  l_comp_event.comp_event_status IN (qdb_competition_mgr.c_compev_ranked,
                                                     qdb_competition_mgr.c_compev_closed)
               WHEN comp_event_closed_for_user (comp_event_id_in, c_user_id)
               THEN
                     /* Not a player - what can everyone see? */
                     (    l_comp_event.ev_see_quiz_after =
                             qdb_competition_mgr.c_resavail_closed
                      AND l_comp_event.comp_event_status =
                             qdb_competition_mgr.c_compev_closed)
                  OR (    l_comp_event.ev_see_results_after =
                             qdb_competition_mgr.c_resavail_ranked
                      AND (   l_comp_event.comp_event_status =
                                 qdb_competition_mgr.c_compev_ranked
                           OR (    l_comp_event.ignore_results =
                                      qdb_config.c_yes
                               AND l_comp_event.comp_event_status =
                                      qdb_competition_mgr.c_compev_closed)))
               WHEN     l_comp_event.pl_see_quiz_after IN (qdb_competition_mgr.c_resavail_closed,
                                                           qdb_competition_mgr.c_resavail_answered,
                                                           qdb_competition_mgr.c_resavail_ranked)
                    AND l_comp_event.comp_event_status =
                           qdb_competition_mgr.c_compev_ranked
               THEN
                  /* 2.5 I do not see what this will do. */
                  /* Can you see it when it's ranked */
                  TRUE
               ELSE
                  FALSE
            END;
      END IF;

      RETURN l_return;
   END;

The Refactored Code

   FUNCTION can_show_information (
      info_type_in       IN VARCHAR2,
      comp_event_id_in   IN qdb_comp_events.comp_event_id%TYPE,
      user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
      c_user_id             CONSTANT INTEGER
            := NVL (user_id_in, qdb_user_mgr.guest_user_id) ;
      c_could_have_played   CONSTANT BOOLEAN
         := can_play (comp_event_id_in, c_user_id) ;
      c_quiz_answered       CONSTANT BOOLEAN
         := ex_u_qdb_compev_answers (comp_event_id_in, c_user_id) ;
      c_quizzes_accepted             BOOLEAN;
      c_results_accepted             BOOLEAN;
      c_closed_or_ranked             BOOLEAN;
      c_ranked_or_voided             BOOLEAN;
      c_is_solve_problem             BOOLEAN;
      c_closed_for_player            BOOLEAN;

      CURSOR required_info_cur
      IS
         SELECT c.players_accept_quizzes,
                c.players_accept_scores,
                ce.comp_event_status,
                ce.ignore_results,
                ce.quizzes_accepted,
                ce.scores_accepted,
                ce.pl_see_quiz_after,
                ce.pl_see_answer_after,
                ce.pl_see_results_after,
                ce.ev_see_quiz_after,
                ce.ev_see_answer_after,
                ce.ev_see_results_after,
                ce.tm_member_results_preview,
                ce.author_can_play,
                ce.hide_quizzes,
                ce.is_private,
                ct.text comp_type,
                ct.comp_type_id
           FROM qdb_competitions c, qdb_comp_events ce, qdb_comp_types ct
          WHERE     c.competition_id = ce.competition_id
                AND c.comp_type_id = ct.comp_type_id
                AND ce.comp_event_id = comp_event_id_in;

      l_required_info                required_info_cur%ROWTYPE;

      l_return                       BOOLEAN;

      PROCEDURE get_required_info
      IS
      BEGIN
         OPEN required_info_cur;

         FETCH required_info_cur INTO l_required_info;

         CLOSE required_info_cur;

         c_quizzes_accepted :=
               l_required_info.players_accept_quizzes = qdb_config.c_no
            OR (    l_required_info.players_accept_quizzes = qdb_config.c_yes
                AND l_required_info.quizzes_accepted = qdb_config.c_yes);
         c_results_accepted :=
               l_required_info.players_accept_scores = qdb_config.c_no
            OR (    l_required_info.players_accept_scores = qdb_config.c_yes
                AND l_required_info.scores_accepted = qdb_config.c_yes);
         c_ranked_or_voided :=
               l_required_info.comp_event_status =
                  qdb_competition_mgr.c_compev_ranked
            OR (    l_required_info.ignore_results = qdb_config.c_yes
                AND l_required_info.comp_event_status =
                       qdb_competition_mgr.c_compev_closed);
         c_closed_or_ranked :=
            /* Also returns TRUE if I took it. So just go with status.
            comp_event_closed_for_user (comp_event_id_in, c_user_id)*/
            l_required_info.comp_event_status IN (qdb_competition_mgr.c_compev_closed,
                                                  qdb_competition_mgr.c_compev_ranked);
         c_closed_for_player :=
            comp_event_closed_for_user (comp_event_id_in, c_user_id);
         c_is_solve_problem :=
            l_required_info.comp_type_id =
               qdb_competition_mgr.c_solve_problem_ct_id;
      END;

      FUNCTION everyone_can (moment_in IN VARCHAR2)
         RETURN BOOLEAN
      IS
      BEGIN
         RETURN CASE moment_in
                   WHEN qdb_competition_mgr.c_resavail_never
                   THEN
                      FALSE
                   WHEN qdb_competition_mgr.c_quiz_accepted
                   THEN
                      c_quizzes_accepted
                   WHEN qdb_competition_mgr.c_result_accepted
                   THEN
                      c_results_accepted
                   WHEN qdb_competition_mgr.c_resavail_closed
                   THEN
                      c_closed_or_ranked
                   WHEN qdb_competition_mgr.c_resavail_ranked
                   THEN
                      c_ranked_or_voided
                   ELSE
                      FALSE
                END;

         IF qdb_utilities.trace_is_on
         THEN
            qdb_utilities.trace_activity (
               'can_show_information for everyone moment ' || moment_in,
               l_return);
         END IF;
      END;

      FUNCTION player_can (moment_in IN VARCHAR2)
         RETURN BOOLEAN
      IS
         l_return   BOOLEAN;
      BEGIN
         l_return :=
            CASE moment_in
               WHEN qdb_competition_mgr.c_resavail_never
               THEN
                  FALSE
               WHEN qdb_competition_mgr.c_quiz_accepted
               THEN
                  c_quizzes_accepted
               WHEN qdb_competition_mgr.c_result_accepted
               THEN
                  c_results_accepted
               WHEN qdb_competition_mgr.c_resavail_answered
               THEN
                  c_quiz_answered
               WHEN qdb_competition_mgr.c_resavail_closed
               THEN
                     c_closed_or_ranked
                  OR (    info_type_in = c_see_correctness
                      AND l_required_info.players_accept_quizzes =
                             qdb_config.c_yes)
               WHEN qdb_competition_mgr.c_resavail_ranked
               THEN
                  c_ranked_or_voided
               ELSE
                  FALSE
            END;

         RETURN l_return;
      END;
   BEGIN
      IF comp_event_id_in IS NULL
      THEN
         /* A reviewer may be reviewing an unscheduled quiz... */
         l_return := info_type_in = c_see_answers;
      ELSE
         get_required_info;

         /* Can always see stuff for a "solve problem" quiz. */
         l_return := c_is_solve_problem;

         IF NOT l_return
         THEN
            CASE info_type_in
               WHEN c_see_my_choices
               THEN
                  /* For this choice ONLY, the acceptance status of quizzes does not apply */
                  l_return :=
                     CASE
                        WHEN c_quiz_answered OR c_could_have_played
                        THEN
                           player_can (l_required_info.pl_see_quiz_after)
                        ELSE
                           everyone_can (l_required_info.ev_see_quiz_after)
                     END;
               WHEN c_see_correctness
               THEN
                  l_return :=
                     CASE
                        WHEN c_quiz_answered OR c_could_have_played
                        THEN
                           player_can (l_required_info.pl_see_answer_after)
                        ELSE
                           everyone_can (l_required_info.ev_see_answer_after)
                     END;
               WHEN c_see_answers
               THEN
                  /* Control display of overall answer, choice explanations, etc. */
                  l_return :=
                     CASE
                        WHEN NOT c_quizzes_accepted
                        THEN
                           FALSE
                        WHEN c_quiz_answered OR c_could_have_played
                        THEN
                           player_can (l_required_info.pl_see_answer_after)
                        ELSE
                           everyone_can (l_required_info.ev_see_answer_after)
                     END;
               WHEN c_see_results
               THEN
                  /* Display stats, rankings, % correct, etc. */
                  l_return :=
                     CASE
                        WHEN NOT c_results_accepted
                        THEN
                           FALSE
                        WHEN c_quiz_answered OR c_could_have_played
                        THEN
                           player_can (l_required_info.pl_see_results_after)
                        ELSE
                           everyone_can (
                              l_required_info.ev_see_results_after)
                     END;
            END CASE;
         END IF;
      END IF;

      RETURN l_return;
   END;
 
   /* And now specialized programs for different scenarios. */
 
   FUNCTION can_see_my_choices (comp_event_id_in   IN INTEGER,
                                user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN can_show_information (c_see_my_choices,
                                   comp_event_id_in,
                                   user_id_in);
   END;

   FUNCTION can_see_correctness (comp_event_id_in   IN INTEGER,
                                 user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN can_show_information (c_see_correctness,
                                   comp_event_id_in,
                                   user_id_in);
   END;

   FUNCTION can_see_answers (comp_event_id_in   IN INTEGER,
                             user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN can_show_information (c_see_answers,
                                   comp_event_id_in,
                                   user_id_in);
   END;

   FUNCTION can_see_results (comp_event_id_in   IN INTEGER,
                             user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN can_show_information (c_see_results,
                                   comp_event_id_in,
                                   user_id_in);
   END;

   FUNCTION results_available_for (comp_event_id_in   IN INTEGER,
                                   user_id_in         IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN can_see_results (comp_event_id_in, user_id_in);
   END results_available_for;

16 February 2014

Results of First-ever SQL Championship for 2013

You will find below the rankings for the first ever 2013 SQL championship.

Congratulations first and foremost to our top-ranked players:

1st Place: Vincent Malgrat of French Republic

2nd Place: Christoph Hillinger of Austria

3rd Place: Tony Winn of Australia


Next, congratulations to everyone who played in the championship. I hope you found it entertaining, challenging and educational. And for those who were not able to participate in the championship, you can take the quizzes next week through the Practice feature. We will also make the championship as a whole available as a Test, so you can take it just like these 39 players did.

And special thanks must go out to Kim Berg Hansen for writing these challenging quizzes and to Vitaliy Lyanchevskiy (a.k.a., Elic) for his thorough reviews. No objections were reported on these quizzes, and that was due entirely to the deep technical skills and attention to detail of both Kim and Vitaliy. 

Warm regards,
Steven Feuerstein


Rank Name Country Total Time % Correct Total Score
1Vincent Malgrat French Republic34 mins 51 secs86%2513
2Christoph Hillinger Austria43 mins 01 secs86%2405
3Tony Winn Australia35 mins 40 secs78%2332
4mentzel.iudith Israel38 mins 59 secs84%2285
5james su Canada37 mins 29 secs78%2240
6Chris Saxon United Kingdom22 mins 52 secs71%2168
7Niels Hecker Germany41 mins 41 secs80%2096
8Leszek Grudzień Poland35 mins 09 secs71%1997
9Sachi United States31 mins 29 secs71%1985
10koko Ukraine40 mins 19 secs78%1974
11Chase Mei Canada44 mins 11 secs76%1966
12Scott Wesley Australia19 mins 45 secs63%1960
13Zoltan Fulop Hungary35 mins 04 secs69%1959
14JohnR United States40 mins 01 secs73%1935
15Ivan Blanarik Slovakia36 mins 27 secs69%1856
16Rimantas Adomauskas Lithuania42 mins 50 secs73%1843
17Justin Cave United States43 mins 46 secs73%1810
18Michal Cvan Slovakia39 mins 23 secs69%1797
19Eric Levin United States41 mins 58 secs69%1786
20Andres Estonia44 mins 51 secs73%1748
21Viacheslav Stepanov Russia43 mins 12 secs71%1736
22Rytis Budreika Lithuania24 mins 23 secs59%1722
23Telmoc Portugal30 mins 18 secs59%1714
24Yuan Tschang United States44 mins 34 secs71%1694
25Krzysztof Helbin Poland34 mins 15 secs65%1690
26Jason H United States38 mins 09 secs65%1677
27Stelios Vlasopoulos Belgium29 mins 16 secs61%1665
28Anna Onishchuk Ireland20 mins 37 secs55%1663
29Matthias Rogel Germany31 mins 35 secs59%1653
30Sean Molloy United States41 mins 57 secs67%1651
31Chad Lee United States42 mins 36 secs67%1633
32Milibor Jovanovic Serbia42 mins 26 secs65%1611
33Marsel Fattakhov Russia27 mins 26 secs59%1571
34Randy Gettman United States44 mins 50 secs65%1493
35Giedrius Deveikis Lithuania44 mins 52 secs57%1418
36Hertha Rettinger Germany30 mins 17 secs55%1414
37swart260 Netherlands39 mins 38 secs55%1282
38Pavel Vorontsov Russia13 mins 43 secs39%1181
39Tobias Stark Germany44 mins 12 secs43%741