Old JOIN Tutorial - Revision history

Marek: Created page with "==The Table Tennis Olympics Database==

The table `ttms` shows the Olympic medal winners for Table Tennis (Men's Singles). The co..."
2012-07-30T11:57:45Z

Created page with "==The Table Tennis Olympics Database== <table class='NoBorder'><tr><td> <p>The table <code>ttms</code> shows the Olympic medal winners for Table Tennis (Men's Singles). The co..."

New page
==The Table Tennis Olympics Database==
<table class='NoBorder'><tr><td>
<p>The table <code>ttms</code> 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 <code>country</code> name you must JOIN this table to the <code>country</code> table.</p>
<table><tr><td>
<p>The two tables <code>country</code> and <code>ttms</code> are ONE TO MANY. One country has many winners, each winner has only one country.</p>
</td><td> <img src='pics/ttms.png'/></td></tr></table>

</td><td>
<table class='Bordered' style='' border='1'><caption>ttms</caption><tr>
<th>games</th> <th>color</th> <th>who</th> <th class='key'>country</th> </tr>
<tr>
<td align='right'>1988</td> <td>gold</td> <td>Yoo Nam-Kyu</td> <td class='key'>KOR</td> </tr>
<tr> <td align='right'>1988</td> <td>silver</td> <td>Kim Ki Taik</td> <td class='key'>KOR</td> </tr>
<tr><td>..</td><td>..</td><td>..</td><td class='key'>..</td></tr>
</table>

</td><td>
<table class='Bordered' style='' border='1'><caption>country</caption><tr>
<th class='key'>id</th> <th>name</th> </tr>
<tr> <td class='key'>ALG</td> <td>Algeria</td> </tr>
<tr> <td class='key'>ARG</td> <td>Argentina</td> </tr>
<tr> <td class='key'>...</td> <td>...</td> </tr>
<tr> <td class='key'>KOR</td> <td>Korea</td> </tr>
<tr><td class='key'>..</td><td>..</td></tr>
</table>
</td></tr>
</table>

<h2>How to do joins</h2>
<p>The phrase <code>FROM ttms JOIN country ON ttms.country=country.id</code> represents the join of the tables <code>ttms</code> and <code>country</code>. This <code>JOIN</code> has one row for every medal winner. In addition to the <code>ttms</code> fields <code>(games, color,</code> who and <code>country</code>) it includes the details of the corresponding <code>country (id, name )</code>.

<div class="progress_panel"><div>
<div class="summary">Summary</div>
<div class="progressbarbg">
<div class="progressbar"></div>
</div>
</div></div>

<div class='qu'>
Show the athelete <code>(who)</code> and the country name for medal winners in 2000.
ttms('''games,color,'''who,country)
country('''id''',name)
<source lang='sql' class='def'>
SELECT who, country.name
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE games = 2000
</source>

<source lang='sql' class='ans'>
SELECT who, country.name
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE games = 2000
</source>
</div>

<div class='qu'>
Show the who and the color of the medal for the medal winners from 'Sweden'.
ttms('''games,color,'''who,country)
country('''id''',name)
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT who, color
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE country.name = 'Sweden'
</source>
</div>

<div class='qu'>
Show the years in which 'China' won a 'gold' medal.

ttms('''games,color,'''who,country)
country('''id''',name)
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT games
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE name='China' AND color='gold'
</source>
</div>

<h2>Women's Singles Table Tennis Olympics Database</h2>
<table class='NoBorder'><tr><td>
<table><tr><td>
The Summer Olympic games are held every four years in a different city. The table <code>games</code> shows which city the games were held in.
The Women's Single's winners are in the table <code>ttws</code>.
</td><td> <img src='pics/ttws.png'/> </td></tr></table>
</td><td>

<table class='Bordered' style='' border='1'><caption>ttws</caption><tr>
<th class='key'>games</th> <th>color</th> <th>who</th> <th>country</th> </tr>
<tr> <td align='right' class='key'>1988</td> <td>gold</td> <td>Jing Chen</td> <td>CHN</td> </tr>
<tr> <td align='right' class='key'>1988</td> <td>silver</td> <td>Li Hui-Fen</td> <td>CHN</td> </tr>
<tr><td class='key'>..</td><td>..</td><td>..</td><td>..</td></tr>
</table>

</td><td>

<table class='Bordered' style='' border='1'><caption>games</caption><tr>
<th class='key'>yr</th> <th>city</th> <th>country</th> </tr>
<tr> <td align='right' class='key'>1988</td> <td>Seoul</td> <td>KOR</td> </tr>
<tr> <td align='right' class='key'>1992</td> <td>Barcelona</td> <td>ESP</td> </tr>
<tr><td class='key'>..</td><td>..</td><td>..</td></tr>
</table>
</td></tr>
</table>

<div class='qu'>
Show who <code>won</code> medals in the 'Barcelona' games.

ttws('''games,color,'''who,country)
games('''yr''',city,country)
<source lang='sql' class='def'>
SELECT who, city
FROM ttws JOIN games
ON (ttws.games=games.yr)
WHERE city = 'Seoul'
</source>

<source lang='sql' class='ans'>
SELECT who
FROM ttws JOIN games
ON (ttws.games=games.yr)
WHERE city = 'Barcelona'
</source>
</div>

<div class='qu'>
Show which city 'Jing Chen' won medals. Show the <code>city</code> and the medal <code>color</code>.
ttws('''games,color,'''who,country)
games('''yr''',city,country)
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT city, color
FROM ttws JOIN games
ON (ttws.games=games.yr)
WHERE who = 'Jing Chen'
</source>
</div>

<div class='qu'>
Show <code>who</code> won the gold medal and the <code>city</code>.

ttws('''games,color,'''who,country)
games('''yr''',city,country)
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT who, city
FROM ttws JOIN games
ON (ttws.games=games.yr)
WHERE color = 'gold'
</source>
</div>

<h2>Table Tennis Mens Doubles</h2>
<table class='NoBorder'><tr><td>
<table><tr><td>
The Table Tennis Mens Double teams are stored in the table <code>team</code>.
Each team has an arbitrary number that is referenced from the table <code>ttmd</code>.
</td><td> <img src='pics/ttmd.png'/> </td></tr></table>
</td><td>

<table class='Bordered' style='' border='1'><caption>ttmd</caption>
<tr> <th>games</th> <th>color</th> <th class='key'>team</th> <th>country</th> </tr>
<tr> <td align='right'>1988</td> <td>gold</td> <td align='right' class='key'>1</td> <td>CHN</td> </tr>
<tr> <td align='right'>1988</td> <td>silver</td> <td align='right' class='key'>2</td> <td>YUG</td> </tr>
<tr> <td align='right'>..</td> <td>..</td> <td align='right' class='key'>..</td> <td>..</td> </tr>
</table>

</td><td>

<table class='Bordered' style='' border='1'><caption>team</caption>
<tr> <th class='key'>id</th> <th style='width:25ex'>name</th> </tr>
<tr> <td align='right' class='key'>1</td> <td>Long-Can Chen</td> </tr>
<tr> <td align='right' class='key'>1</td> <td>Qing-Guang Wei</td> </tr>
<tr> <td align='right' class='key'>2</td> <td>Ilija Lupulesku</td> </tr>
<tr> <td align='right' class='key'>2</td> <td>Zoran Primorac</td> </tr>
<tr> <td align='right' class='key'>..</td> <td>..</td> </tr>
</table>

</td></tr>
</table>

<div class='qu'>
Show the games and color of the medal won by the team that includes 'Yan Sen'.
ttmd('''games,color,'''team,country)
team('''id,,name''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT games, color
FROM ttmd JOIN team ON team=team.id
WHERE name = 'Yan Sen'
</source>
</div>

<div class='qu'>
Show the 'gold' medal winners in 2004.

ttmd('''games,color,'''team,country)
team('''id,,name''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT name
FROM ttmd JOIN team ON team=team.id
WHERE color='gold' AND games=2004
</source>
</div>

<div class='qu'>
Show the <code>name</code> of each medal winner country 'FRA'.

ttmd('''games,color,'''team,country)
team('''id,,name''')
<source lang='sql' class='def'>
</source>

<source lang='sql' class='ans'>
SELECT name
FROM ttmd JOIN team ON team=team.id
WHERE country = 'FRA'
</source>
</div>

<div class="lsclear">Clear your results</div>

[[JOIN Quiz]]</p>
<p>[http://sqlzoo.net/w/index.php/More_JOIN_operations The next tutorial about the Movie database involves some slightly more complicated joins.]</p>