The eight queen problem on oracle sql

Hi, Habrew

In May, the IT-Planet Olympiad was held in Moscow, one of the nominations of which was “Programming Oracle DBMS”. The tasks were interesting and difficult, and I would like to share the decision of some of them.
The first task I’ll tell you about is the eight queens problem, it was necessary to solve it using only SQL and nothing more; which brought me first place and a diploma from the hands of the Minister of Communications and Mass Communications of the Russian Federation.


So, the task is voiced, the answer must be presented in the form:
A B C D E F G H
--- --- --- --- --- --- --- ---
a7 b4 c2 d8 e6 f1 g3 h5
')
How is this solved:
The first thing that needs to be done is to create a query that will reproduce a chessboard with queen spaced, after which you can exclude the wrong decisions.
By means of Oracle hierarchical queries (specifically, connect by level), we create a query that produces numbers from 1 to 8:

select level a from dual connect by level <= 8

* This source code was highlighted with Source Code Highlighter .

These are the queen's possible positions on A (A1, A2, ...)
We make a cross join of eight such queries, changing the letter for clarity in each of the following:

select level a from dual connect by level <= 8
cross join
( select level b from dual connect by level <= 8)
cross join
( select level c from dual connect by level <= 8)
cross join
( select level d from dual connect by level <= 8)
cross join
( select level e from dual connect by level <= 8)
cross join
( select level f from dual connect by level <= 8)
cross join
( select level g from dual connect by level <= 8)
cross join
( select level h from dual connect by level <= 8)

* This source code was highlighted with Source Code Highlighter .

A query constructed in this way yields 16777216 variants, i.e. variants, when there are no two queens on one vertical. Now you need to exclude 2 queens on the same horizontal line and on the diagonals.
Solving the problem, at first I removed everything from the verticals:

select 'a' || a A, 'b' || b B, 'c' || c C, 'd' || d D, 'e' || e E, 'f' || f F, 'g' || g G, 'h' || h H
from
(
select a, b, c, d, e, f, g, h ,
case when a = b or a = c or a = d or a = e or a = f or a = g or a = h
then 0
else case when b = c or b = d or b = e or b = f or b = g or b = h
then 0
else case when c = d or c = e or c = f or c = g or c = h
then 0
else case when d = e or d = f or d = g or d = h
then 0
else case when e = f or e = g or e = h
then 0
else case when f = g or f = h
then 0
else case when g = h
then 0
else 1
end
end
end
end
end
end
end chk
from
( select level a from dual connect by level <= 8)
cross join
( select level b from dual connect by level <= 8)
cross join
( select level c from dual connect by level <= 8)
cross join
( select level d from dual connect by level <= 8)
cross join
( select level e from dual connect by level <= 8)
cross join
( select level f from dual connect by level <= 8)
cross join
( select level g from dual connect by level <= 8)
cross join
( select level h from dual connect by level <= 8)
)
where chk = 1

* This source code was highlighted with Source Code Highlighter .

Now there is the last stage where I removed everything from the diagonals:

select 'a' || a A, 'b' || b B, 'c' || c C, 'd' || d D, 'e' || e E, 'f' || f F, 'g' || g G, 'h' || h H
from
(
select a, b, c, d, e, f, g, h ,
case when a = b or a = b - 1 or a = b + 1 or a = c or a = c - 2 or a = c + 2 or a = d or a = d - 3 or a = d + 3 or a = e or a = e - 4 or a = e + 4 or a = f or a = f - 5 or a = f + 5 or a = g or a = g - 6 or a = g + 6 or a = h or a = h - 7 or a = h + 7
then 0
else case when b = c or b = c - 1 or b = c + 1 or b = d or b = d - 2 or b = d + 2 or b = e or b = e - 3 or b = e + 3 or b = f or b = f - 4 or b = f + 4 or b = g or b = g - 5 or b = g + 5 or b = h or b = h - 6 or b = h + 6
then 0
else case when c = d or c = d - 1 or c = d + 1 or c = e or c = e - 2 or c = e + 2 or c = f or c = f - 3 or c = f + 3 or c = g or c = g - 4 or c = g + 4 or c = h or c = h - 5 or c = h + 5
then 0
else case when d = e or d = e - 1 or d = e + 1 or d = f or d = f - 2 or d = f + 2 or d = g or d = g - 3 or d = g + 3 or d = h or d = h - 4 or d = h + 4
then 0
else case when e = f or e = f - 1 or e = f + 1 or e = g or e = g - 2 or e = g + 2 or e = h or e = h - 3 or e = h + 3
then 0
else case when f = g or f = g - 1 or f = g + 1 or f = h or f = h - 2 or f = h + 2
then 0
else case when g = h or g = h - 1 or g = h + 1
then 0
else 1
end
end
end
end
end
end
end chk
from
( select level a from dual connect by level <= 8)
cross join
( select level b from dual connect by level <= 8)
cross join
( select level c from dual connect by level <= 8)
cross join
( select level d from dual connect by level <= 8)
cross join
( select level e from dual connect by level <= 8)
cross join
( select level f from dual connect by level <= 8)
cross join
( select level g from dual connect by level <= 8)
cross join
( select level h from dual connect by level <= 8)
)
where chk = 1

* This source code was highlighted with Source Code Highlighter .

Removing the excess from the diagonals is not too elegant, you could use abs, but in the haste of the Olympiad it did not occur to me, especially since I sent the answer 2 minutes before the end of time.
This solution is absolutely legal and gives all 92 solutions to this problem.

UPD: Moved to "Abnormal Programming"