Difference between revisions of "The nobel table can be used to practice more subquery./zh"

From SQLZOO
Jump to: navigation, search
 
(3 intermediate revisions by the same user not shown)
Line 2: Line 2:
 
   <p>此教程有關子查詢</p>
 
   <p>此教程有關子查詢</p>
 
   <h2>練習</h2>
 
   <h2>練習</h2>
 +
*Chemistry 化學獎
 +
*Economics 經濟獎
 +
*Literature 文學獎
 +
*Medicine 醫學獎
 +
*Peace 和平獎
 +
*Physics 物理獎
 
   <p>
 
   <p>
  
Line 8: Line 14:
 
  nobel('''yr, subject, winner''')   
 
  nobel('''yr, subject, winner''')   
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
SELECT subject FROM nobel  
+
SELECT * FROM nobel  
 
where winner = 'International Committee of the Red Cross'
 
where winner = 'International Committee of the Red Cross'
 
</source>
 
</source>
Line 70: Line 76:
 
select distinct yr  
 
select distinct yr  
 
from nobel
 
from nobel
where yr not in (
+
where subject = 'Physics' and yr not in (
 
SELECT yr
 
SELECT yr
 
   FROM nobel
 
   FROM nobel
Line 91: Line 97:
 
   FROM nobel
 
   FROM nobel
 
group by yr
 
group by yr
having count(*)<12
+
having count(*)>12
 
)
 
)
  

Latest revision as of 04:10, 1 June 2016

諾貝爾獎:子查詢

此教程有關子查詢

練習

  • Chemistry 化學獎
  • Economics 經濟獎
  • Literature 文學獎
  • Medicine 醫學獎
  • Peace 和平獎
  • Physics 物理獎

紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。 試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份。

nobel(yr, subject, winner)  
SELECT * FROM nobel 
where winner = 'International Committee of the Red Cross'
select winner , yr
from nobel where
subject ='Literature' and
yr in
(
select yr
from nobel 
where winner = 'International Committee of the Red Cross')


日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱。

nobel(yr, subject, winner)  
select winner
from nobel
where winner = 'Toshihide Maskawa'
select winner 
from nobel where
subject = 'Physics' and
winner <> 'Toshihide Maskawa' and
yr = (
select yr
from nobel
where winner = 'Toshihide Maskawa')

首次頒發的經濟獎 (Economics)的得獎者是誰?

nobel(yr, subject, winner)  
select winner
from nobel where
subject = 'Economics' and yr = (
select min(yr)
from nobel
where subject = 'Economics')

哪幾年頒發了物理獎,但沒有頒發化學獎?

nobel(yr, subject, winner)  
select distinct yr 
from nobel
where subject = 'Physics' and yr not in (
SELECT yr
  FROM nobel
 WHERE subject = 'Chemistry')


Using GROUP BY and HAVING.

哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份,獎項和得獎者。

nobel(yr,subject, winner)  
Select yr, subject, winner
From nobel where yr in(
 SELECT yr
  FROM nobel
group by yr
having count(*)>12
)

哪些得獎者獲獎多於1次呢?他們是哪一年獲得哪項獎項呢? 列出他們的名字,獲獎年份及獎項。先按名字,再按年份順序排序。

nobel(yr, subject, winner)  
select winner , yr, subject
from nobel
where winner in (
SELECT winner
  FROM nobel
group by winner
having count(*) >1
)
order by winner, yr