SQLZOO练习-- Old JOIN Tutorial（含题目翻译）

知识点

join on连接的基础用法

PS：这是旧版的join练习，比新版简单很多，且最多只有两个表的连接

 

数据表1

 

 

表的连接关系

• ttms.country = country.id

 

题目内容

1.Show the athelete（who）and the country name for medal winners in 2000.（查询时间为2000年的选手名字及所在国家）

select who,name
from ttms join country on ttms.country=country.id
and games = '2000'

 

2.Show the who and the color of the medal for the medal winners from 'Sweden'.（查询国家名称为'Sweden'的选手名字和奖牌颜色）

select who,color
from ttms join country on ttms.country=country.id
and name = 'Sweden'

 

3.Show the years in which 'China' won a 'gold' medal.（查询国家为 'China' ，且赢得金牌的比赛时间）

select games
from ttms join country on ttms.country=country.id
and color = 'gold'
and name = 'China'

 

数据表2

表连接关系

• ttws.games = games.yr

 

4.Show who won medals in the 'Barcelona' games.（查询比赛城市为'Barcelona' 的选手名字）

select who
from ttws join games on ttws.games = games.yr
and city = 'Barcelona'

 

5.Show which city 'Jing Chen' won medals. Show the city and the medal color.（查询选手'Jing Chen' 的比赛城市和奖牌颜色）

select city,color
from ttws join games on ttws.games = games.yr
and who = 'Jing Chen'

 

6.Show who won the gold medal and the city.（查询赢得金牌的选手名字和比赛城市）

select who,city
from ttws join games on ttws.games = games.yr
and color = 'gold'

 

数据表3

表连接关系

• ttwd.team = team.id

 

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 ttmd.team = team.id
and name = 'Yan Sen'

 

8.Show the 'gold' medal winners in 2004.（查询2004年赢得金牌的选手名字）

select name 
from ttmd join team on ttmd.team = team.id
and games = '2004'
and color = 'gold'

 

9.Show the name of each medal winner country 'FRA'.（查询城市为'FRA'的选手名字）

select name
from ttmd join team on ttmd.team = team.id
and country = 'FRA'