Sqlzoo practice Answer--select from Nobel Tutorial/zh

The Nobel nobel prize winner

We continue to practice simple forms of SQL enquiry.

This tutorial is for the Nobel Prize winner:

Nobel (yr, subject, winner)

yr	subject	winner
1960	Chemistry	Willard F. Libby
1960	Literature	Saint-john Perse
1960	Medicine	Sir Frank Macfarlane Burnet
1960	Medicine	Peter Madawar
...

Yr: Year
Subject: Prizes
Winner: Prize Winner

1 . Change the enquiry to show the awards for 1950.

SELECT yr, subject, winner from  nobel WHERE yr = 1950

2.Show who won the 1962 Literary Prize (literature).

SELECT winner from  nobel WHERE yr = 1962 and   subject = ' Literature '

3.Show "Love Einstein" (' Albert Einstein ') award year and prizes.

Select Yr,subject from nobel where winner = ' Albert Einstein '

4.shows the winner of the 2000 and later Peace Prize (' Peace ').

Select winner from Nobel where yr>=2000 and subject= ' peace '

5.shows all the details (year, title, winner) of the Literature prize winners from 1980 to 1989 (inclusive).

SELECT * from Nobel where yr between 1980 and 1989 and Subject = ' literature '

6.

All the details of the president's winners are shown:

• West Obama Seef Theodore Roosevelt
• Woodside Woodrow Wilson
• Jimmy Carter.

SELECT * from Nobel WHERE winner in (' Theodore Roosevelt ',                  ' Woodrow Wilson ',                  ' Jimmy Carter ')

7 . The winner of John's name is displayed. (Note: Foreign name (first name) before, last name)

Select winner from Nobel where winner like ' john% '

8. shows 1980 Physics (physics) winner, and 1984 Chemistry Prize (chemistry) winner

SELECT * from Nobel where (yr=1980 and subject= ' physics ') or (yr=1984 and subject= ' chemistry ')

9. View 1980 winners, but not including the Chemistry and Medical Awards (Medicine).

SELECT * from Nobel where subject isn't in (' Chemistry ', ' Medicine ') and yr=1980

10.The Early Medical Awards (Medicine, excluding 1910), and recent literary prizes (literature) (2004, including 2004), were shown.

SELECT * from Nobel where (yr<1910 and subject= ' Medicine ') or (yr>=2004 and subject= ' literature ')

11.Find All details about the prize won by PETER Grünberg

SELECT * from nobel where winner = ' Peter Grünberg '

12.Find all the details of the Eugene Obama EUGENE O ' NEILL awards Find all detail of the prize won by EUGENE O ' NEILL

SELECT * from nobel where winner = ' Eugene o\ ' Neill '

13.

Knight Team Knights in order

List the winner, year, and title of the Jazz (the Jazz name starts with Sir). The newest winners are shown first, and then the same year they are ordered by name.

Select Winner,yr,subject from Nobel where winner like ' sir% ' ORDER by yr Desc,winner ASC

14.

The expression subject in (' Chemistry ', ' Physics ') can is used as a value-it would be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; But the list chemistry and Physics last.

SELECT winner, subject from Nobel where yr=1984 ORDER by subject in (' Physics ', ' chemistry '), subject asc,winner ASC

Sqlzoo practice Answer--select from Nobel Tutorial/zh