The JOIN operation
注意：where语句中对表示条件的需要用单引号， 下面的译文使用的是有道翻译如有不正确，请直接投诉有道
01.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER
译文：修改后显示德国队所有进球的比赛id和球员名字。要确定德国球员，请检查:teamid = 'GER'
SELECT matchid, player FROM goal
WHERE teamid = 'GER';
02.Show id, stadium, team1, team2 for just game 1012
SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012;
03.Modify it to show the player, teamid, stadium and mdate for every German goal.
译文：修改它，显示球员，teamid，体育场和mdate为每个德国进球。
SELECT player, teamid, stadium, mdate FROM game JOIN goal ON (id=matchid) WHERE teamid = 'GER';
04.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
译文：展示一个叫马里奥的球员(比如“马里奥%”)每进一球的团队1、团队2和球员。
SELECT team1, team2, player FROM game JOIN goal ON (id=matchid) WHERE player LIKE 'Mario%';
05.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10
译文：显示球员，teamid，教练，在前10分钟内所有的进球gtime<=10
SELECT player, teamid,coach, gtime FROM goal JOIN eteam on teamid=id WHERE gtime<=10;
06.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
译文：请列出比赛日期和由“费尔南多·桑托斯”担任球队教练的球队名称。
SELECT mdate, teamname FROM game JOIN eteam on (team1=eteam.id) WHERE coach = 'Fernando Santos';
07.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
译文：列出球场为“华沙国家体育场”的比赛中每进一球的球员名单
SELECT player FROM game JOIN goal ON (id=matchid) WHERE stadium = 'National Stadium, Warsaw'
08.Instead show the name of all players who scored a goal against Germany.
译文：显示所有在对德国队的比赛中进球的球员的名字。
SELECT DISTINCT player FROM game JOIN goal ON matchid = id WHERE (team1='GER' AND teamid != 'GER') OR (team2='GER' AND teamid != 'GER');
09.Show teamname and the total number of goals scored.
译文：显示球队名称和进球总数
SELECT teamname, COUNT(player) FROM eteam JOIN goal ON id=teamid GROUP BY teamname;
10.Show the stadium and the number of goals scored in each stadium.
译文：显示球场和每个球场的进球数
SELECT stadium, COUNT(player) FROM game JOIN goal ON id=matchid GROUP BY stadium;
11.For every match involving 'POL', show the matchid, date and the number of goals scored.
译文：对于每一场涉及“POL”的比赛，显示比赛id、日期和进球次数。
SELECT DISTINCT matchid, mdate, COUNT(teamid) FROM game JOIN goal ON matchid = id WHERE team1 = 'POL' OR team2 = 'POL' group by matchid, mdate;
12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
译文：对于每一场“GER”进球的比赛，显示matchid、比赛日期和“GER”进球的次数
SELECT DISTINCT matchid, mdate, COUNT(teamid) FROM game JOIN goal ON matchid = id WHERE ( team1 = 'GER' AND teamid = 'GER') OR (team2 = 'GER' AND teamid = 'GER') group by matchid, mdate;
13.Sort your result by mdate, matchid, team1 and team2.
译文：根据mdate, matchid, team1和team2对你的结果排序。
# 写不出来
练习网址：https://sqlzoo.net/wiki/The_JOIN_operation
——————————————————————————————————————————————————————————————————————————————————————————————————————————
