Difference between revisions of "SELECT from Nobel Tutorial"

From SQLZOO
Jump to: navigation, search
Line 179: Line 179:
 
WHERE (subject='Medicine' and yr <1910) OR
 
WHERE (subject='Medicine' and yr <1910) OR
 
       (subject='Literature' AND yr>=2004)
 
       (subject='Literature' AND yr>=2004)
 +
</source>
 +
</div>
 +
 +
<div>
 +
<p><div class="quizlink">[[Nobel Quiz]]</div></p>
 +
</div>
 +
 +
==Harder Questions==
 +
<div class='qu'>
 +
Find the prize won by Peter Grünberg and Eugene O'Neill
 +
<source lang='sql' class='def'>
 +
</source>
 +
 +
<source lang='sql' class='ans'>
 +
SELECT *
 +
FROM nobel
 +
WHERE winner in ('Peter Grünberg', 'Eugene O''Neill')
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 15:47, 1 May 2015

nobel
yr subject winner
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Medawar
...

nobel Nobel Laureates

We continue practicing simple SQL queries on a single table.

This tutorial is concerned with a table of Nobel prize winners:

nobel(yr, subject, winner)

Exercises

Using the SELECT statement.

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

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1960
SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

Show who won the 1962 prize for Literature.

SELECT winner
  FROM nobel
 WHERE yr = 1960
   AND subject = 'Physics'
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 yr,subject,winner
FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989

Show all details of the presidential winners:

  • Theodore Roosevelt
  • Woodrow Wilson
  • Jimmy Carter
SELECT * FROM nobel
 WHERE yr = 1970
  AND subject IN ('Cookery',
                  'Chemistry',
                  'Literature')
SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt', 
                  'Woodrow Wilson', 
                  'Jed Bartlet', 
                  'Jimmy Carter')

Show the winners with first name John


SELECT winner FROM nobel
  WHERE winner LIKE 'John %'

Show the 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)

Show the winners for 1980 excluding the Chemistry and Medicine


SELECT *
FROM nobel
WHERE yr=1980 AND
  subject NOT IN ('Chemistry','Medicine')

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 nobel 
WHERE (subject='Medicine' and yr <1910) OR
      (subject='Literature' AND yr>=2004)

Harder Questions

Find the prize won by Peter Grünberg and Eugene O'Neill


SELECT *
FROM nobel 
WHERE winner in ('Peter Grünberg', 'Eugene O''Neill')