SQL exercises on soccer Database: Find the captains for all the matches in the tournament
SQL soccer Database: Joins Exercise-32 with 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.
Sample table: soccer_country
Sample table: match_captain
Sample table: player_mast
Sample Solution:
SQL Code:
SELECT match_no,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
ORDER BY match_no;
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
3 | Slovakia | Martin Skrtel | 3 | DF
3 | Wales | Ashley Williams | 6 | DF
4 | England | Wayne Rooney | 10 | FD
4 | Russia | Vasili Berezutski | 14 | DF
5 | Turkey | Arda Turan | 10 | MF
5 | Croatia | Darijo Srna | 11 | DF
6 | Poland | Robert Lewandowski | 9 | FD
6 | Northern Ireland | Steven Davis | 8 | MF
7 | Germany | Manuel Neuer | 1 | GK
7 | Ukraine | Vyacheslav Shevchuk | 13 | DF
8 | Spain | Sergio Ramos | 15 | DF
8 | Czech Republic | TomasRosicky | 10 | MF
9 | Republic of Ireland | John OShea | 4 | DF
9 | Sweden | Zlatan Ibrahimovic | 10 | FD
10 | Italy | Gianluigi Buffon | 1 | GK
10 | Belgium | Eden Hazard | 10 | MF
11 | Austria | Christian Fuchs | 5 | DF
11 | Hungary | Balazs Dzsudzsak | 7 | FD
12 | Portugal | Cristiano Ronaldo | 7 | FD
12 | Iceland | Aron Gunnarsson | 17 | MF
13 | Russia | Vasili Berezutski | 14 | DF
13 | Slovakia | Martin Skrtel | 3 | DF
14 | Romania | Vlad Chiriches | 6 | DF
14 | Switzerland | Stephan Lichtsteiner | 2 | DF
15 | France | Hugo Lloris | 1 | GK
15 | Albania | Ansi Agolli | 7 | DF
16 | Wales | Ashley Williams | 6 | DF
16 | England | Wayne Rooney | 10 | FD
17 | Ukraine | Vyacheslav Shevchuk | 13 | DF
17 | Northern Ireland | Steven Davis | 8 | MF
18 | Poland | Robert Lewandowski | 9 | FD
18 | Germany | Manuel Neuer | 1 | GK
19 | Sweden | Zlatan Ibrahimovic | 10 | FD
19 | Italy | Gianluigi Buffon | 1 | GK
20 | Croatia | Darijo Srna | 11 | DF
20 | Czech Republic | TomasRosicky | 10 | MF
21 | Turkey | Arda Turan | 10 | MF
21 | Spain | Sergio Ramos | 15 | DF
22 | Republic of Ireland | John OShea | 4 | DF
22 | Belgium | Eden Hazard | 10 | MF
23 | Iceland | Aron Gunnarsson | 17 | MF
23 | Hungary | Balazs Dzsudzsak | 7 | FD
24 | Portugal | Cristiano Ronaldo | 7 | FD
24 | Austria | Christian Fuchs | 5 | DF
25 | Albania | Ansi Agolli | 7 | DF
25 | Romania | Vlad Chiriches | 6 | DF
26 | Switzerland | Stephan Lichtsteiner | 2 | DF
26 | France | Hugo Lloris | 1 | GK
27 | Russia | Roman Shirokov | 15 | MF
27 | Wales | Ashley Williams | 6 | DF
28 | Slovakia | Martin Skrtel | 3 | DF
28 | England | Gary Cahill | 5 | DF
29 | Ukraine | Ruslan Rotan | 14 | MF
29 | Poland | Robert Lewandowski | 9 | FD
30 | Northern Ireland | Steven Davis | 8 | MF
30 | Germany | Manuel Neuer | 1 | GK
31 | Czech Republic | Petr Cech | 1 | GK
31 | Turkey | Arda Turan | 10 | MF
32 | Croatia | Darijo Srna | 11 | DF
32 | Spain | Sergio Ramos | 15 | DF
33 | Iceland | Aron Gunnarsson | 17 | MF
33 | Austria | Christian Fuchs | 5 | DF
34 | Hungary | Balazs Dzsudzsak | 7 | FD
34 | Portugal | Cristiano Ronaldo | 7 | FD
35 | Republic of Ireland | Seamus Coleman | 2 | DF
35 | Italy | Leonardo Bonucci | 19 | DF
36 | Sweden | Zlatan Ibrahimovic | 10 | FD
36 | Belgium | Eden Hazard | 10 | MF
37 | Poland | Robert Lewandowski | 9 | FD
37 | Switzerland | Stephan Lichtsteiner | 2 | DF
38 | Wales | Ashley Williams | 6 | DF
38 | Northern Ireland | Steven Davis | 8 | MF
39 | Croatia | Darijo Srna | 11 | DF
39 | Portugal | Cristiano Ronaldo | 7 | FD
40 | Republic of Ireland | Seamus Coleman | 2 | DF
40 | France | Hugo Lloris | 1 | GK
41 | Germany | Manuel Neuer | 1 | GK
41 | Slovakia | Martin Skrtel | 3 | DF
42 | Belgium | Eden Hazard | 10 | MF
42 | Hungary | Balazs Dzsudzsak | 7 | FD
43 | Spain | Sergio Ramos | 15 | DF
43 | Italy | Gianluigi Buffon | 1 | GK
44 | Iceland | Aron Gunnarsson | 17 | MF
44 | England | Wayne Rooney | 10 | FD
45 | Portugal | Cristiano Ronaldo | 7 | FD
45 | Poland | Robert Lewandowski | 9 | FD
46 | Wales | Ashley Williams | 6 | DF
46 | Belgium | Eden Hazard | 10 | MF
47 | Italy | Gianluigi Buffon | 1 | GK
47 | Germany | Manuel Neuer | 1 | GK
48 | Iceland | Aron Gunnarsson | 17 | MF
48 | France | Hugo Lloris | 1 | GK
49 | Portugal | Cristiano Ronaldo | 7 | FD
49 | Wales | Ashley Williams | 6 | DF
50 | France | Hugo Lloris | 1 | GK
50 | Germany | Bastian Schweinsteiger | 7 | MF
51 | France | Hugo Lloris | 1 | GK
51 | Portugal | Cristiano Ronaldo | 7 | FD
(102 rows)
Relational Algebra Expression:
Relational Algebra Tree:
Practice Online
Sample Database: soccer
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: 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.
Next: 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.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
SQL Server SELECT into existing table.
INSERT INTO dbo.TABLETWO SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:
INSERT INTO dbo.TABLETWO (col1, col2) SELECT col1, col2 FROM dbo.TABLEONE WHERE col3 LIKE @search_key
Database: SQL Server
Ref: https://bit.ly/3y6tpA3
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework