SQLzoo SELECT from Nobel Tutorial答案

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

select * from nobel where yr = 1950;

• Show who won the 1962 prize for literature.

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

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

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

• Give the name of the 'peace' winners since the year 2000, including 2000.

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

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

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

• Show all details of the presidential winners:

  • Theodore Roosevelt

  • Thomas Woodrow Wilson

  • Jimmy Carter

  • Barack Obama

select * from nobel where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter');

• Show the winners with first name John

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

• Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

select * from nobel where yr = 1980 and subject = 'Physics' OR yr = 1984 and subject = 'Chemistry';

• Show the year, subject, and name of winners for 1980 excluding chemistry and medicine

select * from nobel where subject !='Chemistry' and subject != 'Medicine' and yr = 1980;

• 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;

• Find all details of the prize won by PETER GRÜNBERG

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

• Find all details of the prize won by EUGENE O'NEILL

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

• 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;

• 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 ('chemistry','physics'),subject,winner;