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

Summary

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

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

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 (ttms.country=country.id)
 WHERE country.name = 'Sweden'

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 (ttms.country=country.id)
WHERE name='China' AND color='gold'

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

Show who won medals in the 'Barcelona' games.

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

SELECT who, city
  FROM ttws JOIN games
            ON (ttws.games=games.yr)
  WHERE city = 'Seoul'

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

Show which city 'Jing Chen' won medals. Show the city and the medal color.

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

SELECT city, color
  FROM ttws JOIN games
            ON (ttws.games=games.yr)
  WHERE who = 'Jing Chen'

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 (ttws.games=games.yr)
  WHERE color = 'gold'

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

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

Show the 'gold' medal winners in 2004.

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

SELECT name
  FROM ttmd JOIN team ON team=team.id
 WHERE color='gold' AND games=2004

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=team.id
 WHERE country = 'FRA'

Clear your results

JOIN Quiz

The next tutorial about the Movie database involves some slightly more complicated joins.