SQLZOO JOIN/联结 答案

ZOO 联结 歐洲國家盃 练习1

1.修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = ‘GER’

SELECT matchid, player FROM goal
WHERE teamid = 'GER';

2.由以上查詢，你可見Lars Bender’s 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
只顯示賽事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2 FROM game
WHERE id = 1012

3.顯示每一個德國入球的球員名，隊伍名，場館和日期

SELECT g2.player, g2.teamid, g1.stadium, g1.mdate
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
WHERE g2.teamid = 'GER';

4.列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

SELECT g1.team1, g1.team2, g2.player
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
WHERE g2.player LIKE 'Mario%';

5.列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT g.player, g.teamid, e.coach, g.gtime
FROM eteam e JOIN goal g ON e.id = g.teamid
WHERE g.gtime <= 10;

6.列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期，和隊伍名。

SELECT g.mdate, e.teamname
FROM eteam e JOIN game g ON e.id = g.team1
WHERE e.coach = 'Fernando Santos';

7.列出場館 'National Stadium, Warsaw’的入球球員。

SELECT g2.player
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
WHERE g1.stadium =  'National Stadium, Warsaw';

8.修改它，只列出全部賽事，射入德國龍門的球員名字。

SELECT DISTINCT g2.player
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
WHERE g2.teamid != 'GER'
AND (g1.team1 = 'GER' OR g1.team2 = 'GER');

9.列出隊伍名稱 teamname 和該隊入球總數

SELECT e.teamname, COUNT(g.player)
FROM eteam e JOIN goal g ON e.id = g.teamid
GROUP BY e.teamname;

10.列出場館名和在該場館的入球數字。

SELECT g1.stadium, COUNT(g2.player)
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
GROUP BY g1.stadium;

11.每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。