SQLize | PHPize | SQLtest

CREATE SCHEMA NHL_database; CREATE TABLE NHL_database.Player( player_id SERIAL PRIMARY KEY, player_name VARCHAR(255), height INTEGER CHECK (height > 0), weight INTEGER CHECK (weight > 0), birthday DATE, country VARCHAR(255), cur_contract_id INTEGER, pos VARCHAR(255), goals_scored INTEGER, assists INTEGER, percent_saved FLOAT, plus_minus INTEGER ); CREATE TABLE NHL_database.Team( team_id SERIAL PRIMARY KEY, team_name VARCHAR(255), division VARCHAR(255), conference VARCHAR(255), points INTEGER, tickets_sold_avg FLOAT, stadium_capacity INTEGER, trophies_won INTEGER ); CREATE TABLE NHL_database.Contracts( contract_id SERIAL PRIMARY KEY, player_id INTEGER, team_id INTEGER, contract_type VARCHAR(255), contract_lenght INTEGER, contract_from DATE, contract_to DATE, salary INTEGER ); CREATE TABLE NHL_database.TeamContracts( team_id INTEGER, contract_id INTEGER ); CREATE TABLE NHL_database.Injury( player_id INTEGER, injury_from DATE, injury_to DATE, injury_type VARCHAR(255) ); CREATE TABLE NHL_database.NationalTeam( country VARCHAR(255), world_cup_won INTEGER, olympics_won INTEGER ); INSERT INTO NHL_database.NationalTeam(country, world_cup_won, olympics_won) VALUES ('Russia', 5, 1), ('USA', 2, 2), ('Canada', 27, 9); ALTER TABLE NHL_database.NationalTeam ADD CONSTRAINT c_id PRIMARY KEY (country); ALTER TABLE NHL_database.Player ADD FOREIGN KEY (country) REFERENCES NHL_database.NationalTeam(country) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.Player ADD FOREIGN KEY (cur_contract_id) REFERENCES NHL_database.Contracts(contract_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.Contracts ADD FOREIGN KEY (player_id) REFERENCES NHL_database.Player(player_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.Contracts ADD FOREIGN KEY (team_id) REFERENCES NHL_database.Team(team_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.TeamContracts ADD FOREIGN KEY (team_id) REFERENCES NHL_database.Team(team_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.TeamContracts ADD FOREIGN KEY (contract_id) REFERENCES NHL_database.Contracts(contract_id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE NHL_database.Injury ADD FOREIGN KEY (player_id) REFERENCES NHL_database.Player(player_id) DEFERRABLE INITIALLY DEFERRED; ------------------------------------------------------------------- CREATE FUNCTION func() RETURNS TRIGGER AS $$ BEGIN IF (NOW() > (SELECT contract_from FROM NHL_database.Contracts WHERE contract_id = NEW.contract_id) AND NOW() < (SELECT contract_to FROM NHL_database.Contracts WHERE contract_id = NEW.contract_id)) THEN UPDATE NHL_database.Player SET cur_contract_id = NEW.contract_id WHERE player_id = (SELECT player_id FROM NHL_database.Contracts WHERE contract_id = NEW.contract_id); INSERT INTO NHL_database.TeamContracts(team_id, contract_id) VALUES (NEW.team_id, NEW.contract_id); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER upd_contract AFTER INSERT ON NHL_database.Contracts FOR EACH ROW EXECUTE FUNCTION func(); CREATE FUNCTION get_id_from_name(s text) RETURNS INTEGER AS $$ BEGIN RETURN (SELECT player_id FROM NHL_database.Player WHERE player_name = s); END; $$ LANGUAGE plpgsql; -------------------------------------------------------------------- INSERT INTO NHL_database.Player(player_name, height, weight, birthday, pos, goals_scored, assists, percent_saved, plus_minus, country) VALUES ('Alex Ovechkin', 190, 238, '1985-09-17', 'left wing', 50, 40, NULL, 8, 'Russia'), ('Connor McDavid', 186, 193, '1997-01-13', 'center', 43, 75, NULL, 25, 'Canada'), ('Austin Matthews', 192, 220, '1997-09-17', 'center', 58, 46, NULL, 19, 'USA'), ('Kirill Kaprizov', 180, 201, '1997-04-26', 'left wing', 45, 58, NULL, 25, 'Russia'), ('Matthew Tkachuk', 189, 202, '1997-12-11', 'right wing', 40, 61, NULL, 55, 'USA'), ('Steven Stamkos', 186, 193, '1990-02-07', 'center', 37, 60, NULL, 20, 'Canada'), ('Sergei Bobrovsky', 189, 182, '1988-09-20', 'goaltender', NULL, NULL, 0.912, NULL, 'Russia'), ('Igor Sheserkin', 186, 189, '1995-12-30', 'goaltender', NULL, NULL, 0.936, NULL, 'Russia'), ('Andrei Vasilevskiy', 192, 225, '1994-07-25', 'goaltender', NULL, NULL, 0.917, NULL, 'Russia'), ('Thatcher Demko', 195, 192, '1995-12-08', 'goaltender', NULL, NULL, 0.915, NULL, 'USA'); INSERT INTO NHL_database.Team(team_name, division, conference, points, tickets_sold_avg, stadium_capacity, trophies_won) VALUES ('Florida Panthers', 'central', 'eastern', 120, 12000, 19250, 0), ('Arizona Coyotes', 'central', 'western', 51, 10150, 17799, 0), ('Ottawa Senators', 'atlatic', 'eastern', 69, 9042, 19153, 11), ('Tampa Bay Lightning', 'atlatic', 'eastern', 106, 16234, 19758, 3), ('Toronto Maple Leafs', 'atlatic', 'eastern', 111, 16234, 18819, 13), ('Washington Capitals', 'metropolian', 'eastern', 100, 15360, 18506, 1), ('Montreal Canadiens', 'atlantic', 'eastern', 53, 13250, 21301, 24); INSERT INTO NHL_database.Contracts(player_id, team_id, contract_type, contract_lenght, contract_from, contract_to, salary) VALUES (2, 3, 'CONTRACT', 4, '2015-05-29', '2019-05-29', 1), (5, 4, 'CONTRACT', 5, '2020-07-09', '2025-07-09', 1), (1, 6, 'CONTRACT', 2, '2021-10-09', '2023-10-09', 10000000), (10, 3, 'CONTRACT', 4, '2015-05-29', '2019-05-29', 111111), (9, 4, 'CONTRACT', 5, '2011-10-09', '2016-10-09', 1154), (9, 6, 'CONTRACT', 2, '2016-10-09', '2018-10-09', 400), (3, 3, 'CONTRACT', 1, '2015-05-29', '2016-05-29', 35000), (3, 4, 'CONTRACT', 1, '2017-10-09', '2018-10-09', 70000), (3, 6, 'CONTRACT', 2, '2018-10-09', '2020-10-09', 40000); INSERT INTO NHL_database.Injury(player_id, injury_from, injury_to, injury_type) VALUES (2, '2015-05-29', '2015-06-20', 'ankle'), (5, '2010-07-09', '2010-07-29', 'concussion'), (9, '2021-10-09', '2021-10-11', 'elbow'), (10, '2015-05-29', '2015-05-31', 'backpain'), (4, '2011-10-13', '2011-10-22', 'foot'), (4, '2019-11-22', '2019-12-09', 'concussion'), (3, '2015-05-29', '2016-05-29', 'ankle'), (1, '2017-10-09', '2017-10-20', 'concussion'), (8, '2018-03-09', '2018-03-19', 'backpain'); INSERT INTO NHL_database.Contracts(player_id, team_id, contract_type, contract_lenght, contract_from, contract_to, salary) VALUES (get_id_from_name('Thatcher Demko'), 3, 'CONTRACT', 1, '2010-05-29', '2011-05-29', 1); SELECT * FROM NHL_database.Player; SELECT * FROM NHL_database.Team; SELECT * FROM NHL_database.Contracts; SELECT * FROM NHL_database.NationalTeam; -------------------------------------------------------------------- TRUNCATE NHL_database.Player, NHL_database.Team, NHL_database.Contracts, NHL_database.TeamContracts, NHL_database.NationalTeam, NHL_database.Injury; DROP TABLE NHL_database.Player, NHL_database.Team, NHL_database.Contracts, NHL_database.TeamContracts, NHL_database.NationalTeam, NHL_database.Injury; DROP SCHEMA NHL_database;