The JOIN operation

http://sqlzoo.net/3b.htm

The Table Tennis Olympics Database

 

1a,Show the athelete (who) and the country name for medal winners in 2000.

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

 

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 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 color='gold' and id='CHN'

 

Women's Singles Table Tennis Olympics Database

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 medal color.

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

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

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

Table Tennis Mens Doubles

3a,Show the games and color of the medal won by the team that includes 'Yan Sen'.

select games,color from ttmd join team on ttmd.team=team.id where name='Yan Sen'

 

3b,Show the 'gold' medal winners in 2004.

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

 

3c,Show the name of each medal winner country 'FRA'.

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