Deprecated: Assigning the return value of new by reference is deprecated in /home/ina/public_html/blog/wp-includes/cache.php on line 36

Deprecated: Assigning the return value of new by reference is deprecated in /home/ina/public_html/blog/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /home/ina/public_html/blog/wp-includes/theme.php on line 540
Blog by Ina Centaur » sql

 

SL Forced-Downtime & SQLzoo Fun Part II

Written by ina on Tuesday, 13 of May , 2008 at 12:27 am
Tags: , , , , , , , ,

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

SQLzoo Fun

Written by ina on Monday, 12 of May , 2008 at 1:40 pm
Tags: , , , ,

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

Who is Ina Centaur?

A 25-year old American polymath of Taiwanese ancestry pretending to be old and Caucasian in Second Life. Semi-retired independent scholar also dabbling as an independent artist in new media, particularly theatre and the humanities—notably Shakespeare. Programmer, playwright and novelist. Formal academic background in http://portfolio.inacentaur.com/ina/scientist, philosophy, and bioengineering.

This is largely a personal blog which isn't always up-to-date. There's no one definitive way to stalk me ;-).