Here is another SQL puzzle, based on an old idea.
I must confess that my investigations into 'Fireside Fun', are motivated by an urge to explore ways of whiling away the time during boring meetings and seminars. It is, I fear, considered rude to plug in the noise-cancelling headphones, lean back in your seat with your eyes closed, and listen to your iPod, whereas, if one is seen to take notes and nod sagely, then this goes down well with the presenter, even if one is really engaged in 'Fireside Fun'. There is also the excitement of discovering techniques in SQL to solve these puzzles- a sort of Meta Puzzle, if such a word existed.
After the fun of creating 'Decapitations' and their clues had subsided, I came across Word Squares. These are a precursor to the crossword puzzle but with one or two interesting quirks. Here are a few to show what I mean.
| T |
A |
P |
P |
E |
T |
| A |
S |
S |
U |
R |
E |
| P |
S |
A |
L |
M |
S |
| P |
U |
L |
P |
I |
T |
| E |
R |
M |
I |
N |
E |
| T |
E |
S |
T |
E |
D |
Now, of course, all you have to do is to create the clues and there you have a puzzle that our ancestors would have fallen upon with gusto as an agreeable way of passing the time after the brandy.
Of course, they preferred the clues as a rhyme, and it is curious that some of these rhymes entered the lore of school children
My First has regal powers to sway,
is worshipped as my second in a way.
In harmony my third is sweet and clear;
my fourth midst hills is ever near.
Which, of course, gives
| K |
I |
N |
G |
| I |
D |
O |
L |
| N |
O |
T |
E |
| G |
L |
E |
N |
Obviously, as soon as I came across these puzzles, I was curious to see whether there was a way of discovering the Word Squares in SQL. Well, there is, though my attempt took an hour to run on my slow development system. It was my fault for taking the approach of finding all the six-character puzzles all in one go. There are five thousand and seven of them if one uses the word list of common words I attached to the first blog entry for fireside fun.
As one would only need to run the routine once every fifty years or so in response to changes in the English language I'm perfectly happy with such a poorly performing bit of SQL, though I'm sure there are others out there who could improve on it enormously. The routine takes the approach of finding all viable words, adding them one at a time, and ending up with the solution on adding the sixth. Are there any other ways of doing it? Did Knuth get there first?
Instead of clues, one could take the Sudoku approach of blanking out various letters and getting the poor unfortunate player to guess the entire grid from what is left. This would be more in tune with modern times, I suppose,than writing doggerel clues
As an alternative, one could 'turn the board around', have an empty grid and tap in letter, getting the computer to provide a solution at every point, or complain that what you've set is unsolvable.
And how about even larger puzzles? Are there any seven-character puzzles, or even eight-character puzzles? Well, I hacked the SQL and came up with four seven-character puzzles, though I was expecting more.
M E R G E R S
E T E R N A L
R E G A T T A
G R A V I T Y
E N T I T L E
R A T T L E R
S L A Y E R S
V E R G E R S
E T E R N A L
R E G A T T A
G R A V I T Y
E N T I T L E
R A T T L E R
S L A Y E R S
V I S C E R A
I N C O M E R
S C A M P E R
C O M P I L E
E M P I R E S
R E E L E C T
A R R E S T S
B U G L E R S
U N R A V E L
G R A N O L A
L A N O L I N
E V O L V E D
R E L I E V E
S L A N D E R
So here is a link to the crazy SQL that needs to be run regularly every fifty years, I've attached to this blog entry the 5007 solutions to the puzzle in a CSV format so that you never have to run it! The original wordlist is attached to 'The Fireside Fun of Decapitations'. Here is a nice simple way of importing the words...
CREATE TABLE [wordlist] (
[Word_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Word] [varchar] (25)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_wordlist] PRIMARY KEY NONCLUSTERED
(
[Word]
) ON [PRIMARY] ,
CONSTRAINT [IX_wordlist] UNIQUE CLUSTERED
(
[Word]
) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO wordlist(word)
EXECUTE master..xp_cmdshell
'type C:\MyDirectory\wordlist.txt'
DELETE FROM wordlist WHERE word IS NULL
Finally, let's change the rules and say that the square has to have valid words if you read them from the left, from the right, from the top downwards and from the bottom upwards. There seem to be about eighty of these where the same word is not used in the rows more than once.
Here is a rather symmetrical one...
b a t s
a b u t
t u b a
s t a b
...and here is one that contains more words...
w e t s
e m i t
t i d e
s t e p
So can anyone come up with simple SQL that can find all of these symmetrical four-letter word squares from the wordlist? Once again, the editor will give away a prize to a successful entry. If you can do it all in less than seventy lines of SQL then that will be pretty good