6.2 Old JOIN Tutorial.md

Old JOIN Tutorial

Link to the page - https://sqlzoo.net/wiki/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.

The two tables country and ttms are ONE TO MANY. One country has many winners, each winner has only one country.

ttms

games	color	who	country
1988	gold	Yoo Nam-Kyu	KOR
1988	silver	Kim Ki Taik	KOR
..	..	..	..

country

id	name
ALG	Algeria
ARG	Argentina
...	...
KOR	Korea
..	..

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 ).

1.

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

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

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

who	name
Liu Guoliang	China
Kong Linghui	China
Jan-Ove Waldner	Sweden

2.

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'

who	color
Eric Lindh	bronze
Jan-Ove Waldner	silver

3.

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

• Note this problem can be solved without JOIN

select DISTINCT games
from ttms JOIN country ON id=country
where name = 'china' and color = 'gold';

-- OR

select DISTINCT games
from ttms
where country = 'CHN' and color = 'gold';

games
1992
1996
2000
2008

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.

ttws

games	color	who	country
1988	gold	Jing Chen	CHN
1988	silver	Li Hui-Fen	CHN
..	..	..	..

games

yr	city	country
1988	Seoul	KOR
1992	Barcelona	ESP
..	..	..

4.

Show who won medals in the 'Barcelona' games.

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

SELECT who as winner
  FROM ttws JOIN games
            ON (ttws.games=games.yr)
  WHERE city = 'Barcelona'

winner
Hyun Jung Hwa
Li Bun Hui
Yaping Deng
Hong Qiao

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'

city	color
Seoul	gold
Atlanta	silver
Sydney	bronze

6.

Show who won the gold medal and the city.

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

who	city
Jing Chen	Seoul
Yaping Deng	Barcelona
Yaping Deng	Atlanta
Wang Nan	Sydney
Zhang Yining	Athens
Zhang Yining	Beijing

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.

ttmd

games	color	team	country
1988	gold	1	CHN
1988	silver	2	YUG
..	..	..	..

team

id	name
1	Long-Can Chen
1	Qing-Guang Wei
2	Ilija Lupulesku
2	Zoran Primorac
..	..

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'

games	color
2000	gold

8.

Show the 'gold' medal winners in 2004.

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

name
Lin Ma
Qi Chen

9.

Show the name of each medal winner country 'FRA'.

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

name
Jean-Philippe Gatien
Patrick Chila