Apr 242015

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.

Mar 302015

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

Feb 242015

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

Feb 152015

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!

Jan 172015

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!

Dec 312014

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'
Nov 072014

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!

Oct 192014

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
where  H2O = 'frozen';
Oct 052014

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;


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

select count(*)
from   players;


-- The mass (in kilos) of a typical dairy cow
-- ~7 times the average adult body power 
-- consumption (in watts)

select count(*)
from   answers_submitted;


-- Approximately the length of the Great Wall 
-- of China (in miles) and the mass of 
-- a large dinosaur (in stones)

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

NAME             SCORE
---------------- -----
mentzel.iudith   20618
Justin Cave      20038
Joaquin Gonzalez 19284
Krzysztof Helbin 19242
Elic             19127

-- Iudith and Justin are doing a great job, 
-- the only players to break 20,000 to date!

select avg(rating) 
from   player_surveys;


-- 50 times the half life of uranium 241 
-- (in hours)

-- Great to see the average rating above 4 :)
-- I have to thank my reviewers, particularly 
-- Yuan Tschang, for helping keep the quality 
-- high!

Thanks to everyone who’s taken part, pointed out my mistakes and added to the discussions for the quiz over the past year.

If you’ve not played yet, today’s a great time to get started. Just head over to www.plsqlchallenge.com, register for a free account and get playing!

Jun 032014

I insert a row into a table, roll it back, yet somehow I’ve created a new object in my database!

select count(*) from user_objects;


insert into a_table
values ('that create', 1, 'new object');


select count(*) from user_objects;


There’s no triggers or other “trickery” involved – the code above is a copy-paste in a database where I have the only session.

How did this happen? Put your answers in the comments!

If you’d like to get more posts on using and designing Oracle databases, enter your details the form below to receive future updates.