SQLZOO：The JOIN operation

 数据表：game-gaol-eteam

Q1

Show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'

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

Q2

Show id, stadium, team1, team2 for just game 1012

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

Q3

Show the player, teamid, stadium and mdate for every German goal.

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

JOIN

SQL JOIN 子句用于把来自两个或多个表的行基于共同字段结合

SQL INNER JOIN： 从多个表中返回满足 JOIN 条件的所有行

SELECT column1, column2, ...
FROM table1
JOIN table2 ON condition

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行

Q4

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

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

Q5

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

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

Q6

List 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'

Q7

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

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

Q8

The example query shows all goals scored in the Germany-Greece quarterfinal.

Instead show the name of all players who scored a goal against Germany.

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

Q9

Show teamname and the total number of goals scored.

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

Q10

Show the stadium and the number of goals scored in each stadium.

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

Q11

For every match involving 'POL', show the matchid, date and the number of goals scored.

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

Q12

For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

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

Q13

List every match with the goals scored by each team as shown. This will use "CASE WHEN"

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 mdate,matchid,team1,team2

CASE WHEN

CASE WHEN条件表达式函数类似IF ELSE语句 

CASE WHEN condition THEN result
 [WHEN...THEN...]
 ELSE result
 END

外连接分为左连接和右连接

左连接

select staff.name,deptname from staff left join deptno onstaff.name=deptno.name;

包含左边表中所有的记录，右边表中没有匹配的记录显示为 NULL

右连接

select deptname,deptno.name from staff right join deptno ondeptno.name=staff.name;

包含右边表中所有的记录，左边表中没有匹配的记录显示为 NULL