Old JOIN Tutorial
The Table Tennis Olympics Database
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
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
|
|
|
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
|
|
|
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'
The next tutorial about the Movie database involves some slightly more complicated joins.