Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Hockey SQL Tutorial - Postgres 9.6 - http://sqlfiddle.com/#!17/8395d/4/0
-- All players in the Hall of Fame table.
SELECT *
FROM "Hall of Fame";
-- All players in the Hall of Fame table, ordered by points.
SELECT *
FROM "Hall of Fame"
ORDER BY "Points" desc;
-- All players in the Hall of Fame table, with at least 1000 points.
SELECT *
FROM "Hall of Fame"
WHERE "Points" >= 1000
ORDER BY "Points" desc;
-- How many entries are in the SkaterStats table?
SELECT COUNT(*) FROM "SkaterStats1516";
-- Narrow down the fields to some of the basic stats.
SELECT "Name", "Shots", "Goals", "S%"
FROM "SkaterStats1516"
ORDER BY "Shots" desc;
CREATE TABLE public."Hall of Fame"
(
"Player" character varying,
"Games Played" integer,
"Goals" integer,
"Assists" integer,
"Points" integer
);
INSERT INTO public."Hall of Fame"
("Player", "Games Played", "Goals", "Assists", "Points")
VALUES ('Teemu Selanne', 1451, 684, 773, 1457),
('Dave Andreychuk', 1639, 640, 698, 1338),
('Paul Kariya', 989, 402, 587, 989),
('Mark Recchi', 1652, 577, 956, 1533);
CREATE TABLE public."SkaterStats1516"
(
"Name" character varying,
"Games Played" integer,
"Team" character varying(3),
"League" character varying(3),
"Position" character varying(2),
"Season" character varying(7),
"Age" integer,
"Goals" integer,
"Assists" integer,
"Points" integer,
"+/-" integer,
"PIM" integer,
"EV Goals" integer,
"PP Goals" integer,
"SH Goals" integer,
"GW Goals" integer,
"Shots" integer,
"S%" real,
"TOI" integer,
"Goals Per Game" real,
"Assists Per Game" real,
"Points Per Game" real,
"Shots Per Game" real,
"OPS" real,
"DPS" real,
"Point Shares" real
);
insert into public."SkaterStats1516" ("Name","Games Played","Team","League","Position","Season","Age","Goals","Assists","Points","+/-","PIM","EV Goals","PP Goals","SH Goals","GW Goals","Shots","S%","TOI","Goals Per Game","Assists Per Game","Points Per Game","Shots Per Game","OPS","DPS","Point Shares")
values
('Jamie McGinn',84,'TOT','NHL','LW','2015-16',27,22,17,39,-7,33,13,9,0,5,156,14.1,1201,0.26,0.2,0.46,1.86,3.6,1.3,5),
('Eric Staal',83,'TOT','NHL','C','2015-16',31,13,26,39,-3,34,12,1,0,0,199,6.5,1540,0.16,0.31,0.47,2.4,1.6,1.8,3.4),
('Justin Abdelkader',82,'DET','NHL','LW','2015-16',28,19,23,42,-16,120,13,6,0,4,155,12.3,1512,0.23,0.28,0.51,1.89,2.7,1.1,3.8),
('Karl Alzner',82,'WSH','NHL','D','2015-16',27,4,17,21,14,26,4,0,0,1,75,5.3,1753,0.05,0.21,0.26,0.91,0.6,4.7,5.4),
('Mikael Backlund',82,'CGY','NHL','C','2015-16',26,21,26,47,10,28,15,3,3,4,155,13.5,1347,0.26,0.32,0.57,1.89,3.7,1.7,5.5),
('Francois Beauchemin',82,'COL','NHL','D','2015-16',35,8,26,34,-7,38,6,2,0,2,127,6.3,2057,0.1,0.32,0.41,1.55,2.1,3.9,6),
('Jamie Benn',82,'DAL','NHL','LW','2015-16',26,41,48,89,7,64,22,17,2,5,247,16.6,1642,0.5,0.59,1.09,3.01,10.3,1.7,12.1),
('Troy Brouwer',82,'STL','NHL','RW','2015-16',30,18,21,39,2,62,11,7,0,4,142,12.7,1394,0.22,0.26,0.48,1.73,2.5,1.8,4.3),
('Dustin Brown',82,'LAK','NHL','RW','2015-16',31,11,17,28,-5,30,9,2,0,0,218,5,1326,0.13,0.21,0.34,2.66,0.7,1.2,1.9),
('Brent Burns',82,'SJS','NHL','D','2015-16',30,27,48,75,-5,53,19,7,1,4,353,7.6,2121,0.33,0.59,0.91,4.3,8.8,4.1,12.9),
('Brian Campbell',82,'FLA','NHL','D','2015-16',36,6,25,31,31,26,5,0,1,1,99,6.1,1827,0.07,0.3,0.38,1.21,1.9,5.8,7.8),
('Gregory Campbell',82,'CBJ','NHL','C','2015-16',32,3,8,11,-6,78,3,0,0,2,58,5.2,866,0.04,0.1,0.13,0.71,-0.8,0.7,-0.2),
('Jason Chimera',82,'WSH','NHL','LW','2015-16',36,20,20,40,0,22,14,4,2,3,165,12.1,1152,0.24,0.24,0.49,2.01,3.5,1.2,4.7),
('Andrew Cogliano',82,'ANA','NHL','LW','2015-16',28,9,23,32,2,28,7,0,2,3,131,6.9,1183,0.11,0.28,0.39,1.6,1.3,1.7,3),
('Charlie Coyle',82,'MIN','NHL','C','2015-16',23,21,21,42,1,16,19,2,0,4,140,15,1419,0.26,0.26,0.51,1.71,3.2,1.8,4.9),
('Matt Cullen',82,'PIT','NHL','C','2015-16',39,16,16,32,5,20,13,0,3,4,118,13.6,1138,0.2,0.2,0.39,1.44,2.2,1.3,3.6),
('Trevor Daley',82,'TOT','NHL','D','2015-16',32,6,22,28,9,34,5,1,0,0,130,4.6,1512,0.07,0.27,0.34,1.59,1.9,3.7,5.6),
('Drew Doughty',82,'LAK','NHL','D','2015-16',26,14,37,51,24,52,4,9,1,3,197,7.1,2297,0.17,0.45,0.62,2.4,4.5,7.1,11.6),
('Cody Eakin',82,'DAL','NHL','C','2015-16',24,16,19,35,3,42,11,2,3,1,132,12.1,1342,0.2,0.23,0.43,1.61,2.1,1.3,3.4),
('Mattias Ekholm',82,'NSH','NHL','D','2015-16',25,8,27,35,14,44,6,1,1,3,114,7,1660,0.1,0.33,0.43,1.39,2.8,5,7.8),
('Loui Eriksson',82,'BOS','NHL','RW','2015-16',30,30,33,63,13,12,18,10,2,5,184,16.3,1598,0.37,0.4,0.77,2.24,6,2.2,8.2),
('Vernon Fiddler',82,'DAL','NHL','C','2015-16',35,12,10,22,5,31,9,1,2,0,98,12.2,954,0.15,0.12,0.27,1.2,1.2,1.1,2.3),
('Filip Forsberg',82,'NSH','NHL','LW','2015-16',21,33,31,64,1,47,24,8,1,3,247,13.4,1562,0.4,0.38,0.78,3.01,6.7,1.8,8.5),
('Alex Galchenyuk',82,'MTL','NHL','C','2015-16',21,30,26,56,-8,20,21,9,0,4,201,14.9,1333,0.37,0.32,0.68,2.45,5.8,1.2,7),
('Stephen Gionta',82,'NJD','NHL','C','2015-16',32,1,10,11,-13,43,1,0,0,0,62,1.6,1001,0.01,0.12,0.13,0.76,-1.4,1,-0.5),
('Mark Giordano',82,'CGY','NHL','D','2015-16',32,21,35,56,-5,54,11,9,1,2,212,9.9,2032,0.26,0.43,0.68,2.59,5.9,3.2,9.2),
('Alex Goligoski',82,'DAL','NHL','D','2015-16',30,5,32,37,21,34,4,1,0,1,127,3.9,1954,0.06,0.39,0.45,1.55,2.4,4.9,7.2),
('Mikael Granlund',82,'MIN','NHL','C','2015-16',23,13,31,44,-12,20,10,2,1,3,160,8.1,1486,0.16,0.38,0.54,1.95,2.3,1.3,3.6),
('Andy Greene',82,'NJD','NHL','D','2015-16',33,4,9,13,7,26,3,1,0,0,63,6.3,1882,0.05,0.11,0.16,0.77,-0.4,6.1,5.7),
('Taylor Hall',82,'EDM','NHL','LW','2015-16',24,26,39,65,-4,54,22,4,0,6,286,9.1,1575,0.32,0.48,0.79,3.49,5.8,1.8,7.6),
('Dougie Hamilton',82,'CGY','NHL','D','2015-16',22,12,31,43,-14,46,7,5,0,3,190,6.3,1621,0.15,0.38,0.52,2.32,4,1.7,5.7),
('Nick Holden',82,'COL','NHL','D','2015-16',28,6,16,22,-1,24,6,0,0,0,98,6.1,1794,0.07,0.2,0.27,1.2,0.9,3.9,4.7),
('Patric Hornqvist',82,'PIT','NHL','RW','2015-16',29,22,29,51,15,36,13,9,0,3,257,8.6,1382,0.27,0.35,0.62,3.13,4.4,2,6.4),
('Bo Horvat',82,'VAN','NHL','C','2015-16',20,16,24,40,-30,18,12,4,0,4,155,10.3,1405,0.2,0.29,0.49,1.89,2.4,0.5,2.9),
('Jarome Iginla',82,'COL','NHL','RW','2015-16',38,22,25,47,-22,41,9,13,0,3,182,12.1,1301,0.27,0.3,0.57,2.22,4,0.6,4.6),
('Boone Jenner',82,'CBJ','NHL','C','2015-16',22,30,19,49,-15,77,20,9,1,3,225,13.3,1346,0.37,0.23,0.6,2.74,5.2,0.8,6),
('Patrick Kane',82,'CHI','NHL','RW','2015-16',27,46,60,106,17,30,29,17,0,9,287,16,1674,0.56,0.73,1.29,3.5,12.4,2.6,15),
('Erik Karlsson',82,'OTT','NHL','D','2015-16',25,16,66,82,-2,50,15,1,0,3,248,6.5,2375,0.2,0.8,1,3.02,8.4,3.2,11.7),
('Phil Kessel',82,'PIT','NHL','RW','2015-16',28,26,33,59,9,18,22,4,0,5,274,9.5,1507,0.32,0.4,0.72,3.34,5.4,1.9,7.2),
('Mikko Koivu',82,'MIN','NHL','C','2015-16',32,17,39,56,6,40,6,10,1,2,141,12.1,1634,0.21,0.48,0.68,1.72,3.7,2.2,6),
('Evgeny Kuznetsov',82,'WSH','NHL','C','2015-16',23,20,57,77,27,32,15,5,0,4,193,10.4,1428,0.24,0.7,0.94,2.35,6.9,2.6,9.6),
('Adam Larsson',82,'NJD','NHL','D','2015-16',23,3,15,18,15,77,3,0,0,1,65,4.6,1846,0.04,0.18,0.22,0.79,0.1,6.6,6.7),
('Mark Letestu',82,'EDM','NHL','C','2015-16',30,10,15,25,-21,10,5,3,2,0,107,9.3,1294,0.12,0.18,0.3,1.3,0.3,0.7,1),
('Elias Lindholm',82,'CAR','NHL','C','2015-16',21,11,28,39,-23,24,9,2,0,3,176,6.3,1485,0.13,0.34,0.48,2.15,1.5,1.1,2.6),
('Andrei Markov',82,'MTL','NHL','D','2015-16',37,5,39,44,-6,38,1,4,0,0,117,4.3,1955,0.06,0.48,0.54,1.43,3,3.7,6.7),
('Patrick Marleau',82,'SJS','NHL','LW','2015-16',36,25,23,48,-22,10,13,11,1,5,216,11.6,1560,0.3,0.28,0.59,2.63,3.8,0.9,4.7),
('Cody McLeod',82,'COL','NHL','LW','2015-16',31,8,5,13,1,138,7,1,0,1,73,11,864,0.1,0.06,0.16,0.89,-0.1,1.1,1),
('J.T. Miller',82,'NYR','NHL','C','2015-16',22,22,21,43,10,46,20,2,0,5,135,16.3,1233,0.27,0.26,0.52,1.65,4,1.5,5.5),
('Ryan Murray',82,'CBJ','NHL','D','2015-16',22,4,21,25,-10,40,3,1,0,0,90,4.4,1873,0.05,0.26,0.3,1.1,0.9,2.9,3.8);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment