Lazy loaded image
Technology
Lazy loaded imageSQL Q&A2 Nobel Tutorial
Words 628Read Time 2 min
May 8, 2020
May 16, 2025
type
status
date
slug
summary
tags
category
icon
password

SELECT BASIC

notion image

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

Correct answer
yr
subject
winner
1950
chemistry
Kurt Alder
1950
chemistry
Otto Diels
1950
literature
Bertrand Russell
1950
medicine
Edward Kendall
1950
medicine
Philip Hench
1950
medicine
Tadeus Reichstein
1950
peace
Ralph Bunche
1950
physics
Cecil Powell

2.1962 Literature

Show who won the 1962 prize for literature.

winner

John Steinbeck

3.Albert Einstein

Show the year and subject that won 'Albert Einstein' his prize.
yr
subject
1921
physics

4.Recent Peace Prizes

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

Kim Dae-jung

Kofi Annan

United Nations

Jimmy Carter

Shirin Ebadi

Wangari Maathai

International Atomic Energy Agency

Mohamed ElBaradei

Grameen Bank

Muhammad Yunus

Al Gore

Intergovernmental Panel on Climate Change

Martti Ahtisaari

Barack Obama

Liu Xiaobo

Ellen Johnson Sirleaf

Leymah Gbowee

Tawakkol Karman

European Union

Organisation for the Prohibition of Chemical Weapons

Kailash Satyarthi

Malala Yousafzai

National Dialogue Quartet

Juan Manuel Santos

International Campaign to Abolish Nuclear Weapons

Denis Mukwege

Nadia Murad

Abiy Ahmed Ali

World Food Programme

Dmitry Muratov

Maria Ressa

Ales Bialiatski

Center for Civil Liberties

Memorial

Narges Mohammadi

5.Literature in the 1980's

Show all details (yrsubjectwinner) of the literature prize winners for 1980 to 1989 inclusive.
yr
subject
winner
1980
literature
Czes?aw Mi?osz
1981
literature
Elias Canetti
1982
literature
Gabriel García Márquez
1983
literature
William Golding
1984
literature
Jaroslav Seifert
1985
literature
Claude Simon
1986
literature
Wole Soyinka
1987
literature
Joseph Brodsky
1988
literature
Naguib Mahfouz
1989
literature
Camilo José Cela
 

6.Only Presidents

Show all details of the presidential winners:
  • Theodore Roosevelt
  • Thomas Woodrow Wilson
  • Jimmy Carter
  • Barack Obama
yr
subject
winner
1906
peace
Theodore Roosevelt
2002
peace
Jimmy Carter
2009
peace
Barack Obama

7.John

Show the winners with first name John
winner

John Macleod

John Galsworthy

John Northrop

John Mott

John Cockcroft

John Enders

John Bardeen

John Kendrew

John Steinbeck

John Bardeen

John Cornforth

John Van Vleck

John Vane

John Polanyi

John Walker

John Pople

John Hume

John Fenn

John Sulston

John Hall

John Mather

John O'Keefe

John Goodenough

John Clauser

8.Chemistry and Physics from different years

Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.
yr
subject
winner
1980
physics
James Cronin
1980
physics
Val Fitch
1984
chemistry
Bruce Merrifield

9.Exclude Chemists and Medics

Show the year, subject, and name of winners for 1980 excluding chemistry and medicine
yr
subject
winner
1980
literature
Czes?aw Mi?osz
1980
peace
Adolfo Pérez Esquivel
1980
physics
James Cronin
1980
physics
Val Fitch

10.Early Medicine, Late Literature

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)
yr
subject
winner
1901
medicine
Emil von Behring
1902
medicine
Ronald Ross
1903
medicine
Niels Ryberg Finsen
1904
medicine
Ivan Pavlov
1905
medicine
Robert Koch
1906
medicine
Camillo Golgi
1906
medicine
Santiago Ramón y Cajal
1907
medicine
Alphonse Laveran
1908
medicine
Ilya Mechnikov
1908
medicine
Paul Ehrlich
1909
medicine
Theodor Kocher
2004
literature
Elfriede Jelinek
2005
literature
Harold Pinter
2006
literature
Orhan Pamuk
2007
literature
Doris Lessing
2008
literature
Jean-Marie Gustave Le Clézio
2009
literature
Herta Müller
2010
literature
Mario Vargas Llosa
2011
literature
Tomas Tranströmer
2012
literature
Mo Yan
2013
literature
Alice Munro
2014
literature
Patrick Modiano
2015
literature
Svetlana Alexievich
2016
literature
Bob Dylan
2017
literature
Kazuo Ishiguro
2018
literature
Olga Tokarczuk
2019
literature
Peter Handke
2020
literature
Louise Glück
2021
literature
Abdulrazak Gurnah
2022
literature
Annie Ernaux
2023
literature
Jon Fosse

11.Umlaut

Find all details of the prize won by PETER GRÜNBERG
 
yr
subject
winner
2007
physics
Peter Grünberg

12.Apostrophe

Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes In SQL, if a string contains a single quote ', you need to escape it by using two single quotes ''.
yr
subject
winner
1936
literature
Eugene O'Neill

13.Knights of the realm

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.
winner
yr
subject
Sir Peter Ratcliffe
2019
medicine
Sir Gregory Winter
2018
chemistry
Sir Fraser Stoddart
2016
chemistry
Sir John Gurdon
2012
medicine
Sir Martin Evans
2007
medicine
Sir Peter Mansfield
2003
medicine
Sir Paul Nurse
2001
medicine
Sir Harold Kroto
1996
chemistry
Sir James Black
1988
medicine
Sir Nevill Mott
1977
physics
Sir Bernard Katz
1970
medicine
Sir John Eccles
1963
medicine
Sir Frank Macfarlane Burnet
1960
medicine
Sir Cyril Hinshelwood
1956
chemistry
Sir Robert Robinson
1947
chemistry
Sir Alexander Fleming
1945
medicine
Sir Howard Florey
1945
medicine
Sir Henry Dale
1936
medicine
Sir Norman Angell
1933
peace
Sir Charles Sherrington
1932
medicine
Sir Chandrasekhara Venkata Raman
1930
physics
Sir Frederick Hopkins
1929
medicine
Sir Austen Chamberlain
1925
peace
Sir William Ramsay
1904
chemistry
Pattern
Meaning
Example Match
'John%'
Starts with John
John, Johnny, John Smith
'%John'
Ends with John
Sir John, Dr. John, is John
'%John%'
Contains John anywhere
Johnny, Sir John Smith, Dr. Johnny
'J%n'
Starts with J, ends with n, anything in between
John, Jason, Jordan

14.Chemistry and Physics last

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.
Correct answer
winner
subject
Jaroslav Seifert
literature
César Milstein
medicine
GeorgesF. Köhler
medicine
Niels Jerne
medicine
Desmond Tutu
peace
Bruce Merrifield
chemistry
Carlo Rubbia
physics
Simon van der Meer
physics
SQL Keyword
Equivalent in Programming
CASE
Starts the if structure
WHEN
The if or elif condition
THEN
The result or block to return/execute
ELSE
The default case when no condition is met
END
Closes the CASE block
上一篇
SQL Q&A1
下一篇
SQL Q&A3 SELECT in SELECT