| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Serious Challenge # 1 - Probably Not Much Fun
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)
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
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 )
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
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
 |
 |