SQL Brainteasers: Guess the Card Game

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

SQL puzzles: Which tree am I?

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

--#1
select covering
from   mammals;

--#2
update wood
set    state = 'burnt';

--#3
select admiration
from   people
minus
select name
from   pronouns
where  type = 'second person';

--#4
select target
from   diseases
where  nationality = 'Dutch';

--#5
update oslo
set    appearance = 'smarter';

--#6
select *
from   elements e
join   punishments p
where  e.atomic_number = 47
and    p. (more...)

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