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/ .

DemoCribbageHandScorer.sql

--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