【SQL練習】【SQLZOO】 6 JOIN

6 JOIN

問題はこちらです。
The JOIN operation/ja - SQLZOO

以下が解答となります。

JOIN と UEFA EURO 2012

1.

【解答】

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

2.

【解答】

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.

【解答】

SELECT team1, team2 , player
FROM game JOIN goal ON (id=matchid) 
WHERE player LIKE 'Mario%';

5.

【解答】

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

6.

【解答】

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

7.

【解答】

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

8.

【解答】

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

9.

【解答】

SELECT teamname, COUNT(1)
FROM eteam JOIN goal ON id = teamid
GROUP BY teamname;

10.

【解答】

SELECT stadium, COUNT(1)
FROM game JOIN goal ON id = matchid
GROUP BY stadium;

11.

【解答】

SELECT matchid, mdate, COUNT(1)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid, mdate;

12.

【解答】

SELECT matchid, mdate, COUNT(1)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'GER' OR team2 = 'GER')  
AND teamid = 'GER' GROUP BY matchid, mdate;

13.

【解答】

SELECT mdate, 
team1, SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1, 
team2, SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 
FROM game 
LEFT JOIN goal ON id = matchid
GROUP BY id, game.mdate, goal.matchid, game.team1, game.team2
ORDER BY mdate, matchid, team1, team2