Answers for sqlzoo.net tutorials

sqlzoo-answers.sql

--http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

--1.
SELECT * FROM nobel WHERE yr = 1950

--2.
SELECT winner FROM nobel WHERE yr = 1962 AND subject = 'Literature'

--3.
SELECT yr, subject FROM nobel WHERE winner = 'Albert Einstein'

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

--5.
SELECT yr, subject, winner FROM nobel WHERE subject = 'Literature' AND yr BETWEEN 1980 AND 1989

--6.
SELECT * FROM nobel where winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')

--7.
SELECT winner FROM nobel WHERE winner LIKE 'John%'

--8.
SELECT * FROM nobel WHERE (subject = 'Physics' AND yr = 1980) OR (subject = 'Chemistry' AND yr = 1984)

--9.
SELECT * FROM nobel WHERE yr = 1980 AND NOT (subject = 'Chemistry' OR subject = 'Medicine')

--10.
SELECT * FROM nobel WHERE (yr < 1910 AND subject = 'Medicine') OR (yr >= 2004 AND subject = 'Literature')

--11.
SELECT * FROM nobel WHERE winner = 'Peter Grünberg'

--12.
SELECT * FROM nobel WHERE winner = 'Eugene O''neill'

--13.
SELECT winner, yr, subject FROM nobel WHERE winner LIKE 'Sir%' ORDER BY yr DESC, winner

--14.
SELECT winner, subject FROM nobel WHERE yr = 1984 ORDER BY CASE WHEN subject IN ('Chemistry', 'Physics') THEN 2 ELSE 1 END, subject, winner