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 https://elephantdolphin.blogspot.com/2021/09/sample-databases-for-learning-mysql-and.html for information on both sample data sets) 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'
, country.name as 'country'
, country.Population as 'country pop'
, round(city.Population / country.Population * 100,2) as 'Percent'
JOIN country on (city.CountryCode = country.Code)
ORDER BY Country.Name, city.name
Hint #1 -- The data has moved in some cases
Hint #2 -- You can use JSON_UNQUOTE(JSON_EXTRACT(...)) or the ->> operator (See https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#function_json-extract and https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-inline-path)
There is a solution below and your solution may be different than what is presented.
SELECT city.name 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'
JOIN countryinfo on (city.CountryCode = countryinfo.doc->>"$._id")
ORDER BY countryinfo.doc->>"$.Name", city.name