Difference between revisions of "SUM and COUNT Quiz"
Line 46: | Line 46: | ||
{Select the statement that shows the sum of population of all countries in 'Europe' | {Select the statement that shows the sum of population of all countries in 'Europe' | ||
|type="()"} | |type="()"} | ||
− | - SELECT name, population FROM bbc WHERE region = 'Europe' | + | - <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE region = 'Europe' </syntaxhighlight> |
− | - SELECT population FROM bbc WHERE region = 'Europe' SUM BY region | + | - <syntaxhighlight lang='sql'> SELECT population FROM bbc WHERE region = 'Europe' SUM BY region </syntaxhighlight> |
− | + SELECT SUM(population) FROM bbc WHERE region = 'Europe' | + | + <syntaxhighlight lang='sql'> SELECT SUM(population) FROM bbc WHERE region = 'Europe' </syntaxhighlight> |
− | - SELECT SUM(population FROM bbc WHERE region = 'Europe') | + | - <syntaxhighlight lang='sql'> SELECT SUM(population FROM bbc WHERE region = 'Europe') </syntaxhighlight> |
− | - SUM population FROM bbc WHERE region = 'Europe' | + | - <syntaxhighlight lang='sql'> SUM population FROM bbc WHERE region = 'Europe' </syntaxhighlight> |
{Select the statement that shows the number of countries with population smaller than 150000 | {Select the statement that shows the number of countries with population smaller than 150000 | ||
|type="()"} | |type="()"} | ||
− | + SELECT COUNT(name) FROM bbc WHERE population < 150000 | + | + <syntaxhighlight lang='sql'> SELECT COUNT(name) FROM bbc WHERE population < 150000 </syntaxhighlight> |
− | - SELECT COUNT(population < 150000) FROM bbc | + | - <syntaxhighlight lang='sql'> SELECT COUNT(population < 150000) FROM bbc </syntaxhighlight> |
− | - SELECT name FROM bbc WHERE population < 150000 | + | - <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE population < 150000 </syntaxhighlight> |
− | - SELECT population AS COUNT FROM bbc WHERE population < 150000 | + | - <syntaxhighlight lang='sql'> SELECT population AS COUNT FROM bbc WHERE population < 150000 </syntaxhighlight> |
− | - SELECT SUM() FROM bbc WHERE population < 150000 | + | - <syntaxhighlight lang='sql'> SELECT SUM() FROM bbc WHERE population < 150000 </syntaxhighlight> |
{Select the full set of SQL aggregate functions | {Select the full set of SQL aggregate functions | ||
Line 68: | Line 68: | ||
- COUNT(), 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 | + | {Select the result that would be obtained from the following code: |
+ | <syntaxhighlight lang='sql'> | ||
+ | SELECT region, SUM(area) | ||
+ | FROM bbc | ||
+ | WHERE SUM(area) > 15000000 | ||
+ | GROUP BY region | ||
+ | </syntaxhighlight> | ||
<table style='float:left'><caption>Table-A</caption><tr><td>Europe</td><td>17000000</td></tr></table> | <table style='float:left'><caption>Table-A</caption><tr><td>Europe</td><td>17000000</td></tr></table> | ||
<table style='float:left'><caption>Table-B</caption><tr><td>Europe</td><td>17000000</td></tr><tr><td>Asia-Pacific</td><td>23460000</td></tr><tr><td>North America</td><td>21660000</td></tr></table> | <table style='float:left'><caption>Table-B</caption><tr><td>Europe</td><td>17000000</td></tr><tr><td>Asia-Pacific</td><td>23460000</td></tr><tr><td>North America</td><td>21660000</td></tr></table> | ||
Line 81: | Line 87: | ||
{Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark' | {Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark' | ||
|type="()"} | |type="()"} | ||
− | - SELECT AVG(population) FROM bbc WHERE name = ('Poland', 'Germany', 'Denmark') | + | - <syntaxhighlight lang='sql'> SELECT AVG(population) FROM bbc WHERE name = ('Poland', 'Germany', 'Denmark') </syntaxhighlight> |
− | + SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark') | + | + <syntaxhighlight lang='sql'> SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark') </syntaxhighlight> |
− | - SELECT AVG(population) FROM bbc WHERE name LIKE ('Poland', 'Germany', 'Denmark') | + | - <syntaxhighlight lang='sql'> SELECT AVG(population) FROM bbc WHERE name LIKE ('Poland', 'Germany', 'Denmark') </syntaxhighlight> |
− | - SELECT AVG(population) FROM bbc WHERE name LIKE (Poland, Germany, Denmark) | + | - <syntaxhighlight lang='sql'> SELECT AVG(population) FROM bbc WHERE name LIKE (Poland, Germany, Denmark) </syntaxhighlight> |
− | - SELECT population FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark') | + | - <syntaxhighlight lang='sql'> SELECT population FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark') </syntaxhighlight> |
{Select the statement that shows the medium population density of each region | {Select the statement that shows the medium population density of each region | ||
|type="()"} | |type="()"} | ||
− | - SELECT region, AVG(population/area) AS density FROM bbc | + | - <syntaxhighlight lang='sql'> SELECT region, AVG(population/area) AS density FROM bbc </syntaxhighlight> |
− | - SELECT region, COUNT(population)/COUNT(area) AS density FROM bbc GROUP BY region | + | - <syntaxhighlight lang='sql'> SELECT region, COUNT(population)/COUNT(area) AS density FROM bbc GROUP BY region </syntaxhighlight> |
− | - SELECT region, SUM(population)/COUNT(area) AS density FROM bbc GROUP BY region | + | - <syntaxhighlight lang='sql'> SELECT region, SUM(population)/COUNT(area) AS density FROM bbc GROUP BY region </syntaxhighlight> |
− | - SELECT region, SUM(population)/SUM(area) AS density FROM bbc HAVING region | + | - <syntaxhighlight lang='sql'> SELECT region, SUM(population)/SUM(area) AS density FROM bbc HAVING region </syntaxhighlight> |
− | + SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region | + | + <syntaxhighlight lang='sql'> SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region </syntaxhighlight> |
{Select the statement that shows the name and population density of the country with the largest population | {Select the statement that shows the name and population density of the country with the largest population | ||
|type="()"} | |type="()"} | ||
− | - SELECT name, density AS population/area FROM bbc WHERE population = MAX(population) | + | - <syntaxhighlight lang='sql'> SELECT name, density AS population/area FROM bbc WHERE population = MAX(population) </syntaxhighlight> |
− | - SELECT name, density AS population/area FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) | + | - <syntaxhighlight lang='sql'> SELECT name, density AS population/area FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) </syntaxhighlight> |
− | - SELECT name, MAX (population) FROM bbc WHERE population / (SELECT area FROM bbc) | + | - <syntaxhighlight lang='sql'> SELECT name, MAX (population) FROM bbc WHERE population / (SELECT area FROM bbc) </syntaxhighlight> |
− | + SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) | + | + <syntaxhighlight lang='sql'> SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc) </syntaxhighlight> |
− | - SELECT name, population/area AS density FROM bbc WHERE population > (SELECT MAX(population) FROM bbc) | + | - <syntaxhighlight lang='sql'> SELECT name, population/area AS density FROM bbc WHERE population > (SELECT MAX(population) FROM bbc) </syntaxhighlight> |
− | {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: |
− | <table style='float:left'><caption>Table-A</caption><tr><td | + | <syntaxhighlight lang='sql'> |
− | <table style='float:left'><caption>Table-B</caption><tr><td>Africa</td><td | + | SELECT region, SUM(area) |
− | <table><caption>Table-C</caption><tr><td> | + | FROM bbc |
− | <table><caption>Table-D</caption | + | GROUP BY region |
− | <table><caption>Table-E</caption | + | HAVING SUM(area)<= 20000000 |
+ | </syntaxhighlight> | ||
+ | <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-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-C</caption><tr><td>Africa</td></tr><tr><td>Asia-Pacific</td></tr><tr><td>Europe</td></tr><tr><td>North America</td></tr></table> | ||
+ | <table style='float:left'><caption>Table-D</caption><tr><td>Americas</td><td>732240</td></tr><tr><td>Middle East</td><td>13403102</td></tr><tr><td>South America</td><td>17740392</td></tr><tr><td>South Asia</td><td>49437710</td></tr></table> | ||
+ | <table style='float:left'><caption>Table-E</caption><tr><td>Americas</td></tr><tr><td>Middle East</td></tr><tr><td>South America</td></tr><tr><td>South Asia</td></tr></table> | ||
|type="()"} | |type="()"} | ||
- Table-A | - Table-A | ||
- Table-B | - Table-B | ||
− | + | - Table-C | |
− | + | + Table-D | |
- Table-E | - Table-E | ||
Revision as of 10:46, 12 July 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 |
Africa |
Asia-Pacific |
Europe |
North America |
Americas | 732240 |
Middle East | 13403102 |
South America | 17740392 |
South Asia | 49437710 |
Americas |
Middle East |
South America |
South Asia |
|type="()"} - Table-A - Table-B - Table-C + Table-D - Table-E
</quiz>