The New Order Oracle Coding Challenge 4 – Tic Tac Toe

Tic Tac Toe, the game of X’s and O’s, was an oddly popular game in elementary school. When playing the game you quickly learn a couple of rules:

Because X always places his mark first (alternating between X and O), there is an unfair advantage for the player placing the X marks.

The player placing his mark in the center square typically has an advantage in the game.

Once a player scores three marks in a row horizontally, vertically, or diagonally, there is no point in continuing the game.

–

–

–

–

–

–

–

Consider the above game matches. In the first two matches both X and O score three in a row, but which player won the match – it depends on the order in which the marks were placed. In the third match, X won with a diagnal three in a row. The final match resulted in a tie (neither player won). You can use a SQL statement similar to the following to output all of the roughly 362,000 combinations (note that not all combinations are unique – rotating the board 90 or 180 degrees to generate a unique combination probably is not fair, and the order in which the marks are placed could matter):

WITH
N AS
(SELECT
ROWNUM N
FROM
DUAL
CONNECT BY
LEVEL<=9),
C AS
(SELECT
'XOX'||'OXO'||'XOX' C
FROM
DUAL)
SELECT
SUBSTR(C.C,N1.N,1) ||
SUBSTR(C.C,N2.N,1) ||
SUBSTR(C.C,N3.N,1) || CHR(10) ||
SUBSTR(C.C,N4.N,1) ||
SUBSTR(C.C,N5.N,1) ||
SUBSTR(C.C,N6.N,1) || CHR(10) ||
SUBSTR(C.C,N7.N,1) ||
SUBSTR(C.C,N8.N,1) ||
SUBSTR(C.C,N9.N,1) GAME
FROM
N N1,
N N2,
N N3,
N N4,
N N5,
N N6,
N N7,
N N8,
N N9,
C
WHERE
N1.N<>N2.N
AND N1.N<>N3.N
AND N1.N<>N4.N
AND N1.N<>N5.N
AND N1.N<>N6.N
AND N1.N<>N7.N
AND N1.N<>N8.N
AND N1.N<>N9.N
AND N2.N<>N3.N
AND N2.N<>N4.N
AND N2.N<>N5.N
AND N2.N<>N6.N
AND N2.N<>N7.N
AND N2.N<>N8.N
AND N2.N<>N9.N
AND N3.N<>N4.N
AND N3.N<>N5.N
AND N3.N<>N6.N
AND N3.N<>N7.N
AND N3.N<>N8.N
AND N3.N<>N9.N
AND N4.N<>N5.N
AND N4.N<>N6.N
AND N4.N<>N7.N
AND N4.N<>N8.N
AND N4.N<>N9.N
AND N5.N<>N6.N
AND N5.N<>N7.N
AND N5.N<>N8.N
AND N5.N<>N9.N
AND N6.N<>N7.N
AND N6.N<>N8.N
AND N6.N<>N9.N
AND N7.N<>N8.N
AND N7.N<>N9.N
AND N8.N<>N9.N;

The output of the above SQL statement should appear similar to the following:

The big problem with the above SQL statement is that it is not clear which player won in all cases. Ideally, the N1.N value would be used to output an X mark in the specified position, the N2.N value would be used to output an O mark in a specified position, the N3.N value would be used to output an X in the specified position, etc. until one of the players places three marks in a row. For example, if N1.N is 5, an X would be placed in the center square, if N2.N is 9, an O would be placed in the bottom right square.

Now that we know that the order in which the marks are placed is important, how would be know when a player wins? You could experiment with the following:

Vertical win, with three positions having the same resulting values:

SELECT
MOD(position - 1, 3) + 1 V
FROM
DUAL;

Horizontal win, with three positions having the same resulting values:

OK, now that I have explained the game, given you a couple of SQL statements to possibly help you with the solution… on to the challenge. With the help of Oracle Database build a tic tac toe solver that will help a player win at tic tac toe. Provided a current description of the board, for example ‘_X_’||’O_X’||’OX_’, display all board solutions that allows player O (or player X if his turn is next) to win. (Side note: I have not yet completed the solution to this challenge – it might be possible to accomplish this challenge with just a SQL statement.)

Wow! Great explanations as you stepped through the development of the final solution. As a bonus, you did not use my partial solution, which I tried to use as the basis for a solution without much success for the better part of a couple of hours.

Posting that much detail as quickly as you did, a couple of minor problems are to be expected – see his followup post for the un-Wordpress mangled version of the WHOWINS function. Most people will also have to drop the “control.” characters in front of the TYPE definition name as well as the function name. Some of the intermediate SQL statements result in errors (when tested on 11.2.0.2) – focus on Gary’s description and the transformation to the SQL statement rather than whether or not an error is produced.

So that people do not get hung up on the minor glitches, what I encountered:

Wow, a recursive SQL statement with regular expressions type solution for the problem. I thought that regular expressions might be part of the solution, but I did not have any idea how to get started with such a solution. I worked with regular expressions way back when I was learning Turbo Pascal, but there have not been enough occasions for me to revisit the logic of regular expressions since that time.

I made a slight adjustment at the end of your SQL statement to add line breaks so that it was easier to confirm that your solution worked:

Hints for Posting Code Sections in Comments

********************
When the spacing of text in a comment section is important for readability (execution plans, PL/SQL blocks, SQL, SQL*Plus output, etc.) please use a <pre> tag before the code section and a </pre> tag after the code section:
<pre>

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;
TODAY
---------
01-MAR-12

</pre>
********************
When posting test case samples, it is much easier for people to reproduce the test case when the SQL*Plus line prefixes are not included - if possible, please remove those line prefixes. This:

SELECT
SYSDATE TODAY
FROM
DUAL;

Is easier to execute in a test case script than this:

SQL> SELECT
2 SYSDATE TODAY
3 FROM
4 DUAL;

********************
Greater than and Less than signs in code sections are often interpretted as HTML formatting commands. Please replace these characters in the code sections with the HTML equivalents for these characters: