Jan 202014
 

I’ve done more analysis of the first quarter of the Database Design quiz on the PL/SQL Challenge. This time I’ve had a look at the quiz survey results to see what did and didn’t like, whether or not the quizzes are too hard and what people want to see more or less of.

Here’s the figures:

select round(avg(rating), 2) rating_out_of_5
from   quizzes;

RATING_OUT_OF_5
---------------
           4.14

-- The degrees Culloden in Scotland is 
-- West of the Greenwich Meridian

-- The 3 highest rated quizzes
select * from (
  select quiz || ':' || player_rating
  from   quizzes
  order  by player_rating desc
)
where rownum <= 3;

QUIZ 
-----------------------------------------
Function-based indexes - 2013-11-29: 4.28
Entity Subtyping - 2013-11-08?: 4.27
Second Normal Form - 2013-10-25: 4.23

-- 3 lowest rated quizzes
select * from (
  select quiz || ':' || player_rating
  from   quizzes
  order  by player_rating
)
where rownum <= 3;

QUIZ 
----------------------------------------
Data Types - 2013-11-22: 3.92
Referential Integrity - 2013-11-15: 4.06
Normalization - 2013-12-20: 4.08

-- Which were the hardest quizzes? 
-- We have a tie!
select * from (
  select quiz || ':' || too_hard_count quiz
  from   quizzes
  order  by too_hard_count desc
)
where rownum <= 2;

QUIZ
----------------------------------
Unique Constraints - 2013-10-18: 3
Partitioning - 2013-12-27: 3

-- And for the easiest we have:
select * from (
  select quiz || ':' || too_easy_count
  from   quizzes
  order  by too_easy_count desc
)
where rownum <= 2;

QUIZ
---------------------------------
Data Types - 2013-11-22: 6
First Normal Form - 2013-12-06: 5

-- How well did players know the topics?
-- What did people not know before the quiz:
select * from (
  select quiz || ':' || didnt_know_count
  from   quizzes
  order  by didnt_know_count desc
)
where rownum <= 3;

QUIZ
---------------------------------------
Function-based indexes - 2013-11-29: 11
Partitioning - 2013-12-27: 7
Indexing nulls - 2013-11-01: 5

-- And what was very well known:
select * from (
  select quiz || ':' || very_well_count
  from   quizzes
  order  by very_well_count desc
)
where rownum <= 3;
QUIZ
-------------------------------------
Database constraints - 2013-10-11: 16
Primary keys - 2013-12-13: 13
First Normal Form - 2013-12-06: 11

-- Finally, when it comes to future quizzes, 
-- what do people what to see?
-- More quizzes please:
select * from (
  select quiz || ':' || more_quizzes_count
  from   quizzes
  order  by more_quizzes_count desc
)
where rownum <= 3;

QUIZ
-------------------------------------
Second Normal Form - 2013-10-25: 23
Indexing Nulls - 2013-11-01: 21
Database Constraints - 2013-10-11: 19

-- In general there aren't any topics people 
-- don't want to see more of. The maximum 
-- number of people selecting "One was enough" 
-- on a quiz is one - this has happened 6 times

Overall I’m happy with how it’s gone – and it seems in general players are too! Only one quiz has an average rating less than 4/5 :) I have to thank my reviewers – Andrew Wilson, Kent Graziano and Yuan Tschang for their excellent and invaluable feedback. Without their help I’m sure the feedback scores would be at least a bit lower…

I find the quantative stats interesting and useful when planning what to write quizzes on in the future. The raw numbers can hide some qualitative details though.

Which topics haven’t been covered that you would like to see quizzes on? Which quizzes/topics did you particularly like or dislike and why? If you’ve got any feedback then please share it. You can leave your thoughts in the comments or contact me directly – either way I’m keen to hear from you!

Jan 062014
 

The first quarter of the database design quiz on the PL/SQL challenge finished last week. Being a numbers geek, here’s some facts and figures from how it’s gone so far:

select count(*)
from   quizzes;

COUNT(*)
--------
      13

-- Or about the same mass (in kg) 
-- of a gold bar :)

select count(*)
from   players;

COUNT(*)
--------
     463

-- Also the numbers of days it takes the 
-- planet Ceres to orbit its sun

select count(*)
from   answers_given;

COUNT(*)
--------
    3145

-- that's about 13 times the length of a 
-- Hidenburg Zeppelin!

select name, score
from   (select name, score
        from   players
        order  by score desc
)
where  rownum <= 5;

NAME             SCORE
---------------- -----
Justin Cave       5090	
Andres            4559
Frank Puechl      4477
mentzel.iudith    4439
Krzysztof Helbin  4409

-- Congrats to Justin, 
-- the clear winner for this quarter

select sum(answer_time) TIME_DAYS_HH:MI:SS
from   player_answers

TIME_DAYS_HH:MI:SS
------------------
17 13:36:45

-- Whoa, that's a lot of time taken!
-- there's a few players with abnormally 
-- high times, let's strip those out...

select sum(answer_time) TIME_DAYS_HH:MI:SS
from   player_answers
where  answer_time < interval '5' hour;

TIME_DAYS_HH:MI:SS
------------------
8 17:19:57

-- That's still nearly 9 days!

If you’ve been playing, hope you’ve enjoyed the quiz so far and will continue to play through 2014! If not, then join the game over on www.plsqlchallenge.com

Nov 042013
 
select my_thoughts
from   starting_the_db_design_quiz;

As regular readers will know, last month saw the start of the Database Design Quiz on the PL/SQL Challenge. I thought I’d share some of the background on how this came about and my thoughts and feelings on how it’s going, so here goes…

I’ve been player of PL/SQL quiz for a few years now. Writing code is only a small part of my current day job however – as a “Data Architect” more of my time is spent designing data structures (though I still get called on to help coding from time to time!). With this in mind, I’d started drafting up some questions for the challenge that were more related to my work. These didn’t seem to fit with the PL/SQL quizzes though. While there’s clearly some cross-over, it didn’t feel quite to have these in the PL/SQL or SQL quizzes.

So one night the summer while trying to get my daughter to sleep, I had a crazy idea – wouldn’t it be great to have a whole new type of quiz on the PL/SQL Challenge? It could be based around the theme of database design: creating and normalizing data structures and then the physical implementation of these. With this in mind, I drafted up some questions and sent them over to Steven Feuerstein.

Steven really liked the idea, but suggested that we gather feedback from players before going ahead with it. So we put together a post for the PL/SQL Challenge blog including some sample questions. I was amazed by the positive reception it received – clearly this was something many people felt was worth doing.

With this support clearly we needed to go ahead so I set about writing quizzes. I’d been thinking about possible questions for a while, so I had several ideas lined up and submitting questions has been (relatively) easy so far. I suspect generating new ideas will get harder in time though – I’m really impressed that Steven and Kim have been coming up with new questions for as long as they have!

In addition to writing the questions, I needed to find some people to help review them to ensure they’re up to scratch – it’s amazing how easily I’ll manage to miss a bracket or use slighlty confusing language.

I instantly thought of an old colleague of mine, Andy Wilson, who quickly agreed to help :) I also feel privileged that Kent Graziano – an Oracle ACE and published author on Data Warehouse modelling – agreed to help review questions. They have both provided some excellent feedback on the questions so far. I’m sure the improvements they’ve suggested are part of the reason the quizzes are getting good scores in post-quiz surveys. They’ve tidied up the language and pointed out some flaws in the questions.

With Kent and Andy on the job and a batch of quizzes ready, we settled on a launch date of 5th October to coincide with the start of a new quarter in the PL/SQL & SQL quizzes. As the launch date got closer got more excited and nervous. Would it all work out well? Would people play? Being based in the UK, the new quiz would start at midnight my time. I’d love to say I stayed up to see it start, but having a daugher under 1 is taking its toll and I can barely stay up past 9pm these days ;) I was up first thing on the 5th however (only partly becuase Izzy was already awake!), checking that the quiz was working correctly. Other than a slight typo in the question, it was a relief to see it live and functioning as expected.

So we’re now a month into the quiz and I feel like it’s going well – there’s only been one major disagreement so far and around 400 players per quiz, which is great. In all I’m happy with the way it’s going at the moment and feel really honoured that so many people have taken part. Hopefully this will continue you to grow – but for that part I’ll need help from you: by playing, adding your feedback to quizzes and spreading the word about this quiz we can help grow the community (including the PL/SQL, SQL and Logic quizzes too).

For those of you who’ve been taking part in the DB design quiz, I’d love to hear how you think it’s going. Are the questions interesting and varied enough? Are they too easy/hard? What have we not covered (yet) that you think we should? Please share your thoughts in the comments!

Oct 232013
 

As regular readers will know, two weeks ago marked the launch of the database design quiz on the PL/SQL Challenge, a quiz conceived and authored by me.

This represented several weeks work and preparation, so I felt a combintation of excitement and nerves when the quiz started on the 5 Oct. My reviewers (Andy Wilson and Kent Graziano) have done a great job, pointing out some items which had managed to slip by me when writing the quiz and the first week went by without a hitch.

Last week’s quiz proved less straight forward however. When I checked my email on Saturday morning I saw something I’d been dreading – an objection to the quiz! Surely myself, Andy and Kent hadn’t all missed something obvious? I read the comments and thought – I’m not sure!

The objection centred around a choice where no foreign key was present. I’d selected the choice as correct, but around three quarters of players choce it as incorrect and a few put some arguments forward as to why they felt so.

So I’m looking to get the thoughts of players (and anyone else who cares about data) about this. I’m hoping we can come to an agreement regarding what we need to state in quizzes for the a choices to be considered right or wrong when it comes to foreign keys. There’s full details over on the PL/SQL Challenge blog – please head over there and add your thoughts!