SQLZOO:The JOIN operation/zh & Old JOIN Tutorial 笔记

The JOIN operation/zh

欧洲国家杯赛事资料

各表关系：

赛事表：

id 编号
mdate 日期
stadium 场馆
team1 队伍1
team2 队伍2

入球表：

matchid 赛事编号
teamid 队伍编号
player 入球球员
gtime 入球时间

欧洲队伍表：

id 编号
teamname 队名
coach 教练

联接查询

1、列出赛事编号matchid 和球员名 player ,该球员代表德国队Germany入球的。找出德国队球员: teamid = ‘GER’

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

2、由以上查詢，可知Lars Bender’s 于赛事1012入球。想知道此赛事的对方队伍

goal 表格中的matchid ，对应表格game的id。在表格 game中找出赛事1012的资料。

只显示赛事1012的 id, stadium, team1, team2

SELECT id,stadium,team1,team2
FROM game
WHERE id = (SELECT matchid FROM goal 
            WHERE player = 'Lars Bender')

3、用JOIN同时进行以上两个步骤。

SELECT * FROM game JOIN goal ON (id = matchid)

FROM 表示合并两个表格game和goal的数据。ON 表示如何找出 game中每一列应该配对goal中的哪一列 – goal的 id 必须配对game的matchid 。

ON (game.id = goal.matchid)

显示每一个德国入球的球员名，队伍名，场馆和日期。

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

4、列出球员名字叫Mario (player LIKE ‘Mario%’)有入球的队伍1 team1, 队伍2 team2 和球员名 player

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

5、列出每场球赛中首10分钟gtime<=10有入球的球员 player,队伍teamid, 教练coach, 入球时间gtime

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

6、列出’Fernando Santos’作为队伍1 team1 的教练的赛事日期，和队伍名。

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

7、列出场馆 'National Stadium, Warsaw’的入球球员。

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

8、列出全部赛事，射入德国球门的球员名字。

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

9、列出队伍名称 teamname和该队入球总数

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

10、列出场馆名和在该场馆的入球数字。

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

11、每一场波兰’POL’有参与的赛事中，列出赛事编号 matchid, 日期mdate 和入球数字。

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

12、每一场德国’GER’有参与的赛事中，列出赛事编号 matchid, 日期mdate和德国的入球数字。

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

13、List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
…

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.
列出每场比赛每队的进球数。

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

---

Old JOIN Tutorial

The Table Tennis Olympics Database(乒乓球奥运数据库)


1、Show the athelete (who) and the country name for medal winners in 2000.
显示在2000年获得奖牌的运动员和国家名称。

SELECT who, country.name
FROM ttms JOIN country ON (ttms.country = country.id)
WHERE games = 2000

2、Show the who and the color of the medal for the medal winners from ‘Sweden’.
显示“瑞典”获奖的运动员和奖牌类型。

SELECT who, color
FROM ttms JOIN country ON (country = id)
WHERE name = 'Sweden'

3、Show the years in which ‘China’ won a ‘gold’ medal.
显示“中国”赢得了金牌的年份。

SELECT DISTINCT games
FROM ttms JOIN country ON (country = id)
WHERE color = 'gold'
AND name = 'China'

---

Women’s Singles Table Tennis Olympics Database（女子单打乒乓球奥运数据库）

4、Show who won medals in the ‘Barcelona’ games.
显示谁在“巴塞罗那”获得奖牌。

SELECT who
FROM ttws JOIN games ON (ttws.games = games.yr)
WHERE city = 'Barcelona'

5、Show which city ‘Jing Chen’ won medals. Show the city and the medal color.
显示陈静获得奖牌的城市和奖牌类型。

SELECT city, color
FROM games JOIN ttws ON (yr = games)
WHERE who = 'Jing Chen'

6、Show who won the gold medal and the city.
显示谁赢得了金牌和比赛城市。

SELECT who, city
FROM ttws JOIN games ON (games = yr)
WHERE color = 'gold'

---

Table Tennis Mens Doubles（乒乓球男双）

7、Show the games and color of the medal won by the team that includes ‘Yan Sen’.
显示由包括Yan Sen的团队获奖的比赛和奖牌类型。

SELECT games, color
FROM ttmd JOIN team ON (team = id)
WHERE name = 'Yan Sen'

8、Show the ‘gold’ medal winners in 2004.
显示2004年奖牌获得者。

SELECT name
FROM team JOIN ttmd ON (id = team)
WHERE games = 2004 AND color = 'gold'

9、Show the name of each medal winner country ‘FRA’.
显示FRA的每个奖牌获得者。

SELECT name
FROM team JOIN ttmd ON (id = team)
WHERE country = 'FRA'