Exercise 1

Create Table for Country Emissions

To create a table called co2_emissions_by_country in the carbon_emissions schema, you will use the data from the STG_EMISSIONS_BY_COUNTRY view to analyze and populate the table.

And TotalEmission column is same as Annual_CO2_emissions column from STG_EMISSIONS_BY_COUNTRY view

Your output table should contain:

• YEAR: integer
• COUNTRY: string
• TOTALEMISSIONS: float
• PERCAPITAEMISSIONS: float
• SHAREOFGLOBALEMISSIONS: float

Sample Output

YEAR	COUNTRY	TOTALEMISSIONS	PERCAPITAEMISSIONS	SHAREOFGLOBALEMISSIONS
1978	Suriname	2.391861	6.681176	0.012616
1979	Suriname	2.292768	6.38654	0.011779

tip

To handle null values and convert a column to an integer using COALESCE or NULLIF and CAST, use the following expression:

CAST(COALESCE(column_name, 0) AS INTEGER) AS new_column

This will replace null values in the column_name with 0 before casting it to an integer. Adjust the column name and replacement value as needed in your specific case.

note

In the SQL statement, the use of double quotes around the YEAR column name is necessary because it is a reserved keyword in Snowflake.

select "YEAR" FROM TABLE