Skip to content
Share
Explore
Create & Insert
1- create database with name “deforestation”
CREATE DATABASE
deforestation;
2- create table “regions”
CREATE TABLE regions (
country_code CHAR(3) NOT NULL PRIMARY KEY,
country_name VARCHAR(255) NOT NULL,
region VARCHAR(255) NOT NULL,
income_group VARCHAR(255),
CONSTRAINT code_uppercase_chk CHECK (country_code = UPPER(country_code)) -- للتحقق من ان كود الدولة كابيتال
);
3- create table “forest_area”
CREATE TABLE forest_area (
country_code CHAR(3) NOT NULL,
country_name VARCHAR(255) NOT NULL,
year INT NOT NULL,
forest_area_sqkm DECIMAL(15, 4),
PRIMARY KEY (country_code, year),-- لعدم تكرار سنة مع دولة
FOREIGN KEY (country_code) REFERENCES regions(country_code),
CHECK (year BETWEEN 1900 AND 2100),-- للتاكد من ان السنة صحيحة
CHECK (forest_area_sqkm >= 0)-- للتاكد من المساحة ليست رقم سالب
);
4- create table “land_area”
CREATE TABLE land_area (
country_code CHAR(3) NOT NULL,
country_name VARCHAR(255) NOT NULL,
year INT NOT NULL,
total_area_sq_mi DECIMAL(15, 4),
PRIMARY KEY (country_code, year),-- لعدم تكرار سنة مع دولة
FOREIGN KEY (country_code) REFERENCES regions(country_code),
CHECK (year BETWEEN 1900 AND 2100),-- للتاكد من ان السنة صحيحة
CHECK (total_area_sq_mi >= 0)-- للتاكد من المساحة ليست رقم سالب
);
5- Insert data into “regions”
regions.sql
14.7 kB
6- Insert data into “forest_area”
forest_area.sql
257.2 kB
7- Insert data into “land_area”
land_area.sql
254.3 kB
8- Create a View called “forestation” by joining all three tables - forest_area, land_area, and regions.
In the ‘forestation’ View, include the following:
CREATE VIEW forestation AS
SELECT
r.*,
fa.year,
fa.forest_area_sqkm,
la.total_area_sq_mi * 2.59 AS total_area_sq_km, -- convert miles to km
CASE
WHEN la.total_area_sq_mi IS NOT NULL AND la.total_area_sq_mi > 0
THEN ROUND(((fa.forest_area_sqkm/(la.total_area_sq_mi * 2.59)) * 100)::NUMERIC, 2)
ELSE NULL
END AS forest_area_percent
FROM
regions r
LEFT JOIN
land_area la ON r.country_code = la.country_code
LEFT JOIN
forest_area fa ON r.country_code = fa.country_code
WHERE
fa.forest_area_sqkm IS NOT NULL
AND la.total_area_sq_mi IS NOT NULL
AND la.year = fa.year;
Create a table that shows the Regions and their percent forest area (sum of forest area divided by the sum of land area) in 1990 and 2016. (Note that 1 sq mi = 2.59 sq km).
CREATE VIEW r_insights AS
SELECT
region,
year,
ROUND(((SUM(forest_area_sqkm)/SUM(total_area_sq_km))*100) ::NUMERIC,2) AS percent_forest_area
FROM
forestation
WHERE
year = 1990 OR year = 2016
GROUP BY
1, 2;
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.