This is the final installment of the project! The focus is to write SQL queries that combine data from both Mondial and GapMinder (GM). For most queries you will need both Mondial and GM data, so you may need to use your bridge table(s).
Deliverable D8 (Warm-Up: Mondial Queries). For each (Mondial) country return the minimal,maximal, and average latitude of all cities in that country. The result should be ordered by continent (first) and country (second).
Deliverable D9 (Mondial + GM) Same as D8 but also include the Life Expectancy, Income, and Electricity for each country in the year 2000.
Deliverable D10 (Mondial + GM) For each continent, compute the average income (GDP/capita) in the year 2000 ...
(a) ... by simply averaging over the country income data in the year 2000 (unweighted average), and
(b) ... by taking into account the population of the country (weighted average).
Deliverable D11 (Extra Credit: Skyline Queries)
In the following, your task is to compute the names of countries which are in the skyline of a certain query. Here, a skyline is defined by giving two attributes (e.g. Life Expectancy and Health Spending)and indicating for each whether the min or max is used. Details and examples of expressing skyline queries in SQL will be given in class.
Deliverable D12 (Extra Credit: MyGapMinder)
In a visualization tool of your choosing, display the XY-plot for all years and all (or some) countries ... (a) ... where X is the Year and Y is the Life Expectancy, and
(b) ... where X is the Health Spending and Y is the Life Expectancy
For simplicity, you can “loosely-couple” Postgres with your visualization tool, by exporting the answers of your SQL queries to a file. Deliverable D13 (Extra Credit: EarthMinder) Put the data of the three result attributes from D9 onto a world map. For example, you can create a KML file and then view it using Google Earth.