--Test out some card probabilities. Similar to the CentralValues

--scripts. Here the same ideas are applied to a card game.

--By C. Eric Cashon

CREATE TABLE Cards1(

Cards1ID INTEGER,

Type VARCHAR(6));

CREATE TABLE Cards2(

Cards2ID INTEGER,

Suit VARCHAR(8));

INSERT INTO Cards1(Cards1ID, Type) VALUES ( 1, 'Ace')

,( 2, 'Two')

,( 3, 'Three')

,( 4, 'Four')

,( 5, 'Five')

,( 6, 'Six')

,( 7, 'Seven')

,( 8, 'Eight')

,( 9, 'Nine')

,(10, 'Ten')

,(11, 'Jack')

,(12, 'Queen')

,(13, 'King');

INSERT INTO Cards2(Cards2ID, Suit) VALUES (1, 'Diamonds')

,(2, 'Hearts')

,(3, 'Spades')

,(4, 'Clubs');

--Take a look at all the cards.

CREATE VIEW FiftyTwoCards AS

SELECT ROWNUMBER() OVER() AS rowid, T1.Type || ' of ' || T2.Suit AS Card

FROM Cards1 T1, Cards2 T2;

SELECT * FROM FiftyTwoCards;

--Check your row count for pairs of two.

--C = 52!/(2!(52 - 2)!)

-- = 1326

CREATE VIEW PossibleCombos(RecordID, Card1, Card2) AS

SELECT ROWNUMBER() OVER() AS RecordID, T1.Card, T2.Card FROM FiftyTwoCards T1, FiftyTwoCards T2

WHERE T1.rowid < T2.rowid;

SELECT * FROM PossibleCombos;

--Possible combinations for 21.

CREATE VIEW TwentyOne(Card1, Card2) AS

SELECT Card1, Card2 FROM PossibleCombos

WHERE (Card1 LIKE('Ace%') AND (Card2 LIKE('Ten%') OR Card2 LIKE('Jack%') OR Card2 LIKE ('Queen%') OR Card2 LIKE('King%')))

OR (Card2 LIKE('Ace%') AND (Card1 LIKE('Ten%') OR Card1 LIKE('Jack%') OR Card1 LIKE ('Queen%') OR Card1 LIKE('King%')));

SELECT * FROM TwentyOne;

--Chance of a Natural 21 in Blackjack. 64/1326 = 4.83%.

WITH Natural (Total1, Total2) AS

(SELECT COUNT(*) Total1, 0 Total2 FROM TwentyOne

UNION

SELECT 0 Total1, COUNT(*) Total2 FROM PossibleCombos)

SELECT (CAST(SUM(Total1) AS REAL) / CAST(SUM(Total2) AS REAL)) * 100 FROM Natural;

--Clean Up

DROP VIEW TwentyOne;

DROP VIEW FiftyTwoCards;

DROP VIEW PossibleCombos;

DROP TABLE Cards1;

DROP TABLE Cards2;