[Feedback] World Populations SQL Practice II

Hi guys!

Im new to Codeacademy! Here’s the result of my first practice project hope you guys can give me some feedback!

-- Query to See the Main Databases
SELECT *
FROM countries
LIMIT 10;

SELECT *
FROM population_years
LIMIT 10;


-- How many entries in the database are from Africa?
    SELECT COUNT(*)
    FROM countries
    WHERE continent LIKE 'Africa';

    -- 56 entries are from the continent Africa


-- What was the total population of Oceania in 2005?
    SELECT *
    FROM countries
    WHERE continent LIKE 'Oceania';

    -- Query to find Total Population of Continent per year
    WITH country AS
    (
    SELECT
        id,
        name,
        continent
    FROM countries
    ),
    left_join AS
    (
    SELECT *
    FROM country
    LEFT JOIN population_years
    ON country.id = population_years.country_id
    )
    SELECT 
        continent,
        year,
        ROUND(SUM(population),2) AS 'total population (in millions)'
    FROM left_join
    WHERE 
        continent = 'Oceania'
        AND
        year = 2005;

    -- Oceania had a total population of 32.66 Million People in 2005.


-- What is the average population of countries in South America in 2003?
    WITH country AS
    (
    SELECT
        id,
        name,
        continent
    FROM countries
    ),
    left_join AS
    (
    SELECT *
    FROM country
    LEFT JOIN population_years
    ON country.id = population_years.country_id
    )
    SELECT 
        continent,
        year,
        ROUND(AVG(population),2) AS 'Average Population (in millions)'
    FROM left_join
    WHERE 
        continent = 'South America'
    GROUP BY 2;

    -- The average population of Countries in South America in 2003 is 25.89 Million



-- What country had the smallest population in 2007?
 WITH country AS
    (
    SELECT
        id,
        name,
        continent
    FROM countries
    ),
    left_join AS
    (
    SELECT *
    FROM country
    LEFT JOIN population_years
    ON country.id = population_years.country_id
    )
    SELECT 
        name,
        year,
        population AS 'Population (in millions)'
    FROM left_join 
    WHERE population IS NOT NULL AND year = 2007
    GROUP BY 3;

    -- Niue had the smallest population in 2007.


-- What is the average population of Poland during the time period covered by this dataset?

     WITH country AS
    (
    SELECT
        id,
        name,
        continent
    FROM countries
    ),
    left_join AS
    (
    SELECT *
    FROM country
    LEFT JOIN population_years
    ON country.id = population_years.country_id
    )
    SELECT 
        name,
        ROUND(AVG(population),2) AS 'Average Population (in millions)'
    FROM left_join
    WHERE 
        name = 'Poland';

    -- The average population of Poland during the time period covered by this data set is set at an average of 38.56 Million.

-- How many countries have the word "The" in their name?
    SELECT COUNT(*)
    FROM countries
    WHERE name LIKE '% The%';

    -- 2 countries have the word "The" in their name.


-- What was the total population of each continent in 2010?

    WITH country AS
    (
    SELECT
        id,
        name,
        continent
    FROM countries
    ),
    left_join AS
    (
    SELECT *
    FROM country
    LEFT JOIN population_years
    ON country.id = population_years.country_id
    )
    SELECT 
        continent,
        year,
        ROUND(SUM(population),2) AS 'Total Population (in millions)'
    FROM left_join
    WHERE 
        year = 2010
    GROUP BY 1
    ORDER BY 3 DESC;

    -- Asia             - 4,133.09  Million
    -- Africa           - 1,015.48  Million
    -- Europe           - 723.06    Million
    -- North America    - 539.79    Million
    -- South America    - 396.58    Million
    -- Oceania          - 34.96     Million