SQLZOO答案--The nobel table can be used to practice more SUM and COUNT functions

 

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

SELECT COUNT(winner) FROM nobel

 

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

SELECT DISTINCT subject
  FROM nobel

 

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

SELECT COUNT( winner )
  FROM nobel 
WHERE subject = 'Physics'

 

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

SELECT subject, COUNT(*)
  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('subject')=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

注：必须加DISTINCT 去重，因为一个人可能会同一奖项多次

 

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

SELECT yr, subject
  FROM nobel
WHERE yr >= 2000
GROUP BY yr, subject
  HAVING COUNT(subject) = 3