How to Create a 3 by 3 Puzzle Using Oracle Database Express Edition

In 2012 I wrote a C program to create sudoku puzzles. I took a break from writing about Oracle to set up a blog for these puzzles. You can see it here. I also created a group on Facebook called Sudoku Puzzles. At the time of writing this post it has over 10,000 members.

I recently saw a new kind of puzzle in a UK newspaper. The UK agents for this puzzle are Puzzler and their offices are only a few hundred yards from my own place of work. I paid them a visit one lunchtime and they told me that the name used for these puzzles is copyright so I cannot use it here. However, they said that there is nothing to stop me creating my own puzzles as long as I call them something different. You can see the first one I made here. I used Oracle 18 Express Edition to create it. First I ran the PL/SQL below:

SQL> create table nine_digit_numbers
  2  (col1 varchar2(9))
  3  /

Table created.

SQL> declare
  2   valid_switch boolean;
  3  begin
  4  for a in 123456789..987654321 loop
  5   valid_switch := true;
  6   for b in 2..9 loop
  7    for c in 1..b-1 loop
  8     if substr(a,c,1) = substr(a,b,1) then
  9      valid_switch := false;
 10     end if;
 11    end loop;
 12   end loop;
 13   for d in 1..9 loop
 14    if substr(a,d,1) = 0 then
 15     valid_switch := false;
 16    end if;
 17   end loop;
 18   if valid_switch then
 19    insert into nine_digit_numbers(col1) values(a);
 20    commit;
 21   end if;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL>

It ran for a couple of days on my home PC. I'm well aware that it is not efficient and at some point I hope to use it as the basis for a post entitled Your Poor Performance Could Well Be In Your Application or words to that effect. Eventually it produced a table of all possible numbers containing each of the digits 1 through 9 exactly once. There should be 9 factorial in all. I checked the first few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 asc)
  4* where rownum <= 5
SQL> /

COL1
---------
123456789
123456798
123456879
123456897
123456978


SQL>

I checked the last few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 desc)
  4* where rownum <= 5
SQL> /

COL1
---------
987654321
987654312
987654231
987654213
987654132


SQL>

...and I checked the number of rows:

SQL> l
  1* select count(*) from nine_digit_numbers
SQL> /

  COUNT(*)
----------
    362880


SQL>

I chose a solution at random (719368524) and decided by trial and error which extra number I would provide as a clue. Then I ran the following SQL to ensure the puzzle would only have one solution:

SQL> l
  1  select col1 from nine_digit_numbers
  2  where
  3  substr(col1,1,1) + substr(col1,2,1) +
  4  substr(col1,4,1) + substr(col1,5,1) = 17
  5  and
  6  substr(col1,2,1) + substr(col1,3,1) +
  7  substr(col1,5,1) + substr(col1,6,1) = 24
  8  and
  9  substr(col1,4,1) + substr(col1,5,1) +
 10  substr(col1,7,1) + substr(col1,8,1) = 16
 11  and
 12  substr(col1,5,1) + substr(col1,6,1) +
 13  substr(col1,8,1) + substr(col1,9,1) = 20
 14  and
 15* substr(col1,2,1) = 1
SQL> /

COL1
---------
719368524

SQL>

I put the puzzle away for a couple of days so that I would forget the answer then I solved it myself to ensure the solution could be reached using human logic.

Finally, I used Microsoft Paint to create the puzzle and the solution.