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.
SELECT DISTINCT cc.NAME, AVG(c.POPULATION) FROM
INNER JOIN COUNTRY cc
ON c.COUNTRYCODE = cc.CODE
WHERE cc.CONTINENT = 'a continent'
ORDER BY cc.CONTINENT;
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`;