SELECT from Nobel Tutorial(習(xí)題2)

習(xí)題來源:https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

image.png

1.Change the query shown so that it displays Nobel prizes for 1950.

select yr,subject,winner
from nobel
where yr = 1950;

2.Show who won the 1962 prize for Literature.

select winner
from nobel
where yr='1962' and subject='Literature'

3.Show the year and subject that won 'Albert Einstein' his prize.

select yr,subject
from nobel
where winner='Albert Einstein'

4.Give the name of the 'Peace' winners since the year 2000, including 2000.

select winner
from nobel
where subject= 'Peace'and yr>='2000'

5.Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

select yr,subject,winner
from nobel
where subject='Literature'and yr between 1980 and 1989;

6.Show all details of the presidential winners:
Theodore Roosevelt
Woodrow Wilson
Jimmy Carter
Barack Obama

select yr,subject,winner
from nobel
where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama')

7.Show the winners with first name John

select winner
from nobel
where winner like 'John%';

8.Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.

select *
from nobel 
where subject='Physics' and yr=1980 or (subject='Chemistry'and yr='1984')

9.Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine

select * 
from nobel
where subject not in ('Chemistry','Medicine')and yr='1980';

10.Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)

select * 
from nobel
where (subject= 'Medicine' and yr<'1910')
or (subject= 'Literature' and yr>='2004');

11.Find all details of the prize won by PETER GRüNBERG
Non-ASCII characters

select * 
from nobel
where winner='PETER GRüNBERG';

12.Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes

select * 
from nobel
where winner='EUGENE O''NEILL';

13.Knights in order
List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

select winner,yr,subject 
from nobel
where winner like 'Sir%'
order by yr desc;

14.The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject
 FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry'),subject,winner;

補(bǔ)充:

  1. or 表示一起選中的意思,and是兩個(gè)條件都復(fù)合的情況下再選稻薇。
    2.名稱中有單引號(hào)艾岂,用兩個(gè)單引號(hào)隔開椭坚,將名字中有單引號(hào)的放在第一個(gè)單引號(hào)內(nèi)掏颊。
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末柠座,一起剝皮案震驚了整個(gè)濱河市迄损,隨后出現(xiàn)的幾起案子省有,更是在濱河造成了極大的恐慌,老刑警劉巖贯涎,帶你破解...
    沈念sama閱讀 210,914評(píng)論 6 490
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件听哭,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡塘雳,警方通過查閱死者的電腦和手機(jī)陆盘,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 89,935評(píng)論 2 383
  • 文/潘曉璐 我一進(jìn)店門趾诗,熙熙樓的掌柜王于貴愁眉苦臉地迎上來危号,“玉大人,你說我怎么就攤上這事扫步∑薅ィ” “怎么了酸员?”我有些...
    開封第一講書人閱讀 156,531評(píng)論 0 345
  • 文/不壞的土叔 我叫張陵,是天一觀的道長(zhǎng)讳嘱。 經(jīng)常有香客問我幔嗦,道長(zhǎng),這世上最難降的妖魔是什么沥潭? 我笑而不...
    開封第一講書人閱讀 56,309評(píng)論 1 282
  • 正文 為了忘掉前任崭添,我火速辦了婚禮,結(jié)果婚禮上叛氨,老公的妹妹穿的比我還像新娘呼渣。我一直安慰自己,他們只是感情好寞埠,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,381評(píng)論 5 384
  • 文/花漫 我一把揭開白布屁置。 她就那樣靜靜地躺著,像睡著了一般仁连。 火紅的嫁衣襯著肌膚如雪蓝角。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,730評(píng)論 1 289
  • 那天饭冬,我揣著相機(jī)與錄音使鹅,去河邊找鬼。 笑死昌抠,一個(gè)胖子當(dāng)著我的面吹牛患朱,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播炊苫,決...
    沈念sama閱讀 38,882評(píng)論 3 404
  • 文/蒼蘭香墨 我猛地睜開眼裁厅,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來了侨艾?” 一聲冷哼從身側(cè)響起执虹,我...
    開封第一講書人閱讀 37,643評(píng)論 0 266
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎唠梨,沒想到半個(gè)月后袋励,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,095評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡当叭,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,448評(píng)論 2 325
  • 正文 我和宋清朗相戀三年茬故,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片科展。...
    茶點(diǎn)故事閱讀 38,566評(píng)論 1 339
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡均牢,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出才睹,到底是詐尸還是另有隱情徘跪,我是刑警寧澤,帶...
    沈念sama閱讀 34,253評(píng)論 4 328
  • 正文 年R本政府宣布琅攘,位于F島的核電站垮庐,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏坞琴。R本人自食惡果不足惜哨查,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,829評(píng)論 3 312
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望剧辐。 院中可真熱鬧寒亥,春花似錦邮府、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,715評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至加勤,卻和暖如春仙辟,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背鳄梅。 一陣腳步聲響...
    開封第一講書人閱讀 31,945評(píng)論 1 264
  • 我被黑心中介騙來泰國(guó)打工叠国, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人戴尸。 一個(gè)月前我還...
    沈念sama閱讀 46,248評(píng)論 2 360
  • 正文 我出身青樓粟焊,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親校赤。 傳聞我的和親對(duì)象是個(gè)殘疾皇子吆玖,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,440評(píng)論 2 348

推薦閱讀更多精彩內(nèi)容

  • rljs by sennchi Timeline of History Part One The Cognitiv...
    sennchi閱讀 7,306評(píng)論 0 10
  • **2014真題Directions:Read the following text. Choose the be...
    又是夜半驚坐起閱讀 9,426評(píng)論 0 23
  • The Inner Game of Tennis W Timothy Gallwey Jonathan Cape ...
    網(wǎng)事_79a3閱讀 11,915評(píng)論 3 20
  • 白昨晚從兩點(diǎn)鐘就開始折騰,不愿睡在墊子上马篮,要睡地上沾乘。來來回回折騰到五點(diǎn)鐘。 我都急哭了浑测,怕它著涼翅阵,自己也是困到不行...
    蘇向晚閱讀 338評(píng)論 0 0
  • 為什么滿腹委屈卻無從說起 因?yàn)檎f了你可能也不會(huì)懂 為什么生活如此糟糕 因?yàn)槟氵€沒有認(rèn)真的做好每一件事 為什么你總是...
    倪儀呀閱讀 246評(píng)論 0 0