Sunday 15 January 2012

Select stmt queries


BBC Country Profiles

This tutorial introduces SQL as a query language. We will be using the SELECT command on the table bbc:
nameregionareapopulationgdp
AfghanistanSouth Asia65222526000000
AlbaniaEurope2872832000006656000000
AlgeriaMiddle East24000003290000075012000000
AndorraEurope46864000
...

Exercises

Using the SELECT statement.
1a.Read the notes about this table. Issue the command: SELECT name, region, population FROM bbc. Look at the output.

Well Done, that is correct.
nameregionpopulation
AfghanistanSouth Asia26000000
AlbaniaEurope3200000
AlgeriaMiddle East32900000
AndorraEurope64000
AngolaAfrica14500000
Antigua and BarbudaAmericas77000
ArgentinaSouth America39300000
ArmeniaEurope3000000
AustraliaAsia-Pacific20300000
AustriaEurope8100000
AzerbaijanEurope8500000
BahamasAmericas321000
BahrainMiddle East754000
BangladeshSouth Asia152600000
BarbadosAmericas272000
BelarusEurope9800000
BelgiumEurope10300000
BelizeAmericas266000
BeninAfrica7100000
BhutanSouth Asia2400000
BoliviaSouth America9100000
Bosnia-HercegovinaEurope4200000
BotswanaAfrica1800000
BrazilSouth America182800000
BruneiAsia-Pacific374000
BulgariaEurope7800000
Burkina FasoAfrica13800000
BurmaAsia-Pacific50700000
BurundiAfrica7300000
CambodiaAsia-Pacific14800000
CameroonAfrica16600000
CanadaNorth America32000000
Cape VerdeAfrica482000
Central African RepublicAfrica3900000
ChadAfrica9100000
ChileSouth America16200000
ChinaAsia-Pacific1300000000
ColombiaSouth America45600000
ComorosAfrica812000
Costa RicaAmericas4300000
CroatiaEurope4400000
CubaAmericas11300000
CyprusEurope807000
Czech RepublicEurope10200000
Democratic Republic of CongoAfrica56000000
DenmarkEurope5400000
DjiboutiAfrica721000
DominicaAmericas71000
Dominican RepublicAmericas9000000
East TimorAsia-Pacific857000
Results truncated...
Before attempting the following questions read this! How to use WHERE to filter records.
2a.Show the name for the countries that have a population of at least 200 million. (200 million is 200000000, there are eight zeros)

Well Done, that is correct.
NAME
China
India
Indonesia
United States of America
2b.Give the name and the per capita GDP for those countries with a population of at least 200 million.
per capita GDP is the GDP divided by the populationGDP/population

Well Done, that is correct.
NAMEGDP/POPULATION
China1290
India620
Indonesia1140
United States of America41400
2c.Show the name and population in millions for the countries of 'Middle East'
Divide the population by 1000000 to get population in millions.

Well Done, that is correct.
NAMEPOPULATION/1000000
Algeria32.9
Bahrain.754
Egypt74.9
Iran70.7
Iraq26.5
Israel and Palestinian territories3.8
Jordan5.7
Kuwait2.7
Lebanon3.8
Mauritania3.1
Morocco31.6
Oman3
Qatar.628
Saudi Arabia25.6
Sudan35
Syria18.6
Tunisia10
United Arab Emirates3.1
Yemen21.5
2d.Show the name and population for'France', 'Germany', 'Italy'

Well Done, that is correct.
NAMEPOPULATION
France60700000
Germany82500000
Italy57200000
2e.Identify the countries which have names including the word 'United'

Well Done, that is correct.
NAME
United Arab Emirates
United Kingdom
United States of America

Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)