w3resource

SQL Exercise: Captains of the top four teams in the semifinals

SQL soccer Database: Joins Exercise-31 with 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.

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)

Code Explanation:

The said query in SQL that retrieves the captain's name, jersey number, position to play, and country name for the teams that played in the matches with match numbers 48 and 49.
The JOIN keyword joins the 'match_captain' and the 'soccer_country' tables based on the "team_id" and the "country_id" columns and another joins with the 'player_mast' and the 'match_captain' tables based on the "player_id" and the "player_captain" columns from the respective tables.
The WHERE clause in this query filters the teams that played in the matches with match numbers 48 and 49.

Relational Algebra Expression:

Relational Algebra Expression: Find the captains for the top four teams which participated in the semifinals.

Relational Algebra Tree:

Relational Algebra Tree: Find the captains for the top four teams which participated in the semifinals.

Practice Online


Sample Database: soccer

soccer database relationship structure

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

Previous SQL Exercise: Find the final four teams in the tournament.
Next SQL Exercise: Find captains for all the matches in the tournament.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

What is the best way to paginate results in SQL Server?

SELECT  *
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY OrderDate ) AS RowNum, *
          FROM      Orders
          WHERE     OrderDate >= '1980-01-01'
        ) AS RowConstrainedResult
WHERE   RowNum >= 1
    AND RowNum < 20
ORDER BY RowNum

Database: SQL Server

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

 





We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook