SELECT from Nobel Tutorial
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')