Monday, September 27, 2021

Dave's Quiz 3 -- Change schemas

     This weeks quiz is a little different.  Many of you grew up with MySQL's sample world database but have not used the world_x database (see for details) and may be shocked to find that there is now a forth table to the database and some of the information is now stored in a JSON data type column. 

    The quiz is to take the following code that works with the world database and convert it to work with the world_x database.

SELECT city.Name as 'City'
    , city.Population as 'city pop'
    , as 'country'
    , country.Population as 'country pop'
    , round(city.Population / country.Population * 100,2) as 'Percent'  
FROM city 
JOIN country on (city.CountryCode = country.Code) 
ORDER BY Country.Name,

    The output should look like this:

Hint #1 -- The data has moved in some cases

Hint #2 -- You can use JSON_UNQUOTE(JSON_EXTRACT(...)) or the ->> operator (See and

There is a solution below and your solution may be different than what is presented.


SELECT as 'City'
,city.Info->>"$.Population" as 'city pop'
,countryinfo.doc->>"$.Name" as 'country'
,countryinfo.doc->>"$.demographics.Population" as 'country pop'
,round(city.Info->>"$.Population" / countryinfo.doc->>"$.demographics.Population" * 100,2) as 'Percent'  
FROM city 
JOIN countryinfo on (city.CountryCode = countryinfo.doc->>"$._id") 
ORDER BY countryinfo.doc->>"$.Name",