本文是小编在自学SQL过程中,做完SQLZOO Tutorial练习后整理的答案,仅供参考。
-
目录
select basics
1. The example uses a WHERE clause to show the population of 'France'. Note that strings (pieces of text that are data) should be in 'single quotes';
Modify it to show the population of Germany
SELECT population FROM world
WHERE name = 'Germany'
2.
Checking a list The word IN allows us to check if an item is in a list. The example shows the name and population for the countries 'Brazil', 'Russia', 'India' and 'China'.
Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.
SELECT name, population FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark')
3. Which countries are not too small and not too big? BETWEEN
allows range checking (range specified is inclusive of boundary values). The example below shows countries with an area of 250,000-300,000 sq. km. Modify it to show the country and the area for countries with an area between 200,000 and 250,000.
SELECT name, area FROM world
WHERE area BETWEEN 200000 and 250000
select from world
1. Observe the result of running this SQL command to show the name, continent and population of all countries.
SELECT name, continent, population FROM world
2. Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.
select name from world
where population > 200000000
3.Give the name
and the per capita GDP for those countries with a population
of at least 200 million.
HELP:How to calculate per capita GDP
select name, gdp/population
from world
where population >= 200000000
select from nobel
1. Change the query shown so that it displays Nobel prizes for 1950.
select yr, subject, winner from nobel
where yr = 1950
2. Show who won the 1962 prize for Literature.
select winner from nobel
where yr = 1962 and subject = 'Literature'
3. Show the year and subject that won 'Albert Einstein' his prize
select yr, subject from nobel
where winner = 'Albert Einstein'
4. Give the name of the 'Peace' winners since the year 2000, including 2000.
select winner from nobel
where subject = 'Peace' and yr >= 2000
select in select
1. List each country name where the population is larger than that of 'Russia'.
world(name, continent, area, population, gdp)
SELECT name FROM bbc
WHERE population > ALL
(SELECT MAX(population)
FROM bbc
WHERE region = 'Europe')
AND region = 'South Asia'
2. Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.
Per Capita GDP
select name from world
where continent = 'Europe'
and gdp/population >
(select gdp/population from world
where name = 'United Kingdom')
3. List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
select name, continent from world
where continent in
(select distinct(continent) from world
where name = 'Argentina'
or name = 'Australia')
order by name
4. Which country has a population that is more than Canada but less than Poland? Show the name and the population.
select name, population
from world
where
population > (select population from world where name = 'Canada')
and
population < (select population from world where name = 'Poland')
5.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
The format should be Name, Percentage for example:
Decimal places
You can use the function ROUND to remove the decimal places.
Percent symbol %
select name, concat(round(population * 100 /(select population from world where name = 'Germany'),0),'%') as percentage from world
where continent = 'Europe'
6. Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
select name from world
where gdp > all