A totally unplanned blog but with the serious events going on in the world this week where I wish nothing but love and peace to all those who are suffering; I woke up today on my day-off wanting to do something productive yet fun. It has been a few weeks since I have last placed a fun sports bet so I figured why not apply my developed SQL skills by creating a football database and then querying it to provide some possible betting selections! For those who have no idea what SQL is; SQL – structured query language is a database program where a script can be coded in a programming language to retrieve data from various sources/tables efficiently.
Creating The Database
Using www.football-data.co.uk, I downloaded in Excel format the football results for 13 separate football divisions for this current 2021-22 season which are as follows …
| B1 | Belgium |
| E0 | England Premier League |
| E1 | England Championship |
| E2 | England League One |
| E3 | England League Two |
| F1 | France Ligue 1 |
| G1 | Germany Bundesliga |
| I1 | Italy Serie A |
| N1 | Netherlands Eredivisie |
| P1 | Portugal Primera Liga |
| SC0 | Scotland Premiership |
| SP1 | Spain La Liga |
| T1 | Turkey Super Lig |
With each separate result sheet, I combined all results into a singular Excel sheet which I shall name as the ‘MasterSheet’ which can be exported into SQL. First though I must create a table and specify the columns I want along with an appropriate data type.
Using the SQL import wizard and selecting the Excel option, I was able to import the Excel mastersheet as my columns in the mastersheet matches the above screenshot.
With regards to choice of character types for the columns, an automatic primary key creates an unique record for each match result as they get fed into the database. Tinyint was used for majority of the stats columns as football stats will never reach a figure as high as 256. nvarchar for team names because the format of some teams like Oud-Heverlee Leuven enables the team to be imported with the “-” character. Ideally it is best practice to try use the character types which stores the least amount of bytes to enhance performance which I believe I have done so.
Querying the Database
Now is time to have some fun. I want to retrieve a selection of teams which are capable of winning without conceding so I can opt for the win to nil bet. I much rather bet on a highly prolific team like Man City and take the extra juice on them to not concede also. For example Man City away to Everton is odds of 1.3 where as Man City to win to nil against Everton is 1.91. That will make a huge difference to my sure to win accumulator (one can hope hehe).
The main advantage of using SQL and not say just Excel alone is that with a single script I can pool in all the potential teams across the various leagues into one table of results. Now this can be done in Excel with the use of IF statements and other advanced formulae, but so much time is saved using SQL!
All stats provided here are averages to 3 decimal places. Ideally filtered for teams with great defensive records whilst showing capability of scoring more than 1 and a half goals on average which is a very good recipe for a team to succeed.
The Qualifying Bets
After taking a look at the football fixtures for this weekend and matching the teams from both tables with the fact they are indeed playing at home or away, I have derived to the following eleven fixtures which are as follows. I will be placing an accumulator style bet as I hate betting singularly on odds less than evens but when combined multiply for fun why not?
| Time | Bet | |
| 1 | Sat 16.00 | Nice to win Away at Strasbourg |
| 2 | Sat 17.30 | Man City to win Away at Everton |
| 3 | Sat 20.00 | PSG to win at Home to St Etienne |
| 4 | Sun 11.15 | Feyenoord to win Away at Alkmaar |
| 5 | Sun 15.00 | Rangers to win at Home to Motherwell |
| 6 | Sun 15.15 | Sevilla to win at Home to Betis |
| 7 | Sun 15.45 | Ajax to win Away at Go Ahead Eagles |
| 8 | Sun 17.30 | Panathinaikos to win at Home to AEK |
| 9 | Sun 19.45 | Napoli to win Away at Lazio |
| 10 | Sun 20.30 | FC Porto to win at Home to Gil Vicente |
| 11 | Mon 20.15 | Braga to win at Home to Santa Clara |
Bet Receipts
A big thanks to PaddyPower for accepting my winning bet as they are the chosen bookmaker who will be paying me just shy of £133k when all eleven of these teams win to nil! Links to screenshots of the bets are below, where I have wagered 8folds up to the accumulator.
All seriousness though, this is purely a fun accumulator style bet for £6 which I am more than comfortable to lose and is purely for my own entertainment so please do gamble responsibly if you are betting.
I hope you enjoyed reading this blog post and can see the benefits of using a querying language like SQL to retrieve data so fast. Imagine having to filter every single country results manually and calculating averages etc. In the space of barely a minute, I have been able to identify a list of teams which meet my exact criteria.
I will report back with a concluding post with an update to the results … hopefully not eleven losers! I will also provide an update to my GitHub with the SQL code used in the near future. Anyhow I should lick a six figure win; no doubt a pic of myself in the style of the great scarface Tony Montana will be uploaded… just without the cocaine! Until my next post, take care and see you soon
