-- 1.
SELECT
	MONTH(MDATE) AS Month,
	AVG(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS AverageTemperature
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
GROUP BY
	MONTH(MDATE)
ORDER BY
	MONTH(MDATE);

--2.
SELECT
	MONTH(MDATE) AS Month,
	AVG(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS AverageTemperature
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
GROUP BY
	MONTH(MDATE)
ORDER BY
	AverageTemperature ASC;

--3.
SELECT
	MONTH(MDATE) AS Month,
	MAX(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS MaxTemperature,
	AVG(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS AverageTemperature,
	MIN(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS MinTemperature
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
GROUP BY
	MONTH(MDATE)
ORDER BY
	MONTH(MDATE);

--4.
SELECT
	MONTH(MDATE) AS Month,
	COUNT(*) AS Above25PercentOfAvgTemperature
FROM
	meteob AS m
JOIN
	(SELECT
		MONTH(MDATE) AS Month,
		AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) * 1.25 AS AverageTemperature25Percent
	FROM
		meteob
	WHERE
		YEAR(MDATE) = 2017
	GROUP BY
		MONTH(MDATE)) AAS ang_temp
ON
	MONTH(m.MDATE) = ang_temp.Month
WHERE
	CAST(TEMPERATURE AS DECIMAL(5,2)) > avg_temp.AverageTemperature25Percent
GROUP BY
	MONTH(m.MDATE)
ORDER BY
	MONTH(MDATE);

--5.
SELECT
	MONTH(MDATE) AS Month,
	AVG(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS AvgTemperature
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
	AND (CAST(TEMPERATURE AS DECIMAL(5, 2)) > 35 OR CAST(TEMPERATURE AS DECIMAL(5, 2)) < 0)
GROUP BY
	MONTH(MDATE)
OREDR BY
	MONTH(MDATE);

--6.
SELECT
	COUNT(DISTINCT CAST(TEMPERATURE AS DECIMAL(5, 2))) AS DistinctTemperatureValues
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017;

--7.
SELECT
	MONTH(MDATE) AS Month,
	AVG(CAST(TEMPERATURE AS DECIMAL(5, 2))) AS AverageTemperature
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
	AND CAST(HUMIDITY AS UNSIGNED) < 50
	AND CAST(WIND_COUNT AS UNSIGNED) > 20
GROUP BY
	MONTH(MDATE)
ORDER BY
	MONTH(MDATE);

--8.
SELECT
	COUNT(DISTINCT WIND_DIRECTION) AS DistinctWindDirections
FROM
	meteob;

--9.
SELECT
	MONTH(MDATE) AS Month,
	MAX(CAST(RAIN_COUNT AS DECIMAL(5, 2))) AS MaxPrecipitation
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
	AND CAST(RAIN_COUNT AS DECIMAL(5, 2)) > 0
GROUP BY
	MONTH(MDATE)
ORDER BY
	MaxPrecipitation DESC;

--10.
SELECT
	DISTINCT CAST(HUMIDITY AS UNSIGNED) AS IncorrectHumidityValues
FROM
	meteob
WHERE
	YEAR(MDATE) = 2017
	AND (CAST(HUMIDITY AS UNSIGNED) < 1 OR CAST(HUMIDITY AS UNSIGNED) > 99);
