Hossam Hossam - 5 months ago 17
SQL Question

Basic Join mysql

I've been working on this basic inner join for a little bit now, but I can't seem to get it to work. The question is:


Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.


The following SQL will query the average city populations of one continent but I can't figure out how to generalize it.

SELECT DISTINCT cc.NAME, AVG(c.POPULATION) FROM
CITY c
INNER JOIN COUNTRY cc
ON c.COUNTRYCODE = cc.CODE
WHERE cc.CONTINENT = 'a continent'
ORDER BY cc.CONTINENT;


The City Table
The Country Table

Answer
SELECT cc.CONTINENT, cc.NAME, FLOOR(AVG(c.POPULATION)) AS population
FROM CITY c
INNER JOIN COUNTRY cc ON c.COUNTRYCODE = cc.CODE
GROUP BY cc.NAME
ORDER BY cc.CONTINENT, cc.NAME;

Should work. I prefer to encode names etc. (but that's up to you and own personal preference):

SELECT `cc`.`CONTINENT`, `cc`.`NAME`, FLOOR(AVG(`c`.`POPULATION`)) AS `population`
FROM CITY `c`
INNER JOIN COUNTRY `cc` ON `c`.`COUNTRYCODE` = `cc`.`CODE`
GROUP BY `cc`.`NAME`
ORDER BY `cc`.`CONTINENT`, `cc`.`NAME`;
Comments