Game-Set-Match/[set]full_script.sql at main · patch72/Game-Set-Match · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
-- NoFormat Start
with colors
as
(select 'red'      color   from dual union all
 select 'purple'           from dual union all
 select 'green'            from dual
)
, symbols as
(select 'diamond'  symbol  from dual union all
 select 'squiggle'         from dual union all
 select 'oval'             from dual
)
, shadings as
(select 'solid'    shading from dual union all
 select 'striped'          from dual union all
 select 'open'             from dual
)
, numbers as
(select 1          qty     from dual union all
 select 2                  from dual union all
 select 3                  from dual
)
, all_cards as
(select color
      , symbol
      , shading
      , qty
  from      colors
 cross join symbols
 cross join shadings
 cross join numbers
)
, cards as
(select rownum cardno
      , color
      , symbol
      , shading
      , qty
   from (select color
              , symbol
              , shading
              , qty
           from all_cards
          order by dbms_random.value
        )
  where rownum <= 12
)
select cards.cardno  cards1_cardno
     , cards.symbol  cards1_symbol
     , cards.color   cards1_color
     , cards.shading cards1_shading
     , cards.qty     cards1_qty
     , null          cards2_cardno
     , null          cards2_symbol
     , null          cards2_color
     , null          cards2_shading
     , null          cards2_qty
     , null          cards3_cardno
     , null          cards3_symbol
     , null          cards3_color
     , null          cards3_shading
     , null          cards3_qty
     , 'puzzle'      what
  from cards
 union all
select *
 from (select cards1.cardno  cards1_cardno
            , cards1.symbol  cards1_symbol
            , cards1.color   cards1_color
            , cards1.shading cards1_shading
            , cards1.qty     cards1_qty
            , cards2.cardno  cards2_cardno
            , cards2.symbol  cards2_symbol
            , cards2.color   cards2_color
            , cards2.shading cards2_shading
            , cards2.qty     cards2_qty
            , cards3.cardno  cards3_cardno
            , cards3.symbol  cards3_symbol
            , cards3.color   cards3_color
            , cards3.shading cards3_shading
            , cards3.qty     cards3_qty
            , 'solution'     what
         from      cards     cards1
        cross join cards     cards2
        cross join cards     cards3
        where cards3.cardno > cards2.cardno
          and cards2.cardno > cards1.cardno
      )
 where (   (    cards1_symbol = cards2_symbol
            and cards1_symbol = cards3_symbol
           )
        or (    cards1_symbol <> cards2_symbol
            and cards2_symbol <> cards3_symbol
            and cards1_symbol <> cards3_symbol
           )
       )
   and (   (    cards1_color = cards2_color
            and cards1_color = cards3_color
           )
        or (    cards1_color <> cards2_color
            and cards2_color <> cards3_color
            and cards1_color <> cards3_color
           )
       )
   and (   (    cards1_shading = cards2_shading
            and cards1_shading = cards3_shading
           )
        or (    cards1_shading <> cards2_shading
            and cards2_shading <> cards3_shading
            and cards1_shading <> cards3_shading
           )
       )
   and (   (    cards1_qty = cards2_qty
            and cards1_qty = cards3_qty
           )
        or (    cards1_qty <> cards2_qty
            and cards2_qty <> cards3_qty
            and cards1_qty <> cards3_qty
           )
       )
 order by what
        , cards1_cardno
        , cards2_cardno
        , cards3_cardno
/
-- NoFormat End