SELECT from Nobel Tutorial

Language: English • ⽇本語 • 中文

nobel
yr subject winner
Contents 1960 Chemistry Willard F. Libby
nobel Nobel Laureates 1960 Literature Saint-John Perse
Winners from 1950 1960 Medicine Sir Frank Macfarlane Burnet
1962 Literature 1960 Medicine Peter Madawar
Albert Einstein
...
Recent Peace Prizes
Literature in the 1980's
Only Presidents
John
Chemistry and Physics from different years
Exclude Chemists and Medics
Early Medicine, Late Literature
Harder Questions
Umlaut
Apostrophe
Knights of the realm
Chemistry and Physics last

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)

Using the SELECT statement.

Winners from 1950

1.
Change the query shown so that it displays Nobel prizes for 1950.
Submit SQL Restore default

Correct answer
yr subject winner
1950 Chemistry Kurt Alder
1950 Chemistry Otto Diels
1950 Literature Bertrand Russell
1950 Medicine Philip S. Hench
1950 Medicine Edward C. Kendall
1950 Medicine Tadeus Reichstein
1950 Peace Ralph Bunche
1950 Physics Cecil Powell

1962 Literature

2.
Show who won the 1962 prize for Literature.

Submit SQL Restore default

Correct answer
winner
John Steinbeck

Albert Einstein

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

Submit SQL Restore default

result
Recent Peace Prizes

4.
Give the name of the 'Peace' winners since the year 2000, including 2000.

SELECT winner
FROM nobel
WHERE subject = 'Peace' AND yr >= 2000;

Submit SQL Restore default

Correct answer
winner
Tunisian National Dialogue Quartet
Kailash Satyarthi
Malala Yousafzai
European Union
Ellen Johnson Sirleaf
Leymah Gbowee
Tawakel Karman
Liu Xiaobo
Barack Obama

Literature in the 1980's

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

Submit SQL Restore default

Correct answer
yr subject winner
1989 Literature Camilo José Cela
1988 Literature Naguib Mahfouz
1987 Literature Joseph Brodsky
1986 Literature Wole Soyinka
1985 Literature Claude Simon
1984 Literature Jaroslav Seifert
1983 Literature William Golding
1982 Literature Gabriel García Márquez
1981 Literature Elias Canetti

Only Presidents

6.
Show all details of the presidential winners:

Theodore Roosevelt
Woodrow Wilson
Jimmy Carter
Barack Obama
SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Barack Obama',
'Jimmy Carter')

Submit SQL Restore default

Correct answer
yr subject winner
2009 Peace Barack Obama
2002 Peace Jimmy Carter
1919 Peace Woodrow Wilson
1906 Peace Theodore Roosevelt

John

7.
Show the winners with first name John

SELECT winner FROM nobel WHERE winner LIKE 'John%';

Submit SQL Restore default

Correct answer
winner
John O'Keefe
John B. Gurdon
John C. Mather
John L. Hall
John B. Fenn
John E. Sulston
John Pople
John Hume
John E. Walker

Chemistry and Physics from different years

8.
Show the year, subject, and name of 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;

Submit SQL Restore default

Correct answer
yr subject winner
1984 Chemistry Bruce Merrifield
1980 Physics James Cronin
1980 Physics Val Fitch
Exclude Chemists and Medics

9.
Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine

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

Submit SQL Restore default

Correct answer
yr subject winner
1980 Economics Lawrence R. Klein
1980 Literature Czeslaw Milosz
1980 Peace Adolfo Pérez Esquivel
1980 Physics James Cronin
1980 Physics Val Fitch
Early Medicine, Late Literature

10.
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;

Submit SQL Restore default

Correct answer
yr subject winner
2015 Literature Svetlana Alexievich
2014 Literature Patrick Modiano
2013 Literature Alice Munro
2012 Literature Mo Yan
2011 Literature Tomas Tranströmer
2010 Literature Mario Vargas Llosa
2009 Literature Herta Müller
2008 Literature Jean-Marie Gustave Le Clézio
2007 Literature Doris Lessing

Nobel Quiz

Harder Questions

Umlaut

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

Non-ASCII characters

Submit SQL Restore default

result

Apostrophe

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

Escaping single quotes

Submit SQL Restore default

result

Knights of the realm

13.
Knights in order

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, winner

Submit SQL Restore default

Correct answer
winner yr subject
Sir Martin J. Evans 2007 Medicine
Sir Peter Mansfield 2003 Medicine
Sir Paul Nurse 2001 Medicine
Sir Harold Kroto 1996 Chemistry
Sir James W. Black 1988 Medicine
Sir Arthur Lewis 1979 Economics
Sir Nevill F. Mott 1977 Physics
Sir Bernard Katz 1970 Medicine
Sir John Eccles 1963 Medicine

Chemistry and Physics last

14.
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 ('Physics','Chemistry'),subject,winner

Submit SQL Restore default

sqlzoo Nobel 14
Correct answer
winner subject
Richard Stone Economics
Jaroslav Seifert Literature
César Milstein Medicine
Georges J.F. Köhler Medicine
Niels K. Jerne Medicine
Desmond Tutu Peace
Bruce Merrifield Chemistry
Carlo Rubbia Physics
Simon van der Meer Physics

Retrieved from "https://sqlzoo.net/w/index.php?title=SELECT_from_Nobel_Tutorial&oldid=39246"

This page was last edited on 17 April 2018, at 19:08.