mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |o
| ma12477            |
| meteob             |
+--------------------+
3 rows in set (0,00 sec)

mysql> use meteob;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> SELECT MONTH(MDATE),AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO WHERE YEAR(MDATE) = 2017 GROUP BY MONTH(MDATE) ORDER BY MONTH(MDATE);
+--------------+----------------------------------------+
| MONTH(MDATE) | AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) |
+--------------+----------------------------------------+
|            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,50 sec)


mysql> SELECT MONTH(MDATE),AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO WHERE YEAR(MDATE)=2017 GROUP BY MONTH(MDATE) ORDER BY AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) ;
+--------------+----------------------------------------+
| MONTH(MDATE) | AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) |
+--------------+----------------------------------------+
|            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,49 sec)


mysql> mysql> SELECT MONTH(MDATE), MAX(CAST(TEMPERATURE AS DECIMAL(5,2))) , T(TEMPERATURE AS DECIMAL(5,2))), AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO WHERE YEAR(MDATE) = 2017 GROUP BY MONTH(MDATE) ORDER BY MONTH(MDATE);
+--------------+----------------------------------------+----------------------------------------+----------------------------------------+
| MONTH(MDATE) | MAX(CAST(TEMPERATURE AS DECIMAL(5,2))) | MIN(CAST(TEMPERATURE AS DECIMAL(5,2))) | AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) |
+--------------+----------------------------------------+----------------------------------------+----------------------------------------+
|            1 |                                  13.36 |                                  -9.60 |                               2.362466 |
|            2 |                                  23.80 |                                  -1.96 |                               7.873496 |
|            3 |                                  26.70 |                                   1.96 |                              11.840677 |
|            4 |                                  36.17 |                                   1.40 |                              14.992255 |
|            5 |                                  36.73 |                                   8.50 |                              19.684537 |
|            6 |                                  44.76 |                                  12.61 |                              25.858310 |
|            7 |                                  46.82 |                                  14.67 |                              28.634882 |
|            8 |                                  47.38 |                                  15.60 |                              28.850474 |
|            9 |                                  42.15 |                                   8.50 |                              20.895184 |
|           10 |                                  33.55 |                                   3.27 |                              15.274819 |
|           11 |                                  20.90 |                                  -1.58 |                               9.610324 |
|           12 |                                  14.86 |                                  -3.80 |                               6.903834 |
+--------------+----------------------------------------+----------------------------------------+----------------------------------------+
12 rows in set (0,71 sec)


mysql> SELECT MONTH(m.MDATE) AS M,
        ->     COUNT(*) AS CNT
        -> FROM METEO m
        -> INNER JOIN (
        ->     SELECT
        ->         MONTH(MDATE) AS mon
        ->         AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) AS AVGTEMP
        ->     FROM METEO
        -> GROUP BY MONTH(MDATE)
        -> ) AS STATS ON MONTH(m.MDATE) = STATS.mon
        -> WHERE
        ->CAST(m.TEMPERATURE AS DECIMAL(5,2)) > (0.25 * STATS.AVGTEMP)
        -> GROUP BY MONTH(m.MDATE);
        +------+-------+
        | M    | CNT   |
        +------+-------+
        |    1 | 21970 |
        |    2 | 25513 |
        |    3 | 26704 |
        |    4 | 39224 |
        |    5 | 44806 |
        |    6 | 36749 |
        |    7 | 36879 |
        |    8 | 35329 |
        |    9 | 33471 |
        |   10 | 23026 |
        |   11 | 15531 |
        |   12 | 30030 |
        +------+-------+
        12 rows in set (1,25 sec)

mysql>  SELECT MONTH(MDATE) , AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO WHERE CAST(TEMPERATURE AS DECIMAL(5,2)) >35 OR CAST(TEMPERATURE AS DECIMAL(5,2)) < 0 GROUP BY MONTH(MDATE);
+--------------+----------------------------------------+
| MONTH(MDATE) | AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) |
+--------------+----------------------------------------+
|            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,30 sec)



mysql> SELECT COUNT(DISTINCT CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO ;
+---------------------------------------------------+
| COUNT(DISTINCT CAST(TEMPERATURE AS DECIMAL(5,2))) |
+---------------------------------------------------+
|                                               299 |
+---------------------------------------------------+
1 row in set (0,31 sec)

mysql> SELECT MONTH(MDATE) , AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) FROM METEO WHERE CAST(HUMIDITY AS UNSIGNED) <50 AND CAST(WIND_COUNT AS DECIMAL(5,2)) > 20 GROUP BY MONTH(MDATE);
+--------------+----------------------------------------+
| MONTH(MDATE) | AVG(CAST(TEMPERATURE AS DECIMAL(5,2))) |
+--------------+----------------------------------------+
|            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,38 sec)



mysql> SELECT COUNT(DISTINCT WIND_DIRECTION) FROM METEO;
+--------------------------------+
| COUNT(DISTINCT WIND_DIRECTION) |
+--------------------------------+
|                            214 |
+--------------------------------+
1 row in set (0,42 sec)

mysql> SELECT MONTH(MDATE) , MAX(CAST(RAIN_COUNT AS DECIMAL(5,2))) FROM METEO WHERE CAST(RAIN_COUNT AS DECIMAL (5,2)) > 0 GROUP BY MONTH(MDATE) ORDER BY MONTH(MDATE);
+--------------+---------------------------------------+
| MONTH(MDATE) | MAX(CAST(RAIN_COUNT AS DECIMAL(5,2))) |
+--------------+---------------------------------------+
|            1 |                                 53.64 |
|            2 |                                 22.35 |
|            3 |                                 67.61 |
|            4 |                                 12.85 |
|            5 |                                  4.47 |
|            6 |                                 22.72 |
|            7 |                                  4.19 |
|            8 |                                  1.68 |
|            9 |                                  3.91 |
|           10 |                                 85.50 |
|           11 |                                 15.65 |
|           12 |                                 15.93 |
+--------------+---------------------------------------+
12 rows in set (0,22 sec)



mysql> SELECT MDATE,HUMIDITY FROM METEO WHERE CAST(HUMIDITY AS SIGNED)>100 OR CAST(HUMIDITY AS SIGNED) <0;
Empty set, 65535 warnings (0,44 sec)



