SQL Exercise: Players with their teams booked in the tournament
41. From the following tables, write a SQL query to find out how many times a player had been booked in the tournament. Show the result according to the team and number of times booked in descending order. Return country name, player name, and booked number of times.
Sample table: soccer_country
country_id | country_abbr | country_name
------------+--------------+---------------------
1201 | ALB | Albania
1202 | AUT | Austria
1203 | BEL | Belgium
1204 | CRO | Croatia
1205 | CZE | Czech Republic
1206 | ENG | England
1207 | FRA | France
1208 | GER | Germany
1209 | HUN | Hungary
........
1229 | NOR | Norway
Sample table: player_booked
match_no | team_id | player_id | booking_time | sent_off | play_schedule | play_half
----------+---------+-----------+--------------+----------+---------------+-----------
1 | 1216 | 160349 | 32 | | NT | 1
1 | 1216 | 160355 | 45 | | NT | 1
1 | 1207 | 160159 | 69 | Y | NT | 2
1 | 1216 | 160360 | 78 | | NT | 2
2 | 1221 | 160470 | 14 | | NT | 1
2 | 1201 | 160013 | 23 | | NT | 1
2 | 1201 | 160013 | 36 | | NT | 1
2 | 1201 | 160014 | 63 | | NT | 2
2 | 1221 | 160472 | 66 | | NT | 2
........
51 | 1214 | 160302 | 122 | | ET | 2
Sample table: player_mast
player_id | team_id | jersey_no | player_name | posi_to_play | dt_of_bir | age | playing_club
-----------+---------+-----------+-------------------------+--------------+------------+-----+---------------------
160001 | 1201 | 1 | Etrit Berisha | GK | 1989-03-10 | 27 | Lazio
160008 | 1201 | 2 | Andi Lila | DF | 1986-02-12 | 30 | Giannina
160016 | 1201 | 3 | Ermir Lenjani | MF | 1989-08-05 | 26 | Nantes
160007 | 1201 | 4 | Elseid Hysaj | DF | 1994-02-20 | 22 | Napoli
160013 | 1201 | 5 | Lorik Cana | MF | 1983-07-27 | 32 | Nantes
160010 | 1201 | 6 | Frederic Veseli | DF | 1992-11-20 | 23 | Lugano
160004 | 1201 | 7 | Ansi Agolli | DF | 1982-10-11 | 33 | Qarabag
160012 | 1201 | 8 | Migjen Basha | MF | 1987-01-05 | 29 | Como
160017 | 1201 | 9 | Ledian Memushaj | MF | 1986-12-17 | 29 | Pescara
........
160548 | 1224 | 23 | Simon Church | FD | 1988-12-10 | 27 | MK Dons
Sample Solution:
SQL Code:
-- This query selects the country name, player name, and the count of bookings for each player from the player_booked table, grouped by country and player.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(b.*) Booked -- Counting the number of bookings for each player
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
player_booked b ON a.country_id = b.team_id -- Joining the soccer_country table with the player_booked table based on the team_id
JOIN
player_mast c ON b.player_id = c.player_id -- Joining the player_mast table with the player_booked table based on the player_id
GROUP BY
a.country_name, c.player_name -- Grouping the results by country name and player name
-- Ordering the results by country name in ascending order and by the count of bookings (Booked) in descending order
ORDER BY
a.country_name, Booked DESC;
Sample Output:
country_name | player_name | booked
---------------------+-------------------------+--------
Albania | Lorik Cana | 2
Albania | Burim Kukeli | 2
Albania | Ledian Memushaj | 1
Albania | Mergim Mavraj | 1
Albania | Migjen Basha | 1
Albania | Elseid Hysaj | 1
Albania | Amir Abrashi | 1
Albania | Ergys Kace | 1
Austria | Aleksandar Dragovic | 2
Austria | Marc Janko | 1
Austria | Martin Harnik | 1
Austria | Alessandro Schopf | 1
Austria | Christian Fuchs | 1
Austria | Martin Hinteregger | 1
Belgium | Thomas Vermaelen | 2
Belgium | Marouane Fellaini | 2
Belgium | Michy Batshuayi | 1
Belgium | Thomas Meunier | 1
Belgium | Axel Witsel | 1
Belgium | Toby Alderweireld | 1
Belgium | Jan Vertonghen | 1
Croatia | Milan Badelj | 1
Croatia | Sime Vrsaljko | 1
Croatia | Marko Rog | 1
Croatia | Marcelo Brozovic | 1
Croatia | Ivan PeriSic | 1
Croatia | Ivan Strinic | 1
Croatia | Darijo Srna | 1
Croatia | Domagoj Vida | 1
--------
Turkey | Hakan Balta | 2
Turkey | Cenk Tosun | 1
Turkey | Volkan Sen | 1
Turkey | Burak Yilmaz | 1
Turkey | Ozan Tufan | 1
Turkey | Ismail Koybasi | 1
Ukraine | Ruslan Rotan | 1
Ukraine | Olexandr Kucher | 1
Ukraine | Yevhen Konoplyanka | 1
Ukraine | Serhiy Sydorchuk | 1
Ukraine | Yevhen Seleznyov | 1
Wales | Ben Davies | 2
Wales | Aaron Ramsey | 2
Wales | James Chester | 2
Wales | Chris Gunter | 1
Wales | Sam Vokes | 1
Wales | Joe Allen | 1
Wales | Neil Taylor | 1
Wales | Gareth Bale | 1
(167 rows)
Code Explanation:
The provided query in SQL that retrieves the number of bookings made by each player of each country in soccer matches.
The JOIN clause joins the soccer_country aliased as 'a' and player_booked aliased as 'b' based on the country_id and team_id columns and join the player_mast table aliased as 'c' with the result set based on the player_id column.
The GROUP BY clause is used to group the result set by country and player.
The ORDER BY clause is used to sort the result set by country name and the number of bookings in descending order.
Alternative Solution:
Using INNER JOINs with Subquery and COUNT:
-- This query selects the country name, player name, and the count of bookings for each player from the player_booked table, grouped by country and player.
SELECT
a.country_name, -- Selecting the country name from the soccer_country table aliased as 'a'
c.player_name, -- Selecting the player name from the player_mast table aliased as 'c'
COUNT(b.*) as Booked -- Counting the number of bookings for each player
FROM
soccer_country a -- Specifying the soccer_country table with an alias 'a'
JOIN
(SELECT team_id, player_id FROM player_booked) b ON a.country_id = b.team_id -- Joining the result of a subquery selecting team_id and player_id from player_booked table, aliased as 'b', with the soccer_country table based on the country_id
JOIN
player_mast c ON b.player_id = c.player_id -- Joining the player_mast table with the 'b' subquery based on the player_id
GROUP BY
a.country_name, c.player_name -- Grouping the results by country name and player name
-- Ordering the results by country name in ascending order and by the count of bookings (Booked) in descending order
ORDER BY
a.country_name, Booked DESC;
Explanation:
This query uses a subquery to first select necessary columns from player_booked. It then performs INNER JOINs to retrieve the desired information and applies a COUNT function to count the booked instances for each player in each country.
Go to:
PREV : Find the number of captains who were also goalkeepers.
NEXT : Find the players who booked most number of times.
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.