data.sql

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