-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathhockey-databse.txt
199 lines (184 loc) · 16 KB
/
hockey-databse.txt
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
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
Hockey Database
---------------------------------------------------CREATE-------------------------------------------------
CREATE DATABASE nhl
CREATE TABLE nhl.team(
TeamName varchar(255) NOT NULL PRIMARY KEY
);
CREATE TABLE nhl.player(
TeamName varchar(255)
Name varchar(255) NOT NULL PRIMARY KEY
Number int
Flag varchar(255)
Position char
Height varchar(255)
Weight int
Exp int
DOB date
Age int
FOREIGN KEY (TeamName) REFERENCES team(TeamName)
);
CREATE TABLE nhl.team(
TeamName varchar(255) NOT NULL PRIMARY KEY
);
CREATE TABLE nhl.game(
TeamName varchar(255),
Opponent varchar(255),
Away varchar(1),
Day date,
Time time
);
---------------------------------------------------VALUES-------------------------------------------------
INSERT INTO nhl.team(TeamName)
VALUES ("Bruins"), ("Blackhawks"), ("Sabres"), ("Red Wings"), ("Panthers"), ("Canadiens"),
("Senators"), ("Lightning"), ("Maple Leafs"), ("Hurricanes"), ("Blue Jackets"),
("Devils"), ("Islanders"), ("Rangers"), ("Flyers"), ("Penguins"), ("Capitals"),
("Avalanche"), ("Stars"), ("Wild"), ("Predators"), ("Blues"), ("Jets"), ("Ducks"),
("Coyotes"), ("Flames"), ("Oilers"), ("Kings"), ("Sharks"), ("Canucks"),
("Golden Knights");
INSERT INTO nhl.game(TeamName, Opponent, Away, Day)
VALUES ("Stars", "Bruins", "T", '2019-10-03', "19:00:00"), ("Coyotes", "Bruins", "T", '2019-10-05', "19:00:00"),
("Golden Knights", "Bruins", "T", '2019-10-08', "19:00:00"), ("Avalanche", "Bruins", "T", '2019-10-10', "19:00:00"),
("Devils", "Bruins", "F", '2019-10-12', "19:00:00"), ("Ducks", "Bruins", "F", '2019-10-14', "19:00:00"),
("Lightning", "Bruins", "F", '2019-10-17', "19:00:00"), ("Maple Leafs", "Bruins", "T", '2019-10-19', "19:00:00"),
("Maple Leafs", "Bruins", "F", '2019-10-22', "19:00:00"), ("Blues", "Bruins", "F", '2019-10-26', "19:00:00"),
("Rangers", "Bruins", "T", '2019-10-27', "19:00:00"), ("Sharks", "Bruins", "F", '2019-10-29', "19:00:00"),
("Senators", "Bruins", "F", '2019-11-02', "19:00:00"), ("Penguins", "Bruins", "F", '2019-11-04', "19:00:00"),
("Canadiens", "Bruins", "T", '2019-11-05', "19:00:00"), ("Red Wings", "Bruins", "T",'2019-11-08', "19:00:00"),
("Flyers", "Bruins", "F", '2019-11-10', "19:00:00"), ("Panthers", "Bruins", "F", '2019-11-12', "19:00:00"),
("Maple Leafs", "Bruins", "T", '2019-11-15', "19:00:00"), ("Capitals", "Bruins", "F", '2019-11-16', "19:00:00"),
("Devils", "Bruins", "T", '2019-11-19', "19:00:00"), ("Sabres", "Bruins", "F", '2019-11-21', "19:00:00"),
("Wild", "Bruins", "F", '2019-11-23', "19:00:00"), ("Canadiens", "Bruins", "T", '2019-11-26', "19:00:00"),
("Senators", "Bruins", "F", '2019-11-27', "19:00:00"), ("Rangers", "Bruins", "F", '2019-11-29', "19:00:00"),
("Canadiens", "Bruins", "F", '2019-12-01', "19:00:00"), ("Hurricanes", "Bruins", "F", '2019-12-03', "19:00:00"),
("Blackhawks", "Bruins", "F", '2019-12-05', "19:00:00"), ("Avalanche", "Bruins", "F", '2019-12-07', "19:00:00"),
("Senators", "Bruins", "T", '2019-12-09', "19:00:00"), ("Capitals", "Bruins", "T", '2019-12-11', "19:00:00"),
("Lightning", "Bruins", "T", '2019-12-12', "19:00:00"), ("Panthers", "Bruins", "T", '2019-12-14', "19:00:00"),
("Kings", "Bruins", "F", '2019-12-17', "19:00:00"), ("Islanders", "Bruins", "F", '2019-12-19', "19:00:00"),
("Predators", "Bruins", "F", '2019-12-21', "19:00:00"), ("Capitals", "Bruins", "F", '2019-12-23', "19:00:00"),
("Sabres", "Bruins", "T", '2019-12-27', "19:00:00"), ("Sabres", "Bruins", "F", '2019-12-29', "19:00:00"),
("Devils", "Bruins", "T", '2019-12-31', "19:00:00"), ("Blue Jackets", "Bruins", "F", '2020-01-02', "19:00:00"),
("Oilers", "Bruins", "F", '2020-01-04', "19:00:00"), ("Predators", "Bruins", "T", '2020-01-07', "19:00:00"),
("Jets", "Bruins", "F", '2020-01-09', "19:00:00"), ("Islanders", "Bruins", "T", '2020-01-11', "19:00:00"),
("Flyers", "Bruins", "T", '2020-01-13', "19:00:00"), ("Blue Jackets", "Bruins", "T", '2020-01-14', "19:00:00"),
("Penguins", "Bruins", "F", '2020-01-16', "19:00:00), ("Penguins", "Bruins", "T", '2020-01-19', "19:00:00"),
("Golden Knights", "Bruins", "F", '2020-01-21', "19:00:00"), ("Jets", "Bruins", "T", '2020-01-31', "19:00:00"),
("Wild", "Bruins", "T", '2020-02-01', "19:00:00"), ("Canucks", "Bruins", "F", '2020-02-04', "19:00:00"),
("Blackhawks", "Bruins", "T", '2020-02-05', "19:00:00"), ("Coyotes", "Bruins", "F", '2020-02-08', "19:00:00"),
("Red Wings", "Bruins", "T", '2020-02-09', "19:00:00"), ("Canadiens", "Bruins", "F", '2020-02-12', "19:00:00"),
("Red Wings", "Bruins", "F", '2020-02-15', "19:00:00"), ("Rangers", "Bruins", "T", '2020-02-16', "19:00:00"),
("Oilers", "Bruins", "T", '2020-02-19', "19:00:00"), ("Flames", "Bruins", "T", '2020-02-21', "19:00:00"),
("Canucks", "Bruins", "T", '2020-02-22', "19:00:00"), ("Flames", "Bruins", "F", '2020-02-25', "19:00:00"),
("Stars", "Bruins", "F", '2020-02-27', "19:00:00"), ("Islanders", "Bruins", "T", '2020-02-29', "19:00:00"),
("Lightning", "Bruins", "T", '2020-03-03', "19:00:00"), ("Panthers", "Bruins", "T", '2020-03-05', "19:00:00"),
("Lightning", "Bruins", "F", '2020-03-07', "19:00:00"), ("Flyers", "Bruins", "T", '2020-03-10', "19:00:00"),
("Sabres", "Bruins", "T", '2020-03-13', "19:00:00"), ("Maple Leafs", "Bruins", "F", '2020-03-14', "19:00:00"),
("Blue Jackets", "Bruins", "F", '2020-03-16', "19:00:00"), ("Ducks", "Bruins", "T", '2020-03-18', "19:00:00"),
("Kings", "Bruins", "T", '2020-03-19', "19:00:00"), ("Sharks", "Bruins", "T", '2020-03-21', "19:00:00"),
("Red Wings", "Bruins", "F", '2020-03-24', "19:00:00"), ("Senators", "Bruins", "F", '2020-03-26', "19:00:00"),
("Panthers", "Bruins", "F", '2020-03-28', "19:00:00"), ("Hurricanes", "Bruins", "T", '2020-03-31', "19:00:00"),
("Blues", "Bruins", "T", '2020-04-02', "19:00:00"), ("Hurricanes", "Bruins", "F", '2020-04-04', "19:00:00");
INSERT INTO nhl.game (TeamName, Opponent, Away, Day)
VALUES ("Flyers", "Blackhawks", "T", '2019-10-04', "19:00:00"), ("Sharks", "Blackhawks", "F", '2019-10-10', "19:00:00"),
("Jets", "Blackhawks", "F", '2019-10-12', "19:00:00"), ("Oilers", "Blackhawks", "F", '2019-10-14', "19:00:00"),
("Blue Jackets", "Blackhawks", "F", '2019-10-18', "19:00:00"), ("Capitals", "Blackhawks", "F", '2019-10-20', "19:00:00"),
("Golden Knights", "Blackhawks", "F", '2019-10-22', "19:00:00"), ("Flyers", "Blackhawks", "F", '2019-10-24', "19:00:00"),
("Hurricanes", "Blackhawks", "T", '2019-10-26', "19:00:00"), ("Kings", "Blackhawks", "F", '2019-10-27', "19:00:00"),
("Predators", "Blackhawks", "T", '2019-10-29', "19:00:00"), ("Kings", "Blackhawks", "T", '2019-11-02', "19:00:00"),
("Ducks", "Blackhawks", "T", '2019-11-03', "19:00:00"), ("Sharks", "Blackhawks", "T", '2019-11-05', "19:00:00"),
("Canucks", "Blackhawks", "F", '2019-11-07', "19:00:00"), ("Penguins", "Blackhawks", "T", '2019-11-09', "19:00:00"),
("Maple Leafs", "Blackhawks", "F", '2019-11-10', "19:00:00"), ("Golden Knights", "Blackhawks", "T", '2019-11-13', "19:00:00"),
("Predators", "Blackhawks", "T", '2019-11-16', "19:00:00"), ("Sabres", "Blackhawks", "F", '2019-11-17', "19:00:00"),
("Hurricanes", "Blackhawks", "F", '2019-11-19', "19:00:00"), ("Lightning", "Blackhawks", "F", '2019-11-21', "19:00:00"),
("Stars", "Blackhawks", "T", '2019-11-23', "19:00:00"), ("Stars", "Blackhawks", "F", '2019-11-26', "19:00:00"),
("Avalanche", "Blackhawks", "F", '2019-11-29', "19:00:00"), ("Avalanche", "Blackhawks", "T", '2019-11-30', "19:00:00"),
("Blues", "Blackhawks", "F", '2019-12-02', "19:00:00"), ("Bruins", "Blackhawks", "T", '2019-12-05', "19:00:00"),
("Devils", "Blackhawks", "T", '2019-12-06', "19:00:00"), ("Coyotes", "Blackhawks", "F", '2019-12-08', "19:00:00"),
("Golden Knights", "Blackhawks", "T", '2019-12-10', "19:00:00"), ("Coyotes", "Blackhawks", "T", '2019-12-12', "19:00:00"),
("Blues", "Blackhawks", "T", '2019-12-14', "19:00:00"), ("Wild", "Blackhawks", "F", '2019-12-15', "19:00:00"),
("Avalanche", "Blackhawks", "F", '2019-12-18', "19:00:00"), ("Jets", "Blackhawks", "T", '2019-12-19', "19:00:00"),
("Avalanche", "Blackhawks", "T", '2019-12-21', "19:00:00"), ("Devils", "Blackhawks", "F", '2019-12-23', "19:00:00"),
("Islanders", "Blackhawks", "F", '2019-12-27', "19:00:00"), ("Blue Jackets", "Blackhawks", "T", '2019-12-29', "19:00:00"),
("Flames", "Blackhawks", "T", '2019-12-31', "19:00:00"), ("Canucks", "Blackhawks", "T", '2020-01-02', "19:00:00"),
("Red Wings", "Blackhawks", "F", '2020-01-05', "19:00:00"), ("Flames", "Blackhawks", "F", '2020-01-07', "19:00:00"),
("Predators", "Blackhawks", "F", '2020-01-09', "19:00:00"), ("Ducks", "Blackhawks", "F", '2020-01-11', "19:00:00"),
("Senators", "Blackhawks", "T", '2020-01-14', "19:00:00"), ("Canadiens", "Blackhawks", "T", '2020-01-15', "19:00:00"),
("Maple Leafs", "Blackhawks", "T", '2020-01-18', "19:00:00"), ("Jets", "Blackhawks", "F", '2020-01-19', "19:00:00"),
("Panthers", "Blackhawks", "F", '2020-01-21', "19:00:00"), ("Coyotes", "Blackhawks", "T", '2020-02-01', "19:00:00"),
("Wild", "Blackhawks", "T", '2020-02-04', "19:00:00"), ("Bruins", "Blackhawks", "F", '2020-02-05', "19:00:00"),
("Jets", "Blackhawks", "T", '2020-02-09', "19:00:00"), ("Oilers", "Blackhawks", "T", '2020-02-11', "19:00:00"),
("Canucks", "Blackhawks", "T", '2020-02-12', "19:00:00"), ("Flames", "Blackhawks", "T", '2020-02-15', "19:00:00"),
("Jets", "Blackhawks", "T", '2020-02-16', "19:00:00"), ("Rangers", "Blackhawks", "F", '2020-02-19', "19:00:00"),
("Predators", "Blackhawks", "F", '2020-02-21', "19:00:00"), ("Stars", "Blackhawks", "T", '2020-02-23', "19:00:00"),
("Blues", "Blackhawks", "T", '2020-02-25', "19:00:00"), ("Lightning", "Blackhawks", "T", '2020-02-27', "19:00:00"),
("Panthers", "Blackhawks", "T", '2020-02-29', "19:00:00"), ("Ducks", "Blackhawks", "F", '2020-03-03', "19:00:00"),
("Oilers", "Blackhawks", "F", '2020-03-05', "19:00:00"), ("Red Wings", "Blackhawks", "T", '2020-03-06', "19:00:00"),
("Blues", "Blackhawks", "F", '2020-03-08', "19:00:00"), ("Sharks", "Blackhawks", "F", '2020-03-11', "19:00:00"),
("Senators", "Blackhawks", "F", '2020-03-13', "19:00:00"), ("Capitals", "Blackhawks", "T", '2020-03-14', "19:00:00"),
("Wild", "Blackhawks", "T", '2020-03-17', "19:00:00"), ("Wild", "Blackhawks", "F", '2020-03-19', "19:00:00"),
("Sabres", "Blackhawks", "T", '2020-03-21', "19:00:00"), ("Predators", "Blackhawks", "F", '2020-03-22', "19:00:00"),
("Penguins", "Blackhawks", "F", '2020-03-25', "19:00:00"), ("Stars", "Blackhawks", "F", '2020-03-27', "19:00:00"),
("Kings", "Blackhawks", "F", '2020-03-29', "19:00:00"), ("Canadiens", "Blackhawks", "F", '2020-03-31', "19:00:00"),
("Islanders", "Blackhawks", "T", '2020-04-02', "19:00:00"), ("Rangers", "Blackhawks", "T", '2020-04-04', "19:00:00");
INSERT INTO nhl.player (TeamName, Name, Number, Flag, Position, Height, Weight, Exp, DOB, Age)
VALUES ("Bruins", "David Backes", 42, "USA", 'F', "6-3", 215, 13, '1984-05-01', 35),
("Bruins", "Patrice Bergeron", 37, "CAN", 'F', "6-1", 195, 15, '1985-07-24', 34),
("Bruins", "Anders Bjork", 10, "USA", 'F', "6-0", 190, 2, '1996-08-05', 23),
("Bruins", "Anton Blidh", 81, "SWE", 'F', "6-0", 201, 3, '1995-03-14', 24),
("Bruins", "Peter Cehlarik", 22, "SVK", 'F', "6-2", 202, 3, '1995-08-02', 24),
("Bruins", "Zdeno Chara", 33, "SVK", 'D', "6-9", 250, 21, '1977-03-18', 42),
("Bruins", "Connor Clifton", 75, "USA", 'D', "5-11", 175, 1, '1995-04-28', 24),
("Bruins", "Charlie Coyle", 13, "USA", 'F', "6-3", 200, 7, '1992-03-02', 27),
("Bruins", "Jake DeBrusk", 74, "CAN", 'F', "6-0", 188, 2, '1996-10-17', 23),
("Bruins", "Trent Frederic", 82, "USA", 'F', "6-2", 203, 1, '1998-02-11', 21),
("Bruins", "Matt Grzelcyk", 48, "USA", 'D', "5-9", 174, 3, '1994-01-05', 26),
("Bruins", "Jaroslav Halak", 41, "SVK", 'G', "5-11", 185, 13, '1985-05-13', 34),
("Bruins", "Danton Heinen", 43, "CAN", 'F', "6-1", 188, 3, '1995-07-05', 24),
("Bruins", "Steven Kampfer", 44, "USA", 'F', "5-11", 198, 7, '1988-09-24', 31),
("Bruins", "David Krejci", 46, "CZE", 'F', "6-0", 188, 13, '1986-04-28', 33),
("Bruins", "Torey Krug", 47, "USA", 'D', "5-9", 186, 8, '1991-04-12', 28),
("Bruins", "Karson Kuhlman", 83, "USA", 'F', "5-11", 185, 1, '1995-09-26', 24),
("Bruins", "Sean Kuraly", 52, "USA", 'F', "6-2", 213, 3, '1993-01-20', 27),
("Bruins", "Maxime Lagace", 35, "CAN", 'G', "6-2", 190, 2, '1993-01-12', 27),
("Bruins", "Brad Marchand", 63, "CAN", 'F', "5-9", 181, 10, '1988-05-11', 31),
("Bruins", "Charlie McAvoy", 73, "USA", 'D', "6-0", 208, 2, '1997-12-21', 22),
("Bruins", "Kevan Miller", 86, "USA", 'D', "6-2", 210, 6, '1987-11-15', 32),
("Bruins", "John Moore", 27, "USA", 'D', "6-2", 210, 9, '1990-11-19', 29),
("Bruins", "Joakim Nordstrom", 20, "SWE", 'F', "6-1", 194, 6, '1992-02-25', 27),
("Bruins", "David Pastrnak", 88, "CZE", 'F', "6-0", 194, 5, '1996-05-25', 23),
("Bruins", "Tuukka Rask", 40, "FIN", 'G', "6-3", 176, 12, '1987-03-10', 32),
("Bruins", "Chris Wagner", 14, "USA", 'F', "6-0", 198, 5, '1991-05-27', 28),
("Bruins", "Jakub Zboril", 67, "CZE", 'D', "6-0", 200, 1, '1997-02-21', 22);
INSERT INTO nhl.player (TeamName, Name, Number, Flag, Position, Height, Weight, Exp, DOB, Age)
VALUES ("Blackhawks", "Drake Caggiula", 91, "CAN", 'F', "5-10", 176, 3, '1994-06-20', 25),
("Blackhawks", "Ryan Carpenter", 22, "USA", 'F', "6-0", 200, 4, '1991-01-18', 29),
("Blackhawks", "Corey Crawford", 50, "CAN", 'G', "6-2", 216, 12, '1984-12-31', 35),
("Blackhawks", "Carl Dahlstrom", 63, "SWE", 'D', "6-4", 231, 2, '1995-01-28', 25),
("Blackhawks", "Calvin de Haan", 44, "CAN", 'D', "6-1", 195, 7, '1991-05-09', 44),
("Blackhawks", "Alex DeBrincat", 12, "USA", 'F', "5-7", 165, 2, '1997-12-18', 22),
("Blackhawks", "Collin Delia", 60, "USA", 'G', "6-2", 208, 2, '1994-06-20', 25),
("Blackhawks", "Dennis Gilbert", 39, "USA", 'D', "6-2", 216, 1, '1996-10-30', 23),
("Blackhawks", "Erik Gustafsson", 56, "SWE", 'D', "6-0", 197, 3, '1992-03-14', 27),
("Blackhawks", "David Kampf", 64, "CZE", 'F', "6-2", 188, 2, '1995-01-12', 25),
("Blackhawks", "Patrick Kane", 88, "USA", 'F', "5-10", 177, 12, '1988-11-19', 31),
("Blackhawks", "Duncan Keith", 2, "CAN", 'D', "6-1", 192, 14, '1983-07-16', 36),
("Blackhawks", "Slater Koekkoek", 68, "CAN", 'D', "6-2", 193, 5, '1994-02-18', 25),
("Blackhawks", "Robin Lehner", 40, "SWE", 'G', "6-4", 240, 9, '1991-07-24', 28),
("Blackhawks", "Olli Maatta", 6, "FIN", 'D', "6-2", 206, 6, '1994-08-22', 25),
("Blackhawks", "Connor Murphy", 5, "USA", 'D', "6-4", 212, 6, '1993-03-26', 26),
("Blackhawks", "Alexander Nylander", 92, "CAN",'F', "6-1", 196, 3, '1998-03-02', 21),
("Blackhawks", "Brendan Perlini", 11, "USA", 'F', "6-3", 211, 3, '1996-04-27', 23),
("Blackhawks", "Brandon Saad", 20, "USA", 'F', "6-1", 206, 8, '1992-10-27', 27),
("Blackhawks", "Brent Seabrook", 7, "CAN", 'D', "6-3", 220, 14, '1985-04-20', 34),
("Blackhawks", "Andrew Shaw", 65, "CAN", 'F', "5-11", 182, 8, '1991-07-20', 28),
("Blackhawks", "Zack Smith", 15, "CAN", 'F', "6-2", 208, 11, '1988-04-05', 31),
("Blackhawks", "Dylan Strome", 17, "CAN", 'F', "6-3", 200, 3, '1997-03-07', 22),
("Blackhawks", "Jonathan Toews", 19, "CAN", 'F', "6-2", 201, 12, '1988-04-29', 31);
--------------------------------------------------QUERIES-------------------------------------------------
SELECT * FROM nhl.players;
SELECT * FROM nhl.game;
SELECT * FROM nhl.game
WHERE Day>=today AND Time>now AND Opponent = "Bruins"
LIMIT 1;
SELECT * FROM nhl.game
WHERE Day>=today AND Time>now AND Opponent = "Blackhawks"
LIMIT 1;