use database meteob;

--Medium Temperature order by month
select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month, avg(cast(TEMPERATURE as decimal(5,2))) as med_temp from METEO 
	year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by month, order by month asc;

--Medium Temperature order by medium temperature
select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month, avg(cast(TEMPERATURE as decimal(5,2))) as med_temp from METEO 
	year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by month, order by med_temp asc;

--Minimum, Maximum and Medium Temperature order by month
select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month, 
	max(cast(TEMPERATURE as decimal(5,2))) as max_temp,
	avg(cast(TEMPERATURE as decimal(5,2))) as med_temp, 
	min(cast(TEMPERATURE as decimal(5,2))) as min_temp,
	from METEO 
	where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by month, order by month asc;

--Temperature values <25% of medium temperature
select  T1.month;
	count(*) as temp_val
from
	(select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month, cast(TEMPERATURE as decimal(5,2)) as temp
	from METEO where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017) as T1
join
	(select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month, avg(cast(TEMPERATURE as decimal(5,2))) as avg_temp
	from METEO where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by month) as T2
on T1.month = T2.month
where T1.temp > (0.25 * T2.avg_temp)
group by T1.month
order by T1.month asc;

--Medium Temperature for integers >35 or <0
select  month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month,
	avg(cast(TEMPERATURE as decimal(5,2))) as med_temp
	from METEO
	where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017
	and (cast(TEMPERATURE as decimal(5,2)) > 35 or cast(TEMPERATURE as decimal(5,2)) < 0)
	group by month;
	order by month asc;

--Integer-type Temperature Values
select count(distinct cast(TEMPERATURE as decimal(5,2))) int_temps
from METEO
where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017;

--Medium Temperature per month with humidity <50% and wind count<20
select	month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month,
	avg(cast(TEMPERATURE as decimal(5,2))) as med_temp
	from METEO
	where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017
	and cast(HUMIDITY as unsigned) < 50
	and cast(WIND_COUNT as decimal(5,2)) > 20
	group by month
	order by month asc;

--Integer wind count values
select  count(distinct WIND_DIRECTION) as dir_val
	from METEO
	where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017;
 
--Maximum rain count per month
select month(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) as month,
	max(cast(RAIN_COUNT as decimal(5,2))) as max_rain
	from METEO
	where year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017
	and cast(RAIN_COUNT as decimal(5,2)) > 0
	group by month
	order by max_rain asc;

--Wrong Humidity Values

select distinct HUMIDITY 
from METEO
where cast(HUMIDITY as unsigned) < 0
	or cast(HUMIDITY as unsigned) > 100
	and year(str_to_date(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017;
