SQL ZOO 练习 —— Old JOIN Tutorial

学习原链接：Old JOIN Tutorial

The Table Tennis Olympics Database

The table ttms shows the Olympic medal winners for Table Tennis (Men’s Singles). The country of each winner is given by a three letter code. To get the actual country name you must JOIN this table to the country table.
(译文)表ttms显示了奥运会乒乓球（男子单打）奖牌获得者。每个获胜者的国家由三个字母组成的 id 表示。要获得实际的国家名称，必须将此表联接到国家表。
The two tables country and ttms are ONE TO MANY. One country has many winners, each winner has only one country.
（译文）country表和ttms表是一对多的关系，一个国家有多个获奖者，每一个获奖者只有一个国家。

How to do joins

The phrase FROM ttms JOIN country ON ttms.country=country.id represents the join of the tables ttms and country. This JOIN has one row for every medal winner. In addition to the ttms fields (games, color, who and country) it includes the details of the corresponding country (id, name ).
（译文）短语FROM ttms JOIN country ON ttms.contry=country。id表示表ttms和country的连接。此JOIN为每个奖牌获得者为一行。除了ttms字段（游戏、颜色、姓名和国家）之外，它还包括相应国家的详细信息（id、名称）。

1.Show the athlete (who) and the country name for medal winners in 2000.

SELECT who,name
FROM ttms JOIN country ON 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 games
FROM  ttms JOIN country ON country = id
WHERE name = 'China' AND color = 'gold'

Women’s Singles Table Tennis Olympics Database

The Summer Olympic games are held every four years in a different city. The table games shows which city the games were held in. The Women’s Single’s winners are in the table ttws.
（译文）夏季奥运会每四年在不同的城市举行一次，games表显示举行比赛的城市，女子单打获胜者在ttws表。

4.Show who won medals in the ‘Barcelona’ games.

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

5.Show which city ‘Jing Chen’ won medals. Show the city and the medal color.

SELECT city,color
FROM ttws JOIN games ON games = yr
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

The Table Tennis Mens Double teams are stored in the table team.
Each team has an arbitrary number that is referenced from the table ttmd.
(译文)这乒乓球男子双打队名team保存在team表，每个队的id对应表ttmd里的team字段

7.Show the games and color of the medal won by the team that includes ‘Yan Sen’.

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

8.Show the ‘gold’ medal winners in 2004.

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

9.Show the name of each medal winner country ‘FRA’.

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

总结：从学习效果来看，这节课适合放在 join 的第一节课学习，比较容易，对初学者友好，这篇学完后再学新的JOIN就学习效果循序渐进。