Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Model a card deck and a cribbage hand (5 cards), and demonstrate how to count all the '15's (combos of cards that add up to 15 by face-value). See https://natethedba.wordpress.com/2019/05/14/t-sql-tuesday-114-a-puzzle/ .
--Put this stuff in a new schema so we can easily clean up afterward.
CREATE SCHEMA [play] AUTHORIZATION [dbo];
GO
--Set up our Cards model
--DROP TABLE play.Cards;
CREATE TABLE play.Cards
(
CardID int IDENTITY(1,1) PRIMARY KEY CLUSTERED
, Face varchar(2)
, NumValue int
, PtValue int
, Suit varchar(1)
);
--DROP TABLE play.Suits;
CREATE TABLE play.Suits
(
Suit varchar(1) PRIMARY KEY CLUSTERED
, [Name] varchar(10)
);
INSERT INTO play.Suits (Suit, Name)
SELECT 'c', 'Clubs'
UNION ALL
SELECT 'd', 'Diamonds'
UNION ALL
SELECT 'h', 'Hearts'
UNION ALL
SELECT 's', 'Spades'
;
--Build the deck
INSERT INTO play.Cards (Face, NumValue, PtValue, Suit)
SELECT n.Face, n.NV, n.PV, s.Suit
FROM (
SELECT Face = 'A', NV = 1, PV = 1
UNION ALL SELECT '2', 2, 2
UNION ALL SELECT '3', 3, 3
UNION ALL SELECT '4', 4, 4
UNION ALL SELECT '5', 5, 5
UNION ALL SELECT '6', 6, 6
UNION ALL SELECT '7', 7, 7
UNION ALL SELECT '8', 8, 8
UNION ALL SELECT '9', 9, 9
UNION ALL SELECT '10', 10, 10
UNION ALL SELECT 'J', 11, 10
UNION ALL SELECT 'Q', 12, 10
UNION ALL SELECT 'K', 13, 10
) n
, (
SELECT Suit
FROM play.Suits
) s
;
GO
--Show the full deck of cards
SELECT c.*, SuitName = s.Name
FROM play.Cards c
JOIN play.Suits s
ON c.Suit = s.Suit
ORDER BY c.Suit, c.NumValue
GO
--Set up the Hands
--DROP TABLE play.CribbageHand;
CREATE TABLE play.CribbageHand
(
PlayerNum int
, CardID int FOREIGN KEY REFERENCES play.Cards(CardID)
, IsCut bit DEFAULT(0)
, PRIMARY KEY CLUSTERED (PlayerNum, CardID)
);
--Player 1: two 3's, two 6's, and a 9.
INSERT INTO play.CribbageHand (PlayerNum, CardID, IsCut)
SELECT 1, c.CardID, (CASE WHEN c.CardID = 16 THEN 1 ELSE 0 END)
FROM play.Cards c
WHERE c.CardID IN (3, 6, 9, 16, 19)
--Player 2: the "perfect hand", a Jack and four 5's.
INSERT INTO play.CribbageHand (PlayerNum, CardID, IsCut)
SELECT 2, c.CardID, (CASE WHEN c.CardID = 11 THEN 1 ELSE 0 END)
FROM play.Cards c
WHERE c.CardID IN (5, 11, 18, 31, 44)
GO
--Show each player's full hand
SELECT ch.PlayerNum, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
ORDER BY ch.PlayerNum, ch.CardID
--Count the '15's
SELECT DISTINCT
c1.PlayerNum
, Fifteen = (CASE
WHEN 15 = c1.PtValue + c2.PtValue + c3.PtValue + c4.PtValue + c5.PtValue
THEN c1.Face + c1.Suit + ' + ' + c2.Face + c2.Suit + ' + ' + c3.Face + c3.Suit + ' + ' + c4.Face + c4.Suit + ' + ' + c5.Face + c5.Suit
WHEN 15 = c1.PtValue + c2.PtValue + c3.PtValue + c4.PtValue
THEN c1.Face + c1.Suit + ' + ' + c2.Face + c2.Suit + ' + ' + c3.Face + c3.Suit + ' + ' + c4.Face + c4.Suit
WHEN 15 = c1.PtValue + c2.PtValue + c3.PtValue
THEN c1.Face + c1.Suit + ' + ' + c2.Face + c2.Suit + ' + ' + c3.Face + c3.Suit
WHEN 15 = c1.PtValue + c2.PtValue
THEN c1.Face + c1.Suit + ' + ' + c2.Face + c2.Suit
ELSE 'None' END)
FROM (
SELECT ch.PlayerNum, ch.CardID, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
) c1
JOIN (
SELECT ch.PlayerNum, ch.CardID, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
) c2
ON c2.PlayerNum = c1.PlayerNum
AND c2.CardID > c1.CardID
LEFT JOIN (
SELECT ch.PlayerNum, ch.CardID, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
) c3
ON c3.PlayerNum = c1.PlayerNum
AND c3.CardID > c2.CardID
LEFT JOIN (
SELECT ch.PlayerNum, ch.CardID, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
) c4
ON c4.PlayerNum = c1.PlayerNum
AND c4.CardID > c3.CardID
LEFT JOIN (
SELECT ch.PlayerNum, ch.CardID, ch.IsCut, c.Face, c.Suit, c.PtValue
FROM play.CribbageHand ch
JOIN play.Cards c
ON ch.CardID = c.CardID
) c5
ON c5.PlayerNum = c1.PlayerNum
AND c5.CardID > c4.CardID
WHERE (15 = c1.PtValue + c2.PtValue + c3.PtValue + c4.PtValue + c5.PtValue
OR 15 = c1.PtValue + c2.PtValue + c3.PtValue + c4.PtValue
OR 15 = c1.PtValue + c2.PtValue + c3.PtValue
OR 15 = c1.PtValue + c2.PtValue
)
GO
--Clean up after ourselves
DROP TABLE play.CribbageHand;
DROP TABLE play.Cards;
DROP TABLE play.Suits;
GO
DROP SCHEMA [play];
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.