SELECT within SELECT Tutorial
- 1.Bigger than Russia
- 2.Richer than UK
- 3.Neighbours of Argentina and Australia
- 4.Between Canada and Poland
- 5.Percentages of Germany
- 6.Bigger than every country in Europe
- 7.Largest in each continent
- 8.First country of each continent (alphabetically)
- 9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections
- 10.
1.Bigger than Russia
SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name=‘Russia’)
2.Richer than UK
SELECT name FROM world WHERE continent = ‘Europe’ and gdp/population >
(SELECT gdp/population FROM world WHERE name = ‘United Kingdom’)
3.Neighbours of Argentina and Australia
SELECT name, continent FROM world WHERE continent in
(SELECT continent FROM world WHERE name in (‘Argentina’, ‘Australia’))
ORDER BY name
4.Between Canada and Poland
SELECT name, population FROM world WHERE population >
(SELECT population FROM world WHERE name = ‘Canada’)
AND population <
(SELECT population FROM world WHERE name = ‘Poland’)
5.Percentages of Germany
SELECT name,
concat(ROUND(population/(SELECT population FROM world WHERE name = ‘Germany’)*100, 0), ‘%’) as percentage
FROM world WHERE continent = ‘Europe’
6.Bigger than every country in Europe
SELECT name FROM world
WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp>0 AND continent = ‘Europe’)
7.Largest in each continent
SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
WHERE y.continent=x.continent
AND area>0)
8.First country of each continent (alphabetically)
SELECT continent, name FROM world a WHERE name =
(SELECT name FROM world b
WHERE b.continent = a.continent ORDER BY name LIMIT 1)
9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections
SELECT name, continent, population FROM world
WHERE continent IN
(SELECT continent FROM world a
WHERE population >= ALL(SELECT population FROM world b where b.continent = a.continent AND population > 0)
AND population <= 25000000)
optimised:
SELECT name, continent, population FROM world x
WHERE 25000000>=ALL (SELECT population FROM world y
WHERE x.continent=y.continent
AND population>0)
10.
SELECT name, continent FROM world a
WHERE population/3 > ALL(SELECT population FROM world b
WHERE a.continent = b.continent AND a.name <> b.name AND population > 0)