Written by ina on Tuesday, 13 of May , 2008 at 12:27 am
Tags: dria, IC-skins, Kassia, mysql, shakespeare, slsc, sql, sqlzoo, vsc
I get so much done when SL is offline.
And now for more SQL fun @ SQLzoo…
1b. Show the who and the color of the medal for the medal winners from ‘Sweden’
SELECT who,color FROM ttms JOIN country ON (ttms.country=country.id) WHERE country.name=’Sweden’
1c. Show the years in which ‘China’ won a ‘gold’ medal.
SELECT games FROM ttms x JOIN country y ON (x.country=y.id) WHERE y.name=’China’ AND x.color=’gold’
2b. Show which city ‘Jing Chen’ won medals. Show the city and the medal color
SELECT city, color FROM games JOIN ttws ON (ttws.games=games.yr) WHERE who=’Jing Chen’
2c. Show who won the gold medal and the city.
SELECT who, city FROM games x JOIN ttws y ON (x.yr=y.games) WHERE color=’gold’
3a. Show the games and color of the medal won by the team that includes ‘Yan Sen’.
SELECT games,color FROM ttmd JOIN team on (ttmd.team=team.id) WHERE team.name=’Yan Sen’
3b. Show the ‘gold’ medal winners in 2004.
SELECT name from team JOIN ttmd ON (ttmd.team=team.id) WHERE ttmd.games=2004 AND ttmd.color=’gold’
3c. Show the name of each medal winner country ‘FRA’.
SELECT name FROM team JOIN ttmd on (ttmd.team=team.id) WHERE ttmd.country=’FRA’