The JOIN operation
TABLE game:
id(編號) | mdate(日期) | stadium(場館) | team1(隊伍1) | team2(隊伍2) |
---|---|---|---|---|
1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
… |
goal:
matchid(賽事編號) | teamid(隊伍編號) | player(入球球員) | gtime(入球時間) |
---|---|---|---|
1001 | POL | Robert Lewandowski | 17 |
1001 | GRE | Dimitris Salpingidis | 51 |
1002 | RUS | Alan Dzagoev | 15 |
1001 | RUS | Roman Pavlyuchenko | 82 |
… |
eteam:
id(編號) | teamname(隊名) | coach(教練) |
---|---|---|
POL | Poland | Franciszek Smuda |
RUS | Russia | Dick Advocaat |
CZE | Czech Republic | Michal Bilek |
GRE | Greece | Fernando Santos |
… |
-
代表德国队取得进球的matchid,player
SELECT matchid,player FROM goal WHERE teamid = 'GER';
-
显示赛事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2 FROM game WHERE id = 1012;
-
找出每一个代表德国进球的球员名称、队名、场馆以及日期
SELECT player,teamid,stadium,mdate FROM game JOIN goal ON (id=matchid) WHERE teamid = 'GER';
-
查询Mario的team1,team2,player信息
SELECT team1,team2,player FROM game JOIN goal ON matchid = id WHERE player LIKE 'Mario%';
-
每场比赛前十分钟有进球的球员名,球队,教练以及进球时间
SELECT player, teamid, coach, gtime FROM goal JOIN eteam ON teamid = id WHERE gtime<=10;
-
Fernando Santos作为team1的教练的比赛时间及队伍名
SELECT mdate, teamname FROM game JOIN eteam ON team1 = eteam.id WHERE coach = 'Fernando Santos';
-
在 'National Stadium, Warsaw’球馆取得过进球的球员
SELECT player FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw';
-
对阵德国的比赛中取得进球的球员名称
SELECT DISTINCT(player) FROM game JOIN goal ON matchid = id AND teamid != 'GER' AND (team1 = 'GER' OR team2 = 'GER');
-
列出球队名称及其进球总数
SELECT teamname,count(player) From eteam left join goal On eteam.id=teamid Group by teamname
-
列出球馆名称和球馆内发生的进球数
SELECT stadium, COUNT(player) FROM game LEFT JOIN goal ON id = matchid GROUP BY stadium;
-
波兰参与的比赛,列出其比赛编号,日期和进球数
SELECT matchid, mdate, COUNT(player) FROM game JOIN goal ON matchid = id AND (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid, mdate;
-
德国参与的比赛,列出比赛编号,日期以及德国队的进球数
SELECT matchid, mdate, COUNT(player) FROM game JOIN goal ON matchid = id AND (team1 = 'GER' OR team2 = 'GER') AND teamid = 'GER' GROUP BY matchid, mdate;
-
List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.(使用case when 列出每场比赛的球队及其进球数)
mdate team1 score1 team2 score2 1 July 2012 ESP 4 ITA 0 10 June 2012 ESP 1 ITA 1 10 June 2012 IRL 1 CRO 3 … Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.
SELECT mdate,team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1, team2, SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) score2 FROM game LEFT JOIN goal ON matchid = id GROUP BY mdate, matchid, team1, team2;
JOIN Quiz测验题
game:
id | mdate | stadium | team1 | team2 |
---|---|---|---|---|
1001 | 8 June 2012 | National Stadium, Warsaw | POL | GRE |
1002 | 8 June 2012 | Stadion Miejski (Wroclaw) | RUS | CZE |
1003 | 12 June 2012 | Stadion Miejski (Wroclaw) | GRE | CZE |
1004 | 12 June 2012 | National Stadium, Warsaw | POL | RUS |
… |
goal:
matchid | teamid | player | gtime |
---|---|---|---|
1001 | POL | Robert Lewandowski | 17 |
1001 | GRE | Dimitris Salpingidis | 51 |
1002 | RUS | Alan Dzagoev | 15 |
1001 | RUS | Roman Pavlyuchenko | 82 |
… |
eteam:
id | teamname | coach |
---|---|---|
POL | Poland | Franciszek Smuda |
RUS | Russia | Dick Advocaat |
CZE | Czech Republic | Michal Bilek |
GRE | Greece | Fernando Santos |
… |
-
You want to find the stadium where player ‘Dimitris Salpingidis’ scored. (Dimitris Salpingidis 取得进球的球馆)Select the JOIN condition to use:
game JOIN goal ON (id=matchid)
-
You JOIN the tables goal and eteam in an SQL statement.(表goal和表eteam的连接) Indicate the list of column names that may be used in the SELECT line:
matchid, teamid, player, gtime, id, teamname, coach
-
Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).(显示对阵希腊取得进球的球员名,队名以及进球数)
SELECT player, teamid, COUNT(*) FROM game JOIN goal ON matchid = id WHERE (team1 = "GRE" OR team2 = "GRE") AND teamid != 'GRE' GROUP BY player, teamid
-
Select the result that would be obtained from this code:
SELECT DISTINCT teamid, mdate FROM goal JOIN game on (matchid=id) WHERE mdate = '9 June 2012'
DEN 9 June 2012 GER 9 June 2012 -
Select the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw.(在National Stadium, Warsaw.球馆中对阵波兰时取得进球的球员及其球队)
SELECT DISTINCT player, teamid FROM game JOIN goal ON matchid = id WHERE stadium = 'National Stadium, Warsaw' AND (team1 = 'POL' OR team2 = 'POL') AND teamid != 'POL'
-
Select the code which shows the player, their team and the time they scored, for players who have played in Stadion Miejski (Wroclaw) but not against Italy(ITA).(在Stadion Miejski (Wroclaw)球馆取得进球(排除对阵意大利的比赛)的球员的名称,球队以及进球时间)
SELECT DISTINCT player, teamid, gtime FROM game JOIN goal ON matchid = id WHERE stadium = 'Stadion Miejski (Wroclaw)' AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))
-
Select the result that would be obtained from this code:
SELECT teamname, COUNT(*) FROM eteam JOIN goal ON teamid = id GROUP BY teamname HAVING COUNT(*) < 3
Netherlands 2 Poland 2 Republic of Ireland 1 Ukraine 2