[MySQL] SQL ZOO JOIN 답 해설

The JOIN operation - SQLZOO

문제의 이름이 없어서 내가 맘대로 적었다. 

기본 정보

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

eteam -> id, teamname, coach

 

합칠 수 있는 정보

game.id -- goal.matchid

game.team1, game.team2 -- eteam.id

goal.teamid -- eteam.id

 

#1 독일 팀 득점

teamid 값이 독일인 데이터를 찾아 출력 

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

 

#2 1012 ID값 

1012라는 id값을 가진 데이터를 찾아 출력

SELECT id,stadium,team1,team2
FROM game
WHERE id = '1012'

 

#3 독일 팀의 정보

game -> id, mdate, stadium, team1, team2

goal -> matchid, teamid, player, gtime

teamid값이 독일인 데이터의 goal 테이블에 접근 해야한다.

player, teamid는 goal 테이블 칼럼. 

stadium, mdate는 game 테이블 칼럼.

LEFT JOIN으로 합치고 WHERE문에서 teamid값이 독일인 조건을 추가한다.

SELECT player,teamid, stadium, mdate 
FROM game 
LEFT JOIN goal 
ON (id=matchid)
WHERE teamid = 'GER'

 

#4 Mario선수의 득점 골

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

player가 Mario인 선수를 찾아 team1, team2, player을 출력한다.

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

 

#5 gtime이 10보다 작거나 같다

goal   -> matchid, teamid, player, gtime

eteam -> id, teamname, coach

gtime <= 10인 player, teamid, coach, gtime을 출력한다.

SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
FROM goal 
LEFT JOIN eteam
ON (teamid = id)
WHERE goal.gtime <= 10

 

#6 Fernando Santos코치의 팀 이름

game -> id, mdate, stadium, team1, team2

eteam -> id, teamname, coach

coach가 Fernando Santos인 mdate, teamname을 출력한다.

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

 

#7 National Stadium, Warsaw경기장의 선수

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

stadium이 National Stadium, Warsaw인 player을 출력한다.

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

 

#8 More difficult questions

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

독일을 상대로 골을 넣은 모든 선수의 이름을 출력해야한다.

game 중에 team1 or team2가 독일일 경우에 독일과 상대한 경기를 알 수 있지만, 곧바로 player을 출력하면 안된다.

그렇게 되면 독일 팀이 골을 넣은 것도 포함되어 있기 때문에 독일 팀을 제외시켜야 한다.

goal 중에 teamid가 독일이 아닌 조건을 추가로 걸어주어야 한다.

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

 

#9 More difficult questions

goal   -> matchid, teamid, player, gtime

eteam -> id, teamname, coach

각 팀의 총 골 수를 표시해야 한다. 

teamname으로 그룹화 시켜주고 데이터를 카운트 해주면 된다.

goal의 각 칼럼의 데이터 개수는 곧 골의 수를 의미하기 때문에 전체를 카운트 해주어도 문제가 없다.

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

 

#10 More difficult questions

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

game에 goal을 LEFT JOIN을 사용하면 데이터 수 오류가 난다. 

하지만 goal에 game을 LEFT JOIN하면 오류가 안난다. game 데이터 중에 널 값이 있는게 아닐까 싶다..

어쨌든 stadium으로 그룹화 시켜주고 데이터를 카운트 해주면 된다.

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

 

#11 More difficult questions

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

조금 중요한 문제다. 

goal에는 matchid가 중복된 값이 들어 있고

gmae에는 mdate가 중복된 값이 들어 있다.

matchid만 그룹화시켜주고 mdate를 선택하려면 선택할 mdate가 여러개 생겨버린다. 왜냐면 같은 mdate에 넣은 골이 존재하기 때문에 어떤 mdate를 선택할지 모르기 때문이다.

그래서 둘다 그룹화를 시켜주어야 한다.

POL과 관련된 팀은 WHERE절에서 찾으면 된다.

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

 

#12 More difficult questions

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

위 문제랑 비슷한데 WHERE절에서 골을 넣은 선수의 팀id값이 독일인 팀을 찾아내면 끝이다.

SELECT matchid,mdate, COUNT(*)
FROM game 
JOIN goal 
ON matchid = id 
WHERE teamid = 'GER'
GROUP BY mdate, matchid

 

#13 More difficult questions

game -> id, mdate, stadium, team1, team2

goal   -> matchid, teamid, player, gtime

좀 복잡한 문제다.

각 경기마다 팀1이 득점한 수와 팀2가 득점한 수를 출력하면 된다.

matchid값으로 그룹화 시켜주고 ,경기 데이터마다의 SELECT문에 들어갈 mdate, team1, team2 를 기본으로 그룹화 시켜준다. 

이제 카운트를 해주어야 하는데, 문제가 있다. 골이 어떤 팀이냐에 따라서 점수1 카운트가 되고 점수2 카운트가 된다. 

이때 CASE WHEN문을 사용해서 teamid값이 team1이면 1을 아니면 0을 넣고 그 합을 구해준다. 

team2도 똑같이 진행하면 된다. 

CASE WHEN에 SUM까지 써야하는 복잡한 문제다. 솔직히 못풀어서 구글링했당.

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

 

정리하기

나중에 하겠다.

패쓰~~