Premier League Database Table

SQL

Designed and implemented a database system to manage over 20,000 data sets from Kaggle, covering every match in the Premier League era 1992-Present. (English Soccer League)

I applied a datatable from kaggle that was instructed to include all the matches from the Premier League up to the current data. I addressed the challenge of counting wins, losses, and draws by creating a custom function to interpret the FTR (Full Time Result) column, which only indicated H (Home win), D (Draw), and A (Away win).

By making:

  • Home win = 1 win for home and 1 loss for away
  • Draw = 1 draw for both teams
  • Away win = loss for home team and 1 win for away team

The database counts all of these wins, draws, and scores and organizes the data into a table to represent the teams with the best home records and best away recrods, as well as overall most wins.

Created a stored procedure named UpdateTeamStats to synchronize the GoalsScored and GoalsConceded columns across the HomeTeam and AwayTeam tables, ensuring data integrity and accuracy.

Implemented a database trigger named UpdateTeamStatsTrigger that automatically executes the UpdateTeamStats procedure after any insert or update operation on the relevant tables, enhancing data management.

Database Image 1 Database Image 2 Database Image 3