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
- برمجة سي كيو ال sql
- 2021-09-04
- mhanasmh00489829403
الأجوبة
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)أسئلة مشابهة
القوائم الدراسية التي ينتمي لها السؤال