在 SQL Zoo 做 SQL 練習(xí)，下面記錄下我的答案腮猖，如果沒做出來會注明。

本篇為 SELECT from WORLD 和 SELECT from Nobel 兩節(jié)的答案。

SELECT from WORLD

1./

2.顯示具有至少2億人口的國家名稱府喳。 2億是200000000脖卖，有八個零乒省。

SELECT name FROM world
WHERE population>200000000

3.找出有至少200百萬(2億)人口的國家名稱，及人均國內(nèi)生產(chǎn)總值畦木。

SELECT name, gdp/population FROM world WHERE population > 200000000

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

SELECT name, population/1000000 AS populationM FROM world WHERE continent = 'South America'

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

SELECT name, population FROM world WHERE name IN ('France', 'Germany', 'Italy')

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

SELECT name FROM world WHERE name LIKE '%United%'

7.成為大國的兩種方式：如果它有3百萬平方公里以上的面積惨篱，或擁有250百萬(2.5億)以上人口盏筐。

展示大國的名稱，人口和面積砸讳。

SELECT name, population, area FROM world WHERE area > 3000000 or population > 250000000

8.國琢融、印度和中國(USA, India, China)是人口又大，同時面積又大的國家簿寂。排除這些國家漾抬。

顯示以人口或面積為大國的國家，但不能同時兩者常遂。顯示國家名稱奋蔚，人口和面積。SELECT name, population, area FROM world WHERE name NOT IN ('United States', 'India', 'China') AND (area > 3000000 OR population > 250000000)

9.除以為1000000（6個零）是以百萬計烈钞。除以1000000000（9個零）是以十億計泊碑。使用 ROUND 函數(shù)來顯示的數(shù)值到小數(shù)點後兩位。

對於南美顯示以百萬計人口毯欣，以十億計2位小數(shù)GDP馒过。

SELECT name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) FROM world
where continent = 'South America'

10.顯示國家有至少一個萬億元國內(nèi)生產(chǎn)總值（萬億，也就是12個零）的人均國內(nèi)生產(chǎn)總值酗钞。四捨五入這個值到最接近1000腹忽。

顯示萬億元國家的人均國內(nèi)生產(chǎn)總值，四捨五入到最近的$ 1000砚作。

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

11.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='Oceania' THEN 'Australasia'
ELSE continent END
FROM world
WHERE name LIKE 'N%'

12.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 IN ('Europe','Asia') THEN 'Eurasia'
WHEN continent IN ('North America','South America') THEN 'America'
WHEN continent = 'Caribbean' THEN 'America'
ELSE continent
END
FROM world
WHERE name LIKE 'A%' OR name LIKE 'B%'

13.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 IN ('Eurasia','Turkey') THEN 'Europe/Asia'
WHEN continent = 'Caribbean' THEN
CASE
WHEN name LIKE 'B%' THEN 'North America'
ELSE 'South America'
END
ELSE continent
END
FROM world
ORDER BY name

SELECT from Nobel Tutorial

1.更改查詢以顯示1950年諾貝爾獎的獎項資料窘奏。

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

2.顯示誰贏得了1962年文學(xué)獎(Literature)。

SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'

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

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

4.顯示2000年及以後的和平獎(‘Peace’)得獎?wù)摺?/p>

SELECT winner FROM nobel
WHERE yr >= 2000 AND subject = 'peace'

5.顯示1980年至1989年(包含首尾)的文學(xué)獎(Literature)獲獎?wù)咚屑?xì)節(jié)（年着裹，主題，獲獎?wù)撸?/p>

SELECT * FROM nobel
WHERE yr >= 1980 and yr <= 1989 and subject = 'Literature'

6.顯示總統(tǒng)獲勝者的所有細(xì)節(jié)：

• 西奧多?羅斯福 Theodore Roosevelt

• 伍德羅?威爾遜 Woodrow Wilson

• 吉米?卡特 Jimmy Carter

SELECT * FROM nobel

WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')

7.顯示名字為John 的得獎?wù)摺?(注意:外國人名字(First name)在前米同，姓氏(Last name)在後)

SELECT winner FROM nobel
WHERE winner LIKE 'John%'

8.顯示1980年物理學(xué)(physics)獲獎?wù)吆龋?984年化學(xué)獎(chemistry)獲得者。

SELECT * FROM nobel
WHERE (yr = 1980 AND subject = 'physics') or (yr = 1984 AND subject = 'chemistry')

9.查看1980年獲獎?wù)呙媪福话ɑ瘜W(xué)獎(Chemistry)和醫(yī)學(xué)獎(Medicine)少孝。

SELECT * FROM nobel
WHERE yr = 1980 AND subject NOT IN ('Chemistry', 'Medicine')

10.顯示早期的醫(yī)學(xué)獎(Medicine)得獎?wù)撸?910之前，不包括1910）熬苍，及近年文學(xué)獎(Literature)得獎?wù)撸?004年以後稍走，包括2004年）。

SELECT * FROM nobel
WHERE (yr < 1910 AND subject = 'medicine') OR (yr >= 2004 AND subject = 'Literature')

11.Find all details of the prize won by PETER GRüNBERG

SELECT * FROM nobel
WHERE winner like 'PETER GRüNBERG'

12.查找尤金?奧尼爾EUGENE O'NEILL得獎的所有細(xì)節(jié) Find all details of the prize won by EUGENE O'NEILL

跳脫字符:單引號

你不能把一個單引號直接的放在字符串中。但您可連續(xù)使用兩個單引號在字符串中當(dāng)作一個單引號婿脸。

SELECT * FROM nobel
WHERE winner like 'EUGENE O''NEILL'

13.騎士列隊 Knights in order

列出爵士的獲獎?wù)吡皇ぁ⒛攴荨ⅹ勴?爵士的名字以Sir開始)盖淡。先顯示最新獲獎?wù)撸会嵬暝侔疵Q順序排列凿歼。

SELECT winner, yr, subject FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner

14.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 subject IN ('Chemistry','Physics'), subject, winner