Difference between revisions of "Nested SELECT Quiz"
Jump to navigation
Jump to search
Kobashi.kaz (talk | contribs) |
|||
(12 intermediate revisions by 7 users not shown) | |||
Line 1: | Line 1: | ||
+ | {{Languages}} | ||
Nested SELECT quiz | Nested SELECT quiz | ||
<div class='ref_section'> | <div class='ref_section'> | ||
Line 57: | Line 58: | ||
</table> | </table> | ||
</div> | </div> | ||
− | <quiz | + | <div class=quiz> |
− | + | <div class=q>Select the code that shows the name, region and population of the smallest country in each region | |
− | + | <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 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 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 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> | |
− | + | </div> | |
− | + | <div class=q>Select the code that shows the countries belonging to regions with all populations over 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> | |
− | + | <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 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 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 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> | ||
− | + | <div class=q>Select the code that shows the countries with a less than a third of the population of the countries around it | |
− | + | <syntaxhighlight class='d y' 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.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> | ||
− | + | <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 102: | ||
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> |
− | + | </div> | |
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | <div class=q>Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values). | |
− | + | <syntaxhighlight class=d lang='sql'> | |
− | + | SELECT name FROM bbc | |
− | + | WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0) </syntaxhighlight> | |
− | + | <syntaxhighlight class='d y' lang='sql'> | |
− | + | SELECT name FROM bbc | |
− | + | WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa') </syntaxhighlight> | |
+ | <syntaxhighlight class=d lang='sql'> | ||
+ | SELECT name FROM bbc | ||
+ | WHERE gdp > ALL (SELECT MIN(gdp) FROM bbc WHERE region = 'Africa') </syntaxhighlight> | ||
+ | <syntaxhighlight class=d lang='sql'> | ||
+ | SELECT name FROM bbc | ||
+ | WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa') </syntaxhighlight> | ||
+ | <syntaxhighlight class=d lang='sql'> | ||
+ | SELECT name FROM bbc | ||
+ | WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa' AND gdp<>NULL) </syntaxhighlight> | ||
+ | </div> | ||
− | + | <div class=q>Select the code that shows the countries with population smaller than Russia but bigger than Denmark | |
− | + | <syntaxhighlight class=d 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> | |
− | + | <syntaxhighlight class='d y' 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> | ||
+ | <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> | ||
− | + | <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 |
− | + | WHERE region = 'Europe') | |
− | + | AND region = 'South Asia' | |
− | + | </source> | |
− | + | <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 class='d y'><caption>Table-B</caption><tr><td>Bangladesh</td></tr><tr><td>India</td></tr><tr><td>Pakistan</td></tr></table> | |
− | + | <table class=d><caption>Table-C</caption><tr><td>China</td></tr><tr><td>India</td></tr></table> | |
− | </ | + | <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 15:51, 18 April 2018
Language: | English • 日本語 • 中文 |
---|
Nested SELECT quiz
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')
Andorra |
Albania |
Austria |
Bulgaria |
France | Europe |
Germany | Europe |
Russia | Europe |
Turkey | Europe |
France |
Germany |
Andorra |
Albania |
France |
Germany |
Russia |
Turkey |
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 (some countries may have NULL gdp values).
SELECT name FROM bbc
WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0)
SELECT name FROM bbc
WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')
SELECT name FROM bbc
WHERE gdp > ALL (SELECT MIN(gdp) FROM bbc WHERE region = 'Africa')
SELECT name FROM bbc
WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa')
SELECT name FROM bbc
WHERE gdp > ALL (SELECT gdp FROM bbc WHERE region = 'Africa' AND gdp<>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'
Afghanistan |
Bhutan |
Nepal |
Sri Lanka |
The Maldives |
Bangladesh |
India |
Pakistan |
China |
India |
Brazil |
Bangladesh |
China |
India |
France |
Germany |
Russia |
Trukey |