Old JOIN Tutorial

From SQLZOO
Revision as of 11:57, 30 July 2012 by Marek (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Contents

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.

<img src='pics/ttms.png'/>
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.

<img src='pics/ttws.png'/>
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.
<img src='pics/ttmd.png'/>
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.

Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense