Exercise: 6

Create View for Aggregate Country Emissions Temperatures

In this exercise, you will create a view that combines the results of the co2_emissions_by_country table and the aggregate_country_temperatures table. You will perform an LEFT JOIN between these two tables and standardize the country name using the initcap function.

To create the view aggregate_country_emissions_temperatures with the desired columns in the carbon_emissions schema :

1. Perform an INNER JOIN between the co2_emissions_by_country table and the aggregate_country_temperatures table on the "Year" and "Country" columns.
2. Use the initcap function to standardize the country name in the "Country" column.
3. Select the following columns for the output table:
   • YEAR: Integer
   • COUNTRY: String
   • TOTALEMISSIONS: Float
   • PERCAPITAEMISSIONS: Float
   • SHAREOFGLOBALEMISSIONS: Float
   • AVERAGETEMPERATURE: Float

Sample Output

YEAR	COUNTRY	TOTALEMISSIONS	PERCAPITAEMISSIONS	SHAREOFGLOBALEMISSIONS	AVERAGETEMPERATURE
1933	Kyrgyzstan	1.382419	0.929326	0.041577	10.381833333
1962	Kiribati	0.010992	0.255628	0.000113	26.658083333

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