Tuesday, November 08, 2005

SQL puzzle I.

Recently I've reviewed two and solved two other interesting SQL puzzles.

The first one is a very famous The Tower of Hanoi puzzle. The original SQL solution was posted by me to the Oracle-L mailing list a couple of years ago, below you can find a modified version. Depending on the number of disks either 2nd (even number of disks) or 3rd (odd number of disks) peg is a target.
SQL> SET PAGES 1000 LINES 100 TRIMSPOOL ON VERIFY OFF
SQL> DEFINE disks=3
SQL> COLUMN "Implementation Plan" FORMAT A20
SQL> SELECT 'Move it from '
2 || TO_CHAR(MOD(BITAND(ROWNUM, ROWNUM - 1), 3) + 1)
3 || ' to '
4 || TO_CHAR(MOD(-BITAND(-ROWNUM - 1, -ROWNUM), 3) + 1) AS "Implementation Plan"
5 FROM (
6 SELECT ROWNUM
7 FROM dual
8 CONNECT BY dummy = dummy
9 AND ROWNUM <= POWER(2, &&disks)
10 )
11 WHERE ROWNUM < POWER(2, &&disks)
12 /

No comments: