6. List the 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'
8. Instead show the name of all players who scored a goal against Germany.
SELECT distinct(player)
FROM game JOIN goal ON matchid = id
WHERE (teamid!='ger'and team1='Ger')or (teamid!='ger'and team2='Ger')
11. For every match involving 'POL', show the matchid, date and the number of goals scored.
SELECT matchid,mdate,count(gtime)
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'
SELECT matchid,mdate,count(gtime)
FROM game JOIN goal ON matchid = id
WHERE teamid = 'ger'
group by matchid,mdate
13. List every match with the goals scored by each team as shown.
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,team1,team2
注意 join, left join的区别,因为这里有两场比赛没有得分,所以goal表里没有他们的信息,如果用join就会有缺失