Difference between revisions of "SELECT Quiz"

From SQLZOO
Jump to: navigation, search
(15 intermediate revisions by 3 users not shown)
Line 44: Line 44:
 
{Select the code which shows the countries with a population between 100000000 and 200000000
 
{Select the code which shows the countries with a population between 100000000 and 200000000
 
|type="()"}
 
|type="()"}
- SELECT name, population FROM bbc WHERE population BETWEEN (100000000, 200000000)
+
- <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE area BETWEEN 100000000 AND 200000000 </syntaxhighlight>
- SELECT name, population FROM bbc WHERE population BETWEEN 100000000, 200000000
+
- <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE population BETWEEN (100000000, 200000000) </syntaxhighlight>
+ SELECT name, population FROM bbc WHERE population BETWEEN 100000000 AND 200000000
+
+ <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE population BETWEEN 100000000 AND 200000000 </syntaxhighlight>
- SELECT name, population FROM bbc WHERE area BETWEEN 100000000 AND 200000000
+
- <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE population BETWEEN 100000000, 200000000 </syntaxhighlight>
- SELECT population FROM bbc WHERE population BETWEEN 100000000 AND 200000000
+
- <syntaxhighlight lang='sql'> SELECT population FROM bbc WHERE population BETWEEN 100000000 AND 200000000 </syntaxhighlight>
 +
 
 +
{Pick the result you would obtain from this code:
 +
<syntaxhighlight  lang='sql'>
 +
SELECT name, population
 +
  FROM bbc
 +
WHERE name LIKE "Al%"</syntaxhighlight>
 +
<table style='float:left'><caption>Table-A</caption>
 +
  <tr>
 +
    <td>Albania</td>
 +
  </tr>
 +
<tr>
 +
  <td>Algeria</td></tr></table>
 +
<table style='float:left'><caption>Table-B</caption>
 +
  <tr>
 +
    <td>%bania</td>
 +
    <td>3200000</td>
 +
  </tr>
 +
<tr>
 +
  <td>%geria</td>
 +
<td>32900000</td></tr></table>
 +
<table style='float:left'><caption>Table-C</caption><tr>
 +
  <td>Al</td>
 +
<td>0 </td></tr></table>
 +
<table style='float:left'><caption>Table-D</caption><tr>
 +
  <td>Albania</td>
 +
  <td>3200000</td></tr></table>
 +
<table style='float:left'><caption>Table-E</caption>
 +
  <tr>
 +
    <td>Albania</td>
 +
    <td>3200000</td>
 +
  </tr>
 +
  <tr>
 +
    <td>Algeria</td>
 +
    <td>32900000</td>
 +
  </tr>
 +
</table>
 +
|type="()"}
 +
- Table-A
 +
- Table-B
 +
- Table-C
 +
- Table-D
 +
+ Table-E
  
 
{Select the code which shows the countries that end in A or L
 
{Select the code which shows the countries that end in A or L
 
|type="()"}
 
|type="()"}
- SELECT name FROM bbc WHERE name LIKE '%a' AND name LIKE '%l'
+
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE 'a%' AND name LIKE 'l%' </syntaxhighlight>
- SELECT name FROM bbc WHERE name LIKE 'a%' AND name LIKE 'l%'
+
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE 'a%' OR name LIKE 'l%' </syntaxhighlight>
- SELECT name FROM bbc WHERE name LIKE '%a' OR 'l%'
+
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' AND name LIKE '%l' </syntaxhighlight>
+ SELECT name FROM bbc WHERE name LIKE '%a' OR name LIKE '%l'
+
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' OR 'l%' </syntaxhighlight>
- SELECT name FROM bbc WHERE name LIKE 'a%' OR name LIKE 'l%'
+
+ <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' OR name LIKE '%l' </syntaxhighlight>
  
{Pick the code which shows the total population of Scandinavia (Denmark, Finland, Sweden and Norway)
+
{Pick the result from the query
 +
<syntaxhighlight lang='sql'>
 +
SELECT name,length(name)
 +
  FROM world
 +
  WHERE length(name)=5 and continent='Europe'
 +
</syntaxhighlight>
 +
<table><caption>Table-A</caption><tr><th>name</th><th>length(name)</th></tr><tr><td>Benin</td><td class="r">5</td></tr><tr><td>Lybia</td><td class="r">5</td></tr><tr><td>Egypt</td><td class="r">5</td></tr></table>
 +
<table><caption>Table-B</caption><tr><th>name</th><th>length(name)</th></tr><tr><td>Italy</td><td class="r">5</td></tr><tr><td>Egypt</td><td class="r">5</td></tr><tr><td>Spain</td><td class="r">5</td></tr></table>
 +
<table><caption>Table-C</caption><tr><th>name</th><th>length(name)</th></tr><tr><td>Italy</td><td class="r">5</td></tr><tr><td>Malta</td><td class="r">5</td></tr><tr><td>Spain</td><td class="r">5</td></tr></table>
 +
<table><caption>Table-D</caption><tr><th>name</th><th>length(name)</th></tr><tr><td>Italy</td><td class="r">5</td></tr><tr><td>France</td><td class="r">6</td></tr><tr><td>Spain</td><td class="r">5</td></tr></table>
 +
<table><caption>Table-E</caption><tr><th>name</th><th>length(name)</th></tr><tr><td>Sweden</td><td class="r">6</td></tr><tr><td>Norway</td><td class="r">6</td></tr><tr><td>Poland</td><td class="r">6</td></tr></table>
 
|type="()"}
 
|type="()"}
+ SELECT SUM(population) FROM bbc WHERE name IN ('Denmark', 'Norway', 'Finland','Sweden')
+
- Table-A
- SELECT population FROM bbc WHERE name IN ('Denmark', 'Norway', 'Finland','Sweden')
+
- Table-B
- SELECT SUM(population) FROM bbc WHERE name IN 'Scandinavia'
+
+ Table-C
- SELECT SUM(population) FROM bbc WHERE name LIKE ('Denmark', 'Norway', 'Finland','Sweden')
+
- Table-D
- SELECT TOTAL(population) FROM bbc WHERE name IN ('Denmark', 'Norway', 'Finland','Sweden')
+
- Table-E
  
{Pick the result you would obtain from this code: SELECT name, area * 2 FROM bbc WHERE population = 64000
+
{Pick the result you would obtain from this code:  
 +
<syntaxhighlight lang='sql'>SELECT name, area * 2 FROM bbc WHERE population = 64000</syntaxhighlight>
 +
<table style='float:left'><caption>Table-A</caption><tr><td>Andorra</td><td>234</td></tr></table>
 +
<table style='float:left'><caption>Table-B</caption><tr><td>Andorra</td><td>468</td></tr></table>
 +
<table style='float:left'><caption>Table-C</caption><tr><td>Andorra</td><td>936 </td></tr></table>
 +
<table style='float:left'><caption>Table-D</caption><tr><td>Andorra</td><td>4680</td></tr></table>
 +
<table style='float:left'><caption>Table-E</caption><tr><td>Andorra</td><td>936</td></tr><tr><td>Albania</td><td>57456</td></tr></table>
 
|type="()"}
 
|type="()"}
- Andorra, 234
+
- Table-A
- Andorra, 468
+
- Table-B
+ Andorra, 936
+
+ Table-C
- Andorra, 4680
+
- Table-D
- Andorra, 936,  Albania, 57456
+
- Table-E
  
 
{Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000
 
{Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000
 
|type="()"}
 
|type="()"}
+ SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000
+
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population < 10000000 </syntaxhighlight>
- SELECT name, area, population FROM bbc WHERE area < 50000 AND population < 10000000
+
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000 </syntaxhighlight>
- SELECT name, area, population FROM bbc WHERE area = 50000 AND population = 10000000
+
+ <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000 </syntaxhighlight>
- SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000
+
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000 </syntaxhighlight>
- SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000
+
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area = 50000 AND population = 10000000 </syntaxhighlight>
  
 
{Select the code that shows the population density of China, Australia, Nigeria and France
 
{Select the code that shows the population density of China, Australia, Nigeria and France
 
|type="()"}
 
|type="()"}
- SELECT name, area/population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
+
- <syntaxhighlight lang='sql'> SELECT name, area/population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
- SELECT name, area/population FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
+
- <syntaxhighlight lang='sql'> SELECT name, area/population FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+ SELECT name, population/area FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
+
+ <syntaxhighlight lang='sql'> SELECT name, population/area FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
- SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
+
- <syntaxhighlight lang='sql'> SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
- SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
+
- <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
  
{Pick the result that would be obtained from this code: SELECT CONCAT(name,region), population FROM bbc WHERE region IN ('Africa','Middle East') AND name LIKE 'A%'
+
{Pick the result that would be obtained from this code: SELECT CONCAT(name,region), population FROM bbc WHERE region IN ('Africa','Middle East') AND name LIKE 'A%'  
 +
<table style='float:left'><caption>Table-A</caption><tr><td>(Algeria, Middle East)</td><td>32900000</td></tr><tr><td>(Angola, Africa)</td><td>14500000</td></tr></table>
 +
<table style='float:left'><caption>Table-B</caption><tr><td>Algeria</td><td>32900000</td></tr><tr><td>Angola</td><td>14500000</td></tr></table>
 +
<table style='float:left'><caption>Table-C</caption><tr><td>AlgeriaMiddle East</td><td>32900000</td></tr><tr><td>AngolaAfrica</td><td>14500000</td></tr></table>
 +
<table style='float:left'><caption>Table-D</caption><tr><td>AngolaAfrica</td><td> 14500000</td></tr></table>
 +
<table style='float:left'><caption>Table-E</caption><tr><td>Algeria</td><td>Middle East</td></tr><tr><td>Angola</td><td>Africa</td></tr></table>
 
|type="()"}
 
|type="()"}
- (Algeria, Middle East), 32900000,  (Angola, Africa), 14500000
+
- Table-A
- Algeria, 32900000,  Angola, 14500000
+
- Table-B
+ AlgeriaMiddle East, 32900000,  AngolaAfrica, 14500000
+
+ Table-C
- AngolaAfrica, 14500000
+
- Table-D
- <table><tr><td>Algeria</td><td>Middle East</td></tr><tr><td>Angola</td><td>Africa</td></tr></table>
+
- Table-E
 +
 
 
</quiz>
 
</quiz>
  
 
[[Category:Quizzes]]
 
[[Category:Quizzes]]

Revision as of 10:38, 12 July 2013

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
...

1. Select the code which shows the countries with a population between 100000000 and 200000000

 SELECT name, population FROM bbc WHERE area BETWEEN 100000000 AND 200000000
 SELECT name, population FROM bbc WHERE population BETWEEN (100000000, 200000000)
 SELECT name, population FROM bbc WHERE population BETWEEN 100000000 AND 200000000
 SELECT name, population FROM bbc WHERE population BETWEEN 100000000, 200000000
 SELECT population FROM bbc WHERE population BETWEEN 100000000 AND 200000000

2. Pick the result you would obtain from this code:

SELECT name, population
  FROM bbc 
 WHERE name LIKE "Al%"
Table-A
Albania
Algeria
Table-B
%bania 3200000
%geria 32900000
Table-C
Al 0
Table-D
Albania 3200000
Table-E
Albania 3200000
Algeria 32900000
Table-A
Table-B
Table-C
Table-D
Table-E

3. Select the code which shows the countries that end in A or L

 SELECT name FROM bbc WHERE name LIKE 'a%' AND name LIKE 'l%'
 SELECT name FROM bbc WHERE name LIKE 'a%' OR name LIKE 'l%'
 SELECT name FROM bbc WHERE name LIKE '%a' AND name LIKE '%l'
 SELECT name FROM bbc WHERE name LIKE '%a' OR 'l%'
 SELECT name FROM bbc WHERE name LIKE '%a' OR name LIKE '%l'

4. Pick the result from the query

 SELECT name,LENGTH(name)
   FROM world
  WHERE LENGTH(name)=5 AND continent='Europe'
Table-A
namelength(name)
Benin5
Lybia5
Egypt5
Table-B
namelength(name)
Italy5
Egypt5
Spain5
Table-C
namelength(name)
Italy5
Malta5
Spain5
Table-D
namelength(name)
Italy5
France6
Spain5
Table-E
namelength(name)
Sweden6
Norway6
Poland6
Table-A
Table-B
Table-C
Table-D
Table-E

5. Pick the result you would obtain from this code:

SELECT name, area * 2 FROM bbc WHERE population = 64000
Table-A
Andorra234
Table-B
Andorra468
Table-C
Andorra936
Table-D
Andorra4680
Table-E
Andorra936
Albania57456
Table-A
Table-B
Table-C
Table-D
Table-E

6. Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000

 SELECT name, area, population FROM bbc WHERE area < 50000 AND population < 10000000
 SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000
 SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000
 SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000
 SELECT name, area, population FROM bbc WHERE area = 50000 AND population = 10000000

7. Select the code that shows the population density of China, Australia, Nigeria and France

 SELECT name, area/population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
 SELECT name, area/population FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
 SELECT name, population/area FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
 SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
 SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')

8. Pick the result that would be obtained from this code: SELECT CONCAT(name,region), population FROM bbc WHERE region IN ('Africa','Middle East') AND name LIKE 'A%'

Table-A
(Algeria, Middle East)32900000
(Angola, Africa)14500000
Table-B
Algeria32900000
Angola14500000
Table-C
AlgeriaMiddle East32900000
AngolaAfrica14500000
Table-D
AngolaAfrica 14500000
Table-E
AlgeriaMiddle East
AngolaAfrica
Table-A
Table-B
Table-C
Table-D
Table-E

Your score is 0 / 0
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense