sqlzoo练习记录（The JOIN operation）

sqlzoo练习记录（The JOIN operation）

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

2.在表格 game中找出賽事1012的資料。
只顯示賽事1012的 id, stadium, team1, team2
SELECT id,stadium,team1,team2
FROM game
WHERE id=‘1012’;

3.顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid=‘GER’;

4.列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
SELECT team1,team2,player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE ‘Mario%’;

5.列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime
SELECT player, teamid, coach,gtime
FROM goal JOIN eteam ON teamid=id
WHERE gtime<=10;

6.列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期，和隊伍名。
SELECT mdate,teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach=‘Fernando Santos’;

7.列出場館 'National Stadium, Warsaw’的入球球員。
SELECT player
FROM game JOIN goal ON (id=matchid)
WHERE stadium IN(‘National Stadium, Warsaw’);

8.找非德國球員的入球，德國可以在賽事中作team1 隊伍１（主）或team2隊伍２（客）。 你可以用teamid!=‘GER’ 來防止列出德國球員。 你可以用DISTINCT來防止球員出現兩次以上。
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE teamid !=‘GER’ AND ( team1=‘GER’ OR team2=‘GER’);

9.列出隊伍名稱 teamname 和該隊入球總數SELECT teamname, COUNT(*)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname ;

10.列出場館名和在該場館的入球數字。
SELECT stadium ,COUNT(*)
FROM game JOIN goal ON(id=matchid)
GROUP BY stadium;

11.每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,COUNT(*)
FROM game JOIN goal ON matchid =id
WHERE (team1 = ‘POL’ OR team2 = ‘POL’)
GROUP BY goal.matchid,game.mdate;

12.每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
SELECT matchid,mdate,COUNT(teamid)
FROM game JOIN goal ON (matchid=id)
WHERE (team1=‘GER’ OR team2=‘GER’) AND teamid=‘GER’
GROUP BY goal.matchid,game.mdate;

13.列出每场赛事两队得分
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;

quiz:
1.You want to find the stadium where player ‘Dimitris Salpingidis’ scored. Select the JOIN condition to use:

2. You JOIN the tables goal and eteam in an SQL statement. Indicate the list of column names that may be used in the SELECT line:
   

3.Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).

4.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’

5.Select the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw.

6.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).

7.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