Database design resources: My reading list

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';

(more...)

The Database Design Quiz in Numbers: 2014 Review

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  (more...)

SQL Brainteasers: Guess the Superhero

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';

begin
  insert into boy  (more...)

Using Edition-Based Redefinition to Bypass Those Pesky Triggers

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 (more...)

SQL Brainteasers: Guess the Chocolate Bar

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!

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 (more...)

SQL Brainteasers: Guess the Mythical Creature

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';

begin
  delete from creature
  where  appendage = 'head';

  insert into creature (appendage)
  values ('head');

  insert into creature (appendage)
  values ('head');
end;
/

update  (more...)

SQL Brainteasers: Guess the Country part 2

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 (more...)

SQL Brainteasers: Guess the Country

Same concept, different theme. All the SQL statements below are clues to the English spelling of countries.

Can you get them all?

As always, put your answers in the comments!

select *
from   german_affirmative
join   cooking_vessel;

select *
from   nuts
where  anaphylaxis = 'sexually transmitted';

select *
from   olde_english_the
join   males;

select *
from   elements
where  atomic_number = 47;

select *
from   programming_languages
where  able_to = 'influence PL/SQL';

select *
from   places
 (more...)

One Year of the Database Design Quiz

Today celebrates one year of the database design quiz on the PL/SQL Challenge!

Here’s a look back at some of the stats for the quiz to date with some trivia related to the numbers :)

select count(*)
from   quizzes;

COUNT(*)
--------
      52

-- The number of white keys on a piano
-- the smallest number with 4 representations 
-- as the sum of 4 positive squares

select count(*)
from    (more...)