SQL练习-sqlzoo

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

SELECT matchid,player FROM goal
WHERE teamid = ‘GER’

2 只顯示賽事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=‘National Stadium, Warsaw’

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

SELECT distinct player
FROM game JOIN goal ON matchid = id
where teamid !=‘GER’ and (team1='GER’or team2=‘GER’)

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

SELECT teamname,count(player)
FROM eteam JOIN goal ON id=teamid
group BY teamname

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

SELECT stadium,count(player)
FROM game JOIN goal ON id=matchid
group BY stadium

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

SELECT matchid,mdate, count(player)
FROM game JOIN goal ON matchid = id
WHERE (team1 = ‘POL’ OR team2 = ‘POL’)
group by matchid,mdate
注意：分组编号和日期

12 每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

SELECT matchid,mdate, count(player)
FROM game JOIN goal ON matchid = id
WHERE (team1 = ‘GER’ OR team2 = ‘GER’) and teamid=‘GER’
group by matchid,mdate

13 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