5.1.SUM and COUNT MORE

nobel(yr, subject, winner)

---

1. 显示获奖总数。

SELECT COUNT(winner) FROM nobel;

2. 列出每个主题--只需一次

select distinct subject from nobel;

3. 显示物理学获奖总数。

select count(winner)
 from nobel
 where subject ='Physics';

4. 对每个科目显示科目和奖号。

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

5. 每个科目显示出获奖的第一年。

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

6. 每一个科目都显示2000年的获奖人数。

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

7. 显示每个科目的不同获奖人数。

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

8. 对于每个科目，显示有多少年获得过奖项。

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

9. 出示物理学获得三个奖项的年份。

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. 显示获奖者已超过一个科目。

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

12. 显示获得3个奖项的年份和主题。只显示2000年以后的年份。

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