mysql> use meteob;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1.Υπολογισμός της μέσης θερμοκρασίας ανά μήνα του 2017 ταξινομημένες ανά μήνα

mysql> select MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) as AvgTemperature from METEO
where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by Month order by Month;

+-------+----------------+
| Month | AvgTemperature |
+-------+----------------+
|     1 |       2.362466 |
|     2 |       7.873496 |
|     3 |      11.840677 |
|     4 |      14.992255 |
|     5 |      19.684537 |
|     6 |      25.858310 |
|     7 |      28.634882 |
|     8 |      28.850474 |
|     9 |      20.895184 |
|    10 |      15.274819 |
|    11 |       9.610324 |
|    12 |       6.903834 |
+-------+----------------+
12 rows in set (0,77 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2. Υπολογισμός της μέσης θερμοκρασίας ανά μήνα του 2017 ταξινομημένες ανά θερμοκρασία σε αύξουσα σειρά

mysql> select MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) as AvgTemperature from METEO
where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by Month order by AvgTemperature ASC;

+-------+----------------+
| Month | AvgTemperature |
+-------+----------------+
|     1 |       2.362466 |
|    12 |       6.903834 |
|     2 |       7.873496 |
|    11 |       9.610324 |
|     3 |      11.840677 |
|     4 |      14.992255 |
|    10 |      15.274819 |
|     5 |      19.684537 |
|     9 |      20.895184 |
|     6 |      25.858310 |
|     7 |      28.634882 |
|     8 |      28.850474 |
+-------+----------------+
12 rows in set (0,76 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3. Υπολογισμός της μέγιστης, μέσης, ελάχιστης θερμοκρασίας ανά μήνα του 2017 ταξινομημένες ανά μήνα

mysql> select MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, MAX(CAST(TEMPERATURE AS DECIMAL(5,2))) as maxTemperature
, AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) as AvgTemperature,
MIN(CAST(TEMPERATURE AS DECIMAL(5,2))) as minTemperature from METEO 
where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by Month order by Month;

+-------+----------------+----------------+----------------+
| Month | maxTemperature | AvgTemperature | minTemperature |
+-------+----------------+----------------+----------------+
|     1 |          13.36 |       2.362466 |          -9.60 |
|     2 |          23.80 |       7.873496 |          -1.96 |
|     3 |          26.70 |      11.840677 |           1.96 |
|     4 |          36.17 |      14.992255 |           1.40 |
|     5 |          36.73 |      19.684537 |           8.50 |
|     6 |          44.76 |      25.858310 |          12.61 |
|     7 |          46.82 |      28.634882 |          14.67 |
|     8 |          47.38 |      28.850474 |          15.60 |
|     9 |          42.15 |      20.895184 |           8.50 |
|    10 |          33.55 |      15.274819 |           3.27 |
|    11 |          20.90 |       9.610324 |          -1.58 |
|    12 |          14.86 |       6.903834 |          -3.80 |
+-------+----------------+----------------+----------------+
12 rows in set (1,04 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4. Υπολογισμός του πλήθους των τιμών θερμοκρασίας που είναι πάνω από το 25% της μέσης θερμοκρασίας του μήνα ανά μήνα.

mysql> select count(CAST(TEMPERATURE AS DECIMAL(5,2))) as temperature,month(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month from METEO
 where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 group by Month having temperature>0.25*avg(temperature) order by Month;
+-------------+-------+
| temperature | Month |
+-------------+-------+
|       30394 |     1 |
|       28987 |     2 |
|       27081 |     3 |
|       40098 |     4 |
|       44806 |     5 |
|       36749 |     6 |
|       36879 |     7 |
|       35329 |     8 |
|       33471 |     9 |
|       23076 |    10 |
|       16678 |    11 |
|       34275 |    12 |
+-------------+-------+
12 rows in set (0,74 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
5.Υπολογισμός της μέσης τιμής της θερμοκρασίας που είναι πάνω από 35οC και κάτω από  0oC  του μήνα ανά μήνα.

mysql> select MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) as AvgTemperature 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;
+-------+----------------+
| Month | AvgTemperature |
+-------+----------------+
|     1 |      -3.763643 |
|     2 |      -1.544327 |
|     4 |      35.673125 |
|     5 |      35.582745 |
|     6 |      38.111191 |
|     7 |      39.071827 |
|     8 |      38.770716 |
|     9 |      37.497500 |
|    11 |      -1.339889 |
|    12 |      -2.089901 |
+-------+----------------+
10 rows in set (0,48 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6. Υπολογισμός του πλήθους των διακριτών τιμών θερμοκρασίας για όλο το έτος

mysql> select COUNT(distinct CAST(TEMPERATURE AS DECIMAL(5,2))) as DistinctTemperacture from METEO where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017;
+----------------------+
| DistinctTemperacture |
+----------------------+
|                  299 |
+----------------------+
1 row in set (0,49 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
7. Υπολογισμός της μέσης τιμής της θερμοκρασίας ανά μήνα όταν η υγρασία είναι κάτω του 50% και η ταχύτητα ανέμου πάνω από 20Km/h 

mysql> select MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) as AvgTemperature from METEO 
where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017 and CAST(HUMIDITY AS UNSIGNED INT) <50 and CAST(WIND_COUNT AS DECIMAL(5,2)) > 20 group by Month;
+-------+----------------+
| Month | AvgTemperature |
+-------+----------------+
|     1 |       7.616818 |
|     2 |      11.973602 |
|     3 |      11.479983 |
|     4 |      11.864968 |
|     5 |      13.471188 |
|     6 |      15.610000 |
|     7 |      15.232857 |
|     9 |      12.990000 |
|    10 |      11.622513 |
|    11 |      12.043245 |
|    12 |       8.669366 |
+-------+----------------+
11 rows in set, 65535 warnings (0,56 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
8. Υπολογισμός του πλήθους των διακριτών τιμών για την κατεύθυνση του ανέμου

mysql> select count(distinct WIND_DIRECTION) as DistinctWindDirection from METEO  where YEAR(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) = 2017;
+-----------------------+
| DistinctWindDirection |
+-----------------------+
|                   214 |
+-----------------------+
1 row in set (0,57 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
9. Υπολογισμός μέγιστης τιμής βροχόπτωσης (mm) ανά μήνα για όλο το έτος, εφόσον έχει βρέξει, ταξινομημένη ως προς τη μέγιστη τιμή βροχόπτωσης     

mysql> select  MONTH(STR_TO_DATE(MDATE, '%Y-%m-%d %H:%i:%s')) as Month, MAX(CAST(RAIN_COUNT AS DECIMAL(5,2))) as MaxRain 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 MaxRain DESC;
+-------+---------+
| Month | MaxRain |
+-------+---------+
|    10 |   85.50 |
|     3 |   67.61 |
|     1 |   53.64 |
|     6 |   22.72 |
|     2 |   22.35 |
|    12 |   15.93 |
|    11 |   15.65 |
|     4 |   12.85 |
|     5 |    4.47 |
|     7 |    4.19 |
|     9 |    3.91 |
|     8 |    1.68 |
+-------+---------+
12 rows in set (0,37 sec)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10. Εμφάνιση όλων των λάθος τιμών της υγρασίας (>0%) (>99%) και των διακριτών αυτών τιμών

mysql> select distinct CAST(HUMIDITY AS UNSIGNED INT) as InvalidHumidity from METEO where CAST(HUMIDITY AS UNSIGNED INT) < 0 or CAST(HUMIDITY AS UNSIGNED INT) >99;
Empty set, 65535 warnings (0,45 sec)