+---------------+---------+
|Column Name |Type|+---------------+---------+
| country_id | int || country_name | varchar |+---------------+---------+
country_id is the primarykey (columnwithuniquevalues) for this table.
Eachrowof this tablecontains the ID and the name of one country.
Table: Weather
1
2
3
4
5
6
7
8
9
+---------------+------+
|Column Name |Type|+---------------+------+
| country_id | int || weather_state | int ||day| date |+---------------+------+
(country_id, day) is the primarykey (combination of columns withuniquevalues) for this table.
Eachrowof this table indicates the weather statein a country for one day.
Write a solution to find the type of weather in each country for November 2019.
The type of weather is:
Cold if the average weather_state is less than or equal 15,
Hot if the average weather_state is greater than or equal to 25, and
Input:
Countries table:+------------+--------------+| country_id | country_name |+------------+--------------+|2| USA ||3| Australia ||7| Peru ||5| China ||8| Morocco ||9| Spain |+------------+--------------+Weather table:+------------+---------------+------------+| country_id | weather_state | day |+------------+---------------+------------+|2|15|2019-11-01||2|12|2019-10-28||2|12|2019-10-27||3|-2|2019-11-10||3|0|2019-11-11||3|3|2019-11-12||5|16|2019-11-07||5|18|2019-11-09||5|21|2019-11-23||7|25|2019-11-28||7|22|2019-12-01||7|20|2019-12-02||8|25|2019-11-05||8|27|2019-11-15||8|31|2019-11-25||9|7|2019-10-23||9|3|2019-12-23|+------------+---------------+------------+Output:
+--------------+--------------+| country_name | weather_type |+--------------+--------------+| USA | Cold || Australia | Cold || Peru | Hot || Morocco | Hot || China | Warm |+--------------+--------------+Explanation:
Average weather_state in USA in November is(15)/1=15 so weather type is Cold.Average weather_state in Austraila in November is(-2+0+3)/3=0.333 so weather type is Cold.Average weather_state in Peru in November is(25)/1=25 so the weather type is Hot.Average weather_state in China in November is(16+18+21)/3=18.333 so weather type is Warm.Average weather_state in Morocco in November is(25+27+31)/3=27.667 so weather type is Hot.We know nothing about the average weather_state in Spain in November so we do not include it in the result table.