Posts by saxon:

    SQL Brainteasers: Guess the Card Game

    April 24th, 2015

    In celebration of my trip to Vegas last week, here’s clues to card games in SQL form:

    -- Q 1
    select surname || first_name
    from   actors
    where  films in (
      'Kung Fu Panda',
      'Nacho Libre',
      'King Kong'
    -- Q 2
    select description
    from   dishonest_act;
    -- Q 3
    select (select name 
            from   elements 
            where atomic_number = 79) || 
           (em / 2)
    from   hinged_barrier;
    -- Q 4
    select replace(name, chr(107), chr(116))
    from   cooking_utensils
    where  purpose = 'beating eggs';
    -- Q 5
    select listagg(ingredients, ' ') 
             within group (order by ingredients) ||
           (select word
            from   scrabble
            where  letters = 2
            and    points = 7)
    from   cocktails
    where  name = (
      select name
      from   music_styles
      where  period between 1600 and 1750
    -- Q 6
    select max(name), min(name)
    from   cities
    where  county = (select capital
                     from   isle_of_man);

    There’s some tough ones this time around; bonus points to anyone who gets them all ;)

    As always, put your answers in the comments.


    SQL puzzles: Which tree am I?

    March 30th, 2015

    The following SQL statements are all clues to the names of trees:

    select covering
    from   mammals;
    update wood
    set    state = 'burnt';
    select admiration
    from   people
    select name
    from   pronouns
    where  type = 'second person';
    select target
    from   diseases
    where  nationality = 'Dutch';
    update oslo
    set    appearance = 'smarter';
    select *
    from   elements e
    join   punishments p
    where  e.atomic_number = 47
    and    p.type = 'beating';

    Can you guess them all? Put your answers in the comments :)


    Database design resources: My reading list

    February 27th, 2015

    Following the database design championship playoff over on the PL/SQL Challenge, one player asked:

    “Could you please advise me some resources (boooks, courses) so that I can improve?”

    Of course I can! This is what I love to hear – people looking for resources they can use to increase their skills. This is information I’m sure others will benefit from, so here goes:

    select materials
    from   my_readings
    where  topic = 'Database design';

    I’ve found The Database Programmer blog to be immensely helpful for explaining normal forms and constraints. This provided the inspiration for the “shift patterns” question using the school class schema developed aross the “Table Design Patterns” series.

    Another fun paper for explaining normalization is “Data Normalization, Denormalization and the Forces of Darkness

    Many people struggled with the orders “physical co-locating data” question. The inspiration for that question came from a number of places:

    * Martin Widlake’s IOT series
    * Iggy Fernandez’s “No to SQL and No to NoSQL” which has an example using table clusters at the bottom. His “The Twelve Days of NoSQL” series on the same blog also provides great ideas.
    * The Data Warehouse Insider Blog article on attribute clustering

    One key thing to remember is that physical data location can matter hugely for database performance. Unfortunately this is one area that people often overlook. The lack of index-organized tables in most Oracle databases is a great example of this.

    To understand more about indexing – particularly across different database platforms – Use the Index, Luke! is a great site. If you really want to understand indexes in Oracle, take a look at Richard Foote’s blog (note – this does get very detailed!).

    Of course, no list about designing for Oracle would be complete without Tom Kyte’s books. Reading these helped me understand how Oracle functions.

    Finally I have to wrap up with a quick nod to the Oracle docs. Not exactly bedtime reading, but skimming through topics there has provided inspiration for a number of questions on the PL/SQL Challenge. :)

    Of course this isn’t a complete list (that would span many pages!). These are some of the resources I’ve found most useful (and refer back to often).

    So I’d like to hear from you. Which resources have you found most valuable when it comes to learning about designing (Oracle) databases? Please share your thoughts in the comments – I’d love to hear what other people have found useful.


    The Database Design Quiz in Numbers: 2014 Review

    February 24th, 2015

    The results of the first ever database design championship playoff were announced today. Huge congratulations to Sean Molloy, Pavel Zeman and Justin Cave for taking the top three spots. Also thanks to everyone else who’s taken part in the quiz, answering questions, adding to the discusions and pointing out my mistakes ;)

    With this in mind here’s a some of the facts and figures from 2014’s database design quizzes, along with related trivia:

    -- How many player were there in total?
    select count(*)
    from   players;
    -- The same number of calories as a 
    -- Five Guys BLT
    -- How many people took every quiz?
    select count(*)
    from   players
    where  quizzes_taken = 52;
    -- The ascii character code for 
    -- the exclamation mark!
    -- What was the total of all scores 
    -- across all players?
    select sum(scores)
    from   players;
    -- About the same mass in kg as a 
    -- Saturn V Rocket (2,970,000 kg)
    -- and how long did they take 
    -- to place their answers?
    select sum(user_seconds)
    from   player_answers;
    -- 40 days, 17 hours, 27 minutes and 52 seconds
    -- About 0.46 years on the planet of Mercury

    Want to know what all the fuss is about? Then just head over to PL/SQL Challenge and sign up for a free account. Who knows, it could be your name in lights this time next year ;)


    SQL Brainteasers: Guess the Superhero

    February 15th, 2015

    Here’s another six SQL brainteasers. This time the SQL is a clue to the power(s) of a superhero. Who is each superhero?

    insert into body (skeleton)
    values ('adamantium');
    update person
    set    strength = 'superhuman'
    where  emotion = 'anger';
    delete from powers
    where  location = 'near Kryptonite';
    update body
    set    exoskeleton = 'ferrous material';
    select *
    from   women
    where  equipment = 'noosed rope of veracity';
      insert into boy (mouth)
      values ('Musa × paradisiaca');
      update boy 
      set    strength = '20 men',
             power = 'flight';

    As always, put your answers in the comments!


    Using Edition-Based Redefinition to Bypass Those Pesky Triggers

    February 12th, 2015

    There’s a problem most people who’ve had to do production datafixes have encountered at some point: you need to update a dataset. Unfortunately there’s a trigger on the target table. The trigger either prevents the update outright or just has some unwanted side effects (firing off a business process, etc.). The trigger is necessary for the application to function correctly, so you can’t just drop it.

    How do you apply the changes?

    You can take an outage so you can drop/disable the trigger while applying the fix. Alternatively you can modify the application so that the trigger is no longer necessary. Scheduling the outage can be difficult (particularly if the fix is urgent). Changing the application may take a long time or even be infeasible if it’s supplied by a third party.

    How can you get out of this dilemma?

    Fortunately in Oracle 11gR2 and higher there is another way:

    create edition to_do_your_dirty_work;
    alter session set edition = 
    drop trigger which_breaks_your_datafix;
    update the_table_with_the_trigger
    set    the_data = 
      'values prevented by your trigger';
    alter session set edition = 
    drop edition to_do_your_dirty_work cascade;

    Using edition-based redefinition you can create a new edition and drop the trigger just in that edition. The application will still function as desired because the trigger still exists in the old edition. All you need to worry about now is concurrency issues… ;)

    Warning: you must first editions enable your application before you can do this. Only some object types are “editionable” (see the comments). If you have non-editioned objects that depend upon editioned objects you’ll need to make some changes to your application before you can do this! Doing this is also a “one-way” operation – to revert it you need to recreate your user/restore from backup!

    For a complete script showing this in action, click here.

    For some further reading on EBR, have a look at the following:

    Oracle-BASE article
    Tom Kyte’s Oracle Magazine article, Edition-Based Redefinition, Part 1
    Edition-Based Redefinition Whitepaper, Bryn Llewellyn July 2009


    SQL Brainteasers: Guess the Chocolate Bar

    January 17th, 2015

    Some more SQL clues, this time to chocolate bars.

    select iso_code
    from   countries
    where  capital_city = 'Taipei'
    and    9 = roman;
    select *
    from   planets
    where  position = 4;
    select characters
    from   thundercats
    where  age = 'prepubescent';
    select *
    from   ships
    where  crew = 'mutinous';
    select *
    from   cities
    where  that_is = 'Eboracum';
    select min(volume)
    from   speech;

    When you’ve got them, put your answers in the comments!


    Goodbye Flybe, Hello Oracle!

    January 5th, 2015
    update people
    set    employer = 'Oracle',
           job_title = 'Database Evangelist'
    where  name = 'Chris Saxon';

    That’s right, as of last week I’m now an Oracle employee! I’m joining Steven Feuerstein’s database evangelist team, a hugely exciting opportunity I’m honoured to be a part of. I’ll be joining Natalka, Dan and Todd in helping people customers get the most out of their Oracle databases.

    My focus will be SQL and Oracle’s differentiating features such as Edition Based Redefinition which help make, in my opinion, Oracle the best database available. I love sharing my knowledge and assisting people with improving their skills, so this job suits me well.

    Farewell to all my colleagues at Flybe. I had a great time working with you all. This is an opportunity too good to pass up though.

    2015 is set to be a great year :)


    SQL Brainteasers: Guess the Mythical Creature

    December 31st, 2014

    Another batch of SQL brainteasers. This time the clues are to the powers of mythical creatures – the task is to guess the creature!

    There’s six in all, can you get them all? Put your answers in the comments!

    update target_of_gaze
    set    state = 'stone';
      delete from creature
      where  appendage = 'head';
      insert into creature (appendage)
      values ('head');
      insert into creature (appendage)
      values ('head');
    update bird
    set    state = 'alive'
    where  state = 'burning';
    select *
    from   women
    where  singing = 'deadly';
    update creature
    set    body = 'furry'
    where  moon = 'full';
    select *
    from   men
    where  powers in (
      'conferring playthings',
      'determining moral terpitude'


    SQL Brainteasers: Guess the Country part 2

    November 7th, 2014

    Continuing the nation theme, here’s another six SQL clues to countries. Can you get them all?

    select first_person
    from   roman_eleven
    where  business = 'abbreviated';
    select aquatic_limb
    from   places;
    select *
    from   dinner
    where  contents in ('beef', 'kidney beans');
    select character
    from   films
    where  actor = 'Tom Green';
    select unrepeatable_position
    from   flightless_birds
    where  allegiance = 'Confederate';
    select fortification
    from   wine
    where  second_person = 'female';

    As always, put your answers in the comments!