w3resource

SQL Join Exercises, Practice, Solution - JOINS exercises on soccer Database

SQL [61 exercises with solution]

Sample Database: soccer

soccer database relationship structure

1. From the following tables, write a SQL query to find the venue where EURO cup 2016 final match held. Return venue name, city.  Go to the editor

Sample table: soccer_venue


Sample table: soccer_city


Sample table: match_mast


Sample Output:

   venue_name    |    city
-----------------+-------------
 Stade de France | Saint-Denis
(1 row)

Click me to see the solution

2. From the following tables, write a SQL query to find the number of goal scored by each team in every match within normal play schedule. Return match number, country name and goal score.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no |    country_name     | goal_score
----------+---------------------+------------
        1 | France              |          2
        1 | Romania             |          1
        2 | Albania             |          0
        2 | Switzerland         |          1
		.....
		

Click me to see the solution

3. From the following tables, write a SQL query to count the number of goals scored by each player within normal play schedule. Group the result set on player name and country name and sorts the result-set according to the highest to the lowest scorer. Return player name, number of goals and country name.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

       player_name       | count |    country_name
-------------------------+-------+---------------------
 Antoine Griezmann       |     5 | France
 Cristiano Ronaldo       |     3 | Portugal
 Gareth Bale             |     3 | Wales
 Olivier Giroud          |     3 | France
 .....
 

Click me to see the solution

4. From the following tables, write a SQL query to find the highest individual scorer in EURO cup 2016. Return player name, country name and highest individual scorer.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

     player_name     | country_name | count
--------------------+--------------+-------
 Antoine Griezmann  | France       |     6
(1 row)

Click me to see the solution

5. From the following tables, write a SQL query to find the scorer in the final of EURO cup 2016. Return player name, jersey number and country name.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

  player_name | jersey_no | country_name
-------------+-----------+--------------
 Eder        |         9 | Portugal
(1 row)

Click me to see the solution

6. From the following tables, write a SQL query to find the country where Football EURO cup 2016 held. Return country name.  Go to the editor

Sample table: soccer_country


Sample table: soccer_city


Sample table: soccer_venue


Sample Output:

 country_name
--------------
 France
(1 row)

Click me to see the solution

7. From the following tables, write a SQL query to find the player who scored first goal of EURO cup 2016. Return player_name, jersey_no, country_name, goal_time, play_stage, goal_schedule, goal_half.  Go to the editor

Sample table: soccer_country


Sample table: player_mast


Sample table: goal_details


Sample Output:

   player_name   | jersey_no | country_name | goal_time | play_stage | goal_schedule | goal_half
-----------------+-----------+--------------+-----------+------------+---------------+-----------
 Olivier Giroud  |         9 | France       |        57 | G          | NT            |         2
(1 row)

Click me to see the solution

8. From the following tables, write a SQL query to find the referee who managed the opening match. Return referee name, country name.  Go to the editor

Sample table: soccer_country


Sample table: match_mast


Sample table: referee_mast


Sample Output:

 referee_name  | country_name
---------------+--------------
 Viktor Kassai | Hungary
(1 row)

Click me to see the solution

9. From the following tables, write a SQL query to find the referee who managed the final match. Return referee name, country name.  Go to the editor

Sample table: soccer_country


Sample table: match_mast


Sample table: referee_mast


Sample Output:

   referee_name   | country_name
------------------+--------------
 Mark Clattenburg | England
(1 row)

Click me to see the solution

10. From the following tables, write a SQL query to find the referee who assisted the referee in the opening match. Return associated referee name, country name.  Go to the editor

Sample table: asst_referee_mast


Sample table: soccer_country


Sample table: match_details


Sample Output:

 ass_ref_name | country_name
--------------+--------------
 Gyorgy Ring  | Hungary
 Vencel Toth  | Hungary
(2 rows)

Click me to see the solution

11. From the following tables, write a SQL query to find the referee who assisted the referee in the final match. Return associated referee name, country name.  Go to the editor

Sample table: asst_referee_mast


Sample table: soccer_country


Sample table: match_details


Sample Output:

 ass_ref_name | country_name
--------------+--------------
 Simon Beck   | England
 Jake Collin  | England
(2 rows)

Click me to see the solution

12. From the following tables, write a SQL query to find the city where the opening match of EURO cup 2016 played. Return venue name, city.  Go to the editor

Sample table: soccer_venue


Sample table: soccer_city


Sample table: match_mast


Sample Output:

   venue_name    |    city
-----------------+-------------
 Stade de France | Saint-Denis
(1 row)

Click me to see the solution

13. From the following tables, write a SQL query to find the stadium hosted the final match of EURO cup 2016. Return venue_name, city, aud_capacity, audience.  Go to the editor

Sample table: soccer_venue


Sample table: soccer_city


Sample table: match_mast


Sample Output:

   venue_name    |    city     | aud_capacity | audence
-----------------+-------------+--------------+---------
 Stade de France | Saint-Denis |        80100 |   75868
(1 row)

Click me to see the solution

14. From the following tables, write a SQL query to count the number of matches played in each venue. Sort the result-set on venue name. Return Venue name, city, and number of matches.  Go to the editor

Sample table:soccer_venue


Sample table: soccer_city


Sample table: match_mast


Sample Output:

       venue_name        |     city      | count
-------------------------+---------------+-------
 Parc des Princes        | Paris         |     5
 Stade Bollaert-Delelis  | Lens          |     4
 Stade de Bordeaux       | Bordeaux      |     5
 Stade de France         | Saint-Denis   |     7
 .....
 

Click me to see the solution

15. From the following tables, write a SQL query to find the player who was the first player to be sent off at the tournament EURO cup 2016. Return match Number, country name and player name.  Go to the editor

Sample table: player_booked


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 match_no | country_name |   player_name   | sent_off_time | play_schedule | jersey_no
----------+--------------+-----------------+---------------+---------------+-----------
        1 | France       | Olivier Giroud  |            69 | NT            |         9
(1 row)

Click me to see the solution

16. From the following tables, write a SQL query to find those teams that scored only one goal to the tournament. Return country_name as "Team", team in the group, goal_for.  Go to the editor

Sample table: soccer_team


Sample table: soccer_country


Sample Output:

  Team   | team_group | goal_for
---------+------------+----------
 Albania | A          |        1
 Austria | F          |        1
 Sweden  | E          |        1
(3 rows)

Click me to see the solution

17. From the following tables, write a SQL query to count the yellow cards received by each country. Return country name and number of yellow cards. Go to the editor

Sample table: soccer_country


Sample table: player_booked


Sample Output:

    country_name     | count
---------------------+-------
 Italy               |    16
 France              |    13
 Portugal            |    13
 Hungary             |    12
 .....
 

Click me to see the solution

18. From the following tables, write a SQL query to count number of goals that has seen. Return venue name and number of goals. Go to the editor

Sample table: soccer_country


Sample table: goal_details


Sample table: match_mast


Sample table:soccer_venue


Sample Output:

       venue_name        | count
-------------------------+-------
 Stade de France         |    18
 Stade de Lyon           |    16
 Stade Pierre Mauroy     |    13
 Stade de Bordeaux       |    13
 .....
 

Click me to see the solution

19. From the following tables, write a SQL query to find the match where no stoppage time added in first half of play. Return match number, country name. Go to the editor

Sample table: match_details


Sample table: match_mast


Sample table: soccer_country


Sample Output:

 match_no | country_name
----------+--------------
        4 | England
        4 | Russia
(2 rows)

Click me to see the solution

20. From the following tables, write a SQL query to find the team(s) who conceded the most goals in EURO cup 2016. Return country name, team group and match played. Go to the editor

Sample table: soccer_team


Sample table: soccer_country


Sample Output:

 country_name | team_group | match_played | won | lost | goal_for | goal_agnst
--------------+------------+--------------+-----+------+----------+------------
 Russia       | B          |            3 |   0 |    2 |        2 |          6
(1 row)

Click me to see the solution

21. From the following tables, write a SQL query to find those matches where highest stoppage time added in 2nd half of play. Return match number, country name, stoppage time(sec.). Go to the editor

Sample table: match_details


Sample table: match_mast


Sample table: soccer_country


Sample Output:

 match_no |   country_name   | Stoppage Time(sec.)
----------+------------------+---------------------
       17 | Ukraine          |                 411
       17 | Northern Ireland |                 411
(2 rows)

Click me to see the solution

22. From the following tables, write a SQL query to find those matches ending with a goalless draw in-group stage of play. Return match number, country name. Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | country_name
----------+--------------
       18 | Germany
       18 | Poland
       24 | Austria
       24 | Portugal
	   .....
	   

Click me to see the solution

23. From the following tables, write a SQL query to find those match(s) where the 2nd highest stoppage time had been added in the second half of play. Return match number, country name and stoppage time. Go to the editor

Sample table: match_mast


Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | country_name | stop2_sec
----------+--------------+-----------
       15 | France       |       374
       15 | Albania      |       374
(2 rows)

Click me to see the solution

24. From the following tables, write a SQL query to find the number of matches played a player as a goalkeeper for his team. Return country name, player name, number of matches played as a goalkeeper. Go to the editor

Sample table: player_mast


Sample table: match_details


Sample table: soccer_country


Sample Output:

    country_name     |     player_name     | count_gk
---------------------+---------------------+----------
 Albania             | Etrit Berisha       |        3
 Austria             | Robert Almer        |        3
 Belgium             | Thibaut Courtois    |        5
 Croatia             | Danijel SubaSic     |        4
 .....
 

Click me to see the solution

25. From the following tables, write a SQL query to find the venue that has seen the most number of goals. Return venue name, number of goals.  Go to the editor

Sample table: goal_details


Sample table: soccer_country


Sample table: match_mast


Sample table: soccer_venue


Sample Output:

   venue_name    | count
-----------------+-------
 Stade de France |    18
(1 row)

Click me to see the solution

26. From the following tables, write a SQL query to find the oldest player appeared in a EURO cup 2016 match. Return country name, player name, jersey number and age.  Go to the editor

Sample table: player_mast


Sample table: soccer_country


Sample Output:

    country_name     |  player_name  | jersey_no | age
---------------------+---------------+-----------+-----
 Hungary             | Gabor Kiraly  |         1 |  40
 Republic of Ireland | Shay Given    |        16 |  40
(2 rows)

Click me to see the solution

27. From the following tables, write a SQL query to find those two teams, scored three goals in a single game in this tournament. Return match number and country name.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | country_name
----------+--------------
       34 | Hungary
       34 | Portugal
(2 rows)

Click me to see the solution

28. From the following tables, write a SQL query to find those teams that finished bottom of their respective groups after conceding four times in three games. Return country name, team group and match played.  Go to the editor

Sample table: soccer_team


Sample table: soccer_country


Sample Output:

  team   | team_group | match_played | goal_agnst | group_position
---------+------------+--------------+------------+----------------
 Romania | A          |            3 |          4 |              4
 Austria | F          |            3 |          4 |              4
(2 rows)

Click me to see the solution

29. From the following tables, write a SQL query to find those players, who contracted to ‘Lyon’ club and participated in the EURO cup 2016 Finals. Return player name, jerseyno, position to play, age, country name.  Go to the editor

Sample table: player_mast


Sample table: soccer_country


Sample table: match_details


Sample Output:

    player_name     | jersey_no | posi_to_play | age | country_name
--------------------+-----------+--------------+-----+--------------
 Christophe Jallet  |         2 | DF           |  32 | France
 Samuel Umtiti      |        22 | DF           |  22 | France
 Anthony Lopes      |        12 | GK           |  25 | Portugal
(3 rows)

Click me to see the solution

30. From the following tables, write a SQL query to find the final four teams in the tournament. Return country name.  Go to the editor

Sample table: soccer_country


Sample table: match_details


Sample Output:

 country_name
--------------
 France
 Germany
 Portugal
 Wales
(4 rows)

Click me to see the solution

31. From the following tables, write a SQL query to find the captains of the top four teams that participated in the semi-finals (match 48 and 49) in the tournament. Return country name, player name, jersey number and position to play.  Go to the editor

Sample table: soccer_country


Sample table: match_captain


Sample table: player_mast


Sample Output:

 country_name |    player_name     | jersey_no | posi_to_play
--------------+--------------------+-----------+--------------
 France       | Hugo Lloris        |         1 | GK
 Iceland      | Aron Gunnarsson    |        17 | MF
 Portugal     | Cristiano Ronaldo  |         7 | FD
 Wales        | Ashley Williams    |         6 | DF
(4 rows)

Click me to see the solution

32. From the following tables, write a SQL query to find the captains of all the matches in the tournament. Return match number, country name, player name, jersey number and position to play.  Go to the editor

Sample table: soccer_country


Sample table: match_captain


Sample table: player_mast


Sample Output:

 match_no |    country_name     |       player_name       | jersey_no | posi_to_play
----------+---------------------+-------------------------+-----------+--------------
        1 | France              | Hugo Lloris             |         1 | GK
        1 | Romania             | Vlad Chiriches          |         6 | DF
        2 | Albania             | Lorik Cana              |         5 | MF
        2 | Switzerland         | Stephan Lichtsteiner    |         2 | DF
		.....

Click me to see the solution

33. From the following tables, write a SQL query to find the captain and goalkeeper of all the matches. Return match number, Captain, Goal Keeper and country name.  Go to the editor

Sample table: soccer_country


Sample table: match_captain


Sample table: match_details


Sample table: player_mast


Sample Output:

 match_no |         Captain         |     Goal Keeper     |    country_name
----------+-------------------------+---------------------+--------------------
        1 | Hugo Lloris             | Hugo Lloris         | France
        1 | Vlad Chiriches          | Ciprian Tatarusanu  | Romania
        2 | Lorik Cana              | Etrit Berisha       | Albania
        2 | Stephan Lichtsteiner    | Yann Sommer         | Switzerland
		.....
		

Click me to see the solution

34. From the following tables, write a SQL query to find the player who was selected for the 'Man of the Match' award in the finals of EURO cup 2016.Return player name, country name.  Go to the editor

Sample table: soccer_country


Sample table: match_mast


Sample table: player_mast


Sample Output:

 player_name | country_name
-------------+--------------
 Pepe        | Portugal
(1 row)

Click me to see the solution

35. From the following tables, write a SQL query to find the substitute players who came into the field in the first half of play within normal play schedule. Return match_no, country_name, player_name, jersey_no and time_in_out.  Go to the editor

Sample table: player_in_out


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 match_no | country_name |       player_name       | jersey_no | time_in_out
----------+--------------+-------------------------+-----------+-------------
        9 | Sweden       | Erik Johansson          |         3 |          45
       47 | Germany      | Bastian Schweinsteiger  |         7 |          16
       51 | Portugal     | Ricardo Quaresma        |        20 |          25
(3 rows)

Click me to see the solution

36. From the following table, write a SQL query to prepare a list for the player of the match against each match. Return match number, play date, country name, player of the Match, jersey number.  Go to the editor

Sample table: match_mast


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 match_no | play_date  |    country_name     |  Player of the Match  | jersey_no
----------+------------+---------------------+-----------------------+-----------
       25 | 2016-06-20 | Albania             | Arlind Ajeti          |        18
       22 | 2016-06-18 | Belgium             | Axel Witsel           |         6
       42 | 2016-06-27 | Belgium             | Eden Hazard           |        10
       36 | 2016-06-23 | Belgium             | Eden Hazard           |        10
	   .....
	   

Click me to see the solution

37. From the following tables, write a SQL query to find the player who taken the penalty shot number 26. Return match number, country name, player name.  Go to the editor

Sample table: penalty_shootout


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 match_no | country_name |  player_name
----------+--------------+----------------
       47 | Italy        | Graziano Pelle
(1 row)

Click me to see the solution

38. From the following tables, write a SQL query to find the team against which the penalty shot number 26 had been taken. Return match number, country name.  Go to the editor

Sample table: penalty_shootout


Sample table: soccer_country


Sample Output:

 match_no | country_name
----------+--------------
       47 | Germany
(1 row)

Click me to see the solution

39. From the following tables, write a SQL query to find the captain who was also the goalkeeper. Return match number, country name, player name and jersey number.  Go to the editor

Sample table: match_captain


Sample table: soccer_country


Sample table: player_mast


Sample Output:

 match_no |  country_name  |    player_name    | jersey_no
----------+----------------+-------------------+-----------
        1 | France         | Hugo Lloris       |         1
        7 | Germany        | Manuel Neuer      |         1
       10 | Italy          | Gianluigi Buffon  |         1
       15 | France         | Hugo Lloris       |         1
	   .....
	   

Click me to see the solution

40. From the following tables, write a SQL query to find the number of captains who was also the goalkeeper. Return number of captains.  Go to the editor

Sample table: match_captain


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 count
-------
     4
(1 row)

Click me to see the solution

41. From the following tables, write a SQL query to find the players along with their team booked number of times in the tournament. Show the result according to the team and number of times booked in descending order. Return country name, player name, and team booked number of times.  Go to the editor

Sample table: soccer_country


Sample table: player_booked


Sample table: player_mast


Sample Output:

    country_name     |       player_name       | booked
---------------------+-------------------------+--------
 Albania             | Lorik Cana              |      2
 Albania             | Burim Kukeli            |      2
 Albania             | Ledian Memushaj         |      1
 Albania             | Mergim Mavraj           |      1
 .....
 

Click me to see the solution

42. From the following tables, write a SQL query to count the players who booked the most number of times. Return player name, number of players who booked most number of times.  Go to the editor

Sample table: soccer_country


Sample table: player_booked


Sample table: player_mast


Sample Output:

    player_name    | booked
-------------------+--------
 NGolo Kante       |      3
 William Carvalho  |      3
 Bartosz Kapustka  |      3
(3 rows)

Click me to see the solution

43. From the following tables, write a SQL query to find the number of players booked for each team. Return country name, number of players booked.  Go to the editor

Sample table: soccer_country


Sample table: player_booked


Sample Output:

    country_name     | booked
---------------------+--------
 Italy               |     16
 France              |     13
 Portugal            |     13
 Iceland             |     12
 .....
 

Click me to see the solution

44. From the following tables, write a SQL query to find those matches where most number of cards shown. Return match number, number of cards shown.  Go to the editor

Sample table: soccer_country


Sample table: player_booked


Sample table: player_mast


Sample Output:

 match_no | booked 
----------+--------
       51 |     10
(1 row)

Click me to see the solution

45. From the following table, write a SQL query to find the assistant referees. Return match number, country name, assistant referee name.  Go to the editor

Sample table: match_details


Sample table: asst_referee_mast


Sample table: soccer_country


Sample Output:

 match_no |    country_name     |       ass_ref_name
----------+---------------------+--------------------------
        1 | Hungary             | Gyorgy Ring
        1 | Hungary             | Vencel Toth
        2 | Spain               | Juan Yuste Jimenez
        2 | Spain               | Roberto Alonso Fernandez
		.....
		

Click me to see the solution

46. From the following tables, write a SQL query to find the assistant referees of each country assists the number of matches. Sort the result-set in descending order on number of matches. Return country name, number of matches.  Go to the editor

Sample table: match_details


Sample table: asst_referee_mast


Sample table: soccer_country


Sample Output:

    country_name     | count
---------------------+-------
 England             |     7
 Slovenia            |     4
 Italy               |     4
 Turkey              |     3
.....

Click me to see the solution

47. From the following table, write a SQL query to find the countries from where the assistant referees assist most of the matches. Return country name and number of matches.  Go to the editor

Sample table: match_details


Sample table: asst_referee_mast


Sample table: soccer_country


Sample Output:

 country_name | count
--------------+-------
 England      |     7
(1 row)

Click me to see the solution

48. From the following table, write a SQL query to find the name of referees for each match. Sort the result-set on match number. Return match number, country name, referee name.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Output:

 match_no |  country_name  |      referee_name
----------+----------------+-------------------------
        1 | Hungary        | Viktor Kassai
        2 | Spain          | Carlos Velasco Carballo
        3 | Norway         | Svein Oddvar Moen
        4 | Italy          | Nicola Rizzoli
		.....
		

Click me to see the solution

49. From the following tables, write a SQL query to count the number of matches managed by referees of each country. Return country name, number of matches.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Output:

  country_name  | count
----------------+-------
 England        |     7
 Italy          |     4
 Slovenia       |     4
 Spain          |     3
 .....
 

Click me to see the solution

50. From the following tables, write a SQL query to find the countries from where the referees managed most of the matches. Return country name, number of matches.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Output:

 country_name | count
--------------+-------
 England      |     7
(1 row)

Click me to see the solution

51. From the following tables, write a SQL query to find the number of matches managed by each referee. Return referee name, country name, number of matches.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Output:

      referee_name       |  country_name  | count
-------------------------+----------------+-------
 Damir Skomina           | Slovenia       |     4
 Martin Atkinson         | England        |     3
 Clement Turpin          | France         |     2
 Jonas Eriksson          | Sweden         |     3
 .....
 

Click me to see the solution

52. From the following tables, write a SQL query to find those referees who managed most of the matches. Return referee name, country name and number of matches.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample Output:

   referee_name   | country_name | count
------------------+--------------+-------
 Damir Skomina    | Slovenia     |     4
 Mark Clattenburg | England      |     4
 Nicola Rizzoli   | Italy        |     4
(3 rows)

Click me to see the solution

53. From the following tables, write a SQL query to find those referees who managed the number of matches in each venue. Return referee name, country name, venue name, number of matches.  Go to the editor

Sample table: match_mast


Sample table: referee_mast


Sample table: soccer_country


Sample table: soccer_venue


Sample Output:

      referee_name       |  country_name  |       venue_name        | count
-------------------------+----------------+-------------------------+-------
 Bjorn Kuipers           | Netherlands    | Stade de Bordeaux       |     1
 Bjorn Kuipers           | Netherlands    | Stade de France         |     2
 Carlos Velasco Carballo | Spain          | Stade Bollaert-Delelis  |     2
 Carlos Velasco Carballo | Spain          | Stade Geoffroy Guichard |     1
 .....
 

Click me to see the solution

54. From the following tables, write a SQL query to find the referees and number of booked they made. Return referee name, number of matches.  Go to the editor

Sample table: player_booked


Sample table: match_mast


Sample table: referee_mast


Sample Output:

      referee_name       | count
-------------------------+-------
 Mark Clattenburg        |    21
 Nicola Rizzoli          |    20
 Milorad Mazic           |    13
 Viktor Kassai           |    12
 .....
 

Click me to see the solution

55. From the following tables, write a SQL query to find those referees who booked most number of players. Return referee name, number of matches.  Go to the editor

Sample table: player_booked


Sample table: match_mast


Sample table: referee_mast


Sample Output:

   referee_name   | count
------------------+-------
 Mark Clattenburg |    21
(1 row)

Click me to see the solution

56. From the following tables, write a SQL query to find those players of each team who wore jersey number 10. Return country name, player name, position to play, age and playing club.  Go to the editor

Sample table: player_mast


Sample table: soccer_country


Sample Output:

    country_name     |     player_name      | posi_to_play | age |  playing_club

---------------------+----------------------+--------------+-----+-----------------
 Albania             | Armando Sadiku       | FD           |  25 | Vaduz
 Austria             | Zlatko Junuzovic     | MF           |  28 | Bremen
 Belgium             | Eden Hazard          | MF           |  25 | Chelsea
 Croatia             | Luka Modric          | MF           |  30 | Real Madrid
 .....
 

Click me to see the solution

57. From the following tables, write a SQL query to find those defenders who scored goal for their team. Return player name, jersey number, country name, age and playing club.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

       player_name       | jersey_no |    country_name     | age | playing_club
-------------------------+-----------+---------------------+-----+--------------
 Arnor Ingvi Traustason  |        21 | Iceland             |  23 | Norrkoping
 Ashley Williams         |         6 | Wales               |  31 | Swansea
 Birkir Saevarsson       |         2 | Iceland             |  31 | Hammarby
 Ciaran Clark            |         3 | Republic of Ireland |  26 | Aston Villa
 .....
 

Click me to see the solution

58. From the following table, write a SQL query to find those players who accidentally scores against his own team. Return player name, jersey number, country name, age, position to play, and playing club.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

    player_name     | jersey_no |    country_name     | age | posi_to_play | playing_club
--------------------+-----------+---------------------+-----+--------------+--------------
 Birkir Saevarsson  |         2 | Iceland             |  31 | DF           | Hammarby
 Ciaran Clark       |         3 | Republic of Ireland |  26 | DF           | Aston Villa
 Gareth McAuley     |         4 | Northern Ireland    |  36 | DF           | West Brom
(3 rows)

Click me to see the solution

59. From the following table, write a SQL query to find the results of penalty shootout matches. Return match number, play stage, country name and penalty score.  Go to the editor

Sample table: match_details


Sample table: soccer_country


Sample Output:

 match_no | play_stage | country_name | penalty_score
----------+------------+--------------+---------------
       37 | R          | Poland       |             5
       37 | R          | Switzerland  |             4
       45 | Q          | Poland       |             3
       45 | Q          | Portugal     |             5
	   .....
	   

Click me to see the solution

60. From the following table, write a SQL query to find the goal scored by the players according to their playing position. Return country name, position to play, number of goals.  Go to the editor

Sample table: goal_details


Sample table: player_mast


Sample table: soccer_country


Sample Output:

    country_name     | posi_to_play | Number of goals
---------------------+--------------+-----------------
 Albania             | FD           |               1
 Austria             | MF           |               1
 Belgium             | DF           |               1
 Belgium             | FD           |               3
 .....
 

Click me to see the solution

61. From the following tables, write a SQL query to find those players who came into the field at the last time of play. Return match number, country name, player name, jersey number and time in out.  Go to the editor

Sample table: player_in_out


Sample table: player_mast


Sample table: soccer_country


Sample Output:

 match_no | country_name |   player_name    | jersey_no | time_in_out
----------+--------------+------------------+-----------+-------------
       39 | Croatia      | Andrej Kramaric  |         9 |         120
       47 | Italy        | Simone Zaza      |         7 |         120
(2 rows)

Click me to see the solution

More to come .......

Practice Online


Query visualizations are generated using Postgres Explain Visualizer (pev)

Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.