[SQL] SELECT from NOBEL Tutorial (ORDER BY, CASE WHEN 절)

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

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

Show who won the 1962 prize for literature.

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'literature'

  winner
  John Steinbeck

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

SELECT name FROM world
  WHERE name LIKE '%x%'

  yr	subject
  1921	Physics

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

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

    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
Tawakel Karman
European Union
Kailash Satyarthi
Malala Yousafzai
Tunisian National Dialogue Quartet
Juan Manuel Santos
International Campaign to Abolish Nuclear Weapons

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

SELECT yr, subject, winner
FROM nobel
WHERE 1980<=yr AND yr<=1989 AND subject='literature'

  yr	subject	winner
  1980	Literature	Czeslaw Milosz
  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

Show the winners with first name John

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

  winner
  John Macleod
  John Galsworthy
  John H. Northrop
  John R. Mott
  John Cockcroft
  John F. Enders
  John Bardeen
  John C. Kendrew
  John Steinbeck
  John R. Hicks
  John Bardeen
  John Cornforth
  John H. van Vleck
  John R. Vane
  John C. Polanyi
  John C. Harsanyi
  John F. Nash Jr.
  John E. Walker
  John Pople
  John Hume
  John B. Fenn
  John E. Sulston
  John L. Hall
  John C. Mather
  John B. Gurdon
  John O'Keefe
  John M. Kosterlitz

Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

SELECT yr, subject, winner
FROM nobel
WHERE (subject='physics' AND yr=1980) OR (subject='chemistry' AND yr=1984)

  yr	subject	winner
  1980	Physics	James Cronin
  1980	Physics	Val Fitch
  1984	Chemistry	Bruce Merrifield

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')

  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

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 (yr<1910 AND subject='medicine') OR (yr>=2004 AND subject='literature')

        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

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

SELECT * FROM nobel
WHERE winner='peter grÜnberg'

  yr	subject	winner
  2007	Physics	Peter Grünberg

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

SELECT * FROM nobel
WHERE winner='eugene o''neill'

  yr	subject	winner
  1936	Literature	Eugene O'Neill

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 ASC

        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
  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 Venkata Raman	1930	Physics
  Sir Frederick Hopkins	1929	Medicine
  Sir Austen Chamberlain	1925	Peace
  Sir William Ramsay	1904	Chemistry

The expression subject IN ('chemistry','physics') can be used as a value - it will be 0 or 1.

1984년에 수상한 수상자들을 출력하는데 우선 subject 기준으로 오름차순으로 정렬하고 winner 기준으로 오름차순으로 정렬하라. 이때 subject에서 chemistry 와 physics 값이 들어있는 데이터는 가장 뒤로 정렬하라.

SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY 
CASE WHEN subject IN ('physics', 'chemistry')
THEN 1
ELSE 0
END, subject, winner

      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

ORDER BY절

• 테이블에서 조회한 데이터를 정렬하고 싶을 때 ORDER BY절을 쓰게 됩니다.
• ORDER BY 절은 기본적으로 오름차순입니다.

SELECT*
FROM 테이블명
ORDER BY  칼럼명 (ASC,DESC)

CASE WHEN절

• 조건에 따라 서로 다른 값을 반환한다.
• 프로그래밍 언어에서 if문과 유사하다

CASE WHEN 조건식 THEN 반환값
	ELSE 'N/A' //조건에 만족하지 않을 경우 반환값
END