Difference between revisions of "SUM and COUNT Quiz"
Line 104: | Line 104: | ||
{Pick the result that would be obtained from the following code: SELECT region, SUM(area) FROM bbc GROUP BY region HAVING SUM(area)<= 20000000 | {Pick the result that would be obtained from the following code: SELECT region, SUM(area) FROM bbc GROUP BY region HAVING SUM(area)<= 20000000 | ||
− | <table style='float:left'><caption>Table-A</caption><tr><td>732240</td></tr><tr><td>13403102</td></tr><tr><td>17740392</td></tr><tr><td>4943771</td></tr></table> | + | <table style='float:left'><caption>Table-A</caption><tr><td class="r">732240</td></tr><tr><td class="r">13403102</td></tr><tr><td class="r">17740392</td></tr><tr><td class="r">4943771</td></tr></table> |
− | <table style='float:left'><caption>Table-B</caption><tr><td>Africa</td><td>22550927</td></tr><tr><td>Asia-Pacific</td><td>28759578</td></tr><tr><td>Europe</td><td>23866987</td></tr><tr><td>North America</td><td>21660000</td></tr></table> | + | <table style='float:left'><caption>Table-B</caption><tr><td>Africa</td><td class="r">22550927</td></tr><tr><td>Asia-Pacific</td><td class="r">28759578</td></tr><tr><td>Europe</td><td class="r">23866987</td></tr><tr><td>North America</td><td class="r">21660000</td></tr></table> |
− | <table | + | <table class="sqlmine"><caption>Table-C</caption><tr><th>region</th><th>SUM(area)</th></tr><tr><td>Americas</td><td class="r">732240</td></tr><tr><td>Middle East</td><td class="r">13403102</td></tr><tr><td>South America</td><td class="r">17740392</td></tr><tr><td>South Asia</td><td class="r">4943771</td></tr></table> |
− | + | ||
− | |||
|type="()"} | |type="()"} | ||
- Table-A | - Table-A |
Revision as of 14:06, 9 May 2013
SUM and COUNT 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 | |
... |
<quiz shuffle=none display=simple> {Select the statement that shows the sum of population of all countries in 'Europe' |type="()"} - SELECT name, population FROM bbc WHERE region = 'Europe' - SELECT population FROM bbc WHERE region = 'Europe' SUM BY region + SELECT SUM(population) FROM bbc WHERE region = 'Europe' - SELECT SUM(population FROM bbc WHERE region = 'Europe') - SUM population FROM bbc WHERE region = 'Europe'
{Select the statement that shows the number of countries with population smaller than 150000 |type="()"} + SELECT COUNT(name) FROM bbc WHERE population < 150000 - SELECT COUNT(population < 150000) FROM bbc - SELECT name FROM bbc WHERE population < 150000 - SELECT population AS COUNT FROM bbc WHERE population < 150000 - SELECT SUM() FROM bbc WHERE population < 150000
{Select the full set of SQL aggregate functions |type="()"} - AVG(), COUNT(), FIRST(), LAST(), SUM() - AVG(), COUNT(), MAX(), MEDIAN(), MIN(), ROUND(), SUM() + AVG(), COUNT(), FIRST(), LAST(), MAX(), MIN(), SUM() - AVG(), COUNT(), MAX(), MIN(), SUM() - COUNT(), SUM()
{Select the result that would be obtained from the following code:SELECT region, SUM(area) FROM bbc WHERE SUM(area) > 15000000 GROUP BY region
Europe | 17000000 |
Europe | 17000000 |
Asia-Pacific | 23460000 |
North America | 21660000 |
Europe |
Asia-Pacific |
North America |
|type="()"} - Table-A - Table-B - Table-C - No result due to invalid use of the GROUP BY function + No result due to invalid use of the WHERE function
{Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark' |type="()"} - SELECT AVG(population) FROM bbc WHERE name = ('Poland', 'Germany', 'Denmark') + SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark') - SELECT AVG(population) FROM bbc WHERE name LIKE ('Poland', 'Germany', 'Denmark') - SELECT AVG(population) FROM bbc WHERE name LIKE (Poland, Germany, Denmark) - SELECT population FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')
{Select the statement that shows the medium population density of each region |type="()"} - SELECT region, AVG(population/area) AS density FROM bbc - SELECT region, COUNT(population)/COUNT(area) AS density FROM bbc GROUP BY region - SELECT region, SUM(population)/COUNT(area) AS density FROM bbc GROUP BY region - SELECT region, SUM(population)/SUM(area) AS density FROM bbc HAVING region + SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region
{Select the statement that shows the name and population density of the country with the largest population |type="()"} - SELECT name, density AS population/area FROM bbc WHERE population = MAX(population) - SELECT name, density AS population/area FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) - SELECT name, MAX (population) FROM bbc WHERE population / (SELECT area FROM bbc) + SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) - SELECT name, population/area AS density FROM bbc WHERE population > (SELECT MAX(population) FROM bbc)
{Pick the result that would be obtained from the following code: SELECT region, SUM(area) FROM bbc GROUP BY region HAVING SUM(area)<= 20000000
732240 |
13403102 |
17740392 |
4943771 |
Africa | 22550927 |
Asia-Pacific | 28759578 |
Europe | 23866987 |
North America | 21660000 |
region | SUM(area) |
---|---|
Americas | 732240 |
Middle East | 13403102 |
South America | 17740392 |
South Asia | 4943771 |
|type="()"} - Table-A - Table-B - Table-C + Table-D - Table-E
</quiz>