Nested SELECT Quiz

From SQLZOO
Revision as of 11:55, 6 August 2012 by Connor (Talk | contribs)

Jump to: navigation, search

Nested SELECT quiz

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
Bangladesh South Asia 143998 152600000 67144000000
United Kingdom Europe 242514 59600000 2022824000000
...

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 result that would be obtained from the following code: SELECT name FROM bbc WHERE population (SELECT population FROM bbc WHERE name='United Kingdom') AND region IN (SELECT region FROM bbc WHERE region = 'Europe')

Andorra, Albania, Austria, Bulgaria
France,Europe, Germany,Europe, Russia,Europe, Turkey,Europe
France, Germany, Andorra, Albania
France, Germany, Russia, Turkey
France, Germany, Russia, Turkey, Brazil, United State, Canada

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

6. 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')

7. Select the result that would be obtained from the following code: SELECT name FROM bbc WHERE population > ALL (SELECT MAX(population) FROM bbc WHERE region = 'Europe') AND region ='South Asia'

Afghanistan, Bhutan, Nepal, Sri Lanka, The Maldives
Bangladesh, India, Pakistan
China, India
Brazil, Bangladesh, China, India, Indonesia, Pakistan, United States of America
France, Germany, Russia, Turkey

Your score is 0 / 0
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense