Common football.db SQLite Queries
Example queries to get started with the football.db database.
- List of German Bundesliga Seasons
- List of English Teams
- List World Cup Brazil 2014 Games
- List World Cup Brazil 2014 Teams
- List World Cup Brazil 2014 Rounds
Using SQL View Queries
Football Club Leagues n Clubs Examples
List German Bundesliga Events (Seasons)
SELECT *
FROM events_list
WHERE league_key = 'de';
List English Teams
SELECT *
FROM teams_list
WHERE country_key = 'en';
World Cup Brazil 2014 Examples
Note: The event key for the world cup in Brazil 2014 is world.2014
List World Cup 2014 Games
SELECT *
FROM events_games_list
WHERE event_key = 'world.2014';
List World Cup Brazil 2014 Teams
SELECT *
FROM events_teams_list
WHERE event_key = 'world.2014'
List World Cup Brazil 2014 Rounds
SELECT *
FROM event_rounds_list
WHERE event_key = 'world.2014'
List World Cup Brazil 2014 Groups
SELECT *
FROM event_groups_list
WHERE event_key = 'world.2014'
Using SQL Table Queries
Football Club Leagues n Clubs Examples
List German Bundesliga Seasons
SELECT l.title,
s.*,
e.*
FROM seasons s
INNER JOIN events e ON e.season_id = s.id
INNER JOIN leagues l ON l.id = e.league_id
WHERE l.key = 'de';
List English Teams
SELECT c.name,
t.*
FROM teams t
INNER JOIN countries c ON c.id = t.country_id
WHERE c.key = 'en';
World Cup Brazil 2014 Examples
Note: The event key for the world cup in Brazil 2014 is world.2014
List World Cup 2014 Games
SELECT t1.title,
t2.title,
g.*
FROM games g
INNER JOIN teams t1 ON t1.id = g.team1_id
INNER JOIN teams t2 ON t2.id = g.team2_id
INNER JOIN rounds r ON r.id = g.round_id
INNER JOIN events e ON e.id = r.event_id
WHERE e.key = 'world.2014'
List World Cup Brazil 2014 Teams
SELECT t.*
FROM teams t
INNER JOIN events_teams et ON et.team_id = t.id
INNER JOIN events e ON e.id = et.event_id
WHERE e.key = 'world.2014'
List World Cup Brazil 2014 Rounds
SELECT *
FROM rounds r
INNER JOIN events e ON e.id = r.event_id
WHERE e.key = 'world.2014'
Edit Me! The football.db documentation site is a public GitHub repo. Please help by forking the repo and adding to it.