Aug 182016
update sqlfail 
set    last_posting_date = date'2016-08-18';

It’s been a while since I’ve published here. You see, I’ve been kind of busy.

Back in January 2015 I was lucky enough to join Oracle as part of their Developer Advocate team. My focus is SQL.

You see I love working with SQL. And I love helping others. So joining the Developer Advocates was a great chance to combine these passions :)

Which leads some to ask:

Why is your site called SQLfail?!

Back when I started this, I enjoyed spending my free moments reading the Fail Blog. A site dedicated to showing pictures of people doing things silly, daft or just plain dumb.

So I thought to myself: perhaps I could build a site showing the silly, daft and dumb things people do with SQL. And so SQLfail was born.

But all good things must come to an end. So this is my last post here.

But it’s not goodbye. I’ve just moved :)

Where to? Well, a number of places!

You can find detailed blog posts explaining how to use SQL over at All Things SQL.

Or if you prefer your content in video form, head to The Magic of SQL. Here you’ll find SQL with a splash of magic thrown in.

For your daily dose of SQL in 140 characters or less, check out the SQLDaily Twitter account. Or you can get your SQL tips mixed with musing from me, SQL brainteasers, Haikus and other fun stuff by following me on Twitter.

And of course, if you’re stuck on a tricky Oracle SQL problem, please ask me or my fellow SQL Advocate Connor over on Ask Tom.


No, SQLwin!

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 272015

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.

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!

Feb 122015

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

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!

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

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'