SQLZOO刷题记录-1

1. 找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。人均國內生產總值計算：人均國內生產總值，即是國內生產總值除以人口(GDP/population)。

select name,GDP/population
  from world
 where population>200000000;

2. 顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。

select name,population/1000000 
  from world
 where continent = 'South America';

3. 顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口。

select name,population
  from world
 where name in ('France','Germany','Italy');

4. 顯示包含單詞“United”為名稱的國家。

select name
  from world
 where name like '%United%';

5. 成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。展示大國的名稱，人口和面積。

select name,population,area
  from world
 where area>3000000 or population>250000000;

6. 美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

SELECT name, population, area FROM world 
WHERE (area > 3000000 AND population < 250000000) OR(area < 3000000 AND population > 250000000);

7. 除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。對於南美顯示以百萬計人口，以十億計2位小數GDP。

select name,round(population/1000000,2),round(GDP/1000000000,2)
  from world
 where continent = 'South America';

8. 顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。

SELECT name, ROUND(GDP/population/1000,0)*1000 
  FROM world 
 WHERE GDP > 1000000000000;

9. The CASE statement shown is used to substitute North America for Caribbean in the third column.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name,
       CASE WHEN continent='Australasia' THEN 'Oceania'
            WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

10. Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning with A or B.

select name,
       case when continent='Europe' then 'Eurasia'
            when continent='Asia' then 'Eurasia'
            when continent='North America' then 'America'
            when continent='South America' then 'America'
            when continent='Caribbean' then 'America'
            ELSE continent end
  from world
 where name like 'A%' OR name like 'B%'

11. Put the continents right...Oceania becomes Australasia,Countries in Eurasia and Turkey go to Europe/Asia,Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America,Show the name, the original continent and the new continent of all countries.

select name,continent,
       case when continent='Oceania' then 'Australasia'
            when continent='Eurasia' or continent='Turkey' then 'Europe/Asia'
            when continent='Caribbean' and name like 'B%' THEN 'North America'
            when continent='Caribbean' then 'South America'
            else continent end
  from world;

12. 顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。

SELECT yr,subject
  FROM nobel
 WHERE winner='Albert Einstein';

13. 顯示2000年及以後的和平獎(‘Peace’)得獎者。

select winner
  from nobel
 where yr >= 2000
   and subject = 'Peace';

14. 顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

select yr,subject,winner
  from nobel
 where yr between 1980 and 1989
   and subject = 'Literature';

15. 顯示總統獲勝者的所有細節：西奧多•羅斯福 Theodore Roosevelt;伍德羅•威爾遜 Woodrow Wilson;吉米•卡特 Jimmy Carter

SELECT * FROM nobel
 WHERE winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter');

16. 顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

select winner
  from nobel
 where winner like 'John%';

17. 顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

select yr,subject,winner
  from nobel
 where (subject = 'physics' and yr = 1980) or (subject = 'chemistry' and yr = 1984)

18. 查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。

select yr,subject,winner
  from nobel
 where yr = 1980 and subject not in ('Chemistry','Medicine');

19. 顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。

select yr,subject,winner
  from nobel
 where (subject = 'Medicine' and yr < 1910) or (subject = 'Literature' and yr >= 2004);

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

select yr,subject,winner
  from nobel
 where winner = 'PETER GRÜNBERG';

21. 列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner,yr,subject 
  from nobel
 where winner like 'Sir%'
order by yr desc,winner;

22. The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject 
  from nobel
 WHERE yr='1984'
ORDER BY case when subject IN ('physics','chemistry') then 1
			  else 0 end,subject,winner