SQLzoo.net |
 |
Quick
Ref.
Functions
Data Types
| 卓球オリンピックデータベース |
テーブル
ttmsはオリンピック卓球（男子シングル）のメダリストを表します。
メダリストの国は３文字で表します。国名称を取得する場合は、
テーブルcountryをJOINしなければなりません。
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.
|
|
|
次のFROM ttms JOIN country ON
ttms.country=country.idはテーブルttmsと
countryの結合を表します。
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 ).
1a. Show the athelete ( who) and the
country name for medal winners in 2000.
ttms(games, color, who, country)
country(id, name) |
Results
|
1b. Show the who and the color of the medal
for the medal winners from 'Sweden'.
ttms(games, color, who, country)
country(id, name) |
Results
|
1c. Show the years in which 'China' won a 'gold' medal.
ttms(games, color, who, country)
country(id, name) |
Results
|
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) |
Results
|
2b.
Show which city 'Jing Chen' won medals. Show the
city and the medal color.
ttws(games, color, who, country)
games(yr, city, country) |
Results
|
2c.
Show who won the gold medal and the city.
ttws(games, color, who, country)
games(yr, city, country) |
Results
|
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.
|
|
|
3a.
Show the games and color of the medal won by the team
that includes 'Yan Sen'.
ttmd(games, color, team, country)
team(id, name) |
Results
|
3b.
Show the 'gold' medal winners in 2004.
ttmd(games, color, team, country)
team(id, name) |
Results
|
3c.
Show the name of each medal winner country 'FRA'.
ttmd(games, color, team, country)
team(id, name) |
Results
|
The next tutorial about the Movie database involves some slightly more complicated joins.