I get so much done when SL is offline.
Finished makeup editions for Kassia and Dria, and also finally pdf2tga’ed the SL Shakespeare Company programme!
And now for more SQL fun @ SQLzoo…
Two Table Join
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’
Category: Amusing, Daily Sumly, Designs, Projects
Written by ina on Monday, 12 of May , 2008 at 1:40 pm
Tags: bored, mysql, secondlife, sql, sqlzoo
Really bored. Asset servers failed. Crashed SL is not accepting logins. I feel like doing a problem set! (Seriously!)
SQLzoo answers to…
SELECT within SELECT
1b. List the name and region of countries in the regions containing ‘India’, ‘Iran’.
SELECT name,region FROM bbc WHERE region in (SELECT region FROM bbc WHERE name IN (’India’,'Iran’))
1c. Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’. (Denmark Iceland Ireland Luxembourg Norway Sweden Switzerland)
SELECT name FROM bbc WHERE region=’Europe’ AND gdp/population>(SELECT gdp/population FROM bbc WHERE name=’United Kingdom’)
1d. Which country has a population that is more than Canada but less than Algeria? (Kenya)
SELECT name FROM bbc WHERE population>(SELECT population FROM bbc WHERE name=’Canada’) AND population<(SELECT population from bbc WHERE name='Algeria')
2a. Which countries have a GDP greater than any country in Europe? [Give the name only.]
SELECT name FROM bbc WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region=’Europe’)
SUM and COUNT
1b. List all the regions - just once each.
SELECT DISTINCT region FROM bbc
1c. Give the total GDP of Africa (410196200000)
SELECT sum(gdp) from bbc WHERE region=’Africa’
1d. How many countries have an area of at least 1000000 (29)
SELECT COUNT(name) FROM bbc WHERE area>=1000000
1e. What is the total population of (’France’,'Germany’,'Spain’) (187300000)
SELECT SUM(population) FROM bbc WHERE name IN(’France’,'Germany’,'Spain’)
2a. For each region show the region and number of countries.
SELECT region, COUNT(name) FROM bbc GROUP BY region
2b. For each region show the region and number of countries with populations of at least 10 million.
SELECT region, COUNT(name) FROM bbc WHERE population>=10000000 GROUP BY region
2c. List the regions with total populations of at least 100 million.
SELECT region FROM bbc GROUP BY region HAVING SUM(population)>=100000000
Category: Amusing, Daily Sumly, Tutorials