Difference between revisions of "Nested SELECT Quiz"

From SQLZOO
Jump to: navigation, search
(Created page with "Nested SELECT quiz <quiz shuffle=none display=simple> {Which of these shows the name, region and population of the smallest country in each region? |type="()"} - SELECT region...")
 
Line 1: Line 1:
 
Nested SELECT quiz
 
Nested SELECT quiz
 
<quiz shuffle=none display=simple>
 
<quiz shuffle=none display=simple>
−
{Which of these shows the name, region and population of the smallest country in each region?
+
{Select the code that shows the name, region and population of the smallest country in each region
 
|type="()"}
 
|type="()"}
 
- SELECT region, name, FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
 
- SELECT region, name, FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
Line 9: Line 9:
 
- SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population<0)
 
- SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population<0)
  
−
{Which of these shows the countries belonging to regions with all populations over 50000?
+
{Select the code that shows the countries belonging to regions with all populations over 50000
 
|type="()"}
 
|type="()"}
 
- SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE population>0)
 
- SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE population>0)
Line 18: Line 18:
  
  
−
{Which of these shows the countries with a third of the population of the countries around it?
+
{Select the code that shows the countries with a third of the population of the countries around it
 
|type="()"}
 
|type="()"}
 
+ SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
 
+ SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
Line 26: Line 26:
 
- SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.name != x.name)
 
- SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.name != x.name)
  
−
{Which of these would show the countries with a greater GDP then any country in Africa?
+
{Select the code that would show the countries with a greater GDP then any country in Africa
 
|type="()"}
 
|type="()"}
 
- SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0)
 
- SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0)
Line 34: Line 34:
 
- SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Europe' AND gdp IS NOT NULL)
 
- SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Europe' AND gdp IS NOT NULL)
  
−
{Which of these shows the countries with population smaller than Russia but bigger than Denmark?
+
{Select the code that shows the countries with population smaller than Russia but bigger than Denmark
 
|type="()"}
 
|type="()"}
 
- SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Denmark') AND population > (SELECT population FROM bbc WHERE name='Russia')
 
- SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Denmark') AND population > (SELECT population FROM bbc WHERE name='Russia')

Revision as of 10:44, 23 July 2012

Nested SELECT quiz

1. Select the code that shows the name, region and population of the smallest country in each region

SELECT region, name, FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
SELECT region, name, population FROM bbc WHERE population <= ALL (SELECT population FROM bbc WHERE population>0)
SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
SELECT region, name, population FROM bbc x WHERE population = ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population<0)

2. Select the code that shows the countries belonging to regions with all populations over 50000

SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE population>0)
SELECT name,region,population FROM bbc x WHERE 50000 <= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)
SELECT name,region,population FROM bbc x WHERE 50000 = ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)
SELECT name,region,population FROM bbc x WHERE 50000 >= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)
SELECT name,region,population FROM bbc x WHERE 500000 <= ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)

3. Select the code that shows the countries with a third of the population of the countries around it

SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
SELECT name, region FROM bbc x WHERE population = ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
SELECT name, region FROM bbc x WHERE population > ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
SELECT name, region FROM bbc x WHERE population < ALL (SELECT population*3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
SELECT name, region FROM bbc x WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.name != x.name)

4. Select the code that would show the countries with a greater GDP then any country in Africa

SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp=0)
SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL)
SELECT name FROM bbc WHERE gdp > ALL (SELECT MIN(gdp) FROM bbc WHERE region = 'Africa' AND gdp IS NOT NULL)
SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa' AND population IS NOT NULL)
SELECT name FROM bbc WHERE gdp > ALL (SELECT MAX(gdp) FROM bbc WHERE region = 'Europe' AND gdp IS NOT NULL)

5. Select the code that shows the countries with population smaller than Russia but bigger than Denmark

SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Denmark') AND population > (SELECT population FROM bbc WHERE name='Russia')
SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark')
SELECT name FROM bbc WHERE population = (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark')
SELECT name FROM bbc WHERE population > (SELECT population FROM bbc WHERE name='Russia') AND population > (SELECT population FROM bbc WHERE name='Denmark')
SELECT name FROM bbc WHERE population < (SELECT population FROM bbc WHERE name='Russia' AND population > (SELECT population FROM bbc WHERE name='Denmark')

Your score is 0 / 0
Retrieved from "http://sqlzoo.net/w/index.php?title=Nested_SELECT_Quiz&oldid=2113"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense