SQL入门练习——SQLZOO练习整理（一）

Answer 1

最近正好在学SQL的基本操作，整理了一下SQLZOO上面的练习和答案，供大家参考~

一、SELECT basics

Modify it to show the population of Germany

2. IN用法，IN后是一个查询，数组

Show thename and the population for 'Sweden', 'Norway' and 'Denmark'.

3. BETWEEN用法

Modify it to show the country and the area for countries with an area between 200,000 and 250,000.

quiz答案：3 5 5 3 3 3 3

二、SELECT from WORLD Tutorial

1.

Observe the result of running this SQL command to show the name, continent and population of all countries.

2.

Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros.

3. per capita GDP代表人均GDP，人均GDP=GDP/population

Give thename and the per capita GDP for those countries with a population of at least 200 million.

4.

Show thename and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

5.

Show thename and population for France, Germany, Italy

6. LIKE谓词的用法，用来表示字符串的部分一致查询（模糊匹配）,跟通配符“%”一起用

Show the countries which have aname that includes the word 'United'

7. OR运算符

Two ways to be big: A country isbig if it has an area of more than 3 million sq km or it has a population of more than 250 million. Show the countries that are big by area or big by population. Show name, population and area.

8. AND运算符的优先级高于OR运算符。想要优先执行OR运算符时可以使用括号。

Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area.

9. ROUND四舍五入：ROUND(对象数值，保留小数的位数)

Show thename and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places.

10. ROUND(参数，小数位数)，小数位数为负值时，代表

Show thename and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000. Show per-capita GDP for the trillion dollar countries to the nearest $1000.

或者

11. LENGTH(参数)表示参数的长度

Show the name and capital where the name and the capital have the same number of characters.

12. LEFT（s，n）允许从字符串s的开头提取n个字符

Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.

13.

Find the country that has all the vowels and no spaces in its name.

quiz答案：5 4 2 4 2 4 3

三、SELECT from Nobel Tutorial

Change the query shown so that it displays Nobel prizes for 1950.

2.

Show who won the 1962 prize for Literature.

3.

Show the year and subject that won 'Albert Einstein' his prize.

4.

Give the name of the 'Peace' winners since the year 2000, including 2000.

5.

Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

6.

Show all details of the presidential winners: Theodore Roosevelt, Woodrow Wilson, Jimmy Carter, Barack Obama

7.

Show the winners with first name John

8.

Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.

9.

Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine

10.

Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)

11.

Find all details of the prize won by PETER GRÜNBERG

12. 不能直接在引号字符串中放入单引号。可以在带引号的字符串中使用两个单引号

Find all details of the prize won by EUGENE O'NEILL

13. 未指定ORDER BY子句中排列顺序时会默认使用升序进行排列，使用DESC关键字可以进行降序排序

List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

14.subject IN ('Chemistry','Physics')的值也可以表示为0或1

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

quiz答案：5 3 2 3 3 3 4

四、SELECT within SELECT Tutorial

List each country name where the population is larger than that of 'Russia'.

2.

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

3.

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

4.

Which country has a population that is more than Canada but less than Poland? Show the name and the population.

5. CONCAT(s1, s2 ...) ,将两个或多个字符串粘在一起

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

6. 我们可以使用单词ALL来允许> =或>或<=来对列表进行操作。

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

7.

Find the largest country (by area) in each continent, show the continent, the name and the area:

8.

List each continent and the name of the country that comes first alphabetically.

9.

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

10.

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

quiz答案：3 2 1 4 2 2 2