world表：

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
… … … … …

name: 国家名称
continent: 洲
area: 面积
population: 人口
gdp: 国内生产总值

SELECT within SELECT Tutorial/zh

1
2
3
4
5
1.列出每個國家的名字 name，當中人口 population 是高於俄羅斯'Russia'的人口。
SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')
1
2
3
4
5
2.列出歐州每國家的人均GDP，當中人均GDP要高於英國'United Kingdom'的數值。
select name from world
where continent = 'Europe' and gdp/population >
(select gdp/population from world
where name = 'United Kingdom')
1
2
3
4
5
6
3.在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序
select name,continent from world
where continent in 
(select continent from world
where name in ('Argentina','Australia'))
order by name
1
2
3
4
5
6
7
8
4.哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口population 。
select name,population from world
where population >
(select population from world
where name = 'Canada')
and population < 
(select population from world
where name = 'Poland')
1
2
3
4
5.Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。
顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。
select name,concat(round((population/(select population from world where name = 'Germany'))*100,0),'%') from world
where continent = 'Europe'
1
2
3
4
6.哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)
select name from world
where gdp > all(select gdp from world
where gdp > 0 and continent = 'Europe')
1
2
3
4
5
6
7.在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，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)
1
2
3
4
5
6
7
8
8.列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)
select continent,name from world w1
where name = (select name from world w2
where w1.continent = w2.continent
order by name limit 1)
另一种方法，直接使用group by
select continent,min(name) from world
group by continent

More difficult

1
2
3
4
9.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。
select name,continent,population from world w1
where 25000000 >= all(select population from world w2
where w2.continent = w1.continent)
1
2
3
4
10.有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。
select name,continent from world w1
where w1.population >= all(select 3*population from world w2
where w1.continent = w2.continent and w2.population > 0 and w1.name != w2.name)

The nobel table can be used to practice more subquery./zh

1
2
3
4
1.紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。 試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份。
select winner,yr from nobel
where subject = 'Literature' and yr in (select yr from nobel 
where winner = 'International Committee of the Red Cross' and subject = 'Peace')
1
2
3
2.日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱。
select winner from nobel
where winner != 'Toshihide Maskawa' and subject = 'Physics' and yr = (select yr from nobel where winner = 'Toshihide Maskawa' and subject = 'Physics')
1
2
3
4
3.首次頒發的經濟獎 (Economics)的得獎者是誰?
select winner from nobel
where subject = 'Economics' and yr = (select min(yr) from nobel
where subject = 'Economics')
1
2
3
4
4.哪幾年頒發了物理獎，但沒有頒發化學獎?
select distinct yr from nobel
where subject = 'Physics' and yr not in (select distinct yr from nobel
where subject = 'Chemistry')
1
2
3
4
5
5.哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份，獎項和得獎者。
select yr,subject,winner from nobel
where yr in (select yr from nobel
group by yr
having count(winner) > 12)
1
2
3
4
5
6.哪些得獎者獲獎多於1次呢？他們是哪一年獲得哪項獎項呢？ 列出他們的名字，獲獎年份及獎項。先按名字，再按年份順序排序。
select winner,yr,subject from nobel
where winner in (select winner from nobel
group by winner having count(yr) > 1)
order by winner,yr

Quiz

_5X1B_63_NIY~__@O_P_7VX.png
1.選擇代碼以顯示在每個區域人口最小的國家的國家名稱，區域和人口。
Answer：

1
SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)

2.選擇代碼以顯示國家名稱，該國所在的地區每國人口都超過50000。
Answer：

1
SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)

3.選擇代碼以顯示國家名稱，該國家人口少於它周圍的全部國家的人口三分之一。
Answer：

1
2
SELECT name, region FROM bbc x
 WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)

4.選擇你會從這個代碼獲得的結果。

1
2
3
4
5
6
7
8
9
SELECT name FROM bbc
 WHERE population >
       (SELECT population
          FROM bbc
         WHERE name='United Kingdom')
   AND region IN
       (SELECT region
          FROM bbc
         WHERE name = 'United Kingdom')

Answer：
t4.png
5.選擇代碼以顯示國家名稱，該國有比非洲任何國家更大的國內生產總值GDP。
Answer：

1
2
SELECT name FROM bbc
 WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL)

6.選擇代碼以顯示國家名稱，該國人口比俄羅斯少，但比丹麥的多。
Answer：

1
2
3
SELECT name FROM bbc
 WHERE population < (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

7.選擇你會從這個代碼獲得的結果。

1
2
3
4
5
6
SELECT name FROM bbc
 WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

Answer：
t7.png

Summary

  • 其实感觉all和max，min相似…
  • 有多个非指定数值的筛选条件的话一般都存在子查询，多重查询
  • select的结果可能一行也有可能是多行，注意外层分别用’=’和’in’的情况
  • 多重查询记得考虑内外表是否有关联