sqlzoo练习答案

 本文是小编在自学SQL过程中，做完SQLZOO Tutorial练习后整理的答案，仅供参考。

1. 目录

   select basics

   select from world

   select from nobel

   select in select

   sum and count

   join

   more join

   using null

   self join

   ---

   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 an