MySQL入門（三）：ORDER BY 語句用法

Sqlzoo習題練習：SELECT from nobel

習題鏈接：http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

下面為SELECT from nobel習題內容：

--#1/*Change the query shown so that it displays Nobel prizes for 1950.*/SELECT yr, subject, winnerFROM nobelWHERE 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,subjectFROM nobelWHERE winner = Albert Einstein--#4/*Give the name of the Peace winners since the year 2000, including 2000.*/SELECT winner FROM nobelWHERE (subject LIKE Peace%) AND (yr >= 2000)--#5/*Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive*/SELECT *FROM nobelWHERE (yr BETWEEN 1980 AND 1989) AND (subject = Literature)--#6/*Show all details of the presidential winners:Theodore RooseveltWoodrow WilsonJimmy Carter*/SELECT * FROM nobel WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter, Barack Obama)--#7/*Show the winners with first name John*/SELECT winner FROM nobelWHERE winner LIKE John%--#8/*Show the Physics winners for 1980 together with the Chemistry winners for 1984.*/SELECT *FROM nobelWHERE (subject = PhysicS AND yr = 1980) OR (subject = Chemistry AND yr = 1984)--#9/*Show the winners for 1980 excluding the Chemistry and Medicine*/SELECT * FROM nobelWHERE YR = 1980 AND subject NOT IN (Chemistry,Medicine)--#10/*Show 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 nobelWHERE (subject = Medicine AND yr < 1910) OR (subject = Literature AND yr >= 2004) --#11/*當查詢的內容中出現特殊字元時，使LIKE 語句進行查詢。例如：下面的名字中ü為特殊字元，使用LIKE 語句中的%代替。Find all details of the prize won by PETER GRüNBERG*/SELECT * FROM nobelWHERE winner LIKE peter gr%nberg--#12/*Find all details of the prize won by EUGENE ONEILL不能直接引用人名中的一個引號。可以在引用的字元串中使用兩個單引號。*/SELECT * FROM nobelWHERE winner = EUGENE ONEILL--#13/*List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.在上面的問題中需要使用ORDER BY 語句對結果進行排序ORDER BY 語句用於根據指定的列對結果集進行排序。ORDER BY 語句默認按照升序對記錄進行排序。如果您希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。針對上面的問題先對結果中的年份進行降序排序（先顯示最新的），如果年份相同再按名字字母順序排序。具體操作如下：*/SELECT winner,yr,subjectFROM nobelWHERE winner LIKE sir%ORDER BY yr DESC,winner--#14/*The expression subject IN (Chemistry,Physics) can be used as a value - it will be 0 or 1.Show the 1984 winners ordered by subject and winner name; but list Chemistry and Physics last.*/SELECT winner, subject,subject IN (Physics,Chemistry)FROM nobelWHERE yr=1984ORDER BY subject IN (Physics,Chemistry),subject,winner

推薦閱讀：