w3resource

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

SQL [61 exercises with solution]

You may read our SQL Joins, SQL Left Join, SQL Right Join tutorial before solving the following exercises.

Sample Database: soccer

soccer database relationship structure

1. From the following table, write a SQL query to find out where the final match of the EURO cup 2016 was 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

2. From the following tables, write a SQL query to find the number of goals scored by each team in each match during normal play. 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 table, write a SQL query to count the number of goals scored by each player within a 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 table, write a SQL query to find out who scored the most goals in the 2016 Euro Cup. 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 table, write a SQL query to find out who scored in the final of the 2016 Euro Cup. 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 out which country hosted the 2016 Football EURO Cup. 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 out who scored the first goal of the 2016 European Championship. 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 table, write a SQL query to find the city where the opening match of EURO cup 2016 took place. 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 out which stadium hosted the final match of the 2016 Euro Cup. 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 at 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 the teams that have scored one goal in 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 number of yellow cards each country has received. 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 the number of goals that have been 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 there was no stoppage time in the first half. 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 the highest stoppage time was 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 the matches that ended in a goalless draw at the group stage. 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 second highest amount of stoppage time was added in the second half of the match. 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 by 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 where the most goals have been scored. 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 to have appeared in a EURO 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 the two teams in this tournament that have scored three goals in a single game. 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 which teams finished at the bottom of their respective groups after conceding four goals 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 were contracted to the Lyon club and participated in the final of the EURO cup 2016. Return player name, jerseyno, position to play, age, and 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 competed in the semi-finals (matches 48 and 49) of 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 table, write a SQL query to find out 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 entered the field during the first half of play within the normal time frame for the game. 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 took 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 was 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 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.  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 out how many players each team has booked. 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 the matches in which the most cards are displayed. 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 table, write a SQL query to find the assistant referees of each country assist 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 at 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 the number of bookings 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 the 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 on 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 the defenders who scored goals 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 out which players scored against his own team by accident. 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 each player according to their position. Return country name, position to play, and 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 end 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 .......

Keep Learning: SQL Joins, SQL Left Join, SQL Right Join, SQL Equi Join, SQL Non Equi Join, SQL Inner Join, SQL Natural Join, SQL Cross Join, SQL Outer Join, SQL Full Outer Join, SQL Self Join.

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.



Share this Tutorial / Exercise on : Facebook and Twitter

SQL: Tips of the Day

Count the occurrences of DISTINCT values?

example db
id         name
-----      ------
1          Mark
2          Mike
3          Paul
4          Mike
5          Mike
6          John
7          Mark
SELECT name,COUNT(*) as count 
FROM tablename 
GROUP BY name 
ORDER BY count DESC;

expected result

name       count
-----      -----
Mike       3
Mark       2
Paul       1
John       1

Ref : https://bit.ly/3EXu62o