Difference between revisions of "Nested SELECT Quiz"

From SQLZOO
Jump to: navigation, search
 
(9 intermediate revisions by 5 users not shown)
Line 57: Line 57:
 
</table>
 
</table>
 
</div>
 
</div>
<quiz shuffle=none display=simple>
+
<div class=quiz>
{Select the code that shows the name, region and population of the smallest country in each region
+
<div class=q>Select the code that shows the name, region and population of the smallest country in each region
|type="()"}
+
<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 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 lang='sql'> SELECT region, name, population FROM bbc WHERE population <= ALL (SELECT population FROM bbc WHERE population>0) </syntaxhighlight>
+
<syntaxhighlight class='d y' lang='sql'> SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0) </syntaxhighlight>
+ <syntaxhighlight lang='sql'> SELECT region, name, population 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 x WHERE population = ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT region, name, population 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 x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population<0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population<0) </syntaxhighlight>
+
</div>
  
{Select the code that shows the countries belonging to regions with all populations over 50000
+
<div class=q>Select the code that shows the countries belonging to regions with all populations over 50000
|type="()"}
+
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE population>0)
- <syntaxhighlight lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE population>0)
+
</syntaxhighlight>
+ <syntaxhighlight lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
+
<syntaxhighlight class='d y' lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 = ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 = ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 >= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 50000 > ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name,region,population FROM bbc x WHERE 500000 <= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'> SELECT name,region,population FROM bbc x WHERE 500000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0) </syntaxhighlight>
 +
</div>
  
{Select the code that shows the countries with a third of the population of the countries around it
+
<div class=q>Select the code that shows the countries with a less than a third of the population of the countries around it
|type="()"}
+
<syntaxhighlight class='d y' lang='sql'>
+ <syntaxhighlight lang='sql'> 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) </syntaxhighlight>
+
SELECT name, region FROM bbc x
- <syntaxhighlight lang='sql'> 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) </syntaxhighlight>
+
WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name) </syntaxhighlight>
- <syntaxhighlight lang='sql'> 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) </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
- <syntaxhighlight lang='sql'> 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) </syntaxhighlight>
+
SELECT name, region FROM bbc x
- <syntaxhighlight lang='sql'> SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.name != x.name)
+
WHERE population = ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
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) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'> 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) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'> SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.name != x.name)</syntaxhighlight>
 +
</div>
  
{Select the result that would be obtained from the following code:  
+
<div class=q>Select the result that would be obtained from the following code:  
 
<source lang=sql>
 
<source lang=sql>
 
SELECT name FROM bbc
 
SELECT name FROM bbc
Line 94: Line 101:
 
         WHERE name = 'United Kingdom')
 
         WHERE name = 'United Kingdom')
 
</source>
 
</source>
<table><caption>Table-A</caption><tr><td>Andorra</td></tr><tr><td>Albania</td></tr><tr><td>Austria</td></tr><tr><td>Bulgaria</td></tr></table>
+
<table class=d><caption>Table-A</caption><tr><td>Andorra</td></tr><tr><td>Albania</td></tr><tr><td>Austria</td></tr><tr><td>Bulgaria</td></tr></table>
<table><caption>Table-B</caption><tr><td>France</td><td>Europe</td></tr><tr><td>Germany</td><td>Europe</td></tr><tr><td>Russia</td><td>Europe</td></tr><tr><td>Turkey</td><td>Europe</td></tr></table>
+
<table class=d><caption>Table-B</caption><tr><td>France</td><td>Europe</td></tr><tr><td>Germany</td><td>Europe</td></tr><tr><td>Russia</td><td>Europe</td></tr><tr><td>Turkey</td><td>Europe</td></tr></table>
<table><caption>Table-C</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Andorra</td></tr><tr><td>Albania</td></tr></table>
+
<table class=d><caption>Table-C</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Andorra</td></tr><tr><td>Albania</td></tr></table>
<table><caption>Table-D</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Turkey</td></tr></table>
+
<table class='d y'><caption>Table-D</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Turkey</td></tr></table>
<table><caption>Table-E</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Turkey</td></tr><tr><td>Brazil</td></tr><tr><td>United States of USA</td></tr><tr><td>Canada</td></tr></table>
+
<table class=d><caption>Table-E</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Turkey</td></tr><tr><td>Brazil</td></tr><tr><td>United States of USA</td></tr><tr><td>Canada</td></tr></table>
|type="()"}
+
</div>
- Table-A
+
- Table-B
+
- Table-C
+
+ Table-D
+
- Table-E
+
  
{Select the code that would show the countries with a greater GDP then any country in Africa
+
<div class=q>Select the code that would show the countries with a greater GDP than any country in Africa
|type="()"}
+
<syntaxhighlight class=d lang='sql'>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0) </syntaxhighlight>
+
SELECT name FROM bbc
+ <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL) </syntaxhighlight>
+
WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0) </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE gdp > ALL (SELECT MIN(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL) </syntaxhighlight>
+
<syntaxhighlight class='d y' lang='sql'>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND population IS NOT NULL) </syntaxhighlight>
+
SELECT name FROM bbc
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Europe' AND gdp IS NOT NULL) </syntaxhighlight>
+
WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE gdp > ALL (SELECT MIN(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND population IS NOT NULL) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Europe' AND gdp IS NOT NULL) </syntaxhighlight>
 +
</div>
  
{Select the code that shows the countries with population smaller than Russia but bigger than Denmark
+
<div class=q>Select the code that shows the countries with population smaller than Russia but bigger than Denmark
|type="()"}
+
<syntaxhighlight class=d lang='sql'>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Denmark') AND population > (SELECT population FROM bbc WHERE name='Russia') </syntaxhighlight>
+
SELECT name FROM bbc
+ <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
+
WHERE population < (SELECT population FROM bbc WHERE name='Denmark')
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population = (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
+
  AND population > (SELECT population FROM bbc WHERE name='Russia') </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population > (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
+
<syntaxhighlight class='d y' lang='sql'>
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Russia' AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
+
SELECT name FROM bbc
 +
WHERE population < (SELECT population FROM bbc WHERE name='Russia')
 +
  AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE population = (SELECT population FROM bbc WHERE name='Russia')
 +
  AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE population > (SELECT population FROM bbc WHERE name='Russia')
 +
  AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE population < (SELECT population FROM bbc WHERE name='Russia'
 +
  AND population > (SELECT population FROM bbc WHERE name='Denmark') </syntaxhighlight>
 +
</div>
  
{Select the result that would be obtained from the following code: SELECT name FROM bbc WHERE population > ALL (SELECT MAX(population) FROM bbc WHERE region = 'Europe') AND region ='South Asia'
+
<div class=q>>Select the result that would be obtained from the following code:  
<table><caption>Table-A</caption><tr><td>Afghanistan</td></tr><tr><td>Bhutan</td></tr><tr><td>Nepal</td></tr><tr><td>Sri Lanka</td></tr><tr><td>The Maldives</td></tr></table>
+
<source lang=sql>
<table><caption>Table-B</caption><tr><td>Bangladesh</td></tr><tr><td>India</td></tr><tr><td>Pakistan</td></tr></table>
+
SELECT name FROM bbc
<table><caption>Table-C</caption><tr><td>China</td></tr><tr><td>India</td></tr></table>
+
WHERE population > ALL
<table><caption>Table-D</caption><tr><td>Brazil</td></tr><tr><td>Bangladesh</td></tr><tr><td>China</td></tr><tr><td>India</td></tr></table>
+
      (SELECT MAX(population)
<table><caption>Table-E</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Trukey</td></tr></table>
+
          FROM bbc
|type="()"}
+
        WHERE region = 'Europe')
- Table-A
+
  AND region = 'South Asia'
+ Table-B
+
</source>
- Table-C
+
<table class=d><caption>Table-A</caption><tr><td>Afghanistan</td></tr><tr><td>Bhutan</td></tr><tr><td>Nepal</td></tr><tr><td>Sri Lanka</td></tr><tr><td>The Maldives</td></tr></table>
- Table-D
+
<table class='d y'><caption>Table-B</caption><tr><td>Bangladesh</td></tr><tr><td>India</td></tr><tr><td>Pakistan</td></tr></table>
- Table-E
+
<table class=d><caption>Table-C</caption><tr><td>China</td></tr><tr><td>India</td></tr></table>
</quiz>
+
<table class=d><caption>Table-D</caption><tr><td>Brazil</td></tr><tr><td>Bangladesh</td></tr><tr><td>China</td></tr><tr><td>India</td></tr></table>
 +
<table class=d><caption>Table-E</caption><tr><td>France</td></tr><tr><td>Germany</td></tr><tr><td>Russia</td></tr><tr><td>Trukey</td></tr></table>
 +
</div>
 +
</div>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Latest revision as of 20:47, 27 April 2015

Nested SELECT quiz

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 the code that shows the name, region and population of the smallest country in each region
 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)
Select the code that shows the countries belonging to regions with all populations over 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 the code that shows the countries with a less than a third of the population of the countries around it
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 the result that would be obtained from the following code:
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
Select the code that would show the countries with a greater GDP than any country in Africa
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 the code that shows the countries with population smaller than Russia but bigger than Denmark
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 the result that would be obtained from the following code:
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