Skip to content
Share
Explore
Questions & Answer
Part 1 - Global Situation
a. What was the total forest area (in sq km) of the world in 1990? Please keep in mind that you can use the country record denoted as “World" in the region table.
SQL
SELECT
SUM(forest_area_sqkm)
FROM
forestation
WHERE
year = 1990
AND LOWER(country_name) != 'world';
--------------------------------------OR--------------------------------------------------------
SELECT
SUM(forest_area_sqkm)
FROM
forestation
WHERE
year = 1990
AND LOWER(country_name) = 'world';
b. What was the total forest area (in sq km) of the world in 2016? Please keep in mind that you can use the country record in the table is denoted as “World.”
SELECT
SUM(forest_area_sqkm)
FROM
forestation
WHERE
year = 2016
AND LOWER(country_name) != 'world';
--------------------------------------OR--------------------------------------------------------
SELECT
SUM(forest_area_sqkm)
FROM
forestation
WHERE
year = 2016
AND LOWER(country_name) = 'world';
c. What was the change (in sq km) in the forest area of the world from 1990 to 2016?
WITH fa_1990 AS (
SELECT
SUM(forest_area_sqkm) total_fa_1990
FROM
forestation
WHERE
year = 1990
AND LOWER(country_name) != 'world'
),
fa_2016 AS (
SELECT
SUM(forest_area_sqkm) total_fa_2016
FROM
forestation
WHERE
year = 2016
AND LOWER(country_name) != 'world'
)
SELECT
total_fa_2016 - total_fa_1990 AS fa_change_1990_2016
FROM
fa_2016, fa_1990;
-----------------------------------------OR----------------------------------------------------------------
--same code but using "world" instead of sum of all filtered countries
--the reason why we used sum of all countries is because excluding some null values that counted in world
d. What was the percent change in forest area of the world between 1990 and 2016?
WITH fa_1990 AS (
SELECT
SUM(forest_area_sqkm) total_fa_1990
FROM
forestation
WHERE
year = 1990
AND LOWER(country_name) != 'world'
),
fa_2016 AS (
SELECT
SUM(forest_area_sqkm) total_fa_2016
FROM
forestation
WHERE
year = 2016
AND LOWER(country_name) != 'world'
)
SELECT
((total_fa_2016 - total_fa_1990)/total_fa_1990)*100 AS fa_percent_change_16_90
FROM
fa_2016, fa_1990;
-----------------------------------------OR----------------------------------------------------------------
--same code but using "world" instead of sum of all filtered countries
--the reason why we used sum of all countries is because excluding some null values that counted in world
e. If you compare the amount of forest area lost between 1990 and 2016, to which country's total area in 2016 is it closest to?
WITH loss AS(
WITH fa_1990 AS (
SELECT
SUM(forest_area_sqkm) total_fa_1990
FROM
forestation
WHERE
year = 1990
AND LOWER(country_name) != 'world'
),
fa_2016 AS (
SELECT
SUM(forest_area_sqkm) total_fa_2016
FROM
forestation
WHERE
year = 2016
AND LOWER(country_name) != 'world'
)
SELECT
ABS(total_fa_2016 - total_fa_1990) AS fa_change_16_90
FROM
fa_2016, fa_1990
),
countries AS (
SELECT
f.country_name,
ABS(l.fa_change_16_90 - f.total_area_sq_km) AS differance
--f.total_area_sq_km
FROM
forestation f, loss l
WHERE
year = 2016 AND LOWER(country_name) != 'world'
ORDER BY differance ASC
LIMIT 1
)
SELECT *
FROM countries
-----------------------------------------OR----------------------------------------------------------------
--same code but using "world" instead of sum of all filtered countries
--the reason why we used sum of all countries is because excluding some null values that counted in world
-- if we didn't exclude null values the result would be PERU
Part 2 - Regional Outlook
a. What was the percent forest of the entire world in 2016? Which region had the HIGHEST percent forest in 2016, and which had the LOWEST, to 2 decimal places?
--What was the percent forest of the entire world in 2016
SELECT
*
FROM r_insights
WHERE
year = 2016 AND LOWER(region) = 'world'
--Which region had the HIGHEST percent forest in 2016
SELECT *
FROM r_insights
WHERE
year = 2016
ORDER BY
percent_forest_area DESC
LIMIT 1;
--and which had the LOWEST
SELECT *
FROM r_insights
WHERE
year = 2016
ORDER BY
percent_forest_area ASC
LIMIT 1;
b. What was the percent forest of the entire world in 1990? Which region had the HIGHEST percent forest in 1990, and which had the LOWEST, to 2 decimal places?
--What was the percent forest of the entire world in 1990
SELECT *
FROM r_insights
WHERE
year = 1990 AND LOWER(region) = 'world'
--Which region had the HIGHEST percent forest in 1990
SELECT *
FROM r_insights
WHERE
year = 1990
ORDER BY
percent_forest_area DESC
LIMIT 1;
--and which had the LOWEST
SELECT *
FROM r_insights
WHERE
year = 1990
ORDER BY
percent_forest_area ASC
LIMIT 1;
c. Based on the table you created, which regions of the world DECREASED in forest area from 1990 to 2016?
WITH ri_1990 AS (
SELECT *
FROM r_insights
WHERE
year = 1990
),
ri_2016 AS (
SELECT *
FROM r_insights
WHERE
year = 2016
)
SELECT
ri0.region,
(ri0.percent_forest_area - ri6.percent_forest_area) AS pfa_decrease
FROM
ri_1990 ri0, ri_2016 ri6
WHERE
(ri0.percent_forest_area - ri6.percent_forest_area) > 0
AND ri0.region = ri6.region;
--------------------------------------OR------------------------------------
SELECT
ri_1990.region,
(ri_1990.percent_forest_area - ri_2016.percent_forest_area) AS pfa_decrease
FROM
r_insights ri_1990
JOIN
r_insights ri_2016
ON
ri_1990.region = ri_2016.region
WHERE
ri_1990.year = 1990
AND ri_2016.year = 2016
AND LOWER(ri_1990.region) != 'world'
AND LOWER(ri_2016.region) != 'world'
AND (ri_1990.percent_forest_area - ri_2016.percent_forest_area) > 0;
Part 3 - Country-Level Detail
a. Which 5 countries saw the largest amount decrease in forest area from 1990 to 2016? What was the difference in forest area for each?
SELECT
f90.country_name,
(f90.forest_area_sqkm - f16.forest_area_sqkm) AS fa_difference
FROM
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.