SqlZoo.net习题答案：How to do joins.【Table Tennis】

 

表结构：　　ttms(games, color, who, country)
　　　　　　country(id, name)

　　　　　　ttms.county = county.id

 

1b.Show the who and the color of the medal for the medal winners from 'Sweden'.

select who, color
from ttms join country
on (ttms.country = country.id)
where country.name = 'Sweden'

 

1c.Show the years in which 'China' won a 'gold' medal.

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

 

 

 

表结构：　　ttws(games, color, who, country)
　　　　　　games(yr, city, country)

　　　　　　ttws.games = games.yr

 

2a.Show who won medals in the 'Barcelona' games.

select who
from ttws join games
on (ttws.games=games.yr)
where city = 'Barcelona'

 

2b.Show which city 'Jing Chen' won medals. Show the city and the medalcolor.

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

 

2c.Show who won the gold medal and the city.

select ttws.who, games.city
from ttws join games
on (ttws.games = games.yr)
where ttws.color = 'gold'