SQL Battleship

Question

SQL Battleship

Viewing 15 posts - 31 through 45 (of 74 total)

You must be logged in to reply to this topic. Login to reply

Sean Smith SSCertifiable Points: 5987 More actions · Answer 1

andrei_solntsev (6/30/2014)
That's ok. It is possible to rewrite code to use different rules.
For example, you may define Ship_Definitions table as
IF OBJECT_ID (N'tempdb.dbo.#temp_Ship_Definitions', N'U') IS NULL
BEGIN
CREATE TABLE dbo.#temp_Ship_Definitions
(
DefID tinyint NOT NULL PRIMARY KEY,
Letter char(1) NOT NULL,
Squares tinyint NOT NULL,
Qty tinyint NOT NULL,
Name varchar(20) NOT NULL
);
INSERT dbo.#temp_Ship_Definitions (DefID, Letter, Squares, Qty, Name)
VALUES (1, 'B', 4, 1, 'Battleship'),
(2, 'D', 3, 2, 'Destroyer'),
(3, 'S', 2, 3, 'Submarine'),
(4, 'P', 1, 4, 'Patrol Boat');
END
Also I recommend to use PIVOT to display a grid. Try this
SET @Loop_Player = 0;
WHILE @Loop_Player <= 1 --
BEGIN
SELECT [0] AS [A], [1] AS , [2] AS [C], [3] AS [D], [4] AS [E], [5] AS [F], [6] AS [G], [7] AS [H], [8] AS [ I], [9] AS [ J] -- spaces added as letters I and J are too short
FROM
(
SELECT grid_value_current AS [Value], (row_id-1-@Loop_Player*100)/10 AS [Row], (row_id-1)%10 AS [Col]
FROM dbo.#temp_SB_Ocean_Grid
WHERE row_id BETWEEN (@Loop_Player*100) AND (@Loop_Player*100 + 99)
) AS p
PIVOT (MAX([Value]) FOR [Col] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])) AS pvt
ORDER BY [Row];
SET @Loop_Player = @Loop_Player + 1;
END
This game is a perfect excersise to improve coding skills

Definitely possible, but unfortunately is requires a bit more than just changing the table definition of the ships to play with different rules / set up. If you decide to make your own version with all the rule / set up / code changes you suggested let me know. I would love to see it! Thanks for the feedback on the Pivot. 🙂

Sean Smith SSCertifiable Points: 5987 More actions · Answer 2

andrei_solntsev (6/30/2014)
Also I recommend to use PIVOT to display a grid. Try this
SET @Loop_Player = 0;
WHILE @Loop_Player <= 1 --
BEGIN
SELECT [0] AS [A], [1] AS , [2] AS [C], [3] AS [D], [4] AS [E], [5] AS [F], [6] AS [G], [7] AS [H], [8] AS [ I], [9] AS [ J] -- spaces added as letters I and J are too short
FROM
(
SELECT grid_value_current AS [Value], (row_id-1-@Loop_Player*100)/10 AS [Row], (row_id-1)%10 AS [Col]
FROM dbo.#temp_SB_Ocean_Grid
WHERE row_id BETWEEN (@Loop_Player*100) AND (@Loop_Player*100 + 99)
) AS p
PIVOT (MAX([Value]) FOR [Col] IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9])) AS pvt
ORDER BY [Row];
SET @Loop_Player = @Loop_Player + 1;
END

Just a quick note in case anyone tries the PIVOT solution proposed by Andrei. The code is incorrect. The line:

WHERE row_id BETWEEN (@Loop_Player*100) AND (@Loop_Player*100 + 99)

...should actually be:

WHERE row_id BETWEEN (@Loop_Player*100 + 1) AND (@Loop_Player*100 + 100)

below86 SSChampion Points: 11538 More actions · Answer 3

I've noticed a pattern in the shots the computer takes, at least on my server. Does that mean I played it too much?:-) Shots start out with E05 every time. Here is a list of the next 21 shots:

F06, G07, D04, G03, H04, C07, D08, F02, C03, I05, B06, H08, E09, E01, A05, J06, F10, B02, I09, H01, I02, this is where it ended the pattern after finally hitting one of my ships. It took a lot of rerunning before I could get two set ups for me that missed all of these shots. I won each game, sank two of the computers ships before it got a hit on mine.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Sean Smith SSCertifiable Points: 5987 More actions · Answer 4

below86 (7/3/2014)
I've noticed a pattern in the shots the computer takes, at least on my server. Does that mean I played it too much?:-) Shots start out with E05 every time. Here is a list of the next 21 shots:
F06, G07, D04, G03, H04, C07, D08, F02, C03, I05, B06, H08, E09, E01, A05, J06, F10, B02, I09, H01, I02, this is where it ended the pattern after finally hitting one of my ships. It took a lot of rerunning before I could get two set ups for me that missed all of these shots. I won each game, sank two of the computers ships before it got a hit on mine.

Well observed! 🙂 The pattern is based on the probability density matrix. As the computer starts honing in on ships it will appear more "random", but at the start of the game is follows a distinct path. Surprised it took someone this long to notice! Again, well done! 🙂

mssqlsrv SSCrazy Points: 2375 More actions · Answer 5

Gr8!!!! Njoyed a lot...

Game over. Human player wins in 48 shots!

Sean Smith SSCertifiable Points: 5987 More actions · Answer 6

mssqlsrv (7/22/2014)
Gr8!!!! Njoyed a lot...
Game over. Human player wins in 48 shots!

Well done! Glad you liked it. 🙂

Sean Smith SSCertifiable Points: 5987 More actions · Answer 7

New version coming soon! Changes:

1. Extended "Deployment" Logic

2. Extended "Computer Pick" Logic

3. Reworked "Ocean Grids Display" Code

Gordon.Self SSC Rookie Points: 49 More actions · Answer 8

I love the game. It does not matter what rules are used as long as we know which one are being used. I think that I have played it and the previous one too much as well. This version rarely sets horizontal ships on either the top or bottom of the grid. It is a pity as these are always likely winning scenarios, especially the bottom row on the old version.

Thanks for showing your skill in SQL but there again I am a novice with only using it for a couple of years.

Sean Smith SSCertifiable Points: 5987 More actions · Answer 9

I'm glad you liked it. It was a lot of fun to build (became a bit of an obsession at one point LOL).

RD-201664 Ten Centuries Points: 1334 More actions · Answer 10

The first time I played the computer sunk my entire fleet in 29 shots while I only scored 3 hits!!

Grrrr, the code must have BUGS!! 😉

Thanks for a new obsession. 😎

Sean Smith SSCertifiable Points: 5987 More actions · Answer 11

RD-201664 (10/17/2014)
The first time I played the computer sunk my entire fleet in 29 shots while I only scored 3 hits!!
Grrrr, the code must have BUGS!! 😉
Thanks for a new obsession. 😎

LOL! Glad you like it. 🙂 Thanks for posting your score. 🙂