# SQL Puzzle 1: Magic Squares

This is the first in what I hope will be a semi-regular series of recreational puzzles where SQL can be used to find the answer. I set these puzzles on occasional Fridays in my workplace, and as I now have quite an archive I thought I should start sharing them with a wider audience.

I came up with the idea for this puzzle involving Magic Squares by accident while trying to do something different – and getting it wrong! For a moment I thought I’d had an original idea but then it was “Hang on – I’m sure I must have seen this before.”

A bit of googling and that thought was proved right – I’m just not that original. In fact the first documented instance of these appears to be the Chinese legend of Lo Shu – possibly dating back as far as 650BC. I was only beaten by the best part of three millennia…

You may have seen these before at some point – but hopefully long enough ago that you don’t know all the answers! Possibly like me they ring a bell for you but you can’t remember exactly where from.

A Magic Square is a square grid e.g. 1×1, 2×2, 3×4, 4×4…. or n x n, where numbers are placed in each square in the grid such that if you add up the numbers in each column, row, or diagonally they all add up to the same total.

Usually you use consecutive numbers starting from 1 (placing each number just once), so in the 2 x 2 grid you would place the numbers 1 to 4, in the 3 x 3 the numbers 1 to 9 – and so on.

Here’s the 1 x1 grid:

I thought I’d be generous and give youย that one for free ๐

So my first question is can you find a solution (with the numbers 1 to 4) to the 2 x 2 grid:

Having explored that possibility (not for too long I hope!) the meat of this puzzle is to find solutions to the 3 x 3 grid using the numbers 1 to 9:

In fact, using SQL, can you not just find one solution, but all the possible solutions? How many are there?

Have fun ๐

## 13 thoughts on “SQL Puzzle 1: Magic Squares”

1. Grover Vivek says:

Attempt – 1

declare @num int = 3
declare @count int = 1
declare @count1 int = 1
declare @num1 int = @num
declare @values varchar(8000) = ”

declare @str varchar(500) = ‘create table ##tmpdata( ‘

while @count <= @num
begin

select @str = @str + 'col' + cast(@count as varchar) + ' int, '

select @values = @values + '('

while @count1 <= @num1
begin

select @values = @values + cast(@count1 as varchar) + ','

select @count1 = @count1 + 1
end

select @values = left(@values, len(@values)-1) + '),'

select @num1 = @num1 + @num

set @count = @count + 1
end

select @str = left(@str, len(@str)-1) + ')'
select @values = left(@values, len(@values)-1)

print @str
print 'insert into ##tmpdata values '+ @values

exec (@str)
exec('insert into ##tmpdata values '+ @values)

select * from ##tmpdata
drop table ##tmpdata

Like

2. Matthew McGiffen says:

Hey Grover. You might find it easier to start with a fixed grid of 3×3 rather than trying to do it for a grid of size @num. Then you can create a table that will hold all the possible combinations of the numbers 1 to 9, then test which make a magic square.

Like

1. Grover Vivek says:

Hello,
Thanks for feedback. I am sorry that I could not understand reply. Could you please explain. But the code that I posted works for any number like 5,4,10. Just pass it through @num. I mean it can be converted into a sproc.

Like

1. Matthew McGiffen says:

Hi Grover – it works to generate and populate the table, but it doesn’t find a magic square – was suggesting you may need a different data format to be able to test lots of permutations of the grid to be able to find the “Magic” ones, but I may be misunderstanding your approach so go for it if you feel this is the way. Regards ๐

Like

3. Grover Vivek says:

Hi Matthew,

I misunderstood the requirement and that is “where numbers are placed in each square in the grid such that if you add up the numbers in each column, row, or diagonally they all add up to the same total”. I will work on this.. ๐

Liked by 1 person

4. Mark Farmiloe says:

Here’s my solution which prints out the 8 possible magic squares (all rotations and reflections of a single answer):
Declare @d1 int
Declare @d2 int
Declare @d3 int
Declare @d4 int
Declare @d5 int
Declare @d6 int
Declare @d7 int
Declare @d8 int
Declare @d9 int
Declare @i int = 123456789
Declare @j int
Declare @k int

While @i < 987654322
Begin
— Print @i
Set @k = @i
Set @j = 100000000
Set @d1 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d2 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d3 = @k / @j
If @d1 + @d2 + @d3 15
Begin
Set @i = @i + 1000000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d4 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d5 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d6 = @k / @j
If @d4 + @d5 + @d6 15
Begin
Set @i = @i + 1000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d7 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d8 = @k / @j
Set @d9 = @k % @j
Set @i = @i + 1 — do it now so continue can be used
If @d7 + @d8 + @d9 15 Continue
— Test cols
If @d1 + @d4 + @d7 15 Continue
If @d2 + @d5 + @d8 15 Continue
If @d3 + @d6 + @d9 15 Continue
— Test diags
If @d1 + @d5 + @d9 15 Continue
If @d3 + @d5 + @d7 15 Continue
— all digits used
If @d1 * @d2 * @d3 * @d4 * @d5 * @d6 * @d7 * @d8 * @d9 362880 Continue
— Success
Print Cast(@d1 AS char(1)) + N’ ‘ + Cast(@d2 AS char(1)) + N’ ‘ + Cast(@d3 AS char(1))
Print Cast(@d4 AS char(1)) + N’ ‘ + Cast(@d5 AS char(1)) + N’ ‘ + Cast(@d6 AS char(1))
Print Cast(@d7 AS char(1)) + N’ ‘ + Cast(@d8 AS char(1)) + N’ ‘ + Cast(@d9 AS char(1))
Print N’—–‘
End

Like

5. Matthew McGiffen says:

Hi Mark – I mostly get the idea of your approach, but something seems to have gone wrong with the paste into the comments box, seem to be missing = signs or != signs or something. If you check and re-send I’ll try it out. Cheers, Matthew.

Like

6. Mark Farmiloe says:

Hope this is clearer.

~~~~
Declare @d1 int
Declare @d2 int
Declare @d3 int
Declare @d4 int
Declare @d5 int
Declare @d6 int
Declare @d7 int
Declare @d8 int
Declare @d9 int
Declare @i int = 123456789
Declare @j int
Declare @k int

While @i < 987654322
Begin
— Print @i
Set @k = @i
Set @j = 100000000
Set @d1 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d2 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d3 = @k / @j
If @d1 + @d2 + @d3 15
Begin
Set @i = @i + 1000000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d4 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d5 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d6 = @k / @j
If @d4 + @d5 + @d6 15
Begin
Set @i = @i + 1000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d7 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d8 = @k / @j
Set @d9 = @k % @j
Set @i = @i + 1 — do it now so continue can be used
If @d7 + @d8 + @d9 15 Continue
— Test cols
If @d1 + @d4 + @d7 15 Continue
If @d2 + @d5 + @d8 15 Continue
If @d3 + @d6 + @d9 15 Continue
— Test diags
If @d1 + @d5 + @d9 15 Continue
If @d3 + @d5 + @d7 15 Continue
— all digits used
If @d1 * @d2 * @d3 * @d4 * @d5 * @d6 * @d7 * @d8 * @d9 362880 Continue
— Success
Print Cast(@d1 AS char(1)) + N’ ‘ + Cast(@d2 AS char(1)) + N’ ‘ + Cast(@d3 AS char(1))
Print Cast(@d4 AS char(1)) + N’ ‘ + Cast(@d5 AS char(1)) + N’ ‘ + Cast(@d6 AS char(1))
Print Cast(@d7 AS char(1)) + N’ ‘ + Cast(@d8 AS char(1)) + N’ ‘ + Cast(@d9 AS char(1))
Print N’—–‘
End
~~~~

Like

7. Mark Farmiloe says:

No that failed too. I don’t know how to stop your comment processor from messing with my code. I know it works, but it does take 27 seconds on my PC.

Like

1. Matthew McGiffen says:

I’ll look into the comments issue, standard wordpress so not sure how much I can change. Good to know it takes 27 seconds, be interesting to see if people can beat that!

Like

1. Mark Farmiloe says:

I had a quick look and WordPress seems to use Markdown and markdown suggested that surrounding the code with ~~~~ ~~~~ would do the trick but it didn’t. I’ve never used Markdown so I am not familiar with it, so it’s probably my fault.

Like

2. Mark Farmiloe says:

Another look at Mardown and it should work with spaces at the start of each line, so, if at first …

Declare @d1 int
Declare @d2 int
Declare @d3 int
Declare @d4 int
Declare @d5 int
Declare @d6 int
Declare @d7 int
Declare @d8 int
Declare @d9 int
Declare @i int = 123456789
Declare @j int
Declare @k int

While @i < 987654322
Begin
— Print @i
Set @k = @i
Set @j = 100000000
Set @d1 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d2 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d3 = @k / @j
If @d1 + @d2 + @d3 15
Begin
Set @i = @i + 1000000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d4 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d5 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d6 = @k / @j
If @d4 + @d5 + @d6 15
Begin
Set @i = @i + 1000
Continue
End
Set @k = @k % @j
Set @j = @j / 10
Set @d7 = @k / @j
Set @k = @k % @j
Set @j = @j / 10
Set @d8 = @k / @j
Set @d9 = @k % @j
Set @i = @i + 1 — do it now so continue can be used
If @d7 + @d8 + @d9 15 Continue
— Test cols
If @d1 + @d4 + @d7 15 Continue
If @d2 + @d5 + @d8 15 Continue
If @d3 + @d6 + @d9 15 Continue
— Test diags
If @d1 + @d5 + @d9 15 Continue
If @d3 + @d5 + @d7 15 Continue
— all digits used
If @d1 * @d2 * @d3 * @d4 * @d5 * @d6 * @d7 * @d8 * @d9 362880 Continue
— Success
Print Cast(@d1 AS char(1)) + N’ ‘ + Cast(@d2 AS char(1)) + N’ ‘ + Cast(@d3 AS char(1))
Print Cast(@d4 AS char(1)) + N’ ‘ + Cast(@d5 AS char(1)) + N’ ‘ + Cast(@d6 AS char(1))
Print Cast(@d7 AS char(1)) + N’ ‘ + Cast(@d8 AS char(1)) + N’ ‘ + Cast(@d9 AS char(1))
Print N’—–‘
End

Like

8. Mark Farmiloe says:

No – it’s still taken out the not equals signs. I should have used != instead.

Like