SQLZOO:Old JOIN Tutorial

2.Show the who and the color of the medal for the medal winners from ‘Sweden’.

ttms(games,color,who,country)
country(id,name)

select who,color
from ttms join country on country=id
where name='sweden';

3.Show the years in which ‘China’ won a ‘gold’ medal.

ttms(games,color,who,country)
country(id,name)

select games
from ttms join country on country=id
where name='china'
and color='gold';

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

ttws(games,color,who,country)
games(yr,city,country)

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.

ttws(games,color,who,country)
games(yr,city,country)

在这里插入代码片

6.Show who won the gold medal and the city.

ttws(games,color,who,country)
games(yr,city,country)

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

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

ttmd(games,color,team,country)
team(id,name)

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

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

ttmd(games,color,team,country)
team(id,name)

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

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

ttmd(games,color,team,country)
team(id,name)

select name
from ttmd join team on team=id
where country='fra';