[SQL][sqlzoo] SELECT from Nobel

SELECT from Nobel                                                                                                                      

SELECT from Nobel Tutorial - SQLZOO

1.Winners from 1950

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

2.1962 Literature

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

3.Albert Einstein

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

4.Recent Peace Prizes

select winner 
from nobel 
where subject='peace' and yr>=2000

5.Literature in the 1980's

select yr,subject,winner 
from nobel 
where subject='Literature' and yr between 1980 and 1989

6.Only Presidents

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

7.John

select winner 
from nobel 
where winner like'john%'

8.Chemistry and Physics from different years

select yr,subject,winner 
from nobel 
where (subject='Physics'and yr='1980') or (subject='Chemistry' and yr='1984')

9.Exclude Chemists and Medics

select * 
from nobel 
where yr='1980' and subject not in ('Chemistry', 'Medicine')

10.Early Medicine, Late Literature

select * 
from nobel 
where ( subject='Medicine' and yr<'1910') or ( subject='Literature' and yr>='2004')

11.Umlaut

select * 
from nobel 
where winner='PETER GRÜNBERG'

12.Apostrophe

select * 
from nobel 
where winner like'EUGENE O%'

13.Knights of the realm

select winner, yr, subject 
from nobel 
where winner like 'Sir%' 
order by yr desc, winner

14.Chemistry and Physics last

SELECT winner, subject 
FROM nobel 
WHERE yr=1984 
ORDER BY subject IN ('Physics','Chemistry'), subject, winner;

Nobel Quiz                                                                                                                                         

1.

SELECT winner FROM nobel
 WHERE winner LIKE 'C%' AND winner LIKE '%n'

2.

SELECT COUNT(subject) FROM nobel
 WHERE subject = 'Chemistry'
   AND yr BETWEEN 1950 and 1960

3.

SELECT COUNT(DISTINCT yr) FROM nobel
 WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')

4.

Medicine	Sir John Eccles
Medicine	Sir Frank Macfarlane Burnet

5.

SELECT yr FROM nobel
 WHERE yr NOT IN(SELECT yr 
                   FROM nobel
                 WHERE subject IN ('Chemistry','Physics'))

6.

SELECT DISTINCT yr
  FROM nobel
 WHERE subject='Medicine' 
   AND yr NOT IN(SELECT yr FROM nobel 
                  WHERE subject='Literature')
   AND yr NOT IN (SELECT yr FROM nobel
                   WHERE subject='Peace')

7.

Chemistry	1
Literature	1
Medicine	2
Peace	1
Physics	1