SQL的一点总结(持续更新。。。)

1、Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.显示1984年的获奖者按学科和获奖者排序;但化学和物理排在后面。

对应的SQL语句为：SELECT winner, subject
                      FROM nobel
                       WHERE yr=1984
                       ORDER BY subject IN ('Physics','Chemistry'),subject ,winner ;

2、查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節Find all details of the prize won by EUGENE O'NEILL

对应的SQL语句为：SELECT *
                      FROM nobel

                      WHERE winner='EUGENE O''NEILL';（不能把一个单引号直接的放在字符串中。但可连续使用两个单引号在字符串中当作一个单引号）

3、找出哪些國家的人口是高於歐洲每一國的人口。

SELECT name FROM world
 WHERE population >ALL
      (SELECT population FROM world
        WHERE continent='Europe')；

4、顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

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

5、哪幾年頒發了物理獎，但沒有頒發化學獎?

SELECT DISTINCT yr FROM nobel
WHERE subject='Physics' AND yr NOT IN(SELECT yr FROM nobel WHERE subject='Chemistry');

6、首次頒發的經濟獎 (Economics)的得獎者是誰?

SELECT winner FROM nobel
WHERE subject='Economics'
      AND yr IN(SELECT min(yr) FROM nobel WHERE subject='Economics');