<p class='imper'>List the '''name''' and '''continent''' of countries in the continents containing either '''Argentina''' or '''Australia'''. Order by name of the country.</p>

+

<source lang='sql' class='def'>

<source lang='sql' class='def'>

Line 69:

Line 74:

<source lang='sql' class='ans'>

<source lang='sql' class='ans'>

−

SELECT name FROM world

+

SELECT name,continent

−

WHERE continent='Europe' AND gdp/population >

+

FROM world

−

(SELECT gdp/population FROM world

+

WHERE continent IN (

−

WHERE name='United Kingdom')

+

SELECT continent

+

FROM world

+

WHERE name IN ('Australia','Argentina'))

+

ORDER BY name

</source>

</source>

</div>

</div>

−

+

==Between Canada and Poland==

<div class='qu'>

<div class='qu'>

−

<p class='imper'>Which country has a population that is more than Canada but less than Algeria?</p>

+

<p class='imper'>Which country has a population that is more than Canada but less than Poland? Show the name and the population.</p>

−

<source lang='sql' class='def'>

+

<source lang='sql' class='def'></source>

+

<source lang='sql' class='ans'>

+

SELECT name,population FROM world

+

WHERE population BETWEEN

+

(SELECT population+1 FROM world WHERE name='Canada')

+

AND

+

(SELECT population-1 FROM world WHERE name='Poland')

</source>

</source>

+

</div>

+

+

==Percentages of Germany==

+

<div class='qu'>

+

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

+

<p class='imper'>Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.</p>

+

<div class=hint title='Decimal places'>You can use the function [[ROUND]] to remove the decimal places.</div>

+

<div class=hint title='Percent symbol %'>You can use the function [[CONCAT]] to add the percentage symbol.</div>

+

<source lang='sql' class='def'></source>

<source lang='sql' class='ans'>

<source lang='sql' class='ans'>

−

SELECT name FROM world WHERE

+

SELECT name,

−

population >

+

CONCAT(ROUND(100*population/(SELECT population FROM world WHERE name='Germany')),'%')

−

(SELECT population

+

FROM world

−

FROM world WHERE name='Canada')

+

WHERE continent='Europe'

−

AND

−

population <

−

(SELECT population

−

FROM world WHERE name='Algeria')

</source>

</source>

</div>

</div>

+

{{#ev:youtube|lCbLvHUNBG4}}

Line 101:

Line 121:

To gain an absurdly detailed view of one insignificant feature of the language, read on.

To gain an absurdly detailed view of one insignificant feature of the language, read on.

−

We can use the word <code>ALL</code> to allow >= or > or < or <=to act over a list.

+

We can use the word <code>ALL</code> to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:

+

<source lang=sql>

+

SELECT name

+

FROM world

+

WHERE population >= ALL(SELECT population

+

FROM world

+

WHERE population>0)

+

</source>

+

You need the condition '''population>0''' in the sub-query as some countries have '''null''' for population.

+

==Bigger than every country in Europe==

<div class='qu'>

<div class='qu'>

−

<p class='imper'>Which countries have a GDP greater than any country in Europe? [Give the '''name''' only.] </p>

+

<p class='imper'>Which countries have a GDP greater than every country in Europe? [Give the '''name''' only.] (Some countries may have NULL gdp values) </p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

Line 113:

Line 142:

SELECT name FROM world

SELECT name FROM world

WHERE gdp > ALL

WHERE gdp > ALL

−

(SELECT MAX(gdp) FROM world

+

(SELECT gdp FROM world

WHERE continent = 'Europe'

WHERE continent = 'Europe'

AND gdp IS NOT NULL)

AND gdp IS NOT NULL)

Line 121:

Line 150:

We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.

We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.

+

==Largest in each continent==

<div class='qu'>

<div class='qu'>

−

<p class='imper'>Find the largest country in each continent, show the '''continent''', the '''name''' and the '''population''': </p>

+

<p class='imper'>Find the largest country (by area) in each continent, show the '''continent''', the '''name''' and the '''area''': </p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

Line 133:

Line 163:

<source lang='sql' class='ans'>

<source lang='sql' class='ans'>

−

SELECT continent, name, population FROM world x

+

SELECT continent, name, area

−

WHERE population >= ALL

+

FROM world x

−

(SELECT population FROM world y

+

WHERE area >= ALL

+

(SELECT area FROM world y

WHERE y.continent=x.continent

WHERE y.continent=x.continent

−

AND population>0)

+

and area > 0 )

</source>

</source>

+

+

The above example is known as a '''correlated''' or '''synchronized''' sub-query.

+

<div class='hint' title='Using correlated subqueries'>

+

A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.

+

+

One way to interpret the line in the '''WHERE''' clause that references the two table is ''“… where the correlated values are the same”''.

+

+

In the example provided, you would say ''“select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same”''.

</div>

</div>

+

</div>

+

+

==First country of each continent (alphabetically)==

+

<div class='qu'>

+

<p class='imper'>List each continent and the name of the country that comes first alphabetically.</p>

+

+

<source lang='sql' class='def'>

+

</source>

−

The following questions are very difficult:

+

<source lang='sql' class='ans'>

+

SELECT continent,name FROM world x

+

WHERE x.name <= ALL (

+

SELECT name FROM world y

+

WHERE x.continent=y.continent)

+

</source>

+

</div>

+

==Difficult Questions That Utilize Techniques Not Covered In Prior Sections==

<div class='qu'>

<div class='qu'>

−

<p class='imper'>Find each country that belongs to a continent where all populations are less than 25000000. Show '''name''', '''continent''' and '''population'''. </p>

+

<p class='imper'>Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show '''name''', '''continent''' and '''population'''. </p>

<source lang='sql' class='def'>

<source lang='sql' class='def'>

Line 177:

Line 231:

<div>

<div>

−

<div class="lsclear">Clear your results</div>

<p><div class="quizlink">[[Nested SELECT Quiz]]</div></p>

<p><div class="quizlink">[[Nested SELECT Quiz]]</div></p>

</div>

</div>

Revision as of 14:54, 2 November 2017

This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.

A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.

One way to interpret the line in the WHERE clause that references the two table is “… where the correlated values are the same”.

In the example provided, you would say “select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same”.

First country of each continent (alphabetically)

List each continent and the name of the country that comes first alphabetically.