UPDATE (06/19/17) Please review Using Derived Tables and Nested SELECT to understand more about world x and world y in “Alias” paragraph

New Keywords to learn:

MAX
Returns the largest value

world x and world y

Now this is the second half part where Exercise 4 gets much more complex. Going to do some analysis! The problem states something like this:

Find the largest country (by area) in each continent, show the continent, the name and the area:

SELECT continent, name, area FROM world x
WHERE area >= ALL (SELECT area FROM world y WHERE y.continent=x.continent
  AND area > 0)

I am wondering if this is considered as multiple tables or still one table, and didn’t know there were this many continents too. Two possibly different explanations for this world x and world y:

• world x to search through attributes, and world y to search through tuples?
• table just duplicated using x and y to compare each other?

Let’s focus on what I understand so far.

• ALL means each or every
• world x filters for area larger than ALL area in world y continents
• WHERE in world y filters through and inside continents, and that y.continent and x.continent are “merged” attributes and tuples?

I assume that other problems are going to be about world x and world y.

Exploring more of world x and world y problems!

The next problem states: List each continent and the name of the country that comes first alphabetically.

SELECT continent, name FROM world x
WHERE name <= ALL(SELECT name FROM world y WHERE y.continent = x.continent)

It appears as if operators >= and or <= can set attribute in either ascending or descending order. I am not sure if that is the case. This part will need an UPDATE later on! I will return to either correct, or confirm it.

NEED A UPDATE LATER ON! How does Operator >= or <= cause name to sort in order?

Okay! Moving on to the next problem!

The problem #9 asks for:

• Find the continents where all countries have a population <= 25000000
• Find the names of the countries associated with these continents
• Show name, continent and population

SELECT name, continent, population FROM world x
WHERE 25000000 > ALL (SELECT population FROM world y WHERE x.continent = y.continent AND y.population > 0)

Whew! This problem is easier to solve. Draw data on name, continent, population using world x table, easy! Filtering for populations with the size of 25000000 or less from every continents. It will return only continents that have ALL countries with population less than 25,000,000. I am still having hard time to understand this x table and y table concept. I decide to learn more about database so I can return to explain this better.

Ugh, another tough problem encountered. The problem #10 asks for:

• Some countries have populations more than three times that of any of their neighbors (in the same continent)
• Give the countries and continents

SELECT name, continent FROM world x
WHERE

The first step SELECT is always easy!

SELECT name, continent FROM world x
WHERE  population > ALL(SELECT population FROM world y WHERE x.continent = y.continent AND population > 0)

Didn’t work! I tried to figure out why and I found the answer online. This isn’t taught in SQLZOO, it can be unfair sometime. More on this was hidden in a hyperlink inside “SELECT within SELECT” on SQLZOO. More information can be found in Using Derived Tables and Nested SELECT. It is called aliasing

The answer looks like this:

SELECT name, continent FROM world x
WHERE population > ALL(SELECT population*3 FROM world y WHERE x.continent = y.continent AND population > 0 AND y.name != x.name)

I would have figured the population*3 part, just not y.name != x.name. Makes sense, operator > or < wouldn’t be able to compare if there are two exact same data from “two” tables.

SELECT QUIZ PART!

Hope I will understand world x and world y better through this quiz multiple choices!

I would like to note that this Quiz covers a slightly different table. The table is called “bbc”, and it uses ‘region’ instead of ‘continent’. The table looks likes this below:

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

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

Process goes like this:

• outer query gathers regions, populations, and countries that are less or equals than ALL of inner query results
• inner query gathers all populations on every regions
• smallest population is gathered from each region

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 x.region=y.region AND y.population>0)

3) Select the code that shows the countries with a less than 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)

• outer query gather countries, regions from correlation name bbc x where population is less than ALL inner query results
• inner query gather population that are reduced to a third from every region and as long as country from bbc x and country from bbc y doesn’t compare with each other i.e. (y.name != x.name)
• note that it ISN’T less or equals

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 name = 'United Kingdom')

5) Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values)

SELECT name, gdp FROM bbc x
WHERE gdp > (SELECT MAX(gdp) FROM bbc y WHERE region = 'Africa')

• outer query gather name and gdp in world that are greater than..
• inner query gather the largest gdp from Africa

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='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'