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. |
|
|
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 ).
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
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
..
..
..
2a. 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'
country(id, name)
SELECT who, country.name
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE games = 2000
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. |
|
|
|
2a. 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'