SQLZOO（SELECT from Nobel Tutorial）Writeup

转载自SQLZOO（SELECT from Nobel Tutorial）Writeup

1.

SELECT yr, subject, winner
  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>=1980 and yr<1990

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 (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')

9.

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

10.

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

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 asc

14.

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