# A Practical Introduction to Querying Data with PostgreSQL: Using the 2026 World Cup Dataset Querying implies fetching data from the database. Common keywords include `SELECT`, `FROM`, `WHERE`, etc. The `FROM` keyword specifies the table we want to query, while `WHERE` is used to filter the rows returned. The `SELECT` keyword specifies the columns, expressions, or values we want to retrieve. For this article, we will use a [Kaggle dataset of the 2026 FIFA World Cup](https://www.kaggle.com/datasets/mominullptr/fifa-world-cup-2026-dataset/). Let's say we want to get all the game stages in the World Cup; we would write the query as below. ```sql SELECT * FROM tournament_stages; ``` This would result in: ```shell stage_id | stage_name | is_knockout ----------+-------------------+------------- 1 | Group Stage | f 2 | Round of 32 | t 3 | Round of 16 | t 4 | Quarter-finals | t 5 | Semi-finals | t 6 | Third-place match | t 7 | Final | t (7 rows) ``` Here, we are querying the `tournament_stages` table to get the information we want. The `*` after the `SELECT` keyword is a wildcard for selecting all columns from the specified table. ## The WHERE Keyword The `WHERE` keyword is used for filtering in SQL based on one or more conditions. ```sql SELECT COUNT(*) FROM match_team_stats WHERE player_of_the_match = 'Lionel Andrés Messi'; ``` ```shell count ------- 6 (1 row) ``` In the above query, we counted the rows in `match_team_stats` where Messi was listed as the player of the match during the 2026 FIFA World Cup using the `COUNT()` function, which is an aggregate function used to count rows or non-NULL values in a column. In the example above, `COUNT(*)` counts the number of rows that matched the specified condition. Take note that single quotes were used for the strings. In PostgreSQL, double quotes are used for identifiers (database names, table names, column names), while single quotes are used for string literals (text values, dates, timestamps). If we had used double quotes, we would have gotten the error below: ```shell ERROR: column "Lionel Andrés Messi" does not exist LINE 3: WHERE player_of_the_match = "Lionel Andrés Messi" ^ ``` ### Filtering with Multiple Conditions We could also filter using multiple conditions with `AND` and `OR` keywords to join multiple conditions together. We can also use the `BETWEEN` keyword to filter within a range. Let's imagine we want to check the number of times either Messi or Mbappe were the player of the match; we would make use of the `OR` keyword. ```sql SELECT COUNT(*) FROM match_team_stats WHERE player_of_the_match = 'Lionel Andrés Messi' OR player_of_the_match = 'Kylian Mbappe'; ``` ```shell count ------- 9 (1 row) ``` We can see that the previous answer changed from 6 to 9, meaning Mbappe was named player of the match 3 times. We could also count the number of matches played in June using the `BETWEEN` keyword. ```sql SELECT COUNT(*) FROM matches WHERE date BETWEEN '2026-06-01' AND '2026-06-30'; ``` ```shell count ------- 77 (1 row) ``` This tells us that 77 matches were played in the 2026 World Cup for the month of June. Note that the `BETWEEN` keyword includes both the starting and ending values. ## Aliasing You would notice that the column name for our result in our previous examples is all the same (count) because we are using the same function. If we were to call the `COUNT()` function multiple times in a query, we would have multiple columns named count. To get a more descriptive column name, we would give it an alias using the `AS` keyword. ```sql SELECT MAX(home_score + away_score) AS highest_game_score, MIN(home_score + away_score) AS lowest_game_score FROM matches; ``` ```shell highest_game_score | lowest_game_score --------------------+------------------- 10 | 0 (1 row) ``` In the above example, we introduce two aggregate functions, `MAX` and `MIN`, which are used for finding the maximum and minimum values in columns, respectively. Notice that the output columns are now more descriptive due to aliasing. ### LIKE, ILIKE, IN `LIKE`, `ILIKE` are keywords utilised in filtering text. `LIKE` and `ILIKE` are similar except that `LIKE` is case-sensitive, while `ILIKE` is case-insensitive and would capture results irrespective of the case. Also, `ILIKE` is specific to PostgreSQL and not part of the ANSI SQL standard. `IN` is used in filtering queries based on a list of items. Let's say we want to see all teams from the CONMEBOL and CONCACAF confederations; we could use the `IN` keyword to filter the result as shown below. ```sql SELECT team_name, fifa_code, confederation FROM teams WHERE confederation IN ('CONMEBOL', 'CONCACAF'); ``` ```shell team_name | fifa_code | confederation -----------+-----------+--------------- Mexico | MEX | CONCACAF Canada | CAN | CONCACAF Brazil | BRA | CONMEBOL Haiti | HAI | CONCACAF USA | USA | CONCACAF Paraguay | PAR | CONMEBOL Curaçao | CUW | CONCACAF Ecuador | ECU | CONMEBOL Uruguay | URU | CONMEBOL Argentina | ARG | CONMEBOL Colombia | COL | CONMEBOL Panama | PAN | CONCACAF (12 rows) ``` Note that we could also use multiple conditions with the `WHERE` keyword to achieve the same result. ```sql SELECT team_name, fifa_code, confederation FROM teams WHERE confederation = 'CONMEBOL' OR confederation = 'CONCACAF'; ``` However, we can see that utilising the `IN` keyword makes it more concise and straightforward. For the `LIKE` and `ILIKE` keywords, we make use of wildcards (`%` and `_`) when filtering. The `%` is used to match zero or more characters, while `_` is used to match exactly one character. The dataset also contains the football clubs of individual players in the World Cup; let's say we want to get all clubs that start with FC; we would do the following. ```sql SELECT DISTINCT club_team FROM squads_and_players WHERE club_team ILIKE 'FC%'; ``` ```shell club_team -------------------------- FC FCSB FC Krasnodar FC Lokomotiv Moscow FC Lorient FC Sochaux-Montbéliard FC Tokyo -- snip -- (47 rows) ``` The above gave us clubs of players in the 2026 World Cup that start with FC. Notice that we introduced another keyword, `DISTINCT`. The `DISTINCT` keyword ensures there are no duplicates, as several players would be in the same football club. If we then want to get all clubs that end with FC, we would change the conditions as shown below. ```sql SELECT DISTINCT club_team FROM squads_and_players WHERE club_team ILIKE '%FC'; ``` ```shell club_team --------------------------------- Fluminense FC WS Wanderers FC Maccabi Haifa FC Birmingham City FC Al Qadsiah FC Paris FC -- snip -- (136 rows) ``` Let's say we want both results; we would change the script as shown below. ```sql SELECT DISTINCT club_team FROM squads_and_players WHERE club_team ILIKE '%FC%'; ``` ```shell club_team --------------------------------- Fluminense FC WS Wanderers FC FC FCSB Maccabi Haifa FC Birmingham City FC Al Qadsiah FC -- snip -- (189 rows) ``` Note that this would also show clubs that have the word 'FC' in the middle, such as "1. FC Union Berlin", "AFC Ajax", etc. Imagine I want all players that have the word "onald" with a single preceding character in their name, such as "Donald" or "Ronald". We can do this as shown below: ```sql SELECT player_name FROM squads_and_players WHERE player_name ILIKE '%_onald%'; ``` ```shell player_name --------------------------- Breel Donald Embolo Ronald Federico Araujo Ronaldo Cristiano Ronaldo (3 rows) ``` The `_` was used to match a single character in the example above. We could use more than one, depending on the kind of result we want. Notice, I did not use the `LIKE` keyword. It would give us the same result, as this data has been cleaned and made consistent. However, if we were to change the "FC" to "fc" in the queries above, such as `WHERE club_team ILIKE '%fc%';`, `ILIKE` would still give us the same result, while `LIKE` won't give us any output; that is, 0 rows. ## The LIMIT Keyword This is used to get an overview of a table instead of having to view the entire content of the table. We use the `LIMIT` keyword to determine the number of rows that we want to be displayed. Note that the `LIMIT` keyword is not an ANSI SQL standard keyword. To get just five rows from the preceding example, we will add the last line. ```sql SELECT DISTINCT club_team FROM squads_and_players WHERE club_team ILIKE '%FC%' LIMIT 5; ``` ```shell club_team -------------------- Fluminense FC WS Wanderers FC FC FCSB Maccabi Haifa FC Birmingham City FC (5 rows) ``` This gave us five rows instead of 189. Note that the `LIMIT` keyword is usually used with the `ORDER BY` clause to get a consistent result. The `ORDER BY` is used for sorting and will be discussed later in this article. ## Grouping What if we want aggregate data based on categories? We could group them using the `GROUP BY` clause. For example, we may want to count the number of stadiums per country used in the 2026 World Cup. We would do so using the query below. ```sql SELECT country, count(*) FROM venues GROUP BY country; ``` ```shell country | count ---------+------- USA | 11 CAN | 2 MEX | 3 (3 rows) ``` We can also group by multiple fields. ```sql SELECT club_team, position, COUNT(*) FROM squads_and_players GROUP BY club_team, position LIMIT 5; ``` ```shell club_team | position | count ---------------------+----------+------- Fulham FC | MID | 2 Başakşehir FK | MID | 2 Viking Stavanger | DEF | 1 Terengganu FC | FWD | 1 Coventry City FC | FWD | 2 (5 rows) ``` This gives the clubs of players, positions, and the count of positions represented in the World Cup. Currently, it is unordered; we could, however, choose to order it by one or more columns, which we will be doing in the next subheading. Note that the `GROUP BY` clause requires that we group by every non-aggregate we have with the `SELECT` keyword. For instance, if we group only by `club_team` in the previous example, we get an error indicating `position` must also be in the `GROUP BY` clause. ```sql SELECT club_team, position, COUNT(*) FROM squads_and_players GROUP BY club_team LIMIT 5; ``` ```shell ERROR: column "squads_and_players.position" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT club_team, position, COUNT(*) ``` This can be an issue as we may not want to group by all columns in the `SELECT` keyword, and sometimes the number of columns may be large. To address this, we could use subqueries, window functions, and other complex queries. ## Sorting We use the `ORDER BY` clause to sort queries in ascending (`ASC`) or descending (`DESC`) order. By default, `ORDER BY` sorts in ascending order, and we can sort using multiple fields. Ordering the previous example, our PostgreSQL code becomes: ```sql SELECT club_team, position, COUNT(*) AS player_count FROM squads_and_players GROUP BY club_team, position ORDER BY club_team LIMIT 5; ``` ```shell club_team | position | player_count --------------------+----------+-------------- 1. FC Union Berlin | MID | 1 1. FSV Mainz 05 | MID | 3 1. FSV Mainz 05 | DEF | 3 Aarhus GF | MID | 1 Abha Club | DEF | 1 (5 rows) ``` This makes it easy to see that there were three midfielders and three defenders in the World Cup who play for 1. FSV Mainz 05. Note that it ordered the result based on the `club_team` in ascending order. We can also order it based on the `player_count` starting from the highest using the `DESC` keyword. ```sql SELECT club_team, position, COUNT(*) AS player_count FROM squads_and_players GROUP BY club_team, position ORDER BY player_count DESC LIMIT 5; ``` ```shell club_team | position | player_count ---------------------+----------+-------------- Manchester City FC | DEF | 8 FC Bayern München | DEF | 7 Manchester City FC | MID | 7 Chelsea FC | DEF | 6 Paris Saint-Germain | MID | 6 (5 rows) ``` ## Teaser Thank you for reading. Coming up next, we will be discussing the difference between the `WHERE` clause and the `HAVING` clause and their use cases.