Difference between revisions of "BBC QUIZ"

From SQLZOO
Jump to: navigation, search
Line 96: Line 96:
 
</div>
 
</div>
 
<div class=q>Select the code which shows just the population of United Kingdom?
 
<div class=q>Select the code which shows just the population of United Kingdom?
<syntaxhighlight class=d lang='sql'> SELECT population FROM 'United Kingdom' </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'> SELECT name FROM bbc WHERE population = 'United Kingdom' </syntaxhighlight>
+
SELECT population
<syntaxhighlight class=d lang='sql'> SELECT FROM bbc WHERE population IN 'United Kingdom' </syntaxhighlight>
+
  FROM 'United Kingdom' </syntaxhighlight>
<syntaxhighlight class='d y' lang='sql'> SELECT population FROM bbc WHERE name = 'United Kingdom' </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'> SELECT population FROM bbc WHERE 'United Kingdom' IN name </syntaxhighlight>
+
SELECT name
 +
  FROM bbc
 +
  WHERE population = 'United Kingdom' </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT FROM bbc
 +
WHERE population IN 'United Kingdom' </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT population
 +
  FROM bbc
 +
WHERE name = 'United Kingdom' </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT population
 +
  FROM bbc
 +
WHERE 'United Kingdom' IN name </syntaxhighlight>
 
</div>
 
</div>
 
<div class=q>Select the answer which shows the problem with this SQL code - the indended result should be the single row containing 'Europe':
 
<div class=q>Select the answer which shows the problem with this SQL code - the indended result should be the single row containing 'Europe':
Line 127: Line 140:
 
</div>
 
</div>
 
<div class=q>Select the code which would reveal the name and population of countries in Europe, North America and South America
 
<div class=q>Select the code which would reveal the name and population of countries in Europe, North America and South America
<syntaxhighlight class=d lang='sql'> SELECT name FROM bbc WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class='d y' lang='sql'> SELECT name, population FROM bbc WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
+
SELECT name
<syntaxhighlight class=d lang='sql'> SELECT name, population FROM bbc WHERE region IN (Europe North America South America) </syntaxhighlight>
+
  FROM bbc  
<syntaxhighlight class=d lang='sql'> SELECT name, population FROM bbc WHERE region IS ('Europe', 'North America', 'South America') </syntaxhighlight>
+
WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
<syntaxhighlight class=d lang='sql'> SELECT population FROM bbc WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
+
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT name, population
 +
  FROM bbc
 +
WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, population
 +
  FROM bbc
 +
WHERE region IN (Europe North America South America) </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name, population
 +
  FROM bbc
 +
WHERE region IS ('Europe', 'North America', 'South America') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT population
 +
  FROM bbc
 +
WHERE region IN ('Europe', 'North America', 'South America') </syntaxhighlight>
 
</div>
 
</div>
 
<div class=q >Select the code which would give two rows
 
<div class=q >Select the code which would give two rows
<syntaxhighlight class=d lang='sql'> SELECT name FROM bbc WHERE name = 'United Kingdom' </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'> SELECT name FROM bbc WHERE name = 'United Kingdom' AND name = 'Algeria' </syntaxhighlight>
+
SELECT name FROM bbc
<syntaxhighlight class=d lang='sql'> SELECT name FROM bbc WHERE name EITHER ('United Kingdom', 'Algeria') </syntaxhighlight>
+
WHERE name = 'United Kingdom' </syntaxhighlight>
<syntaxhighlight class='d y' lang='sql'> SELECT name FROM bbc WHERE name IN ('United Kingdom', 'Algeria') </syntaxhighlight>
+
<syntaxhighlight class=d lang='sql'>
<syntaxhighlight class=d lang='sql'> SELECT name FROM WHERE name IS 'Scotland' </syntaxhighlight>
+
SELECT name FROM bbc
 +
WHERE name = 'United Kingdom' AND name = 'Algeria' </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE name EITHER ('United Kingdom', 'Algeria') </syntaxhighlight>
 +
<syntaxhighlight class='d y' lang='sql'>
 +
SELECT name FROM bbc
 +
WHERE name IN ('United Kingdom', 'Algeria') </syntaxhighlight>
 +
<syntaxhighlight class=d lang='sql'>
 +
SELECT name FROM WHERE name IS 'Scotland' </syntaxhighlight>
 
</div>
 
</div>
 
<div class=q>Select the result that would be obtained from this code:  
 
<div class=q>Select the result that would be obtained from this code:  

Revision as of 22:30, 19 April 2015

bbc
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
Brazil South America 8550000 182800000 564852000000
Colombia South America 1140000 45600000
Nauru Asia-Pacific 21 9900
Uzbekistan Central Asia 447000 26000000
...
Select the code which gives the name of countries beginning with U
SELECT name
  FROM bbc
 WHERE name
 BEGIN with U
SELECT name
  FROM bbc
 WHERE name LIKE '%U'
SELECT name
  FROM bbc
 WHERE name LIKE '%u%'
SELECT name
  FROM bbc
 WHERE name LIKE U
SELECT name
  FROM bbc
 WHERE name LIKE 'U%'
Select the code which shows just the population of United Kingdom?
SELECT population
  FROM 'United Kingdom'
SELECT name
  FROM bbc
   WHERE population = 'United Kingdom'
SELECT FROM bbc
 WHERE population IN 'United Kingdom'
SELECT population
  FROM bbc
 WHERE name = 'United Kingdom'
SELECT population
  FROM bbc
 WHERE 'United Kingdom' IN name
Select the answer which shows the problem with this SQL code - the indended result should be the single row containing 'Europe':
 SELECT region 
   FROM bbc 
  WHERE 'name' = 'France'
region should be 'region'
'name' should be name
'France' should be "France"
'France' should be France
= should be IN
Select the result that would be obtained from the following code:
 SELECT name, population / 10 
  FROM bbc 
 WHERE population < 10000
Andorra6400
Nauru990
Andorra64000
Nauru9900
Nauru99
Nauru990
Nauru9900
Select the code which would reveal the name and population of countries in Europe, North America and South America
SELECT name
  FROM bbc 
 WHERE region IN ('Europe', 'North America', 'South America')
SELECT name, population
  FROM bbc
 WHERE region IN ('Europe', 'North America', 'South America')
SELECT name, population
  FROM bbc
 WHERE region IN (Europe North America South America)
SELECT name, population
  FROM bbc
 WHERE region IS ('Europe', 'North America', 'South America')
SELECT population
  FROM bbc
 WHERE region IN ('Europe', 'North America', 'South America')
Select the code which would give two rows
SELECT name FROM bbc
 WHERE name = 'United Kingdom'
SELECT name FROM bbc
 WHERE name = 'United Kingdom' AND name = 'Algeria'
SELECT name FROM bbc
 WHERE name EITHER ('United Kingdom', 'Algeria')
SELECT name FROM bbc
 WHERE name IN ('United Kingdom', 'Algeria')
SELECT name FROM WHERE name IS 'Scotland'
Select the result that would be obtained from this code:
SELECT name FROM bbc WHERE region = 'South America' AND population > 40000000
Afghanistan
Brazil
Colombia
Brazil
Brazil
Colombia
BrazilSouth America
ColombiaSouth America
Brazil182800000
Colombia45600000