SQL Zoo:SUM and COUNT

PART-1:World表

1. 展示世界的總人口。

SELECT sum(population)
FROM world

2. 列出所有的洲份, 每個只有一次。

select distinct continent
from world

3. 找出非洲(Africa)的GDP總和。

select sum(gdp) from world
where continent=‘Africa’

4. 有多少個國家具有至少百萬(1000000)的面積。

select count(distinct name) from world
where area>=1000000

5. (‘France’,‘Germany’,‘Spain’)（“法國”，“德國”，“西班牙”）的總人口是多少？

select sum(population) from world
where name in (‘France’,‘Germany’,‘Spain’)

6. 對於每一個洲份，顯示洲份和國家的數量。

select continent,count(distinct name) from world
group by continent

7. 對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。

select continent ,count(distinct name) from world
where population >=10000000
group by continent

8. 列出有至少100百萬(1億)(100,000,000)人口的洲份。

select continent from world
group by continent
having sum(population)>=100000000

PART-2:The nobel table.

1. 找出總共有多少個獎頒發了。

SELECT COUNT(winner) FROM nobel

2. 列出每一個獎項(subject), 只列一次

select distinct subject from nobel

3. 找出物理獎的總頒發次數。

select count(subject) from nobel
where subject=‘physics’

4. 對每一個獎項(Subject),列出頒發數目。

select subject,count(winner) from nobel
group by subject

5. 對每一個獎項(Subject),列出首次頒發的年份。

select subject,min(yr) from nobel
group by subject

6. 對每一個獎項(Subject),列出2000年頒發的數目。

select subject ,count(winner) from nobel
where yr=2000
group by subject

7. 對每一個獎項(Subject),列出有多少個不同的得獎者。

select subject ,count(distinct winner) from nobel
group by subject

8. 對每一個獎項(Subject),列出有多少年曾頒發過。

select subject,count(distinct yr) from nobel
group by subject

9. 列出哪年曾同年有3個物理獎Physics得獎者。

select yr from nobel
where subject=‘physics’
group by yr
having count(winner)=3

10. 列出誰得獎多於一次。

select winner from nobel
group by winner
having count(winner)>1

11. 列出誰獲得多於一個獎項(Subject)

select winner from nobel
group by winner
having count(distinct subject)>1

12. 哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。

select yr,subject from nobel
where yr>=2000
group by yr,subject
having count(winner)=3