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.

SQLfail?

No, SQLwin!

Jan 132014
 

In addition to SQL I love games of all kinds. Scrabble is one of my favourite games, so I thought I’d combine these passions and build a scrabble word finder using just SQL!

First up, we need to create a table holding all the letters and the points values for each (adjust the points values accordingly if not using the English edition):

create table letters as (
  select  'a' letter, 1 points from dual union 
  select  'b' letter, 3 points from dual union
  select  'c' letter, 3 points from dual union
  select  'd' letter, 2 points from dual union
  select  'e' letter, 1 points from dual union
  select  'f' letter, 4 points from dual union
  select  'g' letter, 2 points from dual union
  select  'h' letter, 4 points from dual union
  select  'i' letter, 1 points from dual union
  select  'j' letter, 8 points from dual union
  select  'k' letter, 5 points from dual union
  select  'l' letter, 1 points from dual union
  select  'm' letter, 3 points from dual union
  select  'n' letter, 1 points from dual union
  select  'o' letter, 1 points from dual union
  select  'p' letter, 3 points from dual union
  select  'q' letter, 10 points from dual union
  select  'r' letter, 1 points from dual union
  select  's' letter, 1 points from dual union
  select  't' letter, 1 points from dual union
  select  'u' letter, 1 points from dual union
  select  'v' letter, 4 points from dual union
  select  'w' letter, 4 points from dual union
  select  'x' letter, 8 points from dual union
  select  'y' letter, 4 points from dual union
  select  'z' letter, 10 points from dual
);

Next, we need to build a list of possible words. I’ve used the TWL06 list downloadable at http://www.scrabblestop.com/lists-of-scrabble-words.html and then loaded these into the table WORDS:

create table words (
  word varchar2(30) primary key
);

We can now use recursive subquery factoring (requires 11gR2) to enter all your tiles and generate the list of possible combinations. Limit to eight characters for now – your seven tiles, plus linking to one on the board. (credit to Radislav Golian: this is based on his Boggle SQL solver (scroll down the comments)):

with tiles (
    not_used_chars, word, max_length) as (
  select :tiles not_used_chars,
         cast(null as varchar2(16)) word,
         8 max_length
  from   dual
  union  all
  select substr(b.not_used_chars, 1, a.lev - 1) ||
           substr(b.not_used_chars, a.lev + 1) 
             as not_used_chars, 
         b.word || 
           substr(b.not_used_chars, a.lev, 1) 
             as word,
         max_length
  from   tiles b,
         (select level lev 
          from   dual 
          connect by level <= 8) a 
  where  nvl(length(b.word), 0) < b.max_length 
  and    a.lev <= 
             nvl(length(b.not_used_chars), 0) 
)
select * from (
  select t.word
  from   tiles t
  where  length(t.word) > 1
);

To calculate the value of each combination, we can add the score in the recursive query. This is done by joining the letters table we built earlier. By then linking this to the words table we can return only those combinations that are real words. You can supply an underscore “_” to represent blanks:

with tiles (
    not_used_chars, word, max_length, score) as (
  select :tiles not_used_chars,
         cast(null as varchar2(16)) word,
         8 max_length,
         0 score
  from   dual
  union  all
  select substr(b.not_used_chars, 1, a.lev - 1) ||
           substr(b.not_used_chars, a.lev + 1) 
             as not_used_chars, 
         b.word || 
           substr(b.not_used_chars, a.lev, 1) 
             as word,
         max_length,
         score + nvl(
           (select points 
            from   letters
            where  letter = 
              substr(b.not_used_chars, a.lev, 1)
         ), 0) score
  from   tiles b,
         (select level lev 
          from   dual 
          connect by level <= 8) a 
  where  nvl(length(b.word), 0) < b.max_length 
  and    a.lev <= 
             nvl(length(b.not_used_chars), 0) 
)
select * from (
  select distinct t.word, t.score
  from   tiles t, words w
  where  length(t.word) > 1
  and    w.word like t.word
  order  by score desc
)
where  rownum <= 3

This works pretty well. If we enter ‘scrabble’ for the tiles, we find the top words are:

WORD     SCORE
-------- -----
scrabble    14
clabbers    14
clabber	    13

Good stuff, but the most points are scored by using the bonus squares. We can add in the letter bonuses by creating a “multiplier” table. This has the possible positions in the word for the bonus square and the value (double or treble). The following shows a treble letter in the first position (sticking with eight positions for your seven plus one already on the board):

with multiplier as (
  select 1 pos, 3 mul from dual union
  select 2 pos, 1 mul from dual union
  select 3 pos, 1 mul from dual union
  select 4 pos, 1 mul from dual union
  select 5 pos, 1 mul from dual union
  select 6 pos, 1 mul from dual union
  select 7 pos, 1 mul from dual union
  select 8 pos, 1 mul from dual
)

By selecting this in the recursive subquery we can multiply the letter values calculating the running score. Putting it all together we have:

with multiplier as (
  select 1 pos, 3 mul from dual union
  select 2 pos, 1 mul from dual union
  select 3 pos, 1 mul from dual union
  select 4 pos, 1 mul from dual union
  select 5 pos, 1 mul from dual union
  select 6 pos, 1 mul from dual union
  select 7 pos, 1 mul from dual union
  select 8 pos, 1 mul from dual
), tiles (
    not_used_chars, word, max_length, score, p) as (
  select :tiles not_used_chars,
         cast(null as varchar2(16)) word,
         8 max_length,
         0 score,
         1 p
  from   dual
  union  all
  select substr(b.not_used_chars, 1, a.lev - 1) ||
           substr(b.not_used_chars, a.lev + 1) 
             as not_used_chars, 
         b.word || 
           substr(b.not_used_chars, a.lev, 1) 
             as word,
         max_length,
         score + nvl(
           (select points *
              (select mul
               from   multiplier
               where  pos = p)
            from   letters
            where  letter = 
              substr(b.not_used_chars, a.lev, 1)
         ), 0) score,
         p + 1 p
  from   tiles b,
         (select level lev 
          from   dual 
          connect by level <= 8) a
  where  nvl(length(b.word), 0) < b.max_length 
  and    a.lev <= 
             nvl(length(b.not_used_chars), 0) 
)
select * from (
  select distinct t.word, t.score
  from   tiles t, words w
  where  length(t.word) > 1
  and    w.word like t.word
  order  by score desc
)
where  rownum <= 3

Using the letters from ‘scrabble’ again and triple letter bonus on the first letter, we get:

WORD     SCORE
-------- -----
clabbers    20
clabber	    19
barbels	    17

We still need to calculate total word multipliers. It would also be good if the bonus locations were dynamically generated, so we didn’t have to go through and enter all the combinations separately.

Can you extend the SQL above to do this? Is there a better way to generate the highest scoring words?

If you have better alternatives, put your approaches in the comments!

Dec 202013
 
update sqlfail
set    next_post_date = date '2014-01-06';

I’m taking a bit of a break over Christmas while I spend time with my daughter and her grandparents. Thanks to everyone who follows this site, hope you have a great Christmas/festive time and 2014 is everything you hope for. :)

In the meantime, there’s a message hidden in the SQL below for you (note, you’ll need to run this on 11.2.0.2 to guarantee it works):

select listagg(keyword, ' ') 
         within group (
           order by decode(rn, 1, 8,
                               45, 2,
                               476, 5, 
                               766, 6, 
                               1638, 4, 
                               1654, 3, 
                               1683, 1,
                               1836, 7)) 
from (
  select keyword, 
         row_number() 
           over (order by keyword) rn
  from   v$reserved_words
)
where rn in (1,45,476,766,1638,1654,1683,1836);
Oct 232013
 

As regular readers will know, two weeks ago marked the launch of the database design quiz on the PL/SQL Challenge, a quiz conceived and authored by me.

This represented several weeks work and preparation, so I felt a combintation of excitement and nerves when the quiz started on the 5 Oct. My reviewers (Andy Wilson and Kent Graziano) have done a great job, pointing out some items which had managed to slip by me when writing the quiz and the first week went by without a hitch.

Last week’s quiz proved less straight forward however. When I checked my email on Saturday morning I saw something I’d been dreading – an objection to the quiz! Surely myself, Andy and Kent hadn’t all missed something obvious? I read the comments and thought – I’m not sure!

The objection centred around a choice where no foreign key was present. I’d selected the choice as correct, but around three quarters of players choce it as incorrect and a few put some arguments forward as to why they felt so.

So I’m looking to get the thoughts of players (and anyone else who cares about data) about this. I’m hoping we can come to an agreement regarding what we need to state in quizzes for the a choices to be considered right or wrong when it comes to foreign keys. There’s full details over on the PL/SQL Challenge blog – please head over there and add your thoughts!

Feb 182013
 
update sqlfail_posts
set    weekly_posts = 2,
       monday = 'Y',
       tuesday = 'N',
       wednesday = 'N',
       thursday = 'N',
       friday = 'Y',
       saturday = 'N',
       sunday = 'N';
Recent changes in my life means I don't have as much time and energy to put into this blog as I did, 
so I'm reducing the number of posts slightly.