Difference between revisions of "Nested SELECT Quiz/zh"

From SQLZOO
Jump to navigation Jump to search
(Created page with "Nested SELECT quiz <div class='ref_section'> <table class='db_ref'> <caption>bbc</caption> <tr> <th>name</th> <th>region</th> <th>area</th> <th>population</th> <th>gdp</th> </...")
 
Line 1: Line 1:
Nested SELECT quiz
+
子查詢測驗
 
<div class='ref_section'>
 
<div class='ref_section'>
 
<table class='db_ref'>
 
<table class='db_ref'>
 
<caption>bbc</caption>
 
<caption>bbc</caption>
 
<tr>
 
<tr>
<th>name</th>
+
<th>name國家名</th>
<th>region</th>
+
<th>region區域</th>
<th>area</th>
+
<th>area面積</th>
<th>population</th>
+
<th>population人口</th>
<th>gdp</th>
+
<th>gdp國民生產總值</th>
 
</tr>
 
</tr>
 
<tr>
 
<tr>
Line 58: Line 58:
 
</div>
 
</div>
 
<div class=quiz>
 
<div class=quiz>
<div class=q>Select the code that shows the name, region and population of the smallest country in each region
+
<div class=q>選擇代碼以顯示在每個區域人口最小的國家的國家名稱,區域和人口。
 
<syntaxhighlight class=d lang='sql'> SELECT region, name, FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0) </syntaxhighlight>
 
<syntaxhighlight class=d lang='sql'> SELECT region, name, FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0) </syntaxhighlight>
 
<syntaxhighlight class=d lang='sql'> SELECT region, name, population FROM bbc WHERE population <= ALL (SELECT population FROM bbc WHERE population>0) </syntaxhighlight>
 
<syntaxhighlight class=d lang='sql'> SELECT region, name, population FROM bbc WHERE population <= ALL (SELECT population FROM bbc WHERE population>0) </syntaxhighlight>
Line 66: Line 66:
 
</div>
 
</div>
  
<div class=q>Select the code that shows the countries belonging to regions with all populations over 50000
+
<div class=q>選擇代碼以顯示國家名稱,該國所在的地區每國人口都超過50000。
 
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE population>0)
 
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE population>0)
 
</syntaxhighlight>
 
</syntaxhighlight>
Line 75: Line 75:
 
</div>
 
</div>
  
<div class=q>Select the code that shows the countries with a less than a third of the population of the countries around it
+
<div class=q>選擇代碼以顯示國家名稱,該國家人口少於它周圍的全部國家的人口三分之一。
 
<syntaxhighlight class='d y' lang='sql'>
 
<syntaxhighlight class='d y' lang='sql'>
 
SELECT name, region FROM bbc x
 
SELECT name, region FROM bbc x
Line 89: Line 89:
 
</div>
 
</div>
  
<div class=q>Select the result that would be obtained from the following code:
+
<div class=q>選擇你會從這個代碼獲得的結果。
 
<source lang=sql>
 
<source lang=sql>
 
SELECT name FROM bbc
 
SELECT name FROM bbc
Line 108: Line 108:
 
</div>
 
</div>
  
<div class=q>Select the code that would show the countries with a greater GDP than any country in Africa
+
<div class=q>選擇代碼以顯示國家名稱,該國有比非洲任何國家更大的國內生產總值GDP。
 
<syntaxhighlight class=d lang='sql'>
 
<syntaxhighlight class=d lang='sql'>
 
SELECT name FROM bbc
 
SELECT name FROM bbc
Line 126: Line 126:
 
</div>
 
</div>
  
<div class=q>Select the code that shows the countries with population smaller than Russia but bigger than Denmark
+
<div class=q>選擇代碼以顯示國家名稱,該國人口比俄羅斯少,但比丹麥的多。
 
<syntaxhighlight class=d lang='sql'>
 
<syntaxhighlight class=d lang='sql'>
 
SELECT name FROM bbc
 
SELECT name FROM bbc
Line 149: Line 149:
 
</div>
 
</div>
  
<div class=q>>Select the result that would be obtained from the following code:
+
<div class=q>>選擇你會從這個代碼獲得的結果。
 
<source lang=sql>
 
<source lang=sql>
 
SELECT name FROM bbc
 
SELECT name FROM bbc

Revision as of 17:21, 26 March 2016

子查詢測驗

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
FranceEurope
GermanyEurope
RussiaEurope
TurkeyEurope
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