Difference between revisions of "SELECT Quiz"

From SQLZOO
Jump to: navigation, search
 
(7 intermediate revisions by 4 users not shown)
Line 1: Line 1:
 +
{{Languages}}
 
Some questions concerning basic SQL statements
 
Some questions concerning basic SQL statements
 
<div class="ref_section">
 
<div class="ref_section">
Line 45: Line 46:
 
     <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 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>
 
     <syntaxhighlight class=d lang='sql'>
 
     <syntaxhighlight class=d lang='sql'>
      FROM world
+
FROM world
      SELECT name, population BETWEEN 1000000 AND 1250000
+
SELECT name, population BETWEEN 1000000 AND 1250000
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
     <syntaxhighlight class=d lang='sql'>
 
     <syntaxhighlight class=d lang='sql'>
      FROM name, population  
+
FROM name, population  
      WHERE population BETWEEN 1000000 AND 1250000
+
WHERE population BETWEEN 1000000 AND 1250000
      SELECT world
+
SELECT world
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
     <syntaxhighlight class='d y' lang='sql'>
 
     <syntaxhighlight class='d y' lang='sql'>
      SELECT name, population
+
SELECT name, population
      FROM world
+
  FROM world
      WHERE population BETWEEN 1000000 AND 1250000
+
WHERE population BETWEEN 1000000 AND 1250000
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
     <syntaxhighlight class=d lang='sql'>
 
     <syntaxhighlight class=d lang='sql'>
      SELECT population BETWEEN 1000000 AND 1250000
+
SELECT population BETWEEN 1000000 AND 1250000
      FROM world
+
FROM world
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
     <syntaxhighlight class=d lang='sql'>
 
     <syntaxhighlight class=d lang='sql'>
      WHERE population BETWEEN 1000000 AND 1250000
+
WHERE population BETWEEN 1000000 AND 1250000
      SELECT name, population FROM world
+
SELECT name, population FROM world
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
   </div>
 
   </div>
Line 70: Line 71:
 
     <syntaxhighlight  lang='sql'>
 
     <syntaxhighlight  lang='sql'>
 
       SELECT name, population
 
       SELECT name, population
       FROM bbc
+
       FROM world
 
       WHERE name LIKE "Al%"
 
       WHERE name LIKE "Al%"
 
     </syntaxhighlight>
 
     </syntaxhighlight>
Line 107: Line 108:
 
     <div class=q>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
  
       <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
+
       <syntaxhighlight class=d lang='sql'>
      WHERE name LIKE 'a%' AND name LIKE 'l%'
+
SELECT name FROM world
 +
WHERE name LIKE 'a%' AND name LIKE 'l%'
 
     </syntaxhighlight>
 
     </syntaxhighlight>
     <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
+
     <syntaxhighlight class=d lang='sql'>  
      WHERE name LIKE 'a%' OR name LIKE 'l%'
+
SELECT name FROM world
 +
WHERE name LIKE 'a%' OR name LIKE 'l%'
 
     </syntaxhighlight>
 
     </syntaxhighlight>
     <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
+
     <syntaxhighlight class=d lang='sql'>  
      WHERE name LIKE '%a' AND name LIKE '%l'  
+
SELECT name FROM world
 +
WHERE name LIKE '%a' AND name LIKE '%l'  
 
     </syntaxhighlight>
 
     </syntaxhighlight>
     <syntaxhighlight class=d lang='sql'> SELECT name FROM bbc
+
     <syntaxhighlight class=d lang='sql'>  
      WHERE name LIKE '%a' OR 'l%'  
+
SELECT name FROM world
 +
WHERE name LIKE '%a' OR 'l%'  
 
     </syntaxhighlight>
 
     </syntaxhighlight>
     <syntaxhighlight class='d y' lang='sql'> SELECT name FROM bbc
+
     <syntaxhighlight class='d y' lang='sql'>
      WHERE name LIKE '%a' OR name LIKE '%l'  
+
SELECT name FROM world
 +
WHERE name LIKE '%a' OR name LIKE '%l'  
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
   </div>
 
   </div>
Line 126: Line 132:
 
   <div class=q>Pick the result from the query
 
   <div class=q>Pick the result from the query
 
     <syntaxhighlight lang='sql'>
 
     <syntaxhighlight lang='sql'>
    SELECT name,length(name)
+
SELECT name,length(name)
    FROM world
+
FROM world
    WHERE length(name)=5 and region='Europe'
+
WHERE length(name)=5 and region='Europe'
 
   </syntaxhighlight>
 
   </syntaxhighlight>
 
   <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>
 
   <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>
Line 137: Line 143:
 
  </div>
 
  </div>
  
  <div class=q>Pick the result you would obtain from this code:  
+
  <div class=q>Here are the first few rows of the world table:
   <syntaxhighlight lang='sql'>SELECT name, area * 2 FROM bbc WHERE population = 64000</syntaxhighlight>
+
  <table class='db_ref'><tr>
 +
    <th>name</th>
 +
    <th>region</th>
 +
    <th>area</th>
 +
    <th>population</th>
 +
    <th>gdp</th>
 +
  </tr>
 +
  <tr>
 +
    <td>Afghanistan</td>
 +
    <td>South Asia</td>
 +
    <td align='right'>652225</td>
 +
    <td align='right'>26000000</td>
 +
    <td> </td>
 +
  </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>Pick the result you would obtain from this code:  
 +
   <pre>SELECT name, area*2 FROM world WHERE population = 64000</pre>
 
   <table class='d'><tr><td>Andorra</td><td>234</td></tr></table>
 
   <table class='d'><tr><td>Andorra</td><td>234</td></tr></table>
 
   <table class='d'><tr><td>Andorra</td><td>468</td></tr></table>
 
   <table class='d'><tr><td>Andorra</td><td>468</td></tr></table>
Line 146: Line 191:
 
</div>
 
</div>
 
<div class=q>Select the code that would show the countries with an area larger than 50000 and a population smaller than 10000000
 
<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'>
   <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area < 50000 AND population > 10000000 </syntaxhighlight>
+
SELECT name, area, population
   <syntaxhighlight class='d y' lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population < 10000000 </syntaxhighlight>
+
  FROM world
   <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area > 50000 AND population > 10000000 </syntaxhighlight>
+
WHERE area < 50000 AND population < 10000000
   <syntaxhighlight class=d lang='sql'> SELECT name, area, population FROM bbc WHERE area = 50000 AND population = 10000000 </syntaxhighlight>
+
</syntaxhighlight>
 
+
   <syntaxhighlight class=d lang='sql'>
 +
SELECT name, area, population
 +
  FROM world
 +
WHERE area < 50000 AND population > 10000000  
 +
</syntaxhighlight>
 +
   <syntaxhighlight class='d y' lang='sql'>
 +
SELECT name, area, population
 +
  FROM world
 +
WHERE area > 50000 AND population < 10000000
 +
  </syntaxhighlight>
 +
   <syntaxhighlight class=d lang='sql'>
 +
SELECT name, area, population
 +
  FROM world
 +
WHERE area > 50000 AND population > 10000000  
 +
</syntaxhighlight>
 +
   <syntaxhighlight class=d lang='sql'>  
 +
SELECT name, area, population
 +
  FROM world
 +
WHERE area = 50000 AND population = 10000000  
 +
</syntaxhighlight>
 +
</div>
 
   <div class=q>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
 
     <syntaxhighlight class=d lang='sql'>  
 
     <syntaxhighlight class=d lang='sql'>  
      SELECT name, area/population
+
SELECT name, area/population
      FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia')  
+
FROM world WHERE name IN ('China', 'Nigeria', 'France', 'Australia')  
 
     </syntaxhighlight>
 
     </syntaxhighlight>
 
     <syntaxhighlight class=d lang='sql'>
 
     <syntaxhighlight class=d lang='sql'>
    SELECT name, area/population
+
SELECT name, area/population
    FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
+
FROM world WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
 
   </syntaxhighlight>
 
   </syntaxhighlight>
   <syntaxhighlight class='d y' lang='sql'> SELECT name, population/area FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
   <syntaxhighlight class='d y' lang='sql'>
   <syntaxhighlight class=d lang='sql'> SELECT name, population/area FROM bbc WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
SELECT name, population/area
  <syntaxhighlight class=d lang='sql'> SELECT name, population FROM bbc WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
+
  FROM world
 +
WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
 +
   <syntaxhighlight class=d lang='sql'>
 +
SELECT name, population/area
 +
  FROM world
 +
WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
 +
</syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, population
 +
  FROM world
 +
WHERE name IN ('China', 'Nigeria', 'France', 'Australia') </syntaxhighlight>
 
  </div>
 
  </div>
 
</div>
 
</div>
 +
[[Category:Quizzes]]

Latest revision as of 01:34, 8 January 2016

Language: English  • 中文

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 world
      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 world
 WHERE name LIKE 'a%' AND name LIKE 'l%'
 
SELECT name FROM world
 WHERE name LIKE 'a%' OR name LIKE 'l%'
 
SELECT name FROM world
 WHERE name LIKE '%a' AND name LIKE '%l'
 
SELECT name FROM world
 WHERE name LIKE '%a' OR 'l%'
SELECT name FROM world
 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
Here are the first few rows of the world table:
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
...
Pick the result you would obtain from this code:
SELECT name, area*2 FROM world 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 world
 WHERE area < 50000 AND population < 10000000
SELECT name, area, population
  FROM world
 WHERE area < 50000 AND population > 10000000
SELECT name, area, population
  FROM world
 WHERE area > 50000 AND population < 10000000
SELECT name, area, population
  FROM world
 WHERE area > 50000 AND population > 10000000
 
SELECT name, area, population
  FROM world
 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 world WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
SELECT name, area/population
FROM world WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
SELECT name, population/area
  FROM world
 WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
SELECT name, population/area
  FROM world
 WHERE name LIKE ('China', 'Nigeria', 'France', 'Australia')
SELECT name, population
  FROM world
 WHERE name IN ('China', 'Nigeria', 'France', 'Australia')