SQL statements for querying game databases in OCGDB (SQLite)

How to query openings and databases based on SQLite, using SQL statements and PQL (Position Query Language)

Moderators: AlexChess, TedSummers

Post Reply
pham
Site Admin
Posts: 128
Joined: Sun Oct 03, 2021 3:08 pm

SQL statements for querying game databases in OCGDB (SQLite)

Post by pham »

1) Query full games
OCGDB databases divide data into 4 main tables, linked by ID:
- Players: player info such as names, Elo
- Events: event info such as names, Elo
- Sites: site info such as names, Elo
- Games: all games info such as results, lengths, FENs, Moves

To query full games we need to join the above tables:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID
2. Query games of a player

Similar to query full games (1), we add a condition about the player:

Code: Select all

WHERE White = 'Carlsen, Magnus' OR Black = 'Carlsen, Magnus'
The statement becomes:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID
WHERE White = 'Carlsen, Magnus' OR Black = 'Carlsen, Magnus'

3. Query games of a player with LIKE

Sometimes the data is not consistent, a player name can be stored in different strings such as 'Carlsen, Magnus', 'Carlsen, M.', 'Magnus, Carlsen'. Or sometimes we want to find out all players with the family name 'Smith'. We can use statement LIKE:

Code: Select all

SELECT g.ID GID, e.Name Event, s.Name Site, w.Name White, b.Name Black, g.*
FROM Games g
INNER JOIN Events e ON EventID = e.ID
INNER JOIN Sites s ON SiteID = s.ID
INNER JOIN Players w ON WhiteID = w.ID
INNER JOIN Players b ON BlackID = b.ID
WHERE White LIKE '%Carlsen%'
Post Reply