sqlzoo练习题之select-from-nobel
关于sqlzoo中的select_from_nobel表的练习题~
00. 前言
练习地址:https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial
使用的为sqlzoo的MySQL库。
表名:nobel
字段名:yr、subject、winner

01.Winners from 1950
Change the query shown so that it displays Nobel prizes for 1950.
更改显示的查询,以显示1950年的诺贝尔奖。
1 | select * |

02.1962 Literature
Show who won the 1962 prize for literature.
显示谁获得了1962年的文学奖。
1 | select winner |

03.Albert Einstein
Show the year and subject that won ‘Albert Einstein’ his prize.
显示Albert Einstein获奖的年份和奖项。
1 | select yr,subject |

04.Recent Peace Prizes
Give the name of the ‘peace’ winners since the year 2000, including 2000.
请说出自2000年以来,包括2000年在内的“和平”获奖者的名字。
1 | select winner |

05.Literature in the 1980’s
Show all details (yr, subject, winner) of the literature prize winners for 1980 to 1989 inclusive.
显示1980年至1989年(含)文学奖获奖者的所有信息(年份、奖项、获奖者)。
1 | select * |

06.Only Presidents
Show all details of the presidential winners:
- Theodore Roosevelt
- Woodrow Wilson
- Jimmy Carter
- Barack Obama
显示总统候选人的得奖信息。
1 | select * |

07.John
Show the winners with first name John.
显示获奖者的名字为John的。
1 | select winner |

08.Chemistry and Physics from different years
Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.
显示1980年诺贝尔物理学和1984年诺贝尔化学奖得主的年份,奖项,姓名。
1 | select yr,subject,winner |

09.Exclude Chemists and Medics
Show the year, subject, and name of winners for 1980 excluding chemistry and medicine.
显示1980年获奖者(化学和医学除外)的年份、奖项和姓名。
1 | select * |

10.Early Medicine, Late Literature
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).
显示早期年份(1910年之前,不包括1910年)获得“医学”奖的人以及后来年份(2004年之后,包括2004年)获得“文学”奖的人的姓名和获奖年份。
1 | select * |

11.Umlaut
Find all details of the prize won by PETER GRÜNBERG.
查找PETER GRÜNBERG获奖的信息。
1 | select * |

12.Apostrophe
Find all details of the prize won by EUGENE O’NEILL.
查找EUGENE O’NEILL获奖的信息。
1 | 字符串中出现单引号时,要在该单引号前加一个单引号,或者\,或者使用双引号来引用单引号 |

13.Knights of the realm
List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.
列出获奖者、年份和奖项信息,获奖者以Sir开头。年份按照最近排序,然后按姓名顺序排列。
1 | select winner,yr,subject |

14.Chemistry and Physics last
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.
表达式subject IN (‘chemistry’,’physics’)可以用作一个值,它将是0或1。
显示1984年获奖者和科目,按科目和获奖者姓名顺序排列,但化学和物理排在最后。
1 | select winner,subject |
