Nested SELECT Quiz/zh

子查詢測驗

bbc

name國家名	region區域	area面積	population人口	gdp國民生產總值
Afghanistan	South Asia	652225	26000000
Albania	Europe	28728	3200000	6656000000
Algeria	Middle East	2400000	32900000	75012000000
Andorra	Europe	468	64000
Bangladesh	South Asia	143998	152600000	67144000000
United Kingdom	Europe	242514	59600000	2022824000000
...

選擇代碼以顯示在每個區域人口最小的國家的國家名稱，區域和人口。

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

 SELECT region, name, population FROM bbc WHERE population <= ALL (SELECT population FROM bbc WHERE population>0)

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

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

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

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

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

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

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

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

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

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

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)

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)

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)

 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)

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

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

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')

Table-A

Andorra

Albania

Austria

Bulgaria

Table-B

France	Europe
Germany	Europe
Russia	Europe
Turkey	Europe

Table-C

France

Germany

Andorra

Albania

Table-D

France

Germany

Russia

Turkey

Table-E

France

Germany

Russia

Turkey

Brazil

United States of USA

Canada

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

SELECT name FROM bbc
 WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0)

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

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

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

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

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

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

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

SELECT name FROM bbc
 WHERE population = (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

SELECT name FROM bbc
 WHERE population > (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

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

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

SELECT name FROM bbc
 WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

Table-A

Afghanistan

Bhutan

Nepal

Sri Lanka

The Maldives

Table-B

Bangladesh

India

Pakistan

Table-C

China

India

Table-D

Brazil

Bangladesh

China

India

Table-E

France

Germany

Russia

Trukey