Difference between revisions of "SELECT Quiz"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
 
Some questions concerning basic SQL statements
 
Some questions concerning basic SQL statements
 
<div class="ref_section">
 
<div class="ref_section">
<table class='db_ref'><tr>
+
  <table class='db_ref'><tr>
<th>name</th>  
+
    <th>name</th>  
<th>region</th>
+
    <th>region</th>
<th>area</th>
+
    <th>area</th>
<th>population</th>
+
    <th>population</th>
<th>gdp</th>
+
    <th>gdp</th>
</tr>
+
  </tr>
<tr>
+
  <tr>
<td>Afghanistan</td>
+
    <td>Afghanistan</td>
<td>South Asia</td>
+
    <td>South Asia</td>
<td align='right'>652225</td>
+
    <td align='right'>652225</td>
<td align='right'>26000000</td>
+
    <td align='right'>26000000</td>
<td> </td>
+
    <td> </td>
</tr>
+
  </tr>
<tr>
 
<td>Albania</td>
 
<td>Europe</td>
 
<td align='right'>28728</td>
 
<td align='right'>3200000</td>
 
<td align='right'>6656000000</td>
 
</tr>
 
<tr>
 
<td>Algeria</td>
 
<td>Middle East</td>
 
<td align='right'>2400000</td>
 
<td align='right'>32900000</td>
 
<td align='right'>75012000000</td>
 
</tr>
 
<tr>
 
<td>Andorra</td>
 
<td>Europe</td>
 
<td align='right'>468</td>
 
<td align='right'>64000</td>
 
<td></td>
 
</tr>
 
<tr>
 
<td colspan='5'>...</td>
 
</tr>
 
</table>
 
</div>
 
<quiz shuffle=none display=simple>
 
{Select the code which produces this table <table class="sqlmine"><tr><th>name</th><th>population</th></tr><tr><td>Bahrain</td><td class="r">1234571</td></tr><tr><td>Swaziland</td><td class="r">1220000</td></tr><tr><td>Timor-Leste</td><td class="r">1066409</td></tr></table>
 
|type="()"}
 
- <syntaxhighlight lang='sql'>FROM world SELECT name, population BETWEEN 1000000 AND 1250000</syntaxhighlight>
 
- <syntaxhighlight lang='sql'>FROM name, population WHERE population BETWEEN 1000000 AND 1250000 SELECT world</syntaxhighlight>
 
+ <syntaxhighlight lang='sql'>SELECT name, population FROM world WHERE population BETWEEN 1000000 AND 1250000</syntaxhighlight>
 
- <syntaxhighlight lang='sql'>SELECT population BETWEEN 1000000 AND 1250000 FROM world</syntaxhighlight>
 
- <syntaxhighlight lang='sql'>WHERE population BETWEEN 1000000 AND 1250000 SELECT name, population FROM world</syntaxhighlight>
 
 
 
{Pick the result you would obtain from this code:
 
<syntaxhighlight  lang='sql'>
 
SELECT name, population
 
  FROM bbc
 
WHERE name LIKE "Al%"</syntaxhighlight>
 
<table class='distractor'><caption>Table-A</caption>
 
 
   <tr>
 
   <tr>
 
     <td>Albania</td>
 
     <td>Albania</td>
 +
    <td>Europe</td>
 +
    <td align='right'>28728</td>
 +
    <td align='right'>3200000</td>
 +
    <td align='right'>6656000000</td>
 
   </tr>
 
   </tr>
<tr>
 
  <td>Algeria</td></tr></table>
 
<table class='distractor'><caption>Table-B</caption>
 
 
   <tr>
 
   <tr>
     <td>%bania</td>
+
     <td>Algeria</td>
     <td>3200000</td>
+
     <td>Middle East</td>
 +
    <td align='right'>2400000</td>
 +
    <td align='right'>32900000</td>
 +
    <td align='right'>75012000000</td>
 
   </tr>
 
   </tr>
<tr>
 
  <td>%geria</td>
 
<td>32900000</td></tr></table>
 
<table class='distractor'><caption>Table-C</caption><tr>
 
  <td>Al</td>
 
<td>0 </td></tr></table>
 
<table class='distractor'><caption>Table-D</caption><tr>
 
  <td>Albania</td>
 
  <td>3200000</td></tr></table>
 
<table class='distractor'><caption>Table-E</caption>
 
 
   <tr>
 
   <tr>
     <td>Albania</td>
+
     <td>Andorra</td>
     <td>3200000</td>
+
    <td>Europe</td>
 +
    <td align='right'>468</td>
 +
    <td align='right'>64000</td>
 +
     <td></td>
 
   </tr>
 
   </tr>
 
   <tr>
 
   <tr>
     <td>Algeria</td>
+
     <td colspan='5'>...</td>
    <td>32900000</td>
 
 
   </tr>
 
   </tr>
 
</table>
 
</table>
|type="()"}
+
</div>
- Table-A
+
<div class=quiz>
- Table-B  
+
  <div class=q>Select the code which produces this table
- Table-C  
+
    <table class="sqlmine"><tr><th>name</th><th>population</th></tr><tr><td>Bahrain</td><td class="r">1234571</td></tr><tr><td>Swaziland</td><td class="r">1220000</td></tr><tr><td>Timor-Leste</td><td class="r">1066409</td></tr></table>
- Table-D  
+
    <syntaxhighlight class=d lang='sql'>
+ Table-E
+
      FROM world
 +
      SELECT name, population BETWEEN 1000000 AND 1250000
 +
    </syntaxhighlight>
 +
    <syntaxhighlight class=d lang='sql'>
 +
      FROM name, population
 +
      WHERE population BETWEEN 1000000 AND 1250000
 +
      SELECT world
 +
    </syntaxhighlight>
 +
    <syntaxhighlight class='d y' lang='sql'>
 +
      SELECT name, population
 +
      FROM world
 +
      WHERE population BETWEEN 1000000 AND 1250000
 +
    </syntaxhighlight>
 +
    <syntaxhighlight class=d lang='sql'>
 +
      SELECT population BETWEEN 1000000 AND 1250000
 +
      FROM world
 +
    </syntaxhighlight>
 +
    <syntaxhighlight class=d lang='sql'>
 +
      WHERE population BETWEEN 1000000 AND 1250000
 +
      SELECT name, population FROM world
 +
    </syntaxhighlight>
 +
  </div>
 +
  <div class=q>Pick the result you would obtain from this code:
 +
    <syntaxhighlight  lang='sql'>
 +
      SELECT name, population
 +
      FROM bbc
 +
      WHERE name LIKE "Al%"
 +
    </syntaxhighlight>
 +
    <table class='d'><caption>Table-A</caption>
 +
    <tr>
 +
      <td>Albania</td>
 +
    </tr>
 +
    <tr>
 +
      <td>Algeria</td></tr></table>
 +
      <table class='d'><caption>Table-B</caption>
 +
      <tr>
 +
        <td>%bania</td>
 +
        <td>3200000</td>
 +
      </tr>
 +
      <tr>
 +
        <td>%geria</td>
 +
        <td>32900000</td></tr></table>
 +
        <table class='d'><caption>Table-C</caption><tr>
 +
        <td>Al</td>
 +
        <td>0 </td></tr></table>
 +
        <table class='d'><caption>Table-D</caption><tr>
 +
        <td>Albania</td>
 +
        <td>3200000</td></tr></table>
 +
        <table class='d y'><caption>Table-E</caption>
 +
        <tr>
 +
          <td>Albania</td>
 +
          <td>3200000</td>
 +
        </tr>
 +
        <tr>
 +
          <td>Algeria</td>
 +
          <td>32900000</td>
 +
        </tr>
 +
      </table>
 +
    </div>
  
{Select the code which shows the countries that end in A or L
+
    <div class=q>Select the code which shows the countries that end in A or L
|type="()"}
 
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE 'a%' AND name LIKE 'l%' </syntaxhighlight>
 
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE 'a%' OR name LIKE 'l%' </syntaxhighlight>
 
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' AND name LIKE '%l' </syntaxhighlight>
 
- <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' OR 'l%' </syntaxhighlight>
 
+ <syntaxhighlight lang='sql'> SELECT name FROM bbc WHERE name LIKE '%a' OR name LIKE '%l' </syntaxhighlight>
 
  
{Pick the result from the query
+
      <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
<syntaxhighlight lang='sql'>
+
      WHERE name LIKE 'a%' AND name LIKE 'l%'
SELECT name,length(name)
+
    </syntaxhighlight>
  FROM world
+
    <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
  WHERE length(name)=5 and region='Europe'
+
      WHERE name LIKE 'a%' OR name LIKE 'l%'
</syntaxhighlight>
+
    </syntaxhighlight>
<table class='distractor'><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>
+
    <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
<table class='distractor'><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>
+
      WHERE name LIKE '%a' AND name LIKE '%l'
<table class='distractor'><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>
+
    </syntaxhighlight>
<table class='distractor'><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>
+
    <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
<table class='distractor'><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>
+
      WHERE name LIKE '%a' OR 'l%'
|type="()"}
+
    </syntaxhighlight>
- Table-A
+
    <syntaxhighlight class='d y' lang='sql'> SELECT name FROM bbc
- Table-B
+
      WHERE name LIKE '%a' OR name LIKE '%l'
+ Table-C
+
    </syntaxhighlight>
- Table-D
+
  </div>
- Table-E
 
  
{Pick the result you would obtain from this code:
+
  <div class=q>Pick the result from the query
<syntaxhighlight lang='sql'>SELECT name, area * 2 FROM bbc WHERE population = 64000</syntaxhighlight>
+
    <syntaxhighlight lang='sql'>
<table class='distractor'><caption>Table-A</caption><tr><td>Andorra</td><td>234</td></tr></table>
+
    SELECT name,length(name)
<table class='distractor'><caption>Table-B</caption><tr><td>Andorra</td><td>468</td></tr></table>
+
    FROM world
<table class='distractor'><caption>Table-C</caption><tr><td>Andorra</td><td>936 </td></tr></table>
+
    WHERE length(name)=5 and region='Europe'
<table class='distractor'><caption>Table-D</caption><tr><td>Andorra</td><td>4680</td></tr></table>
+
  </syntaxhighlight>
<table class='distractor'><caption>Table-E</caption><tr><td>Andorra</td><td>936</td></tr><tr><td>Albania</td><td>57456</td></tr></table>
+
  <table class='d'><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>
|type="()"}
+
  <table class='d'><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-A
+
  <table class='d y'><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-B
+
  <table class='d'><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-C
+
  <table class='d'><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>
- Table-D
+
</div>
- Table-E
 
  
{Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000
+
<div class=q>Pick the result you would obtain from this code:
|type="()"}
+
  <syntaxhighlight lang='sql'>SELECT name, area * 2 FROM bbc WHERE population = 64000</syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population < 10000000 </syntaxhighlight>
+
  <table class='d'><tr><td>Andorra</td><td>234</td></tr></table>
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000 </syntaxhighlight>
+
  <table class='d'><tr><td>Andorra</td><td>468</td></tr></table>
+ <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000 </syntaxhighlight>
+
  <table class='d y'><tr><td>Andorra</td><td>936 </td></tr></table>
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000 </syntaxhighlight>
+
  <table class='d'><tr><td>Andorra</td><td>4680</td></tr></table>
- <syntaxhighlight lang='sql'> SELECT name, area, population FROM bbc WHERE area = 50000 AND population = 10000000 </syntaxhighlight>
+
  <table class='d'><tr><td>Andorra</td><td>936</td></tr><tr><td>Albania</td><td>57456</td></tr></table>
 +
</div>
 +
<div class=q>Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000
 +
  <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population < 10000000 </syntaxhighlight>
 +
  <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000 </syntaxhighlight>
 +
  <syntaxhighlight class='d y' lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000 </syntaxhighlight>
 +
  <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000 </syntaxhighlight>
 +
  <syntaxhighlight class=d 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
+
  <div class=q>Select the code that shows the population density of China, Australia, Nigeria and France
|type="()"}
+
    <syntaxhighlight class=d lang='sql'>  
- <syntaxhighlight lang='sql'> SELECT name, area/population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
      SELECT name, area/population
- <syntaxhighlight lang='sql'> SELECT name, area/population FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
      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>
+
    </syntaxhighlight>
- <syntaxhighlight lang='sql'> SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
    <syntaxhighlight class=d lang='sql'>
- <syntaxhighlight lang='sql'> SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
    SELECT name, area/population
</quiz>
+
    FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
 +
  </syntaxhighlight>
 +
  <syntaxhighlight class='d y' lang='sql'> SELECT name, population/area FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
 +
  <syntaxhighlight class=d lang='sql'> SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
 +
  <syntaxhighlight class=d lang='sql'> SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
 +
</div>
 +
</div>

Revision as of 23:34, 21 April 2015

Some questions concerning basic SQL statements

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
...
Select the code which produces this table
namepopulation
Bahrain1234571
Swaziland1220000
Timor-Leste1066409
      FROM world
      SELECT name, population BETWEEN 1000000 AND 1250000
      FROM name, population 
      WHERE population BETWEEN 1000000 AND 1250000
      SELECT world
      SELECT name, population
      FROM world
      WHERE population BETWEEN 1000000 AND 1250000
      SELECT population BETWEEN 1000000 AND 1250000
      FROM world
      WHERE population BETWEEN 1000000 AND 1250000
      SELECT name, population FROM world
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
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'
Pick the result from the query
     SELECT name,length(name)
     FROM world
     WHERE length(name)=5 and region='Europe'
namelength(name)
Benin5
Lybia5
Egypt5
namelength(name)
Italy5
Egypt5
Spain5
namelength(name)
Italy5
Malta5
Spain5
namelength(name)
Italy5
France6
Spain5
namelength(name)
Sweden6
Norway6
Poland6
Pick the result you would obtain from this code:
SELECT name, area * 2 FROM bbc WHERE population = 64000
Andorra234
Andorra468
Andorra936
Andorra4680
Andorra936
Albania57456
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
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')