sqlzooで詰まった問題

sqlzooをやってみたので，詰まった問題についてまとめておく．

JOIN(1)

問題：ドイツチームの全ゴールのmatchidとplayerを取得する
回答：最初はSELECT matchid, player FROM goal INNER JOIN eteam ON teamid = 'GER'というクエリを書いた．そもそもJOINのONには結合条件を指定するのが普通であり，絞り込み条件を指定している時点で間違っている．正解はSELECT matchid, player FROM goal INNER JOIN eteam ON teamid = eteam.id WHERE teamid = 'GER'になる．このクエリだと「先にgoalテーブルとeteamテーブルを結合してからteamid='GER'で絞り込みをかける」という意味になる．

JOIN(8)

問題：ドイツと試合をしてゴールを決めた選手の名前を取得する
回答：最初はSELECT player FROM goal INNER JOIN game ON goal.matchid = game.id WHERE (team1 = 'GER' AND teamid != 'GER') OR (team2 = 'GER' AND teamid != 'GER')と書いた．しかし，このクエリだと「ドイツと試合して2回以上ゴールした選手」の名前をゴールした回数分だけ出力してしまう．SELECT DISTINCT player ...とすることで，選手名が重複しないようにできる．

JOIN(9)

問題：各チームのチーム名とゴール数を取得する
回答：GROUP BYを使うのが肝．GROUP BYは指定したカラムの値が重複するレコードをまとめて出力する．指定したカラム以外のカラムの値が異なり，かつそれを出力しようとするとエラーになる（異なるカラムが存在する以上，まとめることができないため）．回答はSELECT teamname, COUNT(teamname) FROM goal INNER JOIN eteam ON goal.teamid = eteam.id GROUP BY teamnameになる．

JOIN(11)

問題：ポーランドが参戦している全試合のmatchid, mdate, ゴール数を取得する
回答：おおまかな方針はすぐに分かったので簡単かと思ったが，意外とハマった．GROUP BYでmatchidを指定していたことでmdateが出力できなかった（mdateでGROUP BYすると今度はmatchidが出力できなくなる）．最終的にはGLOUP BYに複数カラムを指定できることが分かり，無事解決した．回答はSELECT matchid, mdate, COUNT(matchid) FROM goal INNER JOIN game ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid, mdateになった．グループ化というのは特定の情報以外の情報を排除することなので，あくまで集計用に使うものだなあと思った．

JOIN(12)

問題：ドイツが参戦した試合のmatchid, mdate, ゴール数を取得する
回答：ほとんど11と同じだが，ドイツのゴールだけをカウントする必要がある．よって，WHEREでgoal.teamid = 'GER'という条件を追加してやればよい．

JOIN(13)

問題：各試合ごとに各チームの得点を取得する
回答：SUMをどう使えばよいのかが分からなかった．SUMの中にCASE WHEN式を書いて，それをscore1, score2とすることで解決した．ただ，末尾のGROUP BYがなぜ必要なのかはまだ理解できていない．