SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
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; CREATE FUNCTION update_points(winner text, loser text, ot bool) RETURNS void AS $$ BEGIN UPDATE NHL_database.Team SET points = points + 2 WHERE team_name = winner; IF ot IS TRUE THEN UPDATE NHL_database.Team SET points = points + 1 WHERE team_name = loser; END IF; 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 Shesterkin', 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', 'atlantic', 'eastern', 69, 9042, 19153, 11), ('Tampa Bay Lightning', 'atlantic', 'eastern', 106, 16234, 19758, 3), ('Toronto Maple Leafs', 'atlantic', '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), (4, 7, 'CONTRACT', 4, '2021-01-01', '2025-01-01', 44), (6, 6, 'CONTRACT', 4, '2021-01-01', '2025-01-01', 4444), (2, 1, 'CONTRACT', 5, '2021-01-01', '2026-01-01', 4444); 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 player_name, team_name FROM (SELECT player_name, team_id FROM NHL_database.Player p INNER JOIN NHL_database.Contracts c ON contract_id = cur_contract_id) tmp INNER JOIN NHL_database.Team ON tmp.team_id = NHL_database.Team.team_id; --Сумма зарплат игроков для каждого клуба SELECT team_name, sum(salary) salary FROM (SELECT salary, team_id FROM NHL_database.Player p INNER JOIN NHL_database.Contracts c ON contract_id = cur_contract_id) tmp INNER JOIN NHL_database.Team ON tmp.team_id = NHL_database.Team.team_id GROUP BY team_name; --Лидеры дивизионов SELECT t.team_name, t.division, t.points FROM NHL_database.Team t INNER JOIN (SELECT division, max(points) points FROM NHL_database.Team GROUP BY division) g ON t.points = g.points; --Дивизионы, в которых команды продают в среднем больше 12000 билетов SELECT division, avg(tickets_sold_avg) a FROM NHL_database.Team GROUP BY division HAVING avg(tickets_sold_avg) > 12000; --Количесвто игроков из каждой страны SELECT country, count(*) FROM NHL_database.Player GROUP BY country; --Таблица команд в атлантическом дивизионе SELECT team_name, points FROM NHL_database.Team WHERE division='atlantic' ORDER BY points DESC; SELECT * FROM NHL_database.Team; SELECT update_points('Toronto Maple Leafs', 'Ottawa Senators', false); SELECT * FROM NHL_database.Team; CREATE VIEW player_contracts AS SELECT NHL_database.Player.player_id, player_name, contract_type, contract_lenght, contract_from, contract_to, salary FROM NHL_database.Player LEFT OUTER JOIN NHL_database.Contracts ON NHL_database.Player.cur_contract_id = NHL_database.Contracts.contract_id; CREATE VIEW player_avg_contracts AS SELECT NHL_database.Player.player_id, player_name, sal FROM NHL_database.Player LEFT OUTER JOIN (SELECT player_id, avg(salary) sal FROM NHL_database.Contracts GROUP BY player_id) tmp ON NHL_database.Player.player_id = tmp.player_id; SELECT * FROM player_avg_contracts; SELECT * FROM player_contracts; -------------------------------------------------------------------- DROP VIEW player_avg_contracts; DROP VIEW player_contracts; 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;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear