SQL exercises on soccer Database: Find the captains for the top four teams which participated in the semifinals

SQL soccer Database: Joins Exercise-31 with Solution

31. Write a query in SQL to find the captains for the top four teams with other information which participated in the semifinals (match 48 and 49) in the tournament.

Sample table: soccer_country

Sample table: match_captain

Sample table: player_mast

Sample Solution:

SQL Code:

SELECT country_name, player_name, jersey_no, posi_to_play 
FROM match_captain a
JOIN soccer_country b ON a.team_id=b.country_id
JOIN player_mast c ON a.player_captain=c.player_id
WHERE match_no IN(48,49);

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)

Practice Online

Sample Database: soccer

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query in SQL to find the final four teams in the tournament.
Next: Write a query in SQL to find the captains with other information for all the matches in the tournament.