sqlzoo--select from nobel ,select in select

• sqlzoo–select basics,select from world

• sqlzoo–select from nobel ,select in select

• sqlzoo–sum and count

• sqlzoo–the join operation

• sqlzoo–More JOIN operations

• sqlzoo–using null

• sqlzoo–self join

select from nobel

select from nobel 字段：yr（年份）,subject（奖项）,winner（获奖者）

yr	subject	winner
1960	Chemistry	Willard F. Libby
1960	Literature	Saint-John Perse
1960	Medicine	Sir Frank Macfarlane Burnet
1960	Medicine	Peter Madawar
…

1. 查询1950年诺贝尔奖的奖项资料

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

2. 查询1962年文学奖获得者

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

3. 查询爱因斯坦的获奖年份和奖项

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

4. 查询2000年及以后的和平奖获得者

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

5. 查询1980年及1989年（包括首尾）的文学奖获得者的所有信息

   SELECT * FROM nobel
    WHERE (yr BETWEEN 1980 AND 1989) 
          AND subject = 'Literature';
   

6. 查询总统获奖者的所有细节：

   • Theodore Roosevelt
   • Woodrow Wilson
   • Jimmy Carter

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

7. 显示名字为John的得奖者

   SELECT winner FROM nobel
    WHERE left(winner, 4) = 'John';
   

8. 查询1980年物理学获奖者及1984年化学奖获得者

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

9. 查询1980年获奖者，但不包括化学奖和医学奖

   SELECT * FROM nobel
    WHERE NOT subject in ('Chemistry', 'Medicine')
          AND yr = 1980; 
   

10. 查询早期医学奖获得者（1910之前，不包括1910），及近年文学奖获得者（2004年以後，包括2004年）

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

11. 查询名为PETER GRÜNBERG获奖者的所有信息

    SELECT * FROM nobel
     WHERE winner = 'PETER GRÜNBERG';
    

12. 查询名为EUGENE O’NEILL获奖者的所有信息

    SELECT * FROM nobel
     WHERE winner = 'EUGENE O\'NEILL';
    

13. 列出爵士获得者，年份，奖项（爵士名字以Sir开始）。先显示最新获奖者，然后同年再按名字顺序排列。

    SELECT winner, yr, subject FROM nobel
     WHERE left(winner, 3) = 'Sir'
      order by yr desc;
    

14. Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

    显示1984年获奖者和奖项，按奖项和获奖者名称排序，要求Chemistry和Physics排在最后

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

SELECT within SELECT

name國家名	continent洲份	area面積	population人口	gdp國民生產總值
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
…

1. 查询人口大于俄罗斯人口数的国家名称

   SELECT name FROM world
     WHERE population >
        (SELECT population FROM world
         WHERE name='Russia');
   

2. 人均gdp高于英国的欧洲国家的人均gdp

   SELECT name FROM world
     WHERE gdp/population >
        (SELECT gdp/population FROM world
         WHERE name='United Kingdom') AND continent = 'Europe';
   

3. 找出阿根廷以及澳大利亚所在大洲对应的国家名称和大洲名，按国家名称顺序排序

   SELECT name,continent FROM world
     WHERE continent IN 
     (SELECT continent FROM world 
        WHERE name = 'Argentina' OR name = 'Australia') 
     ORDER BY name;
   

4. 找出人口比加拿大多但比波兰人口少的国家的名称和人口数

   SELECT name,population FROM world
     WHERE population > (SELECT population from world where name = 'Canada')
       AND population < (SELECT population from world where name = 'Poland');
   

5. 显示欧洲国家名称和其人口，以占德国人口的百分比作为人口显示

   SELECT name,
          CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name = 'Germany')),'%') 
   FROM world
     WHERE continent = 'Europe';
   

6. ALL运算符是一个逻辑运算符，它可将单个值与子查询返回的单列值集进行比较

   哪些国家的gdp比欧洲所有国家的gdp都要高？(列出name)

   SELECT name FROM world 
     WHERE gdp > ALL(SELECT gdp FROM world 
                       WHERE gdp > 0 
                         AND continent = 'Europe');
   

7. 在每一个州中找出最大面积的国家，列出州名，国名以及面积（其中部分area为null）

   SELECT continent, name, area FROM world x
     WHERE area >= ALL
       (SELECT area FROM world y
           WHERE y.continent=x.continent
             AND area>0);
   

8. 列出州名，和每个洲际中国家名字按字母顺序排在首位的国家名。

   SELECT continent, name FROM world x
     WHERE name = (SELECT name FROM world y
           WHERE y.continent=x.continent
           ORDER BY name
           LIMIT 1);
   

9. 找出所有国家都少于或等于25000000人口的州，列出国家名，州份和人口。

   SELECT name,continent,population FROM world x
     WHERE 25000000 >= ALL(select population FROM world y 
                             WHERE x.continent = y.continent 
                             AND population > 0);
   

10. 有些国家的人口是同州份所有国家的三倍以上，列出国家名字和州份

    SELECT name,continent FROM world x
      WHERE population/3 >= ALL(select population FROM world y 
                              WHERE x.continent = y.continent 
                              AND x.name != y.name
                              AND population > 0);