[SQL][sqlzoo] SELECT from Nobel

0.1. SELECT from Nobel                                                                                                                      

SELECT from Nobel Tutorial - SQLZOO

1. 1.Winners from 1950

      

      

      

    
    

      <sql />
    
SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

2. 2.1962 Literature

      

      

      

    
    

      <sql />
    
SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

3. 3.Albert Einstein

      

      

      

    
    

      <sql />
    
SELECT yr, subject
  FROM nobel
 WHERE winner =  'Albert Einstein'

4. 4.Recent Peace Prizes

      

      

      

    
    

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

5. 5.Literature in the 1980's

      

      

      

    
    

      <sql />
    
select yr,subject,winner 
from nobel 
where subject='Literature' and yr between 1980 and 1989

6. 6.Only Presidents

      

      

      

    
    

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

7. 7.John

      

      

      

    
    

      <sql />
    
select winner 
from nobel 
where winner like'john%'

8. 8.Chemistry and Physics from different years

      

      

      

    
    

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

9. 9.Exclude Chemists and Medics

      

      

      

    
    

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

10. 10.Early Medicine, Late Literature

      

      

      

    
    

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

11. 11.Umlaut

      

      

      

    
    

      <sql />
    
select * 
from nobel 
where winner='PETER GRÜNBERG'

12. 12.Apostrophe

      

      

      

    
    

      <sql />
    
select * 
from nobel 
where winner like'EUGENE O%'

13. 13.Knights of the realm

      

      

      

    
    

      <sql />
    
select winner, yr, subject 
from nobel 
where winner like 'Sir%' 
order by yr desc, winner

14. 14.Chemistry and Physics last

      

      

      

    
    

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

14.1. Nobel Quiz                                                                                                                                         

1.

      

      

      

    
    

      <sql />
    
SELECT winner FROM nobel
 WHERE winner LIKE 'C%' AND winner LIKE '%n'

2.

      

      

      

    
    

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

3.

      

      

      

    
    

      <sql />
    
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.

      

      

      

    
    

      <sql />
    
SELECT yr FROM nobel
 WHERE yr NOT IN(SELECT yr 
                   FROM nobel
                 WHERE subject IN ('Chemistry','Physics'))

6.

      

      

      

    
    

      <sql />
    
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

15.