Difference between revisions of "The nobel table can be used to practice more subquery./zh"
Jump to navigation
Jump to search
(4 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 | SELECT * FROM nobel | ||
where winner = 'International Committee of the Red Cross' | where winner = 'International Committee of the Red Cross' | ||
</source> | </source> | ||
Line 38: | Line 44: | ||
from nobel where | from nobel where | ||
subject = 'Physics' and | subject = 'Physics' and | ||
winner <> 'Toshihide Maskawa' and | |||
yr = ( | yr = ( | ||
select yr | select yr | ||
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(*) | 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