Exercise: 7

Create table for Aggregate Country Emissions Temperatures

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

To create the table co2_emissions_and_temperatures_by_country 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. Create COUNTRY_AVGTEMP_BKEY column by combining the COUNTRY ,YEAR ,and AVERAGETEMPERATURE columns using the '||' symbol as a separator.
4. Select the following columns for the output table:
   • COUNTRY_AVGTEMP_BKEY: String
   • YEAR: Integer
   • COUNTRY: String
   • TOTALEMISSIONS: Float
   • PERCAPITAEMISSIONS: Float
   • SHAREOFGLOBALEMISSIONS: Float
   • AVERAGETEMPERATURE: Float

Sample Output

Certainly! Here's the markdown representation of the provided CSV table:

COUNTRY_AVGTEMP_BKEY	YEAR	COUNTRY	TOTALEMISSIONS	PERCAPITAEMISSIONS	SHAREOFGLOBALEMISSIONS	AVERAGETEMPERATURE
Germany||1796||8.64875	1796	Germany	0.534944	0.01397	2.330779	8.64875
Germany||1802||8.988833333	1802	Germany	0.553264	0.02397	1.504134	8.988833333

Please note that the table assumes that the values are separated by commas (,) in the provided CSV data.

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