Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Serious Challenge # 1 - Probably Not Much Fun

Re: SQL Serious Challenge # 1 - Probably Not Much Fun

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sat, 13 Mar 2004 16:31:15 +0000
Message-ID: <n8d650p8hr04ct57fevtrciv0o9435pgd7@4ax.com>


On Sat, 13 Mar 2004 06:26:16 +1100, "Howard J. Rogers" <hjr_at_dizwell.com> wrote:

>Daniel's low-flying one dimensional birds has got me thinking about how
>useful SQL can get.
>
>Suppose a table that contains just words up to, say, 8 characters in length,
>minimum length 4. The words could be anything, but let's just suppose they
>are composer's name. Hence "BACH" is acceptable, "BRITTEN" is almost
>perfect, and "STRAVINSKY" is bloody awful and not allowed.
>
>OK. Here's the challenge. What would the SQL look like to select these words
>at random from the table, and display them randomly packed with other
>letters, in a 15 x 15 word grid, with the original words being read from
>left OR right (you know, like in one of those word square games?). One word
>per line, but not every line should have a word (such lines to have all 15
>characters displayed merely at random, therefore).
>
>Something like (without doing the entire 15 x 15 thing!!):
>
>XFEPBACHUILEEWB
>BACLNETTIRBORFG
 OK, how's this:

 (uses user_objects as a source of rows in part of it so assumes you have at least a few objects)

SQL> create table word (

  2      word varchar2(8) not null,
  3      constraint word_pk
  4          primary key (word),
  5      constraint word_c1
  6          check (length(word) >= 4)

  7 );

Table created

SQL> insert into word (word) values ('BACH');

1 row inserted

SQL> insert into word (word) values ('BRITTEN');

1 row inserted

SQL> commit;

Commit complete

SQL> with
  2 random_grid AS ( -- a 15x15 grid of random characters

  3      select rownum y,
  4             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x0,
  5             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x1,
  6             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x2,
  7             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x3,
  8             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x4,
  9             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x5,
 10             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x6,
 11             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x7,
 12             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x8,
 13             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x9,
 14             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x10,
 15             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x11,
 16             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x12,
 17             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x13,
 18             chr(trunc(dbms_random.value(ascii('A'), ascii('Z')))) x14
 19      from   all_objects
 20      where  rownum <= 15

 21 ),
 22  word_grid AS -- all the words from the word table, split into characters
 23               -- starting at a random column, with a 50% chance of being
 24               -- reversed, and each row having a unique random value from
 25               -- 0 to 14 for the row it will appear in the final grid
 26  (
 27      select word_y,
 28          case when word_x <=  0 then substr(word,  1 - word_x, 1) end x0,
 29          case when word_x <=  1 then substr(word,  2 - word_x, 1) end x1,
 30          case when word_x <=  2 then substr(word,  3 - word_x, 1) end x2,
 31          case when word_x <=  3 then substr(word,  4 - word_x, 1) end x3,
 32          case when word_x <=  4 then substr(word,  5 - word_x, 1) end x4,
 33          case when word_x <=  5 then substr(word,  6 - word_x, 1) end x5,
 34          case when word_x <=  6 then substr(word,  7 - word_x, 1) end x6,
 35          case when word_x <=  7 then substr(word,  8 - word_x, 1) end x7,
 36          case when word_x <=  8 then substr(word,  9 - word_x, 1) end x8,
 37          case when word_x <=  9 then substr(word, 10 - word_x, 1) end x9,
 38          case when word_x <= 10 then substr(word, 11 - word_x, 1) end x10,
 39          case when word_x <= 11 then substr(word, 12 - word_x, 1) end x11,
 40          case when word_x <= 12 then substr(word, 13 - word_x, 1) end x12,
 41          case when word_x <= 13 then substr(word, 14 - word_x, 1) end x13,
 42          case when word_x <= 14 then substr(word, 15 - word_x, 1) end x14
 43      from (
 44          select word_y,
 45                 word,
 46                 round(dbms_random.value(0, length(word)+1)) word_x
 47          from (
 48              select rownum r, word_y
 49              from (
 50                  select distinct round(dbms_random.value(1, 15)) word_y
 51                  from   user_objects
 52              )
 53              where  rownum <= (select count(*) from word)
 54          ) word_y
 55          join (
 56              select rownum r,
 57                     case
 58                         when dbms_random.value(0,1) < 0.5
 59                             then reverse(word)
 60                         else
 61                             word
 62                     end word
 63              from   word
 64          ) word
 65          on (word_y.r = word.r)
 66      )

 67 )
 68  -- outer join the split up words onto the random grid,
 69  -- taking the word characters in preference to the random
 70  -- characters
 71  select   coalesce(word_grid.x0,  random_grid.x0) x0,
 72           coalesce(word_grid.x1,  random_grid.x1) x1,
 73           coalesce(word_grid.x2,  random_grid.x2) x2,
 74           coalesce(word_grid.x3,  random_grid.x3) x3,
 75           coalesce(word_grid.x4,  random_grid.x4) x4,
 76           coalesce(word_grid.x5,  random_grid.x5) x5,
 77           coalesce(word_grid.x6,  random_grid.x6) x6,
 78           coalesce(word_grid.x7,  random_grid.x7) x7,
 79           coalesce(word_grid.x8,  random_grid.x8) x8,
 80           coalesce(word_grid.x9,  random_grid.x9) x9,
 81           coalesce(word_grid.x10, random_grid.x10) x10,
 82           coalesce(word_grid.x11, random_grid.x11) x11,
 83           coalesce(word_grid.x12, random_grid.x12) x12,
 84           coalesce(word_grid.x13, random_grid.x13) x13,
 85           coalesce(word_grid.x14, random_grid.x14) x14
 86  from     random_grid, word_grid

 87 where word_grid.word_y (+) = random_grid.y  88 order by random_grid.y;

X0 X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 X13 X14 -- -- -- -- -- -- -- -- -- -- --- --- --- --- --- D V E F S T Y U G V V M J X Q A I H Y T W E H I W E T E T L H B A C H W R F M H L I K G O R U B R I T T E N D A A X K N G G H C M M J C D D I T P J B M V D B R X W B D V K M V O B K Y L T J X V T I S U D M L D E I F M F S Y U L R D R U X C O T B P X R S M E J F R G N D C C J J J Q F U K M D C E P I B K O K I T U F X I O O W T T W B X V B N I B T M B X E U N A T W T U O L C S C K Q X H D K C V I I E P N Q C N M M P E H V Y V C K D J V M Y L O T I

15 rows selected

>etc etc
>
>Even harder challenge, I think: what would the SQL look like that allows the
>randomly selected words to be read left, right *and up and down* within the
>15 x 15 word square. For example:
>
>TFEPBBDHUILEDSB
>IFEPABHHUXLVOWE
>RFEPCBRCUIPGEQE
>BFEPHBHEUIYAEWT
>
>Strictly no Java.

 Other than maybe the MODEL clause in 10g, can't think of a way of doing this in SQL :-(

-- 
Andy Hassall <andy_at_andyh.co.uk> / Space: disk usage analysis tool
<http://www.andyh.co.uk> / <http://www.andyhsoftware.co.uk/space>
Received on Sat Mar 13 2004 - 10:31:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US