Skip to content

Instantly share code, notes, and snippets.

@birjj
Created March 9, 2018 09:17
Show Gist options
  • Save birjj/1d66438b5615212abc0ef33d7c314cf7 to your computer and use it in GitHub Desktop.
Save birjj/1d66438b5615212abc0ef33d7c314cf7 to your computer and use it in GitHub Desktop.
INSERT INTO Tournaments (name, prize, date) VALUES
('Intel Extreme Masters XII - World Championship', 250000, '2018-02-27'),
('StarLadder & i-League StarSeries Season 4', 130000, '2018-02-17'),
('ELEAGUE Major: Boston 2018', 500000, '2018-01-12'),
('Esports Championship Series Season 4 - Finals', 250000, '2017-12-15'),
('ESL Pro League Season 6 - Finals', 225000, '2017-12-05');
INSERT INTO Teams (name, country) VALUES
('Astralis', 'DK'),
('mousesports', 'EU'),
('Cloud9', 'US'),
('FaZe Clan', 'EU'),
('SK Gaming', 'BR');
INSERT INTO People (name, email, nickname) VALUES
('Nicolai Reedtz', 'dev1ce@astralis.dk', 'dev1ce'),
('Peter Rothmann', 'dupreeh@astralis.dk', 'dupreeh'),
('Andreas Højsleth', 'xyp9x@astralis.dk', 'Xyp9x'),
('Lukas Rossander', 'gla1ve@astralis.dk', 'gla1ve'),
('Emil Reif', 'magisk@astralis.dk', 'Magisk'),
('Danny Sørensen', 'zonic@astralis.dk', 'zonic'),
('Chris de Jong', 'chrisj@mousesports.com', 'chrisj'),
('Tomáš Šťastný', 'oskar@mousesports.com', 'oskar'),
('Robin Kool', 'ropz@mousesports.com', 'ropz'),
('Miikka Kemppi', 'sunny@mousesports.com', 'suNny'),
('Martin Styk', 'styko@mousesports.com', 'STYKO'),
('Sergey Bezhanov', 'lmbt@mousesports.com', 'lmbt'),
('Tyler Latham', 'skadoodle@cloud9.com', 'Skadoodle'),
('Jacky Yip', 'stewie2k@cloud9.com', 'Stewie2K'),
('Timothy Ta', 'automatic@cloud9.com', 'automatic'),
('Will Wierzba', 'rush@cloud9.com', 'RUSH'),
('Taric Celik', 'tarik@cloud9.com', 'tarik'),
('Soham Chowdhury', 'valens@cloud9.com', 'valens'),
('Håvard Nygaard', 'rain@faze.com', 'rain'),
('Finn Andersen', 'karrigan@faze.com', 'karrigan'),
('Nikola Kovač', 'niko@faze.com', 'Niko'),
('Ladislav Kovács', 'guardian@faze.com', 'GuardiaN'),
('Olof Kajbjer', 'olofmeister@faze.com', 'olofmeister'),
('Robert Dahlström', 'robban@faze.com', 'RobbaN'),
('Gabriel Toledo', 'fallen@sk.com', 'FalleN'),
('Fernando Alvarenga', 'fer@sk.com', 'fer'),
('Marcelo David', 'coldzera@sk.com', 'coldzera'),
('Epitácio de Melo', 'taco@sk.com', 'TACO'),
('Ricardo Prass', 'boltz@sk.com', 'boltz');
-- RELATIONS
-- PlaysOn
INSERT INTO PlaysOn (people_id, team_id)
SELECT people.id, teams.id AS tid FROM people, teams
WHERE teams.name = 'Astralis'
AND people.email LIKE '%@astralis.dk'
AND people.nickname != 'zonic';
INSERT INTO PlaysOn (people_id, team_id)
SELECT people.id, teams.id AS tid FROM people, teams
WHERE teams.name = 'mousesports'
AND people.email LIKE '%@mousesports.com'
AND people.nickname != 'lmbt';
INSERT INTO PlaysOn (people_id, team_id)
SELECT people.id, teams.id AS tid FROM people, teams
WHERE teams.name = 'Cloud9'
AND people.email LIKE '%@cloud9.com'
AND people.nickname != 'valens';
INSERT INTO PlaysOn (people_id, team_id)
SELECT people.id, teams.id AS tid FROM people, teams
WHERE teams.name = 'FaZe Clan'
AND people.email LIKE '%@faze.com'
AND people.nickname != 'RobbaN';
INSERT INTO PlaysOn (people_id, team_id)
SELECT people.id, teams.id AS tid FROM people, teams
WHERE teams.name = 'SK Gaming'
AND people.email LIKE '%@sk.com';
-- Coaches
INSERT INTO Coaches
SELECT people.id, teams.id AS team_id FROM people, teams
WHERE (people.nickname = 'zonic' AND teams.name = 'Astralis')
OR (people.nickname = 'lmbt' AND teams.name = 'mousesports')
OR (people.nickname = 'valens' AND teams.name = 'Cloud9')
OR (people.nickname = 'RobbaN' AND teams.name = 'FaZe Clan');
-- ParticipatesIn
INSERT INTO ParticipatesIn (team_id, tournament_id)
SELECT teams.id, tournaments.id AS tid FROM teams, tournaments
WHERE teams.name = 'Astralis';
INSERT INTO ParticipatesIn (team_id, tournament_id)
SELECT teams.id, tournaments.id AS tid FROM teams, tournaments
WHERE teams.name = 'mousesports'
AND (
tournaments.name != 'Intel Extreme Masters XII - World Championship'
AND
tournaments.name != 'ESL Pro League Season 6 - Finals'
);
INSERT INTO ParticipatesIn (team_id, tournament_id)
SELECT teams.id, tournaments.id AS tid FROM teams, tournaments
WHERE teams.name = 'Cloud9'
AND tournaments.name != 'ESL Pro League Season 6 - Finals';
INSERT INTO ParticipatesIn (team_id, tournament_id)
SELECT teams.id, tournaments.id AS tid FROM teams, tournaments
WHERE teams.name = 'FaZe Clan';
INSERT INTO ParticipatesIn (team_id, tournament_id)
SELECT teams.id, tournaments.id AS tid FROM teams, tournaments
WHERE teams.name = 'SK Gaming'
AND tournaments.name != 'Esports Championship Series Season 4 - Finals';
-- Winners
INSERT INTO Winners (team_id, tournament_id)
SELECT teams.id, tournaments.id as tid FROM teams, tournaments
WHERE tournaments.name = 'StarLadder & i-League StarSeries Season 4'
AND teams.name = 'mousesports';
INSERT INTO Winners (team_id, tournament_id)
SELECT teams.id, tournaments.id as tid FROM teams, tournaments
WHERE tournaments.name = 'ELEAGUE Major: Boston 2018'
AND teams.name = 'Cloud9';
INSERT INTO Winners (team_id, tournament_id)
SELECT teams.id, tournaments.id as tid FROM teams, tournaments
WHERE tournaments.name = 'Esports Championship Series Season 4 - Finals'
AND teams.name = 'FaZe Clan';
INSERT INTO Winners (team_id, tournament_id)
SELECT teams.id, tournaments.id as tid FROM teams, tournaments
WHERE tournaments.name = 'ESL Pro League Season 6 - Finals'
AND teams.name = 'SK Gaming';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment